Skip to content

ora-12547 TNS: lost contact – troubleshooting- for the new oracle dba





you are a new oracle dba and your application connection to your oracle database server failed with the ORA-12547 TNS: lost contact error. Below metalink article explains in detail about troubleshooting this issue.

Troubleshooting ORA-12547 TNS: Lost Contact [ID 555565.1]

Modified:30-Nov-2011Type:TROUBLESHOOTINGStatus:PUBLISHEDPriority:3

 

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
Bequeath (Local) :
Remote Connections:
Listener specific :
Diagnosis :
References


Applies to:

Oracle Net Services – Version: 9.2.0.1.0 to 11.1.0.6.0 – Release: 9.2 to 11.1
Information in this document applies to any platform.

Purpose

Step by Step Troubleshooting Guide for ORA-12547 TNS LOST CONTACT error faced while using Oracle Database server.

Last Review Date

September 21, 2010

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Background / Why ORA-12547

12547 indicates that the communication channel has been broken.
It’s most often thrown because the other end of the process went away unexpectedly.

Known Scenarios / Where ORA-12547 can Occur
This error can occur in following scenarios:


Bequeath (Local) :

The following/below are common scenarios in which 12547 occurs while making a BEQ
connection.

Problem:-
BEQ connection fails when connecting with / as sysdba

Cause:-
Oracle binaries have not been linked correctly

Solution :-
relink the Oracle binaries by executing the following command :

$ ORACLE_HOME/bin relink all

(OR)

Cause :-
Shared memory segment and/or semaphores are not flushed properly.

A system level trace taken while connecting to the database ” / as sysdba ” reveals the following :

2654: write(5, ” O R A – 0 0 6 0 0 : i”.., 81) = 81
2654: write(5, “\n”, 1) = 1
2654: write(5, ” O R A – 2 7 1 0 0 : s”.., 45) = 45

Solution :-
Please identify and clear the problematic memory segment and semaphores.

The following metalink document describes the steps in detail.
Note 381566.1 connect / as sysdba Fails with Ora-12547 And Tns-12514

(OR)

Cause :-
External Shared Memory is configured in the environment.Extended Shared Memory is not supported by Oracle and should not be set/exist in the environment.

Truss of sqlplus connection shows a trace file being generated in $ORACLE_HOME/rdbms/log directory instead of the BDUMP location.

Excerpt from truss output:

569536: statx(“/opt/oracle/oracle/product/11.1.0/rdbms/log/mdsp_ora_569536.trc”, 0x0FFFFFFFFFFF7DF0, 176, 01) = 0
569536: statx(“/opt/oracle/oracle/product/11.1.0/rdbms/log/mdsp_ora_569536.trc”, 0x0FFFFFFFFFFF7DF0, 176, 0) = 0
569536: open(“/opt/oracle/oracle/product/11.1.0/rdbms/log/mdsp_ora_569536.trc”, O_WRONLY|O_APPEND|O_LARGEFILE) = 3
569536: kwrite(3, ” D u m p f i l e “, 10) = 10
569536: kwrite(3, ” / o p t / o r a c l e /”.., 63) = 63
569536: kwrite(3, “\n”, 1) = 1
569536: kwrite(3, “\n * * * 2 0 0 9 – 0 2″.., 29) = 29
569536: kwrite(3, ” O r a c l e D a t a b”.., 200) = 200

 

 

Solution :-
1. Unset the EXTSHM environment variable in the session where you are connecting to database:
unsetenv EXTSHM
2. Run sqlplus:
sqlplus / as sysdba

(OR)

Cause :-
The ulimit settings for ‘open files’ and ‘pipe size’ is very low

Truss on sqlplus may look like :

 
4074: shmat(9, 0x380000000, 040000) = 0x380000000
4074: Incurred fault #6, FLTBOUNDS %pc = 0x100E59824
4074: siginfo: SIGSEGV SEGV_MAPERR addr=0x000006C0
4074: Received signal #11, SIGSEGV [caught]
4074: siginfo: SIGSEGV SEGV_MAPERR addr=0x000006C0
4074: lwp_sigmask(SIG_SETMASK, 0x9FBEF457, 0x0000FFF7) = 0xFFBFFEFF [0x0000FFFF]
4074: lwp_sigmask(SIG_SETMASK, 0x9FBEF057, 0x0000FFF7) = 0xFFBFFEFF [0x0000FFFF]
4074: Incurred fault #6, FLTBOUNDS %pc = 0x100E5BD3C
4074: siginfo: SIGSEGV SEGV_MAPERR addr=0x00000008

 

 

Solution:-

Increase the open files and pipe size (optional) resource limit to a higher value with ulimit

Problem :-
Bequeath connections fail with ORA-12547.
Remote connections fail with ORA-12500.

The below error stack is noticed in the listener log followed by a operating system error :

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact

The version of the database server is 9.2.0.2

Cause :-
This issue is identified as unpublished Bug 2654576 which is raised when the processes value reaches a limit.

Solution :-
The bug is fixed in patch set 9.2.0.3 and in 10g.

The workaround will be :
1. Increase the PROCESSES parameter in the init.ora to a large value say 2000.
2. If increasing the PROCESSES parameter did not help, than the the following parameter in init.ora along with increasing the processes parameter.
_attach_count_slack = 2000

Remote Connections:
The following/below are common scenarios in which 12547 occurs while making a TCP connection.

Problem :-
Remote connections to the database server fail with ORA-12547

Cause :-
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server’s sqlnet.ora and listener.ora

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.

From 10g and higher ORA-3136 errors may appear in the alert.log

Solution :-
Tune the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.

The database server and the listener has to be restarted for these parameters to take effect.

(OR)

Cause :-
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.

Solution :-
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list

It is recommended to restart the database server and the listener for these parameters to take effect.


Listener specific :

Problem :-
Listener is failing to start with the error ORA-12547.

Cause :-
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the Database server specified in the address definition of the listener.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the Database server specified in the address definition of the listener.

Solution :-
Either add the IP address of the Database server in the TCP.INVITED_NODES list and remove it from the TCP.EXCLUDED_NODES list if present

It is recommended to restart the database server and the listener  for these parameters to take effect.

Problem :-
This is specific to the listener versions below 9.2

Attempt to set the lsnrctl feature SAVE_CONFIG_ON_STOP to ON and stopping the listener further result in ORA-12547. A core dump or Dr. Watson error may follow the error.

Cause :-
In the listener.ora if the SID_LIST_<listener_name> section exceeds 2048 Bytes, the SAVE_CONFIG_ON_STOP feature fails with TNS errors.
This behavior is filed as unpublished BUG1965005 .

Solution :-
This bug is fixed in Oracle version 9.2.

The work around for this would be to keep the size of the SID_LIST with in 2048 bytes .

Problem:-

After changing variables in /etc/system and rebooting), the 10g R2 TNS Listener will not start.

lsnrctl> start
Starting /u01/oracle/product/10.2.0/Db_1/bin/tnslsnr: please wait…
TNSLSNR for Solaris: Version 10.2.0.1.0 – Production
System parameter file is /u01/oracle/product/10.2.0/Db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/Db_1/network/log/listener.log
Trace information written to /u01/oracle/product/10.2.0/Db_1/network/trace/listener.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server11)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc10)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server11)(PORT=1521)))
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 131: Connection reset by peer
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc10)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused
Cause:-
Issue is due to a DNS client process, ‘BIND 9’ not running/started on the Solaris 10 Server.

“BIND allows DNS clients and applications to query DNS servers for the IPv4 and IPv6 networks.
BIND includes two main components: a stub resolver API, resolver(3resolv), and the DNS name server
with various DNS tools.”

Reference:
http://docs.sun.com/app/docs/doc/817-0547/6mgbdbsna?a=view
Solaris 10 Release and Installation Collection –> Solaris 10 What’s New –> 2. What’s New in the
Solaris 10 3/05 Release –>Freeware Enhancements

Solution:-
Contact the System Administrator to start the BIND 9 DNS Client on the Solaris10 Server.

Diagnosis :
If the above discussed scenarios did not help than please collect the following diagnostic information and the provide the same while logging a service request .

The diagnosis on this error depends on the context or the scenario in which the error is occurring:

1. If the error is occurring during the connection establishment from a remote installation than

a. Enable a support level client, listener and server side SQLNET traces.
Reproduce the issue or wait until the error reoccurs and upload the corresponding traces .
Note 395525.1 How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager

b. Upload the SQLNET.ORA, LISTENER.ORA of the database server.

c. From the client where connection fails, check whether telnet to the listener port works.(expected to bring a blank screen)
$ telnet <database server hostname/ IP> <listener port>

2. If the error is occurring on a remote installation after connection establishment than

a. Enable a support level client and server side SQLNET traces.
Reproduce the issue or wait until the error reoccurs and upload the corresponding traces .

b. From the client where connection fails, check whether telnet or putty to the database server also fails with idle time.
$ telnet <database server hostname/ IP>

3. If the error occurs while performing listener operations than

a. Enable a support level Listener traces.
Reproduce the issue and upload the corresponding traces.

 

Please see the other referenced documents for more information.

References

NOTE:372143.1 – ORA-12547 connecting to sqlplus ‘/ as sysdba’ on AIX
NOTE:381566.1 – connect / as sysdba Fails with Ora-12547 And Tns-12514
NOTE:395525.1 – How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager
NOTE:422173.1 – ORA-12547: TNS:Lost Contact From SqlPlus

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.