代码之家 › 专栏 › 技术社区 › Solat Ali

突然得到ORA-12154错误Oracle 11gr2

ora-12514 oracle11gr2

1

Solat Ali · 技术社区 · 12 年前

直到昨天,我安装了Oracle 11g r2 fine,有两个数据库(se3和mydb,都有db_domain.orcl,即mydb.orcl和se3.orcl)在上面运行。但突然出现ORA-121514错误(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”很好。。也尝试更改tnsnames.ora和listener.ora文件(现已恢复),但无法通过sqlplus或sqldeveloper/visionstudio连接到其中任何一个。。请帮帮我…谢谢!

2 回复 | 直到 12 年前

1

0

kubanczyk 12 年前

根据您自己的评论“我可以设置ORACLE_SID,但无法通过sqlplus连接…ORA-01034-ORACLE不可用”,很可能是您的数据库实例已关闭。理论上可以让底层的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

2

0

DavidRR Chris Brandsma 11 年前

就我而言,我收到了 口服-12514 尝试通过连接时出错 SQL*增强版 来自 仅限客户端 机器到远程数据库:

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 (通过普通文本编辑器)设置为 服务名称 ,我能够连接到远程Oracle数据库。

在我的Oracle 11.2客户端计算机上,以下是 tnsnames.ora公司 :

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