當連線異常時，可以透過分析監聽日誌來查詢線索
~ [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

Oracle 11g 檢視監聽日誌

相關文章