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
上一篇:理解 Oracle 多租户体系中(12c,18c,19c)Grant授权作用域范围


下一篇:Streamr助你掌控自己的数据(3)——教你在Streamr市场上发布数据