某省新业务数据库（192.168.1.1）实现独立组网后，在新业数据库通过dblink访问核心业务数据库经常报tns超时：
SQL> select count(*) from icp@newdb_sid;
select count(*) from icp@newdb_sid
*
ERROR at line 1:
ORA-12535: TNS:operation timed out
——在新sims上测试访问，长时间没有响应，在约3分钟之后，报错TNS没有响应。
在数据库的alterlog看到有大量的TNS超时的报错：
Mon Mar 12 03:58:47 2007
Errors in file /oracle/app/oracle/admin/newdb/bdump/newdb_j001_28626.trc:
ORA-12012: error on auto execute of job 263
ORA-20001: here=, ORA-12535: TNS:operation timed out
ORA-06512: at “P_USER.PROC_SIMS_AUDIT”, line 100
ORA-06512: at line 1
Mon Mar 12 06:10:03 2007
Errors in file /oracle/app/oracle/admin/newdb/bdump/newdb_j000_29096.trc:
ORA-12012: error on auto execute of job 263
ORA-20001: here=, ORA-12535: TNS:operation timed out
ORA-06512: at “P_USER.PROC_SIMS_AUDIT”, line 100
ORA-06512: at line 1
Mon Mar 12 09:09:55 2007
Thread 1 advanced to log sequence 67
Current log# 1 seq# 67 mem# 0: /oradata/lfile/redo01.log
Mon Mar 12 10:29:19 2007
Errors in file /oracle/app/oracle/admin/newdb/bdump/newdb_j000_29393.trc:
ORA-12012: error on auto execute of job 263
ORA-20001: here=, ORA-12535: TNS:operation timed out
ORA-06512: at “P_USER.PROC_SIMS_AUDIT”, line 100
ORA-06512: at line 1
Tue Mar 13 00:03:11 2007
Errors in file /oracle/app/oracle/admin/newdb/bdump/newdb_j009_30853.trc:
ORA-12012: error on auto execute of job 84
ORA-20001: 执行业务正式下线出错:错误编号:-12535-错误信息:ORA-12535: TNS:operation timed out
ORA-06512: at “P_USER.SERV_DOWN”, line 235
ORA-06512: at line 1
Tue Mar 13 14:04:21 2007
此外直接在新业务主机上sqlplus p_core数据库，还是超时：
[oracle@gd-simsdb oracle]$ sqlplus p_core/XXXXX@p_db_sid
SQL*Plus: Release 9.2.0.6.0 – Production on Thu Mar 15 17:35:46 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12535: TNS:operation timed out
oracle@gd_rac01:/oracle/app/oracle/product/9.2.0/network/admin$ lsnrctl status LISTENER_1
LSNRCTL for HPUX: Version 9.2.0.6.0 – Production on 15-MAR-2007 19:59:36
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.203.2.20)(PORT=1521)))
STATUS of the LISTENER
————————
Alias listener_1
Version TNSLSNR for HPUX: Version 9.2.0.6.0 – Production
Start Date 02-MAR-2007 03:33:03
Uptime 13 days 16 hr. 26 min. 32 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/9.2.0/network/log/listener_1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.203.2.20)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “gdp_core” has 3 instance(s).
Instance “gdrac1”, status READY, has 2 handler(s) for this service…
Instance “gdrac2”, status READY, has 1 handler(s) for this service…
Instance “gdrac3”, status READY, has 1 handler(s) for this service…
Service “gdrac1” has 1 instance(s).
Instance “gdrac1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
检查新业务上的tns配置：
P_CORE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.34.56.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gdp_core)
)
)
应该为：
P_CORE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.34.56.78)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = gdrac1)
)
)
问题分析： SERVICE_NAME = p_db_sid这样的配置，在同一网段内，是可以的，因为根据SERVICE_NAME =p_db_sid会随机去找3个节点中负载较轻的一个节点。但是在独立组网的情况下，核心业务的数据库的公网IP只映射到rac01。当通过gdp_core去访问时，会随机连接到其他节点，如果随机分配到连接rac02或者rac03，由于没有响应，所以数据库连接就挂起。