ORA-12516問題處理

stonebox1122發表於2017-05-11
在測試環境中經常會遇到ORA-12516錯誤，登入資料庫報錯如下：
[oracle@D2-PISIT22 ~]$ sqlplus lissit/lissit@10.163.91.22:1521/pocsit02

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 11 08:51:49 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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


檢視監聽服務：
[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:50:53

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:8095 refused:0 state:blocked
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

可以看到對於例項pocsit02的服務pocsit02，已經建立了8095個連線，狀態為blocked，處於封鎖狀態。
這個問題對於測試環境，直接重啟監聽和資料庫ok了，但生產環境就不能使用這麼重的方式了，可以執行如下語句重新讓pmon註冊一下監聽：

SQL> alter system register;

System altered.


[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:54:42

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:8100 refused:0 state:ready
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

可以看到狀態變為ready了。也可以對監聽開一下跟蹤，然後關掉的方式來解決這個問題並重置連線數：

SQL> alter system set events='immediate trace name listener_registration level 3';

System altered.

SQL> alter system set events='immediate trace name listener_registration level 0';

System altered.

[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:55:58

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
  Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-2138929/，如需轉載，請註明出處，否則將追究法律責任。

相關文章