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

公司兩年前上的系統(tǒng)，當(dāng)時要用12c的新特性，就上線投產(chǎn)了。初始o(jì)racle版本為12.1，該版本在實際使用中bug不斷，簡直頭疼。近日開發(fā)投產(chǎn)一些新的應(yīng)用服務(wù)器大概20多臺，在投產(chǎn)至10臺的時候，數(shù)據(jù)庫內(nèi)部鏈接在910的時候，就會報錯，但是我們的session數(shù)很大，遠(yuǎn)遠(yuǎn)沒有到達臨界值，具體報錯如下所示；

成都創(chuàng)新互聯(lián)公司從2013年開始，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站建設(shè)、網(wǎng)站制作網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元鳳山做網(wǎng)站,已為上家服務(wù),為鳳山各地企業(yè)和個人服務(wù),聯(lián)系電話:028-86922220

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.

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

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)
)
)

后將監(jiān)聽修改為；
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)
)
)
)

和開發(fā)人員逐個上應(yīng)用服務(wù)器，并投產(chǎn)，觀察連接數(shù)和監(jiān)聽日志，并沒有出現(xiàn)，鏈接至910的時候瘋狂報錯現(xiàn)象；

SQL> /

SESSIONS_CURRENT SESSIONS_HIGHWATER

        1202               1456

當(dāng)前題目：12C數(shù)據(jù)庫之坑爹又坑隊友的無故鏈接不上問題處理
文章鏈接：http://bm7419.com/article40/igecho.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、外貿(mào)網(wǎng)站建設(shè)、服務(wù)器托管、微信小程序、App設(shè)計、企業(yè)網(wǎng)站制作

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容