Listener Log
=================================
To find where is the Oracle Listener log file:
find . -type f -name lsnr_orainst.log
=================================
Listener Log File location
=================================
Listener log file location can be found by running lsnrctl status
The header section got the full path for listener log file.
In Oracle 10:
/home/app/oracle/product/10.2/network/log/listener.log
In Oracle 11:
/software/oracle/diag/tnslsnr/<server>/lsnr_<SID>/trace/lsnr_<SID>.log
/software/oracle/diag/tnslsnr/<server>/lsnr_<SID>/alert/log.xml
lsnr_<SID>.log - Is one file, getting bigger and bigger.
log.xml - many files, with sequence, log1.xml, log2.xml, log3.xml, etc...
=================================
How to find where is the listener log.
=================================
Option A.
SELECT value FROM V$PARAMETER
WHERE name = 'background_dump_dest';
VALUE
------------------------------------------
/software/oracle/diag/rdbms/orainst/orainst/trace
Option B.
lsnrctl status LSNR_<SID>
And take the path from the output
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 25-FEB-2015 21:00:34
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jpn-sbm-5-ora-1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LSNR_IGT
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 24-FEB-2015 21:23:22
Uptime 0 days 23 hr. 37 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /software/oracle/111/network/admin/listener.ora
Listener Log File /software/oracle/111/log/diag/tnslsnr/my_server/lsnr_orainst/alert/log.xml
Option C.
Using adrci utility.
adrci stands for ADR Command Interpreter.
>% adrci
ADRCI: Release 11.1.0.7.0 - Production on Wed Feb 25 21:08:12 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/software/oracle"
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci> HELP EXTENDED
HELP [topic]
Available Topics:
BEGIN BACKUP
CD
DDE
DEFINE
DESCRIBE
END BACKUP
LIST DEFINE
MERGE ALERT
MERGE FILE
MIGRATE
QUERY
REPAIR
SET COLUMN
SHOW CATALOG
SHOW DUMP
SHOW SECTION
SHOW TRACE
SHOW TRACEMAP
SWEEP
UNDEFINE
VIEW
How to find where is the listener log.
=================================
Option A.
SELECT value FROM V$PARAMETER
WHERE name = 'background_dump_dest';
VALUE
------------------------------------------
/software/oracle/diag/rdbms/orainst/orainst/trace
Option B.
lsnrctl status LSNR_<SID>
And take the path from the output
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 25-FEB-2015 21:00:34
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jpn-sbm-5-ora-1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LSNR_IGT
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 24-FEB-2015 21:23:22
Uptime 0 days 23 hr. 37 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /software/oracle/111/network/admin/listener.ora
Listener Log File /software/oracle/111/log/diag/tnslsnr/my_server/lsnr_orainst/alert/log.xml
Option C.
Using adrci utility.
adrci stands for ADR Command Interpreter.
>% adrci
ADRCI: Release 11.1.0.7.0 - Production on Wed Feb 25 21:08:12 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/software/oracle"
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci> HELP EXTENDED
HELP [topic]
Available Topics:
BEGIN BACKUP
CD
DDE
DEFINE
DESCRIBE
END BACKUP
LIST DEFINE
MERGE ALERT
MERGE FILE
MIGRATE
QUERY
REPAIR
SET COLUMN
SHOW CATALOG
SHOW DUMP
SHOW SECTION
SHOW TRACE
SHOW TRACEMAP
SWEEP
UNDEFINE
VIEW
adrci> show tracefiles
This would cause all trace file to flash by. not very useful.
adrci> show tracefiles %.log
diag/rdbms/igt/igt/trace/alert_igt.log
diag/rdbms/igt/igt/trace/alert_last_3000.log
diag/clients/user_oracle/host_1265807696_11/trace/sqlnet.log
diag/tnslsnr/jpn-sbm-5-aps-2/lsnr_igt/trace/lsnr_igt.log
adrci> show tracefiles alert_igt.log
diag/rdbms/igt/igt/trace/alert_igt.log
adrci> show trace alert_igt.log
This would open the alert_igt.log in vi mode
=================================
service name
=================================
How to find Oracle service on a server:
ps -ef | grep pmon | grep -v grep
oracle 4354 1 0 2013 ? 00:01:59 ora_pmon_dbprod
=================================
listener_name
=================================
Default listener name, in listener.ora is the string LISTENER.=================================
When another name is specified, one needs to provide the listener name in each lsnrctl command.
For example:
This is the listener.ora file
LSNR_DBPROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
)
)
)
SID_LIST_LSNR_DBPROD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbprod)
(ORACLE_HOME = /software/oracle/101)
(SID_NAME = dbprod)
)
)
=================================
lsnrctl commands
=================================
lsnrctl commands should look like:
lsnrctl status LSNR_DBPROD
lsnrctl stop LSNR_DBPROD
lsnrctl start LSNR_DBPROD
Using lsnrctl utility
General syntax:
lsnrctl command [listener_name]
Where listener_name is the name of the listener to be administered.
If no name is specified, then the default name,
LISTENER, is assumed.Would display current
lsnrctl show [listener_name]
Would display all possible lsnrctl parameters
lsnrctl show parameter [listener_name]
Would display the parameter value.
Would list all services on the server.
=================================
Real example:
=================================
oracle@my_server:/software/oracle/diag/rdbms/sid/sid/trace>% lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 23-JUL-2014 07:30:53
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias lsnr_sid
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 25-DEC-2013 08:04:28
Uptime 209 days 23 hr. 26 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /software/oracle/111/network/admin/listener.ora
Listener Log File /software/oracle/diag/tnslsnr/server-1/lsnr_sid/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
Services Summary...
Service "sid" has 2 instance(s).
Instance "sid", status UNKNOWN, has 1 handler(s) for this service...
Instance "sid", status READY, has 1 handler(s) for this service...
Service "sidXDB" has 1 instance(s).
Instance "igt", status READY, has 1 handler(s) for this service...
Service "sid_XPT" has 1 instance(s).
Instance "sid", status READY, has 1 handler(s) for this service...
The command completed successfully
less /software/oracle/111/network/admin/listener.ora
# listener.ora Network Configuration File: /software/oracle/111/network/admin/listener.ora
# Generated by Oracle configuration tools.
LSNR_SID =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server-1)(PORT = 1521))
)
)
)
SID_LIST_LSNR_SID =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sid)
(ORACLE_HOME = /software/oracle/111)
(SID_NAME = sid)
)
)
===================================================
oracle@server-1:/software/oracle/diag/rdbms/igt/igt/trace>% lsnrctl stop LSNR_SID
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 23-JUL-2014 07:59:10
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server-1)(PORT=1521)))
The command completed successfully
oracle@server-1:/software/oracle/diag/rdbms/sid/sid/trace>% lsnrctl start LSNR_SID
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 23-JUL-2014 07:59:16
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Starting /software/oracle/111/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.1.0.7.0 - Production
System parameter file is /software/oracle/111/network/admin/listener.ora
Log messages written to /software/oracle/111/log/diag/tnslsnr/server-1/lsnr_sid/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server-1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias lsnr_sid
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 23-JUL-2014 07:59:16
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /software/oracle/111/network/admin/listener.ora
Listener Log File /software/oracle/111/log/diag/tnslsnr/server-1/lsnr_sid/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
Services Summary...
Service "igt" has 1 instance(s).
Instance "igt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@nzl-tel-1-dwh-1:/software/oracle/diag/rdbms/sid/sid/trace>% lsnrctl status LSNR_SID
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 23-JUL-2014 07:59:34
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server-1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias lsnr_igt
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 23-JUL-2014 07:59:16
Uptime 0 days 0 hr. 0 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /software/oracle/111/network/admin/listener.ora
Listener Log File /software/oracle/diag/tnslsnr/my_server/lsnr_orainst/alert/log.xml
Listening Endpoints Summary...
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
Services Summary...
Service "sid" has 1 instance(s).
Instance "sid", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@server-1:/software/oracle/diag/rdbms/sid/sid/trace>%
=================================
Control logging to Listener.log
=================================
To stop/resume listener logging, set LOG_STATUS parameter to OFF/ON.
lsnrctl show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
lsnrctl set log_status off
LISTENER parameter "log_status" set to ON
The command completed successfully
lsnrctl set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-1)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LISTENER parameter "log_status" set to OFF
The command completed successfully
To make the change permanent, edit listener.ora file.
Need to add this line:
LOGGING_<LISTENER_NAME>=OFF
*The parameters are added at the end of listener.ora file.
For example:
LSNR_ORAINST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_server)(PORT = 1521))
)
)
)
SID_LIST_LSNR_ORAINST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orainst)
(ORACLE_HOME = /software/oracle/111)
(SID_NAME = orainst)
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LSNR_ORAINST=OFF
LOGGING_LSNR_ORAINST=OFF
Working with Named Listener
=================================
Some cammands would not work with named listener.
The workaround it to set listener name with SET CURRENT_LISTENER lsnr_<sid> command and then there is no need to provide the listener name.
lsnrctl
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 09-JUN-2015 08:48:40
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> set CURRENT_LISTENER lsnr_<sid>
Current Listener is lsnr_igt
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server)(PORT=1521)))
lsnr_igt parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server)(PORT=1521)))
lsnr_igt parameter "log_status" set to OFF
The command completed successfully
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server)(PORT=1521)))
lsnr_igt parameter "log_status" set to OFF
The command completed successfully
To set trace on. This would generate trc file under $ORACLE_HOME/log/diag/tnslistener/<server>/<listener_name>/trace
LSNRCTL> show trac_level
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server)(PORT=1521)))
lsnr_igt parameter "trc_level" set to OFF
The command completed successfully
LSNRCTL> set trac_level admin
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server)(PORT=1521)))
lsnr_igt parameter "trc_level" set to admin
The command completed successfully
=================================
sqlnet.ora parameters
=================================
sqlnet.ora file is located under /software/oracle/112/network/admin/
It can have many optional parameters.
Parameters for the sqlnet.ora File Reference
Some of the more useful parameters:
----------------------------------
NAMES.DIRECTORY_PATH
----------------------------------
To specify the order of the naming methods used for client name resolution lookups.
Example: NAMES.DIRECTORY_PATH=(tnsnames,ldap,ezconnect,nis)
----------------------------------
SQLNET.EXPIRE_TIME
----------------------------------
- Specify a time interval, in minutes, to send a check to verify that client/server connections are active.
Default value: 0 (Not Active)
Recommended value: 10
Example: SQLNET.EXPIRE_TIME=10
=================================
Static vs Dynamic Listener configuration.
=================================
$ORACLE_HOME/network/admin/listener.ora
Static Listener Configuration.
In this configuration, the listener service name is predefined, and hard codded in listener.ora file, and is mapped to a specific server:port:sid
SID_LIST_LSNR_ORAINST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orainst)
(ORACLE_HOME = /software/oracle/111)
(SID_NAME = orainst)
)
)
LSNR_ORAINST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_server)(PORT = 1521))
)
)
)
Dynamic Listener Configuration.
In this configuration, the listener service is not mapped to a specific sid, and just listens on all requests on port 1521.
The PMON process dynamically registers the server service with listener service.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_server)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
To block dynamic registrations, add this line to listener.ora
DYNAMIC_REGISTRATION_lsnr_name = off
=================================
Listener all possible parameters
=================================
lsnrctl
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> help 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
LSNRCTL> help set
The following operations are available after set
An asterisk (*) denotes a modifier or extended command:
password rawmode
displaymode trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
save_config_on_stop dynamic_registration
=================================
Listener trace:
=================================
Inside listener.ora file set the TRACE_LEVEL_listener
TRACE_LEVEL_listener=admin
Listener trace:
=================================
Inside listener.ora file set the TRACE_LEVEL_listener
TRACE_LEVEL_listener=admin
=================================
Common Lister Connection Errors
=================================
A. ORA-12162: TNS:net service name is incorrectly specified
The error:
ORA-12162: TNS:net service name is incorrectly specified
The Solution:
Check the environment variable ORACLE_SID and sid in the connection string.
For example, when using the command "sqlplus / as sysdba" the sid in the connection string is taken from ORACLE_SID environment variable.
Common Lister Connection Errors
=================================
A. ORA-12162: TNS:net service name is incorrectly specified
The error:
ORA-12162: TNS:net service name is incorrectly specified
The Solution:
Check the environment variable ORACLE_SID and sid in the connection string.
For example, when using the command "sqlplus / as sysdba" the sid in the connection string is taken from ORACLE_SID environment variable.
B. Fatal NI connect error 12170.
In alert.log, following error appears:
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 25-FEB-2017 17:12:15
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.144.3.200)(PORT=45052))
Sat Feb 25 17:12:16 2017
***********************************************************************
The Solution:
Add to sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10
Per Oracle Documentation:
SQLNET.EXPIRE_TIME
Purpose
To specify a time interval, in minutes, to send a check to verify that client/server connections are active.
The following usage notes apply to this parameter:
Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.
If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.
This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default: 0
Minimum Value: 0
Recommended Value: 10
=================================In alert.log, following error appears:
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 25-FEB-2017 17:12:15
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.144.3.200)(PORT=45052))
Sat Feb 25 17:12:16 2017
***********************************************************************
The Solution:
Add to sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10
Per Oracle Documentation:
SQLNET.EXPIRE_TIME
Purpose
To specify a time interval, in minutes, to send a check to verify that client/server connections are active.
The following usage notes apply to this parameter:
Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.
If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.
This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default: 0
Minimum Value: 0
Recommended Value: 10
Listener.log contents
=================================
Reference
Example:
08-JUN-2015 10:21:27 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=iu))(SID=igt)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.14.98)(PORT=62423)) * establish * igt * 0
08-JUN-2015 10:21:27 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=iu))(SID=igt)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.14.98)(PORT=62421)) * establish * igt * 0
08-JUN-2015 10:21:27 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=iu))(SID=igt)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.14.98)(PORT=62425)) * establish * igt * 0
08-JUN-2015 10:21:27 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=iu))(SID=igt)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.14.98)(PORT=62467)) * establish * igt * 0
08-JUN-2015 10:21:27 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=iu))(SID=igt)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.14.98)(PORT=62480)) * establish * igt * 0
Listener log format is:
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
Each field is delimited by *
Successful connections return *
Failed connections return error code.
=================================
Listener Common Errors
=================================
A. ERROR: ORA-12560: TNS:protocol adapter error
What to check:
- Oracle service is up and running
- Oracle SID is set
- tnsping <connection_string>
- Connect with ezconnect: sqlplus user/pass@SID:1521/host
-$PATH
B. ORA-12535: TNS: operation timed out
This is general "wrapper" error.
Look for the second error message.
C. TNS-03505
This error came when connect with any user except oracle user.
The issue was, missing read permission for all on tnsnames.ora and sqlnet.ora files.
error:
iu@oracle_server:/starhome/dbinstall/DBKIT>% tnsping igt
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JUL-2019 15:29:30
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
Fix:
oracle@oracle_server:~>% chmod a+r /software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
oracle@oracle_server:~>% chmod a+r /software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JUL-2019 15:29:54
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = igt)))
OK (0 msec)
Reference
lsnrctl Reference.
Oracle Reference to lsnrctl.
Listener Parameters
Listener Parameters in Oracle 10G
=================================
Listener Common Errors
=================================
A. ERROR: ORA-12560: TNS:protocol adapter error
What to check:
- Oracle service is up and running
- Oracle SID is set
- tnsping <connection_string>
- Connect with ezconnect: sqlplus user/pass@SID:1521/host
-$PATH
B. ORA-12535: TNS: operation timed out
This is general "wrapper" error.
Look for the second error message.
C. TNS-03505
This error came when connect with any user except oracle user.
The issue was, missing read permission for all on tnsnames.ora and sqlnet.ora files.
error:
iu@oracle_server:/starhome/dbinstall/DBKIT>% tnsping igt
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JUL-2019 15:29:30
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
Fix:
oracle@oracle_server:~>% chmod a+r /software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
oracle@oracle_server:~>% chmod a+r /software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Test again, it is working
iu@oracle_server:/starhome/dbinstall/DBKIT>% tnsping igt
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JUL-2019 15:29:54
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/ogg/oracle/12.2/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = igt)))
OK (0 msec)
Reference
lsnrctl Reference.
Oracle Reference to lsnrctl.
Listener Parameters
Listener Parameters in Oracle 10G
No comments:
Post a Comment