DataGuard備庫ORA-01196故障恢複一則，dataguardora-01196

DataGuard備庫ORA-01196故障恢複一則，dataguardora-01196

問題現象

在使用shutdown abort停DataGuard備庫後，備庫不能open，報ORA-01196錯誤。

 

具體如下：

 

發現一備庫不能應用日誌，查看備庫日誌沒發現報錯，懷疑是備庫應用Log Service停止，於是嘗試重啟備庫；

可能因為備庫是讀業務比較繁忙，在shutdown immediate關閉備庫時等時間過長，於是使用了shutdown abort命令；

但後面在啟動備庫時發生報錯，造成資料檔案損壞，控制檔案和資料檔案的scn號不一致。

 

--啟動備庫時報錯

SQL> startup

ORACLE 常式已經啟動。

 

Total System Global Area 2.0310E+10 bytes

Fixed Size                  2235256 bytes

Variable Size            9328133256 bytes

Database Buffers         1.0939E+10 bytes

Redo Buffers               40894464 bytes

資料庫裝載完畢。

 

ORA-10458: standby database requiresrecovery

ORA-01196: 檔案 1 由於介質恢複會話失敗而不一致

ORA-01110: 資料檔案 1:'+DATA/htdb5/datafile/system.261.759082693'

 

 

--查看日誌

alter database open

Data Guard Brokerinitializing...

Data Guard Brokerinitialization complete

Beginning standby crash recovery.

Serial Media Recovery started

Managed Standby Recoverystarting Real Time Apply

Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180068.1541.885192077

Thu Jul 16 12:00:47 2015

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-01013: 使用者請求取消當前的操作

ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: 資料檔案 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-00339: 歸檔日誌未包含任何重做

ORA-00334: 歸檔日誌: '+DATA/htdb5/onlinelog/group_2.280.759082845'

ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: 資料檔案 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc  (incident=116743):

ORA-00600: 內部錯誤碼, 參數: [3020],[47], [1187724], [198320012], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: 資料檔案 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837

Incident details in:/u01/app/ora11g/diag/rdbms/htdb5/htdb5/incident/incdir_116743/htdb5_ora_10154_i116743.trc

Use ADRCI or Support Workbenchto package the incident.

See Note 411.1 at My OracleSupport for error and packaging details.

Standby crash recovery aborteddue to error 600.

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-00600: 內部錯誤碼, 參數: [3020],[47], [1187724], [198320012], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistentwith data block (file# 47, block# 1187724, file offset is 1139900416 bytes)

ORA-10564: tablespace JDYWP_IDX

ORA-01110: 資料檔案 47:'+DATA/htdb5/datafile/jdywp_idx.336.856967805'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 251837

Recovery interrupted!

Some recovered datafiles maybeleft media fuzzy

Media recovery may continue butopen resetlogs may fail

Completed standby crashrecovery.

Errors in file/u01/app/ora11g/diag/rdbms/htdb5/htdb5/trace/htdb5_ora_10154.trc:

ORA-10458: standby databaserequires recovery

ORA-01196: 檔案 1 由於介質恢複會話失敗而不一致

ORA-01110: 資料檔案 1:'+DATA/htdb5/datafile/system.261.759082693'

ORA-10458 signalled during:alter database open...

Thu Jul 16 12:00:49 2015

Sweep [inc][116743]: completed

Sweep [inc2][116743]: completed

Thu Jul 16 12:00:49 2015

Dumping diagnostic data indirectory=[cdmp_20150716120049], requested by (instance=1, osid=10154),summary=[incident=116743].

Thu Jul 16 12:01:50 2015

 

 

解決辦法：

 

把備庫閃回到正常的狀態的時點。

 

--前提資料庫閃回之前已經開啟

 

SQL> select FLASHBACK_ON from v$database;

 

FLASHBACK_ON

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

YES

 

 

SQL> Flashback database to timestamp to_timestamp('2015-07-16 4:00:05','yyyy-mm-ddhh24:mi:ss');

--或是使用Flashbackdatabase to scn 947921

 

SQL> alter database open;

 

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY

 

--啟動即時應用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY WITH APPLY

 

--查看日誌看到日誌已經從閃回的時點開始應用

Thu Jul 16 13:36:01 2015

Flashback database to timestampto_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')

Flashback Restore Start

Thu Jul 16 13:39:30 2015

Flashback Restore Complete

Flashback Media Recovery Start

 started logmerger process

Parallel Media Recovery startedwith 16 slaves

Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180047.2212.885180637

Thu Jul 16 13:41:54 2015

Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180061.2611.885182343

Thu Jul 16 13:42:04 2015

Flashback Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537

Thu Jul 16 13:42:12 2015

Incomplete Recovery applieduntil change 71489772016 time 07/16/2015 04:00:06

Flashback Media RecoveryComplete

Completed: Flashback databaseto timestamp to_timestamp('2015-07-16 4:00:05','yyyy-mm-dd hh24:mi:ss')

Thu Jul 16 13:43:25 2015

Deleted Oracle managed file+FRA/htdb5/archivelog/2015_07_15/thread_1_seq_179690.2885.885083087

Thu Jul 16 13:43:25 2015

Standby controlfile consistentwith primary

RFS[3]: Selected log 8 forthread 1 sequence 180122 dbid 1083719948 branch 759079182

Archived Log entry 180115 addedfor thread 1 sequence 180121 ID 0x40a48484 dest 1:

Thu Jul 16 13:45:41 2015

alter database open

Data Guard Brokerinitializing...

Data Guard Brokerinitialization complete

 

SMON: enabling cache recovery

Dictionary check beginning

Dictionary check complete

Database Characterset isZHS16GBK

No Resource Manager plan active

replication_dependency_trackingturned off (no async multimaster replication found)

Physical standby databaseopened for read only access.

Completed: alter database open

Thu Jul 16 13:45:44 2015

ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE  THROUGH ALL SWITCHOVERDISCONNECT  USING CURRENT LOGFILE

Attempt to start backgroundManaged Standby Recovery process (htdb5)

Thu Jul 16 13:45:44 2015

MRP0 started with pid=51, OSid=14743

MRP0: Background ManagedStandby Recovery process started (htdb5)

 started logmerger process

Thu Jul 16 13:45:50 2015

Managed Standby Recoverystarting Real Time Apply

Parallel Media Recovery startedwith 16 slaves

Waiting for all non-currentORLs to be archived...

All non-current ORLs have beenarchived.

Media Recovery Log +FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180062.2861.885182537

Completed: ALTER DATABASERECOVER MANAGED STANDBY DATABASE  THROUGHALL SWITCHOVER DISCONNECT  USING CURRENTLOGFILE

Thu Jul 16 13:46:08 2015

Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180063.3683.885182777

Thu Jul 16 13:46:35 2015

Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180064.2542.885183119

Thu Jul 16 13:47:07 2015

Media Recovery Log+FRA/htdb5/archivelog/2015_07_16/thread_1_seq_180065.2717.885183615

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。