当连接异常时,可以通过分析监听日志来查找线索
~ [DBCHC]$ find $ORACLE_HOME -name listener.log
/opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener.log
~ [DBCHC]$ vim /opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener.log
110856 Fri Oct 21 17:00:31 2016
110857 21-OCT-2016 17:00:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBCHC)(CID=(PROGRAM=sqlplus)(HOST=sinrndvud062)(USER=ora112))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.131)(PORT=47405)) * establish * DBCHC * 12514
110858 TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
110859 Fri Oct 21 17:02:47 2016
110860 21-OCT-2016 17:02:47 * service_update * OSMSRDB * 0
110861 21-OCT-2016 17:02:53 * service_update * OSMSRDB * 0
110862 21-OCT-2016 17:02:54 * (CONNECT_DATA=(SID=OSMSRDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.132)(PORT=32823)) * establish * OSMSRDB * 0
110863 21-OCT-2016 17:02:56 * service_update * OSMSRDB * 0
110864 Fri Oct 21 17:03:07 2016
110865 21-OCT-2016 17:03:07 * service_update * XSONGDB * 0
110866 21-OCT-2016 17:03:08 * (CONNECT_DATA=(SID=OSMSRDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.132)(PORT=32835)) * establish * OSMSRDB * 0
110867 21-OCT-2016 17:03:08 * service_update * OSMSRDB * 0
110868 Fri Oct 21 17:06:19 2016
110869 21-OCT-2016 17:06:19 * service_update * XSONGDB * 0
110870 21-OCT-2016 17:06:21 * (CONNECT_DATA=(SID=XSONGDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.200)(PORT=60498)) * establish * XSONGDB * 0
110871 21-OCT-2016 17:06:22 * service_update * XSONGDB * 0
110872 21-OCT-2016 17:06:24 * (CONNECT_DATA=(SID=XSONGDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.200)(PORT=60502)) * establish * XSONGDB * 0
110873 21-OCT-2016 17:06:25 * service_update * XSONGDB * 0
在RAC环境中,需要切换到grid用户来查询监听日志
vgerndpud852: / # su - grid
/home/ora11grid[FRWK]:find $ORACLE_HOME -name listener.log
find: `/opt/ora11grid/crf/admin/run/crflogd': Permission denied
find: `/opt/ora11grid/crf/admin/run/crfmond': Permission denied
/opt/ora11grid/log/diag/tnslsnr/vgerndpud852/listener/trace/listener.log
/home/ora11grid[FRWK]:lsnrctl
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-NOV-2016 04:28:53
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode displaymode
rules trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
snmp_visible save_config_on_stop
dynamic_registration enable_global_dynamic_endpoint
oracle_home pid
connection_rate_limit
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
LISTENER parameter "log_status" set to ON
The command completed successfully
~ [DBCHC]$ find $ORACLE_HOME -name listener.log
/opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener.log
~ [DBCHC]$ vim /opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener.log
110856 Fri Oct 21 17:00:31 2016
110857 21-OCT-2016 17:00:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBCHC)(CID=(PROGRAM=sqlplus)(HOST=sinrndvud062)(USER=ora112))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.131)(PORT=47405)) * establish * DBCHC * 12514
110858 TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
110859 Fri Oct 21 17:02:47 2016
110860 21-OCT-2016 17:02:47 * service_update * OSMSRDB * 0
110861 21-OCT-2016 17:02:53 * service_update * OSMSRDB * 0
110862 21-OCT-2016 17:02:54 * (CONNECT_DATA=(SID=OSMSRDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.132)(PORT=32823)) * establish * OSMSRDB * 0
110863 21-OCT-2016 17:02:56 * service_update * OSMSRDB * 0
110864 Fri Oct 21 17:03:07 2016
110865 21-OCT-2016 17:03:07 * service_update * XSONGDB * 0
110866 21-OCT-2016 17:03:08 * (CONNECT_DATA=(SID=OSMSRDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.132)(PORT=32835)) * establish * OSMSRDB * 0
110867 21-OCT-2016 17:03:08 * service_update * OSMSRDB * 0
110868 Fri Oct 21 17:06:19 2016
110869 21-OCT-2016 17:06:19 * service_update * XSONGDB * 0
110870 21-OCT-2016 17:06:21 * (CONNECT_DATA=(SID=XSONGDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.200)(PORT=60498)) * establish * XSONGDB * 0
110871 21-OCT-2016 17:06:22 * service_update * XSONGDB * 0
110872 21-OCT-2016 17:06:24 * (CONNECT_DATA=(SID=XSONGDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=linqus))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.160.164.200)(PORT=60502)) * establish * XSONGDB * 0
110873 21-OCT-2016 17:06:25 * service_update * XSONGDB * 0
在RAC环境中,需要切换到grid用户来查询监听日志
vgerndpud852: / # su - grid
/home/ora11grid[FRWK]:find $ORACLE_HOME -name listener.log
find: `/opt/ora11grid/crf/admin/run/crflogd': Permission denied
find: `/opt/ora11grid/crf/admin/run/crfmond': Permission denied
/opt/ora11grid/log/diag/tnslsnr/vgerndpud852/listener/trace/listener.log
/home/ora11grid[FRWK]:lsnrctl
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-NOV-2016 04:28:53
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode displaymode
rules trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
snmp_visible save_config_on_stop
dynamic_registration enable_global_dynamic_endpoint
oracle_home pid
connection_rate_limit
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
LISTENER parameter "log_status" set to ON
The command completed successfully
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2126891/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2126891/