ORA-12516, TNS:listener could not find available handler with matching protocol stack

通常是由于很多人或者很多应用（java应用、R应用等）连接数据库，导致连接数（session）数量超出限制。

select status,machine,con_id,count(*) from v$session group by machine,status,con_id order by count(*);

查出是由于这三台主机的连接数过多导致的，根据主机名在CMDB表格中查出应用的负责人，沟通后重启应用释放连接。根本原因是应用配置的连接数过多导致的，后续优化重点在于调整连接数。

一般有两个原因，一个是session数或者process数不够，再有一个就是客户端和服务端建立连接的时候不稳定所引起的。

解决方法：

1. 针对第一个原因就是修改连接数的限制或者清理会话

sql > alter system set processes=xxx;

在清理会话时，最好是使用kill -9杀系统进程。如果在使用alter system kill session时，出现报错ORA-00030。

在metalink上，查看了ORA-00030错误的描述、原因、解决方案。如下所示

SQL> ho oerr ora 30

00030, 00000, "User session ID does not exist."

// *Cause: The user session ID no longer exists, probably because the

// session was logged out.

// *Action: Use a valid session ID.

 

The command may have been issued for one or more of the following reasons:

1. The process no longer exists at the os level, but does show up as active in v$session.

2. The user reboots the client machine without logging off, leaving a shadow process.

3. That session is holding onto a lock that needs to be released.

CAUSE

This error occurs because PMON is already trying to kill the session.

This is indicated by the fact that the serial number keeps changing.

When PMON attempts to clean up a dead session, it will increase the serial number.

PMON may take a long time to clean up the process. If the process was doing a very large transaction at the time it aborted, then PMON has to rollback the large transaction.

When PMON makes progress, i.e. if it manages to free at least some of the process's resource, it will repeatedly keep trying to delete the process. When it finally gets to the point where it can't free up any of the process's resource (i.e. there are no more free buffers), it will print a message to the trace file and try to delete that process a second time. 

The problem is encountered when PMON lacks the resources needed to remove the process. If there are not enough buffers, then the removal of  the process is delayed. This is a free buffer problem in the data cache.

SOLUTION

Encountering an ORA-30 when attempting to manually kill a process is not necessarily a bug but a result of trying to kill a process already marked as killed. 

PMON can take anywhere from 5 minutes to over 24 hours to clean up a job. The impact is that often the process being cleaned up is holding locks that prevents others from performing certain operations.

The solution is to wait for PMON to clean up the process.

1. 针对第二个原因，可以尝试把TNS的动态注册改为静态注册，把global_name和sid_name 都写到linster.ora文件中