【ORA】ORA-12547 TNS: Lost Contact錯誤診斷

PURPOSE

Aid in diagnosing and resolving the TNS-12547 or "lost contact" errors for both local and remote Oracle client connections.

TROUBLESHOOTING STEPS

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
AND/OR
Non-Oracle user fails where all other users ok.  (See nosuid mount cause below)

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 :

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

The following Knowledge Base 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:

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' is inadequate:

Truss on sqlplus may look like :

  Solution:-

Increase the open files resource limit to a higher value with ulimit

(OR)


Cause :-
Possibly the following issue:

Unpublished BUG 4335746 - STARTUP GIVES TNS LOST CONTACT , TRACES GIVE SKGM ERROR 27148

Collect a truss of the failure using:

 Solution:-
Confirm what the current ulimit setting is for stack

Cause :-  The file system entry, associated with the $ORACLE_HOME directory, in the OS /etc/fstab configuration file has specified the nosuid option.
This should be changed to suid (usually the default if neither suid nor nosuid is specified).
If the nosuid option is used all local connections fail with ORA-12547

Solution:-
Replacing it with suid fixed the issue.

Note 188149.1 How to Display and Change UNIX Process Resource Limits

Check the install guide for your specific platform and version of Oracle and set stack as stated.

     example:  ulimit -s -1 ** This is Unlimited, the recommended setting on AIX **

Note 188149.1 How to Display and Change UNIX Process Resource Limits

Cause :-
SHMMX has recently been changed. 

An incident trace is being generated with the following code:

ORA-00600: internal error code, arguments: [SKGMINVALID], [13], [16777216], [0], [8164864]

Solution :-Set SHMMX correctly or back to value prior to change.

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.

In 10g and higher, you may see ORA-609 or ORA-3136 errors in the alert.log.

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

(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 (not reload)  the database listener for these parameters to take effect.

(OR)

Cause :-
If the $ORACLE_HOME parameter is different for the Database and the listener.
For example
If the database is started by a process (e.g. srvctl) with $ORACLE_HOME set to /u01/oracle/ for example (note the trailing '/')
but then users try to connect with $ORACLE_HOME set to /u01/oracle (no trailing forward slash) or via tnslsnr with that $ORACLE_HOME setting,
the connection will fail.

Solution :-
Confirm the $ORACLE_HOME is properly set for both the Database and the listener inside SRVCTL or in the environment if tools
like Sqlplus or lsnrctl are used to manage the database and listener.

Listener specific : 

Problem :-
Listener is failing to start with the error ORA-12547 and  Error: 104: Connection reset by peer 

Cause :-
In the strace trace file of the tnslsnr executable, we see segmentation fault after following calls:

28567      0.000054 open("/u01/home/oracle/ldap", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000021>
28567      0.000068 open("/u01/home/oracle/.ldap", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000020>
28567      0.000063 open("ldap", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000018>
28567      0.000084 --- SIGSEGV (Segmentation fault) @ 0 (0) ---

And we also see the first reference to ldap files being made after reading file nsswitch.conf

Solution :-

LDAP is not used for authentication and not configured. However, in nsswitch.conf file we have some entries with ldap resolution. The solution is to modify nsswitch.conf to assure there is no reference to ldap.

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

The listener must be restarted for these changes to take effect. 

Problem :-
Tnsping to the database listener is failing with ORA-12547

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 11-AUG-2016 04:01:33

Used parameter files:

/u01/oracle/product/10.1.0.5/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=10710)) (CONNECT_DATA= (SERVICE_NAME=myservice.example.com) (INSTANCE_NAME=myservice)))

TNS-12547: TNS:lost contact

Cause :-
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the client that is attempting this tnsping.  Alternatively, TCP.EXCLUDED_NODES
list contains this client's ip address.

Solution :- 
Add the client's ip address to the server side sqlnet.ora file in the TCP.INVITED_NODES list OR remove it from the TCP.EXCLUDED_NODES list if present
The listener must be restarted for these changes to take effect. 

Problem:-

After changing variables in /etc/system and rebooting), a 10g or newer Listener will not start.

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:

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.

Permissions:

Problem:- 
Connections via the listener are failing with ORA-12547.  It is likely in this scenario that LOCAL or BEQ connections to the instance are successful.

Cause:-
The permissions on the Oracle binary and not correct and the listener cannot spawn a server process.  This is common in environments where the listener is running in the GRID home
and servicing connections to an instance in a different $ORACLE_HOME. 
See the following document for both RAC/SCAN: 
Note 1069517.1  ORA-12537 if Listener (including SCAN Listener) and Database are Owned by Different OS User

Issue the following checks:

$ ls -l $RDBMS_HOME/bin/oracle
ls: /home/oracle/app/oracle/product/11.2/db/bin/oracle:  Permission denied

1. Listener owner (including SCAN listener) cannot access oracle binary in database home:

As listener owner:

$ ls -l $RDBMS_HOME/bin/oracle
ls: /home/oracle/app/oracle/product/11.2/db/bin/oracle:  Permission denied

2. Oracle binary in database home has wrong permission:

ls -l $RDBMS_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 184286251 Aug  9 16:25 /home/oracle/app/oracle/product/11.2/db/bin/oracle
The permission " -rwxr-x--x" is wrong as it's missing suid bit, oracle binary should have permission of 6751:

-rwsr-s--x 1 oracle asmadmin 184286251 Aug  9 16:25 /home/oracle/app/oracle/product/11.2/db/bin/oracle

Solution:-

 Solution is to make sure file system for database home has setuid/suid set, database binary($RDBMS_HOME/bin/oracle) has correct ownership and permission, and listener owner is able to access database oracle binary (as listener owner, "ls -l $RDBMS_HOME/bin/oracle" will tell)

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 or IP address> <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 or IP address>


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:395525.1  - How to Enable Oracle SQL*Net Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager
NOTE:744512.1  - Ora-12547: Tns:Lost Contact Creating Database After Clean Installation
NOTE:1069517.1  - ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User

NOTE:372143.1  - ORA-12547 connecting to sqlplus '/ as sysdba' on AIX
NOTE:422173.1  - Local SQL*Plus Connection and DBCA Fails With: ORA-12547: TNS:Lost Contact
NOTE:381566.1  - connect / as sysdba Fails with Ora-12547 And Tns-12514
NOTE:2206832.1  - DBCA and Local SQL*Plus Connection Fails With: ORA-12547: TNS:Lost Contact ORA-12753