最近在viadeazhu的空间里面看到的，不错
转自http://space.itpub.net/15415488/viewspace-615573
当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.
