Wednesday, December 18, 2013

ORA-12547 TNS lost contact



Issue / Oracle error

sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle.

Issue description

I saw that TNS connection issue along with ORA-12547 Oracle error several times, usually when trying to connect to Oracle database server on Unix / Linux host with an OS user that does not belong to oinstall group (Oracle binaries owner group).  In this case, interesting enough that local TNS connection to database (when using tnsnames alias) works fine:
sqlplus scott/tiger@orcl
and only local BEQ protocol connection through an Oracle error ORA-12547:

sqlplus scott/tiger
ERROR:
ORA-12547: TNS:lost contact
There can be different reasons of this issue, but usually the problem is in wrong privileges/ownership of some Oracle binaries located in $ORACLE_HOME/bin directory. Perhaps, Oracle database binaries were installed / linked not correctly or something or somebody has changed the files’ ownership.

Solution

  1. Ensure the DB is up and running and you can connect locally AS SYSDBA to the database using Oracle binaries owner (usually oracle:oinstall Unix / Linux user). If it does not work, probably you encounter a different problem.
  2. Check privileges of an Oracle file on Unix / Linux host where database is running:
  3. cd $ORACLE_HOME/bin
    ls -ltr oracle
    -rwxr-xr-x    1 oracle   oinstall       136803483 Mar 16 20:32 oracle
  4. Change permissions as below:
  5. chmod 6751 oracle
    ls -ltr oracle
    -rwsr-s--x    1 oracle   oinstall       136803483 Mar 16 20:32 oracle
  6. Usually the above operation should fix the issue but I suggest continue investigating privileges of other files to avoid further possible problems.  As the matter of fact these special rights of Oracle binaries are set by $ORACLE_HOME/root.sh script on Unix / Linux after the Oracle installation. You can run the script again as root user, if you see more files have wrong permissions or ownership (make backup before, just in case). For that I provide as an example 2 lists of oracle binary files in $ORACLE_HOME/bin directory with correct rights below.

ORA-12637 Packet receive failed


                                                   ORA-12637 Packet Receive Failed


Issue: Sometimes when your trying to login for  oracle database  from WINDOWS client box to Linux box/Windows server box  we will face issue below issues

ORA-12637 Packet receive failed


Symptoms:
Trying to connect from a remote client or even loopback on the server to the database, results in an ORA-12637 Packet receive failed. Connection attempt is in the standard form:
sqlplus scott/tiger@ORCL


Cause:

Check to see if you have the SQLNET.AUTHENTICATION_SERVICES=(NTS) line in the sqlnet.ora file (client and/or server). If you are passing usernames and passwords that will be authenticated via the Oracle database, then you should not have NTS authentication enabled or attempted. If this parameter is set, then authentication will be retrieved from the OS and, if the database and OS are not setup for OS or External Authentication, an abnormal condition exists and errors are likely

Solution:
Remove the SQLNET.AUTHENTICATION_SERVICES=(NTS) line.
Only if an external authentication mechanism (OS authentication) is to be employed will this line be used.
Note:
- If this line is removed from the Server, then the database and listener will need to be restarted.
- If this line is removed from only the Client, then no Oracle processes need to be restarted.