[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在備庫連線資料庫了.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2144036/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20170825]11G備庫啟用DRCP連線3.txt
- Oracle 11g DRCP連線跟蹤配置Oracle
- Oracle 11g DRCP連線方式——基本原理Oracle
- [20130730]11G的DRCP特性.txt
- Oracle 11g 新特性之DRCPOracle
- Oracle 11g DRCP配置與使用(上)Oracle
- Oracle 11g DRCP配置與使用(下)Oracle
- 用Navicat連線資料庫-資料庫連線(MySQL演示)資料庫MySql
- Oracle 11g連線遠端資料庫Oracle資料庫
- [20210428]資料庫連線加密.txt資料庫加密
- Oracle備庫TNS連線失敗的分析Oracle
- Oracle 11g Data Guard 物理備庫開啟日誌延時應用流程Oracle
- Oracle備庫無法連線主庫的問題分析Oracle
- 用thinkphp連線mysql資料庫PHPMySql資料庫
- 連線池優化之啟用PoolPreparedStatements優化
- Oracle 11g RAC通過SCAN IP連線資料庫 - JDBC連線串問題Oracle資料庫JDBC
- 11g備庫無法開啟ADG的原因分析
- MySQL資料庫遠端連線開啟方法MySql資料庫
- spring boot 不連線資料庫啟動Spring Boot資料庫
- 遠端桌面連線必備:Microsoft Remote Desktop正式啟用版「相容macos14」ROSREMMac
- [20181224]使用odbc連線oracle資料庫.txtOracle資料庫
- 用rman建立dataguard備用資料庫連線報錯!(eygle版主幫忙看一下)資料庫
- 11G RAC無法連線
- 11G的SYS連線阻止SHUTDOWN IMMEDIATE關閉資料庫資料庫
- 一個好用的短連線服務,mark備用
- 備忘錄:關於.net程式連線Oracle資料庫Oracle資料庫
- Go實戰準備工作---建立資料庫連線池Go資料庫
- 配置postfix和dovecot啟用SSL以加密連線加密
- 優秀的資料庫連線工具:DBeaverEE for Mac v23.3.0啟用版資料庫Mac
- 優秀的資料庫連線工具:DBeaverEE for Mac v23.2.5啟用版資料庫Mac
- Mac電腦多連線資料庫管理 Navicat Premium中文啟用最新版Mac資料庫REM
- 從HelloWorld啟航——資料庫連線字串的困惑資料庫字串
- [20190102]連線串不配置服務名能連線資料庫嗎.txt資料庫
- DRCP總結
- 11g文件學習----sql連線SQL
- 利用flashback將Dataguard備庫啟用可讀寫
- ORACLE 11G 無法連線到資料庫例項故障排除Oracle資料庫
- DG-在主庫/備庫刪除已經應用過的日誌.txt