首页 > 代码库 > ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

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

一台测试服务器上的数据库无法访问,远程能访问服务器1521端口,但是通过pl/sql连不上。
    登录数据库服务器,执行:
     $ lsnrctl status
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.155.100)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
居然没有service注册上。检查数据库service配置:
sql> show parameter service_names
service_names                        string      dg1.oracle.com,hr,oa 
sql>show parameter instance_name
instance_name                        string      dg
sql> show parameter domian_name
db_domain                            string      oracle.com
 
检查listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
配置无问题
 
检查tnsnames.ora
OA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (SERVER = DEDICATED)
      (SERVICE_NAME = oa.oracle.com)
    )
  )
HR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (SERVICE_NAME = hr.oracle.com)
    )
  )
DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg1.oracle.com)
    )
  )
也没发现有问题,但是通过tns登录:sqlplus user/user@oa报错:
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
手工注册service:
sql> alter system register
服务仍然不能注册到listener。
这时突然想到了local_listner参数,
sql> show parameter local_listener
(address=(protocol=tcp)(host=192.168.129.201)(port=1521))
而listener实际用的ip是192.168.155.100。
发现这台机器有两张网卡,ip分别为:192.168.155.100和192.168.129.201,之前有维护人员大概想将listener绑定到192.168.129.201这个ip上,但采用的方法不对。
修改local_listener参数,sql> alter system set local_listener=‘‘;
再重新注册服务,sql> alter system register;
查看注册情况,$ lsnrctl status
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1.oracle.com" has 1 instance(s).
  Instance "dg", status READY, has 4 handler(s) for this service...
Service "dg1_XPT.oracle.com" has 1 instance(s).
  Instance "dg", status READY, has 4 handler(s) for this service...
Service "hr.oracle.com" has 1 instance(s).
  Instance "dg", status READY, has 4 handler(s) for this service...
Service "oa.oracle.com" has 1 instance(s).
  Instance "dg", status READY, has 4 handler(s) for this service...
service_names中定义的服务都成功动态注册到listener。
远程pl/sql连接数据库成功。
 
--本篇文章转自:http://blog.sina.com.cn/s/blog_8345cdd90100v6wx.html

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