oracle监听程序无法识别连接,ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务...

前几天我建立了数据库,名为Demo,并且配置了监听,端口为1521,一切正常运行.今天我又建立了个数据库,名为ORCL,和Demo共用一个监听.

现在遇到一个问题,我每一次用sqlplus

sys/orcl@orcl as sysdba  登录没有问题,但是我执行SHUTDOWN IMMEDIATE后,再用sqlplus sys/orcl@orcl

as sysdba 登录,就不行了,

C:\>sqlplus sys/orcl@orcl as

sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 30 13:02:30

2008

Copyright (c) 1982, 2005, Oracle.  All rights

reserved.

ERROR:

ORA-12514: TNS:

监听程序当前无法识别连接描述符中请求的服务

但是用 sqlplus sys/orcl as sysdba

登录就可以,不知道这是什么,另一个库DEMO 一切正常.

为什么用sqlplus sys/orcl@orcl as sysdba 不行,用sqlplus

sys/orcl as sysdba 就可以登录呢？listener.ora和tnsnames.ora如下

listener.ora

SID_LIST_LISTENER =

(SID_LIST

=

(SID_DESC =

(SID_NAME = demo)

(ORACLE_HOME =

D:\oracle\product\10.2.0\db_1)

)

)

LISTENER

=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = Colin)(PORT =

1521))

)

tnsnames.ora

DEMO =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Colin)(PORT =

1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = demo)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Colin)(PORT =

1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

EXTPROC_CONNECTION_DATA

=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL =

IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID =

PLSExtProc)

(PRESENTATION = RO)

)

)

解决办法是把listener.ora改为如下：SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME

= demo)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(SID_NAME = demo)

)

(SID_DESC =

(GLOBAL_DBNAME =

ORCL)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(SID_NAME

= ORCL)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = Colin)(PORT = 1521))

)

重启下监听

还有类似的一个问题解决办法：昨天配置了oracle数据库,出现TNS问题问题描述:

当配置完listener和tnsname后,在确认listener服务有正常启动的前提下,在sqlplus下尝试连接数据库,返回以下错误:

ORA-12514:

TNS:监听程序当前无法识别连接描述符中请求的服务

ORA-12514: TNS:listener does not currently know of

service requested in connect descriptor解决方法:

在listener.ora文件中添加以下内容:

(SID_DESC =

(GLOBAL_DBNAME =

orcl)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(SID_NAME =

orcl)

)

注意:orcl为数据库名,D:\oracle\product\10.2.0\db_1为$ORACLE_HOME的路径,修改后的listener.ora文件内容如下:

#

listener.ora Network Configuration File:

e:\oracle\product\10.1.0\Db_1\network\admin\listener.ora

# Generated by

Oracle configuration tools.SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = e:\oracle\product\10.1.0\Db_1)

(PROGRAM =

extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(SID_NAME = orcl)

)

)LISTENER =

(DESCRIPTION_LIST

=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL

= TCP)(HOST = linewell-d29e66.mshome.net)(PORT = 1521))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

)

)相关说明:Cause:The listener received a request to

establish a connection to a database or other service. The connect descriptor

received by the listener specified a service name for a service (usually a

database service) that either has not yet dynamically registered with the

listener or has not been statically configured for the listener. This may be a

temporary condition such as after the listener has started, but before the

database instance has registered with the listener.Action:- Wait a moment and try to connect a second

time.

- Check which services are currently known by the listener by

executing: lsnrctl services - Check that the

SERVICE_NAME parameter in the connect descriptor of the net service name used

specifies a service known by the listener.

- If an easy connect naming

connect identifier was used, check that the service name specified is a service

known by the listener.

- Check for an event in the listener.log

file.