sometime can connect to remote db

某省新业务数据库(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,由于没有响应,所以数据库连接就挂起。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注