12C 數據庫之坑爹又坑隊友的無故鏈接不上問題處理

公司兩年前上的系統，當時要用12c的新特性，就上線投產了。初始oracle版本為12.1，該版本在實際使用中bug不斷，簡直頭疼。近日開發投產一些新的應用服務器大概20多臺，在投產至10臺的時候，數據庫內部鏈接在910的時候，就會報錯，但是我們的session數很大，遠遠沒有到達臨界值，具體報錯如下所示；

TNS-12518: TNS:listener could not hand off client connection
TNS-12536: TNS:operation would block
TNS-12560: TNS:protocol adapter error
TNS-00506: Operation would block
Linux Error: 11: Resource temporarily unavailable

通過MOS賬戶查詢；Ora-12518 on Oracle 12c Multitenant Architecture (文檔 ID 2252001.1)

Ora-12518 on Oracle 12c Multitenant Architecture (文檔 ID 2252001.1)

SYMPTOMS
When attempting to connect to PDB via listener,
the following error occurs.

ERROR

ORA-12518: TNS:listener could not hand off client connection

Listener log:

=======

03-APR-2017 08:57:31 (CONNECT_DATA=(SID=wd1)(CID=(PROGRAM=d:\orasfw\prd1\bin\sqlplus.exe)(HOST=Host name )(USER=ismeirb))) (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=63994)) establish wd1 * 12518

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 203: Unknown error

CAUSE
PDB SiD name was not properly configured in the listener.ora file

SID_LIST_LSNRPD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ct1)
(ORACLE_HOME = d:\orasfw\prd1)
(SID_NAME = ct1) <<<< CDB Instance_name
)
(SID_DESC =
(GLOBAL_DBNAME = wd1)
(ORACLE_HOME = d:\orasfw\prd1)
(SID_NAME = wd1) <<<<< Suppose to be CDB instance_name but PDB Instance_name has been configured
)
)

SOLUTION
PDB instance_name 'wd1' will always exists within in the CDB instance_name 'ct1'. Configure the SID for PDB service name wd1 has shown below.

SID_LIST_LSNRPD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ct1)
(ORACLE_HOME = d:\orasfw\prd1)
(SID_NAME = ct1)
)
(SID_DESC =
(GLOBAL_DBNAME = wd1) <<<< PDB Instance_name
(ORACLE_HOME = d:\orasfw\prd1)
(SID_NAME = ct1) <<<< CDB Instance_name
)
)

After adding the SID WD1 the connections to PDB worked fine.

比較適用我們的場景，原來的監聽配置文件為；

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =salesdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesdb)
)
(SID_DESC =
(GLOBAL_DBNAME =salesstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesstdydb)
)
(SID_DESC =
(GLOBAL_DBNAME =salesdb_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesdb)
)
)

后將監聽修改為；
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =salesdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesdb)
)
(SID_DESC =
(GLOBAL_DBNAME =salesstdydb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesstdydb)
)
(SID_DESC =
(GLOBAL_DBNAME =salesdb_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesdb)
(SID_DESC =
(GLOBAL_DBNAME =salespdb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = salesdb)
)
)
)

和開發人員逐個上應用服務器，并投產，觀察連接數和監聽日志，并沒有出現，鏈接至910的時候瘋狂報錯現象；

SQL> /

SESSIONS_CURRENT SESSIONS_HIGHWATER

        1202               1456