一台测试服务器上的数据库无法访问，远程能访问服务器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 = (SERVER = DEDICATED) (SERVICE_NAME = oa.oracle.com) ) )
HR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = hr.oracle.com) ) )
DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.155.100)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg1.oracle.com) ) )
也没发现有问题，但是通过tns登录：sqlplus 报错：
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连接数据库成功。
--本篇文章转自：