TNS-12518 is an old error, but has new cause in 11GR2.
Environment: Oracle 11.2..0.2 3-node RAC
RHEL 5
Problem: On one of the 3-nodes, Oracle listener failed to establish the connection.
As we used the shared server and dedicated server, the shared server connection is working fine but the dedicated server doesn't work.
Here is the error message,
[root@waleucp02 trace]# tail -f listener.log
28-OCT-2011 17:46:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRD)(CID=(PROGRAM=perl@REMOTE)(HOST=REMOTE)(USER=nagios))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*******.8.17)(PORT=41612)) * establish * PRD * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
Troubleshooting procedure:
1)Follow the Metlink article:
TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection [ID 550859.1]
2)As this is a DEDICATED server, we increased the Oracle parameter "processes" from 150 to 500. After I bounce the database, but it doesn't work
3)Initially, we thinks it was caused by Firewall setting, but the networking team didn't find anything suspicious.
4Finally, we figured out it is caused by the file permission (setuid)of oracle executable. This is the second time we encountered the similar issues since installing 11GR2 on two different servers.
go to $ORACLE_HOME/bin, the oracle's permission should be 6751, but for some reason, it was changed to 4751.
Note: I encountered a case, if tar backup by root, and recovered by oracle user, it may cause this issue. However, our cluster is brand new installation.
Incorrect permission.
# cd /u01/app/oracle/product/11.2.0/bin
# ll oracle
-rwxr-s--x 1 oracle asmadmin 228886426 Aug 17 14:25 oracle
Correct one:
# chmod 6751 oracle
# ll oracle
-rwsr-s--x 1 oracle asmadmin 228886426 Aug 17 14:25 oracle
After setting the correct setuid, listener started working again.
Similarly, one months ago, we encountered a similar issue for ASM. Please reference Metalink ID:
"DBCA Does Not Display ASM Disk Groups In 11.2 [ID 1177483.1]".
That error is also caused by the setuid bit of oracle executable under the $GRID_HOME/bin
We've submitted this error to Oracle and Oracle support has created a bug for this error.