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

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

--//参考链接:
http://blog.itpub.net/267265/viewspace-2099397/
blogs.oracle.com/database4cn/adg%e5%a4%87%e5%ba%93%e7%9a%84drcp%e8%bf%9e%e6%8e%a5%e6%8a%a5%e9%94%99oci-21500%e8%a7%a3%e5%86%b3%e4%b8%80%e4%be%8b

1.测试环境:

SYS@bookdg> startup
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

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> alter database recover managed standby database using current logfile disconnect ;
Database 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@book> exec dbms_connection_pool.start_pool()
PL/SQL procedure successfully completed.

SYS@bookdg> exec dbms_connection_pool.start_pool();
PL/SQL procedure successfully completed.

--//这样ok了.

2.测试备库是否能连接使用POOL.

$ sqlplus scott/book@192.168.100.40:1521/bookdg:POOLED

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 24 09:22:44 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-56600: DRCP: Illegal call [First call inconsistency]

Errors in file :
OCI-21500: internal error code, arguments: [kpplcSyncState:Error in sync], [56600], [], [], [], [], [], []
???????????????????????????????????????????????????????????????????????????????????????????????????????Errors in file :
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [kpplcSyncState:Error in sync], [56600], [], [], [], [], [], []
????????????????????????????????????????????????????????????????????????????????????????????????????????Errors in file :
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [kpplcSyncState:Error in sync], [56600], [], [], [], [], [], []
b?縝?縝?縝???????????????????????????????????????????????????????????????????????????????????????????

--//可以发现连接报错.OCI-21500.

$ oerr ora 56600
56600, 0000, "DRCP: Illegal call [%s]"
// *Cause: An illegal OCI function call was issued.
// *Action: Check the documentation for Database Resident Connection Pool (DRCP) usage.

$ oerr oci 21500
21500, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause:  This is the generic error number for the OCI
//          environment (client-side) internal errors.  This indicates
//          that the OCI environment has encountered an exceptional
//          condition.
// *Action: Report as a bug - the first argument is the internal error number.

--//作者采用跟踪包,发现执行dml语句,因为备库是只读打开,应用日志.

SCOTT@bookdg> select * from emp for update;
select * from emp for update
              *
ERROR at line 1:
ORA-16000: database open for read-only access

--//作者通过跟踪16000事件,确定问题语句.

SYS@bookdg> alter system set events='16000 trace name errorstack forever,level 12';
System altered.

--//再次执行:
$ sqlplus scott/book@192.168.100.40:1521/bookdg:POOLED
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 24 09:31:54 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-56600: DRCP: Illegal call [First call inconsistency]

--//alert显示如下:
Errors in file /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_l002_22744.trc:
ORA-16000: database open for read-only access
Dumping diagnostic data in directory=[cdmp_20170824093412], requested by (instance=1, osid=22744 (L002)), summary=[abnormal process termination].
Dumping diagnostic data in directory=[cdmp_20170824093413], requested by (instance=1, osid=22744 (L002)), summary=[abnormal process termination].

*** 2017-08-24 09:32:00.997
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-16000: database open for read-only access
----- Current SQL Statement for this session (sql_id=4m7m0t6fjcs5x) -----
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

--//不知道作者如何猜出audit$=:9,与审计有关...

$ ps -ef | grep l002
oracle   22744     1  1 09:19 ?        00:00:14 ora_l002_bookdg

$ ps -ef | grep -e ora_l0 -e ora_n0 | grep -v grep
oracle   22738     1  0 09:19 ?        00:00:00 ora_n000_bookdg
oracle   22740     1  0 09:19 ?        00:00:00 ora_l000_bookdg
oracle   22742     1  0 09:19 ?        00:00:00 ora_l001_bookdg
oracle   22744     1  0 09:19 ?        00:00:14 ora_l002_bookdg
oracle   22746     1  0 09:19 ?        00:00:00 ora_l003_bookdg

ora_n000_XXX => Connection Broker Process
ora_l000_XXX => Pooled Server Process(Handles client requests in Database Resident Connection Pooling)

3.如果真是审计引起的,关闭就ok了.
--//实际上对于备库及时审计打开的,备库也会设置为OS.备库设置read only时,alert提示如下:
Thu Aug 24 10:02:39 2017
ALTER DATABASE OPEN
AUDIT_TRAIL initialization parameter is changed to OS, as DB,EXTENDED is NOT compatible for database opened with read-only access

SYS@bookdg> show parameter audit_trail
NAME        TYPE   VALUE
----------- ------ -------
audit_trail string OS

SYS@bookdg> show spparameter audit_trail
SID      NAME         TYPE    VALUE
-------- ------------ ------- ---------
*        audit_trail  string  DB
*        audit_trail  string  EXTENDED

--//修改为none看看.先备份
$ cp spfilebookdg.ora spfilebookdg.ora_20170824

SYS@bookdg> alter system set audit_trail=none scope=spfile ;
System altered.

--//重启看看.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.

SYS@bookdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@bookdg> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.

SYS@bookdg> exec dbms_connection_pool.start_pool();
PL/SQL procedure successfully completed.

$ sqlplus scott/book@192.168.100.40:1521/bookdg:POOLED
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 24 09:59:16 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@192.168.100.40:1521/bookdg:POOLED> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@192.168.100.40:1521/bookdg:POOLED> select * from dept for update ;
select * from dept for update
              *
ERROR at line 1:
ORA-16000: database open for read-only access

--//OK,现在能使用DRCP在备库连接数据库了.

时间： 2024-09-23 11:00:14

[20170824]11G备库启用DRCP连接.txt的相关文章

[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();失败. --//今天分

11g备库无法开启ADG的原因分析

今天碰到一个有些奇怪的问题,但是奇怪的现象背后都是有本质的因果. 下午在做一个环境的检查时,发现备库是在mount阶段,这可是一个11gR2的库,没有ADG实在是太浪费了,对于这种情况感觉太不应该了. 所以尝试启动至open阶段,发现状态一直是read only,在ADG中应该是READ ONLY WITH APPLY才对啊. 使用dg broker设置为READ-ONLY,备库的数据库日志如下:      Standby Database:           stestdb3, Enable

11g备库中碰到自己给自己埋的坑

记得之前在一半技术一半生活中分享过一个设计,因为业务的需求,为了提高业务的处理效率,采用了根据业务的拆库拆表的方式,类似下面的图示. 开发团队也很给力,帮我们协调了好的机器,加了内存,也在新业务2的环境上同步了表结构,抽取了部分数据,然后业务2就开始了紧张的测试, 通过这几天的测试,发现系统的性能逐步稳定下来.忙完了这茬,赶紧来考虑搭建备库. 自己也算是搭建过很多dataguard环境了,一般的环境中检测dataguard搭建成功与否的一种方式就是使用dg broker来验证,一条简单的show

Oracle备库TNS连接失败的分析

今天在测试12c的temp_undo的时候,准备在备库上测试一下,突然发现备库使用TNS连接竟然失败. 抛出的错误如下: $ sqlplus sys/oracle@testdb as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 15:30:10 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. ERROR: ORA-12514: TNS:listen

备库密码文件问题一波三折的插曲

昨天下午开始给一个新环境搭备库,本来想一两个小时全速搞定,没想到因为密码文件的问题耽搁了,整个过程也是一波三折,希望大家能够吸取过程之中的经验和教训. 首先这个环境没有安装oracle软件,只安装了操作系统,所以搭建备库先需要安装数据库软件,然后开始从主库使用duplicate的方式同步数据文件,然后用dg broker来配置即可. 没有安装数据库软件,又没有图形界面,也好办,采用克隆方式安装 首先在主库中发现$ORALE_HOME下有一个压缩包,看来已经提前准备好了. /U01/app/ora

Oracle 11g Dataguard物理备库配置(六) broker fastfailover测试

本文采用Oracle 11g Dataguard broker fastfailover测试 Oracle 11g Dataguard fast failover配置,需要主备数据库开启闪回功能,闪回功能开启本文略过. 闪回开启需要启动到mount状态时,主备库的监听不要随意关闭. 1. dgmgrl查看主备库状态 $ dgmgrl sys/oracle DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2

Oracle 11g Dataguard物理备库配置(四) broker snapshot standby测试

Oracle 11g Dataguard Snapshot Standby数据库功能,可将备库置于打开读写状态,进行模拟生产环境主库中测试.当备库Snapshot standby任务完成后,可以切换回物理备库角色.在Snapshot Standby数据库状态下,备库是可以接受主库传过来的日志,但是不能够将变化应用在备库中. 本文采用Oracle 11g Dataguard broker snapshot standby配置 1. 采用dg broker配置snapshot standby配置 1

Oracle 11g Dataguard物理备库配置(五) broker switchover测试

本文采用Oracle 11g Dataguard broker switchover测试 1. 采用dataguard broker 测试switchover 1) 主库情况 SQL> select open_mode,database_role,db_unique_name from v$database; OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME -------------------- ---------------- ---

Oracle 11g Dataguard物理备库配置(二) Active Dataguard测试

在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,数据库需要处于mount状态.从11g开始,应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard,这种状态可以实现实时查询功能. 1. 备库上操作 1) 查看备库当前状态 mount SQL> select open_mode,database_role,db_unique_name from v$database; OPEN_MODE