Friday, June 20, 2014

Potential cause of ORA-12547: TNS:lost contact

I was running a script against the database directly on the host, when the script timed out with:

ERROR:
 ORA-12547: TNS:lost contact

After a search on Oracle's support site I found doc 555565.1: Troubleshooting ORA-12547 TNS: Lost Contact

One potential cause was incorrect permissions on the oracle binary in the $ORACLE_HOME/bin directory. I checked my old OH and compared it with the new OH:

server1>cd $ORACLE_HOME
server1>pwd
 /u01/oracle/product/11204
server1>ls -latr bin/oracle
 -rwxr-x--x    1 ora11g   dba       309704130 Jun 11 11:00 bin/oracle
server1>ls -latr /u01/oracle/product/11.2.0.2/bin/oracle
 -rwsr-s--x    1 ora11g   dba       279291858 Mar 12 13:42 /u01/oracle/product/11.2.0.2/bin/oracle
As can be seen from the above statement, the oracle server executable was lacking the setuid bit

This bit is needed, since we need to make sure that any user who runs the executable file will inherit the user ID of the owner (or group) of the executable file. Note that LOCAL connections would work just fine, even if the permissions on the oracle executable is set incorrectly; only connections that are passed from the listener to the Oracle server are affected. These sessions must be able to spawn a new server process.

Oracle points out that this error is commonly seen "in environments where the listener is running in the GRID home and servicing connections to an instance in a different $ORACLE_HOME."

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.

Solution to my particular problem this time was to give $ORACLE_HOME/bin/oracle the proper permissions:
server1> sqlplus / as sysdba
SQL> shutdown immediate

server1>chmod 6751 oracle
server1>ls -latr oracle
 -rwsr-s--x    1 ora11g   dba       309704130 Jun 11 11:00 oracle

server1> sqlplus / as sysdba
SQL> startup

After this change the script ran through with no ORA-12547 errors.

Sources: Oracle System Administration Guide

No comments:

Post a Comment