Ошибка ora 12537 tns connection closed

ORA-12537 Overview

There’re several possible causes to raise ORA-12537:

1. Incorrect File Permission
2. White or Black List

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\edchen>sqlplus hr/hr@db11g

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=edchen))) * (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.

1. On the first node.

$ ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x   1 oracle     asmadmin   534683872 Mar 12 16:08

2. On the second node.

$ 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