突然得到ORA-12514错误的Oracle 11gR2的(Suddenly getting ORA

我安装的Oracle 11g R2细，有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

我能“用tnsping MYDB / SE3”好....尝试变更listener.ora和tnsnames.ora文件了（目前已恢复），但不能连接到无论是通过SQLPLUS或的SQLDeveloper /视觉工作室其中之一.. plz帮助我。 。 谢谢！

Answer 1:

根据您自己的评论“IM能够设置ORACLE_SID，但无法通过SQLPLUS连接... ORA-01034 - 甲骨文不可用”很可能是你的数据库实例下降。 这在理论上是可能有潜在的Windows服务运行OracleServiceMYDB在处理与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 *连接加上从客户端专用机到远程数据库时，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)

最终，我找到了我的问题，因为不正确的SERVICE_NAME我tnsnames.ora文件值：

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

改变后HOST13 （通过一个普通的文本编辑器），为正确的值SERVICE_NAME ，我是能够连接到远程Oracle数据库。

在My Oracle 11.2客户端机器，这里是tnsnames.ora中的位置：

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

文章来源: Suddenly getting ORA-12514 error Oracle 11gr2