ORA-12516错误解决办法
昨天客户打电话说，系统上线，一下子有几百人用，数据库出现异常
ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程
查看帮助文档，说明如下：
|
oracle@RH55_11g:/home/oracle> oerr ora 12516 12516, 00000, "TNS:listener could not find available handler with matching protocol stack" // *Cause: None of the known and available service handlers for the given // SERVICE_NAME support the client's protocol stack: transport, session, // and presentation protocols. // *Action: Check to make sure that the service handlers (e.g. dispatchers) // for the given SERVICE_NAME are registered with the listener, are accepting // connections, and that they are properly configured to support the desired // protocols. |
问题首先查看一下数据库现有的进程数，是否已经达到参数processes的大小。
select count(*) from
v$process;
取得数据库目前的进程数。
select value from v$parameter where name =
'processes'; 取得进程数的上限。
根据记录，在虚拟机上的解决办法如下：
1.查看当前session数量
|
sys@PROD>select count(*) from v$session;
COUNT(*) ---------- 45 |
2.查看processes参数和sessions参数
sys@PROD>show parameter processes
|
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 50
sys@PROD>show parameter sessions
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 97 shared_server_sessions integer
|
3.只要会话连接数超过上面的process数150或者sessions数170，再来一个的会话进程，就会产生12516错误。因此可以修改一下该值：sessions=1.1*processes+5。这个是sessions值和processes值的关系，最好按照这样做。
如果要将processes数设置为1000，则sessions数必须为1.1*1000+5=1105
|
sys@PROD>alter system set processes=1000 scope=spfile;
System altered.
sys@PROD>alter system set sessions=1105 scope=spfile;
System altered. |
4. 重新启动数据库服务。
|
sys@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@PROD>startup |
5.查看两个参数的值
|
sys@PROD>show parameter sessions
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 1522 shared_server_sessions integer sys@PROD>show parameter processes
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 1000 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29949829/viewspace-1312738/ ，如需转载，请注明出处，否则将追究法律责任。
