Oracle DataGuard歸檔日誌丟失處理方法

問題描述

9.3日dg備庫異常關閉，9.17日主庫磁碟空間滿，刪除了歸檔日誌，導致備庫丟失了9.3號到9.17號得歸檔日誌，所以當9.24日啟動備庫時報錯

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: '/u01/oracle/oradata/system/system01.dbf'

解決辦法：

1、在主庫備份控制檔案

backup current controlfile for standby format '/archivelog/0924_ctl_%U.bka'

2、已備庫得最大SCN號在主庫備份

在備庫上執行

select name,to_char(checkpoint_change#) from v$datafile order by checkpoint_change#;

NAME                                                        TO_CHAR(CHECKPOINT_CHANGE#)

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

/u01/app/oracle/oradata/TBSP_STAT_DATA_202009_01.dbf  446416908432

3、在主庫上執行備份語句

rman target /

run

{

allocate channel c1 device type disk; 

 allocate channel c2 device type disk;

 allocate channel c3 device type disk;

 allocate channel c4 device type disk; 

 allocate channel c5 device type disk;

  backup as compressed backupset incremental from scn 446416908432 database format '/archivelog/0924_%U.bka' tag 'forstandby';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

}

4、把生成得備份檔案都傳到備庫上

scp 0924* x.x.x.x:/tmp/

5、 這時候我們需要關閉備庫,然把把例項啟動到nomount關態


STANDBY> shutdown immediate;
ORA-01109: database not open




Database dismounted.
ORACLE instance shut down.
STANDBY> startup nomount;
ORACLE instance started.


Total System Global Area 1224736768 bytes
Fixed Size    2923824 bytes
Variable Size  939524816 bytes
Database Buffers  268435456 bytes
Redo Buffers   13852672 bytes

6、在備庫上執行restore,先恢復控制檔案，在恢復資料檔案

rman target / nocatalog


RMAN> restore standby controlfile from '/tmp/0924_ctl_0rr22q7u_1_1.dbf';


Starting restore at 03-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
............................................


很快控制檔案就了恢復完成了，我們開啟另一個視窗，通過sqlplus把備庫以standby的方式mount起來


STANDBY> alter database mount;

恢復資料檔案

先註冊一下備份檔案

rman> catalog start with '/tmp';

開始恢復

run

{

allocate channel c1 device type disk; 

allocate channel c2 device type disk;

allocate channel c3 device type disk;

allocate channel c4 device type disk; 

allocate channel c5 device type disk;

recover database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

}

7、啟動備庫同步
SQL> alter database open read only;
Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.

 select process,client_process,sequence#,status from v$managed_standby;


PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH  ARCH    0 CONNECTED
ARCH  ARCH    0 CONNECTED
ARCH  ARCH    0 CONNECTED
ARCH  ARCH    0 CONNECTED
ARCH  ARCH    0 CONNECTED
RFS  ARCH    0 IDLE
RFS  LGWR   33 IDLE
MRP0  N/A   33 WAIT_FOR_LOG


現在我們檢視備庫狀態，MRP程式已經開始最新的日誌應用了.到此我們通過增量SCN備份和恢復來修復
備庫歸檔日誌丟失的過程已經全部完成了.