ORA-12537 Overview
There're several possible causes to raise ORA-12537:
ORA-12537 Caused by Incorrect File Permission
Incorrect file permission will prevent connections from being established. That is to say, potentially successful connections will be interrupted by ORA-12537 due to wrong file permissions, specifically, the execution permission. As a result, we saw the error on the client side.
For Single-instance
One simple change can reproduce ORA-12537:
[oracle@primary ~]$ ll $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle oinstall 239952653 Sep 11 20:36 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[oracle@primary ~]$ chmod u-x $ORACLE_HOME/bin/oracle
[oracle@primary ~]$ ll $ORACLE_HOME/bin/oracle
-rwSr-s--x. 1 oracle oinstall 239952653 Sep 11 20:36 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
In the above, I removed the user execution permission on $ORACLE/bin/oracle. Now let's try to make a connection from outside.
C:\Users\scott>sqlplus hr/hr@db11g
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 4 19:25:51 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:
What we saw in listener.log can prove that the connection was established, but somehow the listener cannot hand out the connection due to permission problem.
[oracle@primary ~]$ tail -f $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log
...
Tue Mar 04 19:25:51 2019
04-MAR-2019 19:25:51 * (CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:\instantclient\sqlplus.exe)(HOST=MACHINE_NAME)(USER=scott))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.12.123)(PORT=51385)) * establish * ORCL * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 13: Permission denied
Further reading: Where is Oracle Listener Log Location
Errors in stack are all around permission problems and pointed to Linux error at the bottom.
Solution
That is, the solution is to add group permission to the file, which can rescue incoming connections from ORA-12537. Therefore, our action should be inspecting all the file permissions in $ORACLE, especially in $ORACLE_HOME/bin.
For RAC Database
After installing 11g RAC, we can test the database access, it would be successful via SCAN. But when we directly access on specific node, we found the connection failed on the second node with an error:
ORA-12537:TNS:connection closed
Generally speaking, the listener belongs to user grid, and the database belongs to user oracle in a RAC environment. Two users belong to a very special group oinstall. When we connected to the listener of the second node, the listener was acknowledged that it had no permission to access database. This could be the root cause of ORA-12537.
Let's check some files' permission from user grid's point of view.
- On the first node.
- On the second node.
$ ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 534683872 Mar 12 16:08
$ ls -al $ORACLE_HOME/bin/oracle
/oracle/database/product/11.2.0/dbhome_1/bin/oracle not found
That is to say, user grid can access oracle's files on the first node, but not on the second node. So we checked the following directories by user oracle on the second node:
$ cd $ORACLE_BASE
$ ls -l
total 2
drwx------ 3 oracle oinstall 96 Mar 12 16:02 admin
drwx------ 3 oracle oinstall 96 Mar 12 16:02 cfgtoollogs
drwxrwxr-x 11 oracle oinstall 1024 Mar 12 16:00 diag
drwxr-xr-x 3 oracle oinstall 96 Mar 12 16:09 product
$ cd product
$ ls -l
total 0
drwx------ 3 oracle oinstall 96 Mar 12 16:09 11.2.0
As you can see, it show that $ORACLE_BASE/product do have group permission, but $ORACLE_BASE/product/11.2.0 do not. As a result, grid cannot access the database files. That's why we received ORA-12537 while connecting to the database.
Solution
Therefore, we should add group permission for grid to access on this directory in a cascading fashion:
$ chmod 755 11.2.0
$ ls -l
total 0
drwxr-xr-x 3 oracle oinstall 96 Mar 12 16:09 11.2.0
Now, connections are back to work. No more ORA-12537 are thrown.
Theoretically, two nodes should have symmetric structures with same permissions at the beginning. So I think it might be a bug, because I didn't create the directory "11.2.0", OUI did.
ORA-12537 Caused by White or Black List
As I said in the above, ORA-12537 is meant for interrupting potentially successful connections. Here is another error pattern of ORA-12537.
In some cases, DBA explicitly blocks or allows some nodes listed in sqlnet.ora to limit the access to the database, which is essentially a black or white list in terms of network security. For example, we can implement a white list in sqlnet.ora like this:
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(weblogic1.example.com, weblogic2.example.com, 10.10.0.0/16)
Solution
Make sure you are in the white list. Otherwise you might get ORA-12537 when you connect to the database.
For those who want to implement black lists, TCP.EXCLUDED_NODES parameter should be in sqlnet.ora.
Further reading: TNSPING Errors Collections
Thanks a lot…you saved my day 🙂
My pleasure!
Just check the permission of $ORACLE_HOME/bin/oracle
it should be 6751
[oracle@node1 admin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ ls -lrt oracle
-rwxr-x–x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle
[oracle@node1 bin]$ chmod 6751 oracle
[oracle@node1 bin]$ ls -lrt oracle
-rwsr-s–x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle
Thanks for your feedback!
Thank you..! That was a good explanation..
I’m glad it’s helpful.
Hello,
I get error sam after i do relink all and the tns connection is closed
I check the permission of oracle is different
node 1:
/apps/oracle/product/19c/dbhome_1/bin$ ls -lrth oracle
-rwsr-s–x 1 oracle asmadmin 517M Feb 1 01:12 oracle
node 2:
/apps/oracle/product/19c/dbhome_1/bin$ ls -lrth oracle
-rwxr-xr-x 1 oracle asmadmin 517M Jun 1 21:39 oracle
So what i must do for this?
Thanks.
To align with Node 1, you can add the sticky bit for user and group on the file on Node 2.
chmod ug+s oracle