[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> @ ver1PORT_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 ProductionSYS@bookdg> exec dbms_connection_pool.start_pool();BEGIN dbms_connection_pool.start_pool(); END;*ERROR at line 1:ORA-56501: DRCP: Pool startup failedORA-56501: DRCP: Pool startup failedORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 4ORA-06512: at line 1--//在主库没有启动drcp的情况下,在备库启动会报错.$ oerr ora 5650156501, 0000, "DRCP: Pool startup failed"// *Cause: The connection pool failed to start up.// *Action: Check logs for details.2.分析:--//先做一个10046跟踪看看.SYS@bookdg> @ &r/10046on 12old 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 failedORA-56501: DRCP: Pool startup failedORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 4ORA-06512: at line 1SYS@bookdg> @ &r/10046offSession 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 STMTBINDS #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=1503624921300745FETCH #182929053448:c=0,e=30,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=1895327128,tim=1503624921300818EXEC #182928806584:c=999,e=502,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1503624921300951ERROR #182928806584:err=56501 tim=1503624921300974WAIT #182928806584: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=5762 tim=1503624921301028WAIT #182928806584: nam='SQL*Net break/reset to client' ela= 53 driver id=1650815232 break?=0 p3=0 obj#=5762 tim=1503624921301103WAIT #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.227WAIT #182928806584: nam='SQL*Net message from client' ela= 3926451 driver id=1650815232 #bytes=1 p3=0 obj#=5762 tim=1503624925227651CLOSE #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 4000012 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 4000012 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 ACTIVESYS@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 failedORA-56506: DRCP: Pool shutdown failedORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 16ORA-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 INACTIVESYS@bookdg> exec dbms_connection_pool.stop_pool();PL/SQL procedure successfully completed.