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

朱雀 2022-01-10 02:47 58阅读 0赞

\[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在备库连接数据库了.

转载于:https://www.cnblogs.com/lfree/p/7421644.html