点击(此处)折叠或打开
- 在检查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
点击(此处)折叠或打开
- 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
点击(此处)折叠或打开
- #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博客 ” ,链接:http://blog.itpub.net/28551528/viewspace-2123488/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28551528/viewspace-2123488/