点击(此处)折叠或打开

在检查dblink采集结果时发现，有一个库的dblink没有查询到结果(LINK_106_132_TO_PMSDB132_TAB_COMPRESS)，经实际检查，发现该dblink在使用时发生ORA-12537错误
dblink名称：
LINK_106_132_TO_PMSDB132_TAB_COMPRESS
LINK_106_159_TO_OBIDB3_TAB_COMPRESS
LINK_106_159_TO_PMSDB_TAB_COMPRESS
下面是分析思路
检查数据库监听状态
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zj25k2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date 10-AUG-2016 17:06:57
Uptime 4 days 18 hr. 22 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/zj25k2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zj25k2)(PORT=1521)))
Services Summary...
Service "pmsdb132" has 1 instance(s).
Instance "pmsdb132", status READY, has 1 handler(s) for this service...
Service "pmsdb132XDB" has 1 instance(s).
Instance "pmsdb132", status READY, has 1 handler(s) for this service...
The command completed successfully
从这里看监听的最近一次启动时间是8月10日17:06与dblink采集日志中，8月10日05点执行正常，以后均为执行的情况复合，初步怀疑与监听重启又关联

dblink采集日志：
SELECT_TIME
-------------------
2016-08-15 05:00:02
2016-08-15 05:00:02
2016-08-14 05:00:01
2016-08-14 05:00:01
2016-08-13 05:00:02
2016-08-13 05:00:02
2016-08-12 05:00:02
2016-08-12 05:00:02
2016-08-10 05:00:03
2016-08-10 05:00:03
2016-08-10 05:00:02
2016-08-10 05:00:02
2016-08-10 05:00:02
2016-08-10 05:00:01

接着检查监听文件以及sqlnet.ora文件是否发生过修改

点击(此处)折叠或打开

rw-r--r-- 1 oracle dba 77 Jan 12 2015 listener.ora
-rw-r--r-- 1 oracle dba 530 Jan 8 2015 listener.ora_bak
drwxr-xr-x 2 oracle dba 512 Dec 29 2014 samples
-rw-r--r-- 1 oracle dba 381 Dec 17 2012 shrept.lst
-rw-r--r-- 1 oracle dba 859 Aug 10 18:06 sqlnet.ora
-rw-r--r-- 1 oracle dba 257 May 31 16:26 sqlnet.ora.20160531
-rw-r--r-- 1 oracle dba 258 Aug 10 16:56 sqlnet.ora20160810
-rw-r--r-- 1 oracle dba 273 Jun 24 2015 sqlnet.ora_0627
-rw-r--r-- 1 oracle dba 3900 Oct 5 2015 tnsnames.ora

监听文件时间比较久，说明为修改，sqlnet.ora文件的最近修改日期是Aug 10 18:06，说明在监听启动后，该文件又发生了变化。检查sqlnet.ora文件中内容

点击(此处)折叠或打开

#for an quan jia gu
tcp.validnode_checking = yes
tcp.invited_nodes =(10.212.211.4,10.211.106.120,10.211.106.121,10.211.106.132,10.211.106.136,********)
SQLNET.EXPIRE_TIME = 10
LOCAL_OS_AUTHENTICATION_LISTENER = OFF
PASSWORDS_LISTENER = ******
#sqlnet.encryption_server = rejected
#sqlnet.encryption_server = requested

经检查sqlnet.ora文件发现，增加了新的信任IP，该部分经过询问业务侧，确定业务侧在该时间点进行信任IP的增加工作，并在监听重启后，又修改该文件后，未reload监听。

经过以上分析，最终定位问题为，新增信任IP后，未reload lsnrctl 导致，最终reload监听后，恢复正常。

ITPub博客

ORA-12537 dblink

最新文章