Logon database Tiger引起DataGuard不同步問題，tigerdataguard

最後更新：2014-10-24 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

當DataGuard升級CPU後，發現主備庫不能同步了，日誌報錯，所以在主庫的執行的升級的後指令碼“@catbundle.sql cpu apply”不能同步到備庫

現象：

broker狀態：

[oracle@webdb2 ~]$ dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0 -64bit Production

歡迎使用 DGMGRL, 要擷取有關資訊請鍵入 "help"。

已串連。

DGMGRL> show configuration

配置 - webdb

保護模式: MaxAvailability

資料庫:

webdb - 主要資料庫

webdb2 - 物理備用資料庫

快速啟動容錯移轉: DISABLED

配置狀態:

ORA-16664: 無法從資料庫接收結果

DGM-17017: 無法確定配置狀態

--查看主庫狀態：

SQL> Select timestamp, message FROMV$DATAGUARD_STATUS Where Rownum < 5;

TIMESTAMP MESSAGE

-------------------------------------------------------------------------------------------

2014-10-23 Error 604 received logging on to the standby

2014-10-23 PING[ARC2]: Heartbeat failed to connect tostandby'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'.Error is 604.

2014-10-23 Error 604 received logging on to the standby

2014-10-23 PING[ARC2]: Heartbeat failed to connect to standby'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'.Error is 604.

--此時查看日誌

主庫日誌：

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

Thu Oct 23 00:25:52 2014

Errors in file/u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_18833.trc:

ORA-00604: 遞迴 SQL層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

Thu Oct 23 00:25:52 2014

Errors in file/u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_18842.trc:

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

Thu Oct 23 00:25:53 2014

Error 604 receivedlogging on to the standby

PING[ARC2]: Heartbeatfailed to connect to standby'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'.Error is 604.

Thu Oct 23 00:26:12 2014

Errors in file /u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_18961.trc:

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

備庫日誌：

ORA-00604: 遞迴 SQL層級 1 出現錯誤

ORA-16000: 開啟資料庫以進行唯讀訪問

ORA-06512: 在 line 3

Thu Oct 23 00:27:39 2014

Errors in file /u01/app/ora11g/diag/rdbms/webdb2/webdb2/trace/webdb2_ora_30130.trc:

ORA-00604: 遞迴 SQL層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

Errors in file/u01/app/ora11g/diag/rdbms/webdb2/webdb2/trace/webdb2_ora_30130.trc:

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-16000: 開啟資料庫以進行唯讀訪問

ORA-06512: 在 line 3

--進一步查看trace檔案

主庫trace：

[oracle@webdb1 trace]$ more/u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_8589.trc

Trace file/u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_8589.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME =/u01/app/ora11g/product/11.2.0/db_1

System name: Linux

Node name: webdb1

Release: 2.6.32-200.13.1.el5uek

Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011

Machine: x86_64

Instance name: webdb

Redo thread mounted by this instance: 1

Oracle process number: 176

Unix process pid: 8589, image:oracle@webdb1

*** 2014-10-14 13:19:18.860

*** SESSION ID:(21.12621) 2014-10-1413:19:18.860

*** CLIENT ID:() 2014-10-14 13:19:18.860

*** SERVICE NAME:(SYS$USERS) 2014-10-1413:19:18.860

*** MODULE NAME:(emagent@webdb1 (TNSV1-V3)) 2014-10-14 13:19:18.860

*** ACTION NAME:() 2014-10-14 13:19:18.860

Error in executing triggers on connectinternal

*** 2014-10-14 13:19:18.861

dbkedDefDump(): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-00604: 遞迴 SQL層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 28

Trace file/u01/app/ora11g/diag/rdbms/webdb/webdb/trace/webdb_ora_8589.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME =/u01/app/ora11g/product/11.2.0/db_1

System name: Linux

Node name: webdb1

Release: 2.6.32-200.13.1.el5uek

Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011

Machine: x86_64

Instance name: webdb

Redo thread mounted by this instance: 1

Oracle process number: 163

Unix process pid: 8589, image:oracle@webdb1

備庫trace:

[oracle@webdb2 trace]$ more/u01/app/ora11g/diag/rdbms/webdb2/webdb2/trace/webdb2_ora_29791.trc

Trace file/u01/app/ora11g/diag/rdbms/webdb2/webdb2/trace/webdb2_ora_29791.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME =/u01/app/ora11g/product/11.2.0/db_1

System name: Linux

Node name: webdb2

Release: 2.6.32-200.13.1.el5uek

Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011

Machine: x86_64

Instance name: webdb2

Redo thread mounted by this instance: 1

Oracle process number: 22

Unix process pid: 29791, image:oracle@webdb2

*** 2014-10-23 11:30:21.649

*** SESSION ID:(854.10603) 2014-10-2311:30:21.649

*** CLIENT ID:() 2014-10-23 11:30:21.649

*** SERVICE NAME:(SYS$USERS) 2014-10-2311:30:21.649

*** MODULE NAME:(emagent@webdb2 (TNSV1-V3)) 2014-10-23 11:30:21.649

*** ACTION NAME:() 2014-10-23 11:30:21.649

Error in executing triggers on connectinternal

*** 2014-10-23 11:30:21.649

dbkedDefDump(): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

ORA-06512: 在 line 29

Error in executing triggers on connectinternal

*** 2014-10-23 11:30:21.650

dbkedDefDump(): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

----- Error Stack Dump -----

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-16000: 開啟資料庫以進行唯讀訪問

ORA-06512: 在 line 3

分析：

可以從看到trace log中看到都與tiger執行有關，於是想到在主庫是建立過的兩個 logon on database tiger

解決辦法：

--禁用登入tiger

把一個登入IP限制的tiger的禁用後，主庫的

ORA-00604: 遞迴 SQL 層級 1 出現錯誤

ORA-20154: TNS: 監聽程式當前無法識別串連描述符中請求的服務

報錯消失，只剩下下面的報錯：

Error 604 received logging on to thestandby

PING[ARC2]: Heartbeat failed to connect tostandby'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'.Error is 604.

備庫依然報錯

--啟動備庫到mount狀態

SQL> shutdown immediate

SQL> startup mount

ORACLE 常式已經啟動。

Total System Global Area 1.0456E+10 bytes

Fixed Size 2237488 bytes

Variable Size 5435821008 bytes

Database Buffers 4999610368 bytes

Redo Buffers 17973248 bytes

資料庫裝載完畢。

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

--查看日誌

主庫：

Thu Oct 23 13:45:15 2014

Archived Log entry 2225 added for thread 1sequence 1122 ID 0xc8d579d3 dest 1:

ARC3: Standby redo logfile selected forthread 1 sequence 1122 for destination LOG_ARCHIVE_DEST_2

Thu Oct 23 13:46:46 2014

ARC3: Archive log rejected (thread 1sequence 1120) at host'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'

Thu Oct 23 13:46:46 2014

ARC0: Archive log rejected (thread 1sequence 1121) at host'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=webdb2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=webdb2)(SERVER=DEDICATED)))'

FAL[server, ARC3]: FAL archive failed, seetrace file.

ARCH: FAL archive failed. Archivercontinuing

ORACLE Instance webdb - Archival Error.Archiver continuing.

FAL[server, ARC0]: FAL archive failed, seetrace file.

ARCH: FAL archive failed. Archivercontinuing

ORACLE Instance webdb - Archival Error.Archiver continuing.

Thu Oct 23 13:46:49 2014

Destination LOG_ARCHIVE_DEST_2 isSYNCHRONIZED

LGWR: Standby redo logfile selected toarchive thread 1 sequence 1124

LGWR: Standby redo logfile selected forthread 1 sequence 1124 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 1124(LGWR switch)

Current log# 2 seq# 1124 mem# 0:+DATA/webdb/onlinelog/group_2.262.819826783

Current log# 2 seq# 1124 mem# 1:+FRA/webdb/onlinelog/group_2.258.819826785

Archived Log entry 2244 added for thread 1sequence 1123 ID 0xc8d579d3 dest 1

備庫：

Using STANDBY_ARCHIVE_DEST parameterdefault value as USE_DB_RECOVERY_FILE_DEST

Thu Oct 23 13:45:15 2014

RFS[1]: Assigned to RFS process 17293

RFS[1]: Opened log for thread 1 sequence1102 dbid -925543465 branch 819826777

Thu Oct 23 13:45:15 2014

Primary database is in MAXIMUM AVAILABILITYmode

Standby controlfile consistent with primary

RFS[2]: Assigned to RFS process 17297

RFS[2]: Selected log 7 for thread 1sequence 1123 dbid -925543465 branch 819826777

Deleted Oracle managed file+FRA/webdb2/archivelog/2014_10_13/thread_1_seq_1049.350.860808821

Archived Log entry 1104 added for thread 1sequence 1102 rlc 819826777 ID 0xc8d579d3 dest 2:

可以看到主備庫恢複心跳，備庫能恢複應用日誌

開啟備庫操作：

SQL> alter database open;

資料庫已更改。

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE DISCONNECT

第 1 行出現錯誤:

ORA-01153: 啟用了不相容的介質恢複

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

--主庫切換日誌

SQL> alter system switch logfile;

系統已更改。

--查看日誌

主庫：

Thu Oct 23 14:19:52 2014

LGWR: Standby redo logfile selected toarchive thread 1 sequence 1125

LGWR: Standby redo logfile selected forthread 1 sequence 1125 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 1125(LGWR switch)

Current log# 3 seq# 1125 mem# 0:+DATA/webdb/onlinelog/group_3.263.819826787

Current log# 3 seq# 1125 mem# 1:+FRA/webdb/onlinelog/group_3.259.819826789

Thu Oct 23 14:19:53 2014

Archived Log entry 2246 added for thread 1sequence 1124 ID 0xc8d579d3 dest 1:

備庫：

Completed: ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILE

Thu Oct 23 14:15:51 2014

ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE DISCONNECT

ORA-1153 signalled during: ALTER DATABASERECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT...

Thu Oct 23 14:19:52 2014

Standby controlfile consistent with primary

RFS[2]: Selected log 7 for thread 1sequence 1125 dbid -925543465 branch 819826777

Thu Oct 23 14:19:52 2014

Archived Log entry 1123 added for thread 1sequence 1124 ID 0xc8d579d3 dest 1:

Thu Oct 23 14:19:52 2014

Media Recovery Waiting for thread 1sequence 1125 (in transit)

Recovery of Online Redo Log: Thread 1 Group7 Seq 1125 Reading mem 0

Mem# 0: +DATA/webdb2/onlinelog/group_7.282.819884421

Mem# 1: +FRA/webdb2/onlinelog/group_7.278.819884423

可以看到在唯讀應用狀態open備庫後，日誌應用仍然正常

--查看DataGuard狀態

[oracle@webdb1 trace]$ dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0 -64bit Production

歡迎使用 DGMGRL, 要擷取有關資訊請鍵入 "help"。

已串連。

DGMGRL> show configuration

配置 - webdb

保護模式: MaxAvailability

資料庫:

webdb - 主要資料庫

webdb2 - 物理備用資料庫

快速啟動容錯移轉: DISABLED

配置狀態:

SUCCESS

總結：

通過報錯分析看到，主備不同步跟CPU補丁升級沒有直接關係，跟建立的logon on database 觸發器有關，

如果在主要資料建立一個logon on database tiger，可能會引起主備庫不能同步傳輸日誌的問題，

這時，我們要做是把備庫重新mount，然後再open到唯讀應用狀態（READ ONLY WITH APPLY）

因為當備庫重啟後，備庫直接啟動到唯讀應用狀態（READ ONLY WITH APPLY），就會出現上面的問題。

參考：

http://www.cnblogs.com/Spiritual-journey/archive/2012/10/21/2732806.html

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More