利用incremental backup恢復丟失日誌的standby

當standby已經丟失archive log之後怎麼辦？

除了重建還有什麼辦法？答案就是利用incremental backup前滾standby。

這個feature早就在官檔上作為場景出現過，以前一直沒有注意也沒有看到，最近在生產資料庫上做了一次這樣的恢復。

需要注意的地方：

1.在10g的官檔上面miss掉的一個步驟是：需要重建standby的control file。否則在recover新的archive log時報錯。

（其實我們認為Oracle其實應該直接讀取v$datafile_header的checkpoint_change#來進行選擇該應用哪個日誌。因為重建standby control file是沒有必要的操作。希望在將來的12G時可以不用重建standby control file，因為11G的官檔上已經加了這一步：restore controlfile..sigh）

2.如果在丟失的日誌裡有新加的datafile，需要提前在standby上create一個空的出來，否則在應用incremental backup時報錯。

3.我們需要估算incremental backup的大小，因為如果丟失過多的日誌，增量備份時有可能很大的。對於整個incremental backup sets，Oracle預設是將3個datafiles的change放入一個incremental backup檔案中的，我想是便於我們儲存、搬取和傳輸。

 

以下post一個在測試資料庫上做的例子：

1. Disaster happens
on pirmary:
SQL> alter tablespace data01 add datafile '/xxx/xxx/xxx/haozhu/data/data01_07.dbf' size 1m;

Tablespace altered.

SQL> alter system switch logfile;

System altered.

\mv haozhu_365.1_690165123_arc deletedlog3

2. on primary, recreate the standby controlfile.
SQL> alter database create standby controlfile as '/tmp/ctl_hao_1.ctl';

Database altered.

3. on standby , check current scn and shutdown
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
  213879349

SQL> shutdown immediate;

4. on primary, transfer the new control file to standby.
rcp /tmp/ctl_hao_1.ctl xxx.xxx.xxx.com:/xxx/xxx/data01/haozhu/control/ctl_hao_1.ctl
rcp /tmp/ctl_hao_1.ctl xxx.xxx.xxx.com:/xx/xxx/data01/haozhu/control/ctl_hao_2.ctl

5. on primary, check the newly created datafile.

SQL>select FILE#,name from v$datafile where CREATION_CHANGE#>=213879349;
   
     FILE#                                                                                                                                                  
----------                                                                                                                                                  
NAME                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------
        14                                                                                                                                                  
/xxx/xxx/data01/haozhu/data/data01_07.dbf                      

6. on standby, startup using the newly created control file.
  
SQL> startup mount

    rename files accordingly and skip the newly created datafiles in the gap.
   
SQL> select 'alter database rename file '''||name||''' to ''/xxx/xxx/xxx/haozhu/data/'||substr(name,32,length(name))||''';'
  2  from v$datafile where file#<>14;

'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/xxx/xxx/xxx/HAOZHU/DATA/'||SUBSTR(NAME,32,LENGTH(NAME))||''';'
-----------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/oracle/xxx/data01/haozhu/data/system01.dbf' to '/oracle/xxx/data01/haozhu/data/system01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/undotest.dbf' to '/oracle/xxx/data01/haozhu/data/undotest.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/sysaux01.dbf' to '/oracle/xxx/data01/haozhu/data/sysaux01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/data01_01.dbf' to '/oracle/xxx/data01/haozhu/data/data01_01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/data01_04.dbf' to '/oracle/xxx/data01/haozhu/data/data01_04.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/data01_05.dbf' to '/oracle/xxx/data01/haozhu/data/data01_05.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/undo01_01.dbf' to '/oracle/xxx/data01/haozhu/data/undo01_01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/test32k_01.dbf' to '/oracle/xxx/data01/haozhu/data/test32k_01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/wwf_01.dbf' to '/oracle/xxx/data01/haozhu/data/wwf_01.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/data01_02.dbf' to '/oracle/xxx/data01/haozhu/data/data01_02.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/data01_03.dbf' to '/oracle/xxx/data01/haozhu/data/data01_03.dbf';
alter database rename file '/oracle/xxx/data01/haozhu/data/alex_01.dbf' to '/oracle/xxx/data01/haozhu/data/alex_01.dbf';
                      
7. on primary, create the incremental backup since the SCN recorded on standby:
rman target /
BACKUP INCREMENTAL FROM SCN 213879349 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY';
                      
rcp /tmp/ForStandby_* xxx.xxx.xxx.com:/oracle/xxx/data01/haozhu/archive
         
8. on standby , recreate the newly created datafile and apply the incremental backup
SQL> alter database create datafile 14 as '/oracle/xxx/data01/haozhu/data/data01_data07.dbf';

Database altered.

rman target / nocatalog
RMAN> CATALOG START WITH '/oracle/xxx/data01/haozhu/archive/ForStandby';

searching for all files that match the pattern /oracle/xxx/data01/haozhu/archive/ForStandby

List of Files Unknown to the Database
=====================================
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0ekpglmn_1_1
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0fkpgloe_1_1
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0ekpglmn_1_1
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0fkpgloe_1_1
File Name: /oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 17-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2193 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oracle/xxx/data01/haozhu/data/system01.dbf
destination for restore of datafile 00002: /oracle/xxx/data01/haozhu/data/undotest.dbf
destination for restore of datafile 00003: /oracle/xxx/data01/haozhu/data/sysaux01.dbf
destination for restore of datafile 00004: /oracle/xxx/data01/haozhu/data/data01_01.dbf
destination for restore of datafile 00005: /oracle/xxx/data01/haozhu/data/data01_04.dbf
destination for restore of datafile 00006: /oracle/xxx/data01/haozhu/data/data01_05.dbf
destination for restore of datafile 00007: /oracle/xxx/data01/haozhu/data/undo01_01.dbf
destination for restore of datafile 00009: /oracle/xxx/data01/haozhu/data/wwf_01.dbf
destination for restore of datafile 00010: /oracle/xxx/data01/haozhu/data/data01_02.dbf
destination for restore of datafile 00011: /oracle/xxx/data01/haozhu/data/data01_03.dbf
destination for restore of datafile 00012: /oracle/xxx/data01/haozhu/data/alex_01.dbf
destination for restore of datafile 00014: /oracle/xxx/data01/haozhu/data/data01_data07.dbf
channel ORA_DISK_1: reading from backup piece /oracle/xxx/data01/haozhu/archive/ForStandby_0ekpglmn_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/xxx/haozhu/archive/ForStandby_0ekpglmn_1_1 tag=FOR STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /oracle/xxx/data01/haozhu/data/test32k_01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1 tag=FOR STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 17-SEP-09

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_08kpgiif_1_1
2       2       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0bkpgktm_1_1
3       3       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0ckpgkve_1_1
4       4       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0dkpgkvf_1_1
5       5       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0ekpglmn_1_1
6       6       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0fkpgloe_1_1
7       7       1   1   AVAILABLE   DISK        /oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_08kpgiif_1_1 recid=1 stamp=697849191
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0bkpgktm_1_1 recid=2 stamp=697849191
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0ckpgkve_1_1 recid=3 stamp=697849191
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0dkpgkvf_1_1 recid=4 stamp=697849191
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0ekpglmn_1_1 recid=5 stamp=697849692
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0fkpgloe_1_1 recid=6 stamp=697849692
deleted backup piece
backup piece handle=/oracle/xxx/data01/haozhu/archive/ForStandby_0gkpglog_1_1 recid=7 stamp=697849692
Deleted 7 objects

9.check if recover is OK.
on primary,transfer the new logs:
rcp haozhu_36[6-8].1_690165123_arc xxx.xxx.xxx.com:/oracle/xxx/data01/haozhu/archive

on standby:
alter database recover automatic standby database
Thu Sep 17 23:13:19 2009
Media Recovery Start
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 3 processes
Thu Sep 17 23:13:19 2009
Media Recovery Log /oracle/xxx/data01/haozhu/archive/haozhu_366.1_690165123_arc
Thu Sep 17 23:13:22 2009
Media Recovery Log /oracle/xxx/data01/haozhu/archive/haozhu_367.1_690165123_arc
Thu Sep 17 23:13:22 2009
Media Recovery Log /oracle/xxx/data01/haozhu/archive/haozhu_368.1_690165123_arc
Thu Sep 17 23:13:22 2009

SQL> alter database open read only;

Database altered.