Oracle error 12537

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:Usersedchen>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:instantclientsqlplus.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

          ORA-609 with
TNS-12537: TNS:connection closed

We almost time receive an error such as ora-609 which shows
in alert log as

TNS- TNS-12537: TNS:connection closed
        ns secondary err code: 12560
        nt main err code: 0
        nt secondary err code: 0
        nt OS err code: 0
    opiodr aborting process unknown ospid (12345) as a result of
ORA-609

This error means could not attach to incoming
connection, so our database process was aborted or closed because incoming
connection was not passed to it by listener. In our case we found sqlnet error
stack as shown below

 TNS-12537: TNS:connection
closed

It means the dedicated process didn’t have a client connection
anymore to work.

As we all know the process of client connections:

1. First
Client initiates a connection to the
database so it connects to the listener.

2. Then
listener starts a dedicated DB process that will receive this connection or
session.

3. After
that, this dedicate process is started and listener passes the connection from
the client to this process.

4. Then
the sever process takes the connection from the listener to continue the
handshake with the client.

5. After
that Server process and client exchange messages or information required for
establishing a session or user logon.

6. Then
the session is opened now.

In
our case, it may be when the dedicated process tries to communicate with the
client it finds that connection was closed.

Solution or
Work around:

We
can eliminate this error by increasing the value of SQLNET.INBOUND_CONNECT_TIMEOUT
in sqlnet.ora file. 

By default the SQLNET.INBOUND_CONNECT_TIMEOUT value is 60
seconds.

The
sqlnet.ora file can be found in RDBMS_HOME/network/admin.

For example:

$ cd $ORACLE_HOME/network/admin

$vi sqlnet.ora

And change the value of SQLNET.INBOUND_CONNECT_TIMEOUT as:

SQLNET.INBOUND_CONNECT_TIMEOUT=300

Then
save the file.

There are many more possibilities which can cause this error
such as:

1. This can happen suppose our client closed the
connection immediately after initiating it.

2. This may occur
when firewall kill the connection.

3. Sometimes, it may happen because of Database, OS or network
performance problem.

4. This error may occur when some client crashes.

Hope this may also useful here:

Hope this may useful and helpful.
Please let us know for any concerns either by below comments or write to @contact us: https://ora-data.blogspot.in/

Regards,
ora-data Team