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