ORA-12537: TNS connection closed 问题排查

1.检查监听状态
lsnrctl status
lsnrctl status正常，查看listener.log(当PLSQL连接错误时，listener.log会有TNS:12518的错误)

2.TNS问题
造成TNS：12518错误的原因可能是processes和session值设置太小或者dispatcher使用率过高
1）使用PLSQL本地连接到Oracle，分别查看process进程数和session会话数
--取得数据库目前的进程数。
select count(*) from v$process;
--取得进程数的上限。
select value from v$parameter where name = 'processes';

--取得数据库目前的会话数。
select count(*) from v$session;
--取得会话数的上限。
select value from v$parameter where name = 'sessions';

a) 修改process值
alter system set processes=1000 scope=spfile;
b) 修改session值
alter system set sessions=1105 scope=spfile;

2）当前dispatcher个数不够，dispatcher使用率过高
--查看当前dispatchers个数和部分信息。一般默认安装的库只有一个
select name,busy,status,accept,idle from v$dispatcher;
--查看dispatchers使用率
select name,(busy/(busy+idle))*100 "busy rate%" from v$dispatcher;

--修改dispatchers个数为3.之后重启数据库
alter system set dispatchers='(protocol=tcp)(dispatchers=3)(service=db01XDB)';

----------
修改$ORACLE_HOME/bin 下的oracle文件的权限
具体操作过程：
[oracle@rac1 ~]$ cd $ORACLE_HOME/bin/
[oracle@rac1 bin]$ ls -l oracle
-r-xr-sr-x 1 oracle asmadmin 173515905 Dec 21 16:46 oracle

oracle 运行程序的权限应该是 6751:

[oracle@rac1 bin]$ chmod 6751 oracle
[oracle@rac1 bin]$ ls -l oracle
-rwsr-s--x 1 oracle asmadmin 173515905 Dec 21 16:46 oracle

各个节点确认oracle文件权限,正确权限如下,否则无法写入asm
ll $ORACLE_HOME/bin/oracle
rwsr-s--x 1 oracle asmadmin 173515905 Dec 21 16:46 oracle

如果不对,需要使用root修改权限
chgrp asmadmin /u01/app/oracle/product/19.0/db_1/bin/oracle
chmod 6751 /u01/app/oracle/product/19.0/db_1/bin/oracle

一般监听发生问题不外乎检查如下几项：
1）/etc/hosts及域名解析配置文件
2）监听日志大小
3）监听状态
4）$ORACLE_HOME/bin下oracle可执行文件权限

----------------
https://logic.edchen.org/how-to-resolve-ora-12537-tns-connection-closed/

ORA-12537 Caused by Incorrect File Permission

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
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=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

For RAC Database

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.
$ ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 534683872 Mar 12 16:08

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

ORA-12537 Caused by White or Black List

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.