突然得到ORA-12514错误oracle11gr2

Question

我安装了Oracle11g r2 fine，有2个数据库(se3和mydb，都有db_domain .orcl，即mydb.orcl和se3.orcl)一直运行到昨天。但是突然我得到了ORA-12514错误(TNS:侦听器不知道连接描述符中请求的服务)。

我的tnsnames.ora文件：

# tnsnames.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_MYDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb.orcl)
    )
  )

SE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = se3.orcl)
    )
  )

LISTENER_SE3 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER.ORA FILE:
# listener.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = G:\oracledb

我可以“我的数据库/SE3”很好..也试图更改tnsnames.ora和listener.ora文件(现在已恢复)，但无法通过sqlplus或sqldeveloper/visual studio连接到其中任何一个文件。请帮帮我..。谢谢!

Answer 1

根据您自己的评论"im能够设置ORACLE_SID，但不能通过sqlplus . ORA-01034 - Oracle不可用“连接，很可能您的数据库实例已经关闭。理论上可以让底层的Windows在进程中运行，并在进程中运行相应的oracle.exe，但是数据库实例正在关闭。最好的方法是在服务器上查找包含alert_mydb.log的跟踪目录，并检查最后的条目。

如果数据库根本没有运行，并且警报日志显示没有严重错误，请按如下方式启动：

set ORACLE_SID=MYDB
set ORACLE_HOME=D:\my\oracle\home

%ORACLE_HOME%\bin\sqlplus "/ as sysdba"
ORACLE not available.
SQL> STARTUP 
SQL> EXIT

lsnrctl services

最后一个命令应该显示MYDB已经注册。继续进行ORACLE_SID=SE3

Answer 2

在我的示例中，当我试图通过SQL*Plus从只使用客户端的计算机连接到远程数据库时，收到了ORA-12514错误：

C:\Temp>sqlplus username@connect_descriptor

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 24 09:43:04 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

我对此感到困惑，因为tnsping成功地做出了回应：

C:\Temp>tnsping connect_descriptor

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 24-SEP-2
014 09:48:04

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\Oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host
.intranet.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
HOST13)))
OK (20 msec)

最后，我在tnsnames.ora文件中将问题作为一个不正确的SERVICE_NAME值进行了跟踪：

CONNECT_DESCRIPTOR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.intranet.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HOST13)
    )
  )

在将HOST13 (通过普通文本编辑器)更改为SERVICE_NAME的正确值之后，我能够连接到远程Oracle数据库。

在我的Oracle11.2客户机上，下面是tnsnames.ora的位置

C:\Oracle\product\11.2.0\client_1\network\admin\tnsnames.ora