[20170825]11G备库启用DRCP连接3.txt

简介: [20170825]11G备库启用DRCP连接3.txt --//昨天测试了11G备库启用DRCP连接,要设置alter system set audit_trail=none scope=spfile ; --//参考链接http://blog.

[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.

目录
相关文章
|
8月前
关闭文件
关闭文件。
48 2
Oracle 19c 启动和关闭实例保存PDB状态
十年以上 MySQL Oracle DBA从业者,MySQL 5.7 OCP, 微信号: jinjushuke
364 0
PostgreSQL PITR 任意时间点恢复过程中如何手工得到recovery需要的下一个WAL文件名 - 默认情况下restore_command自动获取
标签 PostgreSQL , recovery , recovery.conf , restore_command , timeline , 时间线 , next wal , PITR , 时间点恢复 背景 PostgreSQL数据库支持PITR时间点恢复。默认情况下,只需要配置目标是时间点,resotre_command即可,PG会自动调用resotre_command去找需要的WA
1559 0
【原】Oracle回收站功能开启与关闭
作者:david_zhang@sh 【转载时请以超链接形式标明文章】 链接:http://www.cnblogs.com/david-zhang-index/archive/2012/04/19/2457912.html Oracle10g以后增加了回收站的功能,其作用类似于windows的回收站。
1042 0
Confluence 6 从关闭的连接中恢复
当数据库服务器进行重启或者因为网络问题导致连接中断。所有在数据库连接池中的连接都会被中断。希望处理这个问题,通常需要 Confluence 进行重启。
866 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等