[20170825]11G備庫啟用DRCP連線3.txt
[20170825]11G備庫啟用DRCP連線3.txt
--//昨天測試了11G備庫啟用DRCP連線,要設定alter system set audit_trail=none scope=spfile ;
--//參考連結http://blog.itpub.net/267265/viewspace-2144036/.
--//在測試過程中我遇到1個奇怪問題,就是如果主庫沒有開啟drcp,備庫執行exec dbms_connection_pool.start_pool();失敗.
--//今天分析看看.
1.環境:
SYS@bookdg> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@bookdg> exec dbms_connection_pool.start_pool();
BEGIN dbms_connection_pool.start_pool(); END;
*
ERROR at line 1:
ORA-56501: DRCP: Pool startup failed
ORA-56501: DRCP: Pool startup failed
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 4
ORA-06512: at line 1
--//在主庫沒有啟動drcp的情況下,在備庫啟動會報錯.
$ oerr ora 56501
56501, 0000, "DRCP: Pool startup failed"
// *Cause: The connection pool failed to start up.
// *Action: Check logs for details.
2.分析:
--//先做一個10046跟蹤看看.
SYS@bookdg> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SYS@bookdg> exec dbms_connection_pool.start_pool();
BEGIN dbms_connection_pool.start_pool(); END;
*
ERROR at line 1:
ORA-56501: DRCP: Pool startup failed
ORA-56501: DRCP: Pool startup failed
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 4
ORA-06512: at line 1
SYS@bookdg> @ &r/10046off
Session altered.
--//檢查跟蹤檔案發現如下:
...
=====================
PARSING IN CURSOR #182929053448 len=274 dep=1 uid=0 oct=3 lid=0 tim=1503624921300601 hv=3872345143 ad='7e890610' sqlid='2s0zgjvmcym1r'
SELECT connection_pool_name, status, minsize, maxsize, incrsize, session_cached_cursors, inactivity_timeout,
max_think_time, max_use_session, max_lifetime_session, num_cbrok, maxconn_cbrok FROM cpool$ WHERE (connection_pool_name = :1)
END OF STMT
BINDS #182929053448:
Bind#0
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0000 frm=01 csi=852 siz=32 off=0
kxsbbbfp=2a97747a38 bln=32 avl=27 flg=05
value="SYS_DEFAULT_CONNECTION_POOL"
EXEC #182929053448:c=999,e=178,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1895327128,tim=1503624921300745
FETCH #182929053448:c=0,e=30,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=1895327128,tim=1503624921300818
EXEC #182928806584:c=999,e=502,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1503624921300951
ERROR #182928806584:err=56501 tim=1503624921300974
WAIT #182928806584: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=5762 tim=1503624921301028
WAIT #182928806584: nam='SQL*Net break/reset to client' ela= 53 driver id=1650815232 break?=0 p3=0 obj#=5762 tim=1503624921301103
WAIT #182928806584: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=5762 tim=1503624921301128
*** 2017-08-25 09:35:25.227
WAIT #182928806584: nam='SQL*Net message from client' ela= 3926451 driver id=1650815232 #bytes=1 p3=0 obj#=5762 tim=1503624925227651
CLOSE #182928806584:c=0,e=41,dep=0,type=0,tim=1503624925227808
=====================
--//很明顯因為訪問底層基表cpool$
SYS@bookdg> @ &r/pt2 'select * from cpool$';
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ------- ---------------------- ----------------------------
1 1 CONNECTION_POOL_NAME SYS_DEFAULT_CONNECTION_POOL
2 STATUS INACTIVE
3 MINSIZE 4
4 MAXSIZE 40
5 INCRSIZE 2
6 SESSION_CACHED_CURSORS 20
7 INACTIVITY_TIMEOUT 300
8 MAX_THINK_TIME 120
9 MAX_USE_SESSION 500000
10 MAX_LIFETIME_SESSION 86400
11 NUM_CBROK 1
12 MAXCONN_CBROK 40000
12 rows selected.
--//而且在執行成功後STATUS='ACTIVE'.也就是exec dbms_connection_pool.start_pool();後如果STATUS='INACTIVE'要變成'狀態是'ACTIVE'.
--//執行類似的dml語句,這在備庫read only的情況下是不可行的.知道這個道理就明白為什麼要在主庫先執行exec dbms_connection_pool.start_pool();
--//修改STATUS='ACTIVE',這樣備庫也跟著修改.在備庫執行exec dbms_connection_pool.start_pool();才能OK了.
3.有了以上知識,就可以透過一個特殊的例子說明問題.
--//在主庫上執行如下,啟動drcp.
SYS@book> exec dbms_connection_pool.start_pool()
PL/SQL procedure successfully completed.
--//檢查備庫
SYS@bookdg> @ &r/pt2 'select * from cpool$';
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ---------- ---------------------- ---------------------------
1 1 CONNECTION_POOL_NAME SYS_DEFAULT_CONNECTION_POOL
2 STATUS ACTIVE
3 MINSIZE 4
4 MAXSIZE 40
5 INCRSIZE 2
6 SESSION_CACHED_CURSORS 20
7 INACTIVITY_TIMEOUT 300
8 MAX_THINK_TIME 120
9 MAX_USE_SESSION 500000
10 MAX_LIFETIME_SESSION 86400
11 NUM_CBROK 1
12 MAXCONN_CBROK 40000
12 rows selected.
--//取消redo應用.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
--//在主庫上執行如下,停止drcp.
SYS@book> exec dbms_connection_pool.stop_pool()
PL/SQL procedure successfully completed.
SYS@book> select CONNECTION_POOL_NAME,STATUS from cpool$;
CONNECTION_POOL_NAME STATUS
--------------------------- ---------
SYS_DEFAULT_CONNECTION_POOL INACTIVE
--//由於停止備庫的日誌應用,cpool$的記錄不會修改.依舊是status='ACTIVE'
SYS@bookdg> select CONNECTION_POOL_NAME,STATUS from cpool$;
CONNECTION_POOL_NAME STATUS
--------------------------- -------
SYS_DEFAULT_CONNECTION_POOL ACTIVE
SYS@bookdg> exec dbms_connection_pool.start_pool();
PL/SQL procedure successfully completed.
$ sqlplus scott/book@192.168.100.40:1521/bookdg:POOLED
--//測試連線ok.
--//同樣的道理在當前的狀態,無法在備庫執行exec dbms_connection_pool.stop_pool();因為這樣要修改staus,
--//而只讀資料庫是無法執行dml語句的.
SYS@bookdg> exec dbms_connection_pool.stop_pool();
BEGIN dbms_connection_pool.stop_pool(); END;
*
ERROR at line 1:
ORA-56506: DRCP: Pool shutdown failed
ORA-56506: DRCP: Pool shutdown failed
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 16
ORA-06512: at line 1
--//而同步應用日誌後,status變成了'INACTIVE',在備庫再執行停止drcp就ok了.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> select CONNECTION_POOL_NAME,STATUS from cpool$;
CONNECTION_POOL_NAME STATUS
--------------------------- ---------
SYS_DEFAULT_CONNECTION_POOL INACTIVE
SYS@bookdg> exec dbms_connection_pool.stop_pool();
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2144151/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20170824]11G備庫啟用DRCP連線.txt
- Oracle 11g DRCP連線跟蹤配置Oracle
- Oracle 11g DRCP連線方式——基本原理Oracle
- [20170825]不啟動監聽遠端能連線資料庫嗎2資料庫
- Oracle 11g 新特性之DRCPOracle
- Oracle 11g DRCP配置與使用(上)Oracle
- Oracle 11g DRCP配置與使用(下)Oracle
- 用Navicat連線資料庫-資料庫連線(MySQL演示)資料庫MySql
- Oracle 11g連線遠端資料庫Oracle資料庫
- Oracle備庫TNS連線失敗的分析Oracle
- Oracle 11g Data Guard 物理備庫開啟日誌延時應用流程Oracle
- Oracle備庫無法連線主庫的問題分析Oracle
- 用thinkphp連線mysql資料庫PHPMySql資料庫
- 連線池優化之啟用PoolPreparedStatements優化
- Oracle 11g RAC通過SCAN IP連線資料庫 - JDBC連線串問題Oracle資料庫JDBC
- 11g備庫無法開啟ADG的原因分析
- [20160330]關於連線順序3.txt
- MySQL資料庫遠端連線開啟方法MySql資料庫
- spring boot 不連線資料庫啟動Spring Boot資料庫
- 遠端桌面連線必備:Microsoft Remote Desktop正式啟用版「相容macos14」ROSREMMac
- 用rman建立dataguard備用資料庫連線報錯!(eygle版主幫忙看一下)資料庫
- 11G RAC無法連線
- 11G的SYS連線阻止SHUTDOWN IMMEDIATE關閉資料庫資料庫
- 一個好用的短連線服務,mark備用
- 備忘錄:關於.net程式連線Oracle資料庫Oracle資料庫
- Go實戰準備工作---建立資料庫連線池Go資料庫
- 配置postfix和dovecot啟用SSL以加密連線加密
- [20130730]11G的DRCP特性.txt
- 優秀的資料庫連線工具:DBeaverEE for Mac v23.3.0啟用版資料庫Mac
- 優秀的資料庫連線工具:DBeaverEE for Mac v23.2.5啟用版資料庫Mac
- Mac電腦多連線資料庫管理 Navicat Premium中文啟用最新版Mac資料庫REM
- 從HelloWorld啟航——資料庫連線字串的困惑資料庫字串
- DRCP總結
- 11g文件學習----sql連線SQL
- 利用flashback將Dataguard備庫啟用可讀寫
- ORACLE 11G 無法連線到資料庫例項故障排除Oracle資料庫
- zk 啟用kerberos後 hbase連線不上問題ROS
- 遠端連線Remote Desktop mac啟用安裝REMMac