先说一下为什么要写这个异机完全恢复的blog，作为一个年轻的DBA，相信大多数都是备份长做而恢复不常做，我会经常担心我的生产库的服务器挂了怎么办，我的备份完好，但我们又没有容灾，我能否保证我的数据不丢失呢，所以就要rman利用备份重建，而如果此时我能找到完好无损的在线日志，我是不是可以完全恢复了呢，理论上大家都说可以，确从未在网上见到过完全恢复的事例（完全其实很简单，只是网上阐述了太多的不完全恢复，会让我觉得set until time等等是必备的其中一步），甚至oracle的官方网文档上也使用了“SET UNTIL SCN 123456;” 的案例，有鉴于此，我做了这个实验，并记录下来，跟大家分享，不见得这篇原创有多高深的技术和见解O(∩_∩)O
--手动0级全库备份 run { CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUPTYPETO BACKUPSET; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+DATA/backup/ctl_atuo_%F'; ALLOCATE CHANNEL c1 TYPE DISK; ALLOCATE CHANNEL c2 TYPE DISK; CROSSCHECK ARCHIVELOG ALL; BACKUP INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA/backup/db_%U' TAG 'db_rman'; SQL 'ALTERSYSTEMARCHIVELOGCURRENT'; BACKUP ARCHIVELOG ALL FORMAT '+DATA/backup/arc_%U' TAG 'ARC_rman' DELETE INPUT; DELETE NOPROMPT OBSOLETE; RELEASE CHANNEL c1; RELEASE CHANNEL c2; }
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 9 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 10 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 11 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221650 12 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN 13 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN 14 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221703
startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmin.ora'
starting Oracle instance without parameter file for retrieval of spfile Oracle instance started
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 9 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 10 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 11 B F A DISK 17-SEP-15 1 1 NO TAG20150917T221650 12 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN 13 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
Starting implicit crosscheck copy at18-SEP-15 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 Finished implicit crosscheck copy at18-SEP-15
searching forall files in the recovery area cataloging files... no files cataloged
using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 crosschecked backup piece: foundto be 'EXPIRED' backup piece handle=+DATA/backup/db_0aqhdno6_1_1 RECID=9 STAMP=890691334 crosschecked backup piece: foundto be 'EXPIRED' backup piece handle=+DATA/backup/db_09qhdno6_1_1 RECID=10 STAMP=890691334 crosschecked backup piece: foundto be 'EXPIRED' backup piece handle=+DATA/backup/ctl_atuo_c-2496627597-20150917-00 RECID=11 STAMP=890691411 crosschecked backup piece: foundto be 'EXPIRED' backup piece handle=+DATA/backup/arc_0dqhdnqq_1_1 RECID=12 STAMP=890691418 crosschecked backup piece: foundto be 'EXPIRED' backup piece handle=+DATA/backup/arc_0cqhdnqq_1_1 RECID=13 STAMP=890691418 Crosschecked 5 objects
RMAN> listbackup;
List of BackupSets ===================
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Incr 0433.65M DISK 00:01:1017-SEP-15 BP Key: 9Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_0aqhdno6_1_1 ListofDatafilesinbackupset9 File LV Type Ckp SCN Ckp TimeName ---- -- ---- ---------- --------- ---- 20 Incr 113804317-SEP-15 +DATA/min/datafile/sysaux.257.854775097 30 Incr 113804317-SEP-15 +DATA/min/datafile/undotbs1.258.854775097
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Incr 0638.84M DISK 00:01:1017-SEP-15 BP Key: 10Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_09qhdno6_1_1 ListofDatafilesinbackupset10 File LV Type Ckp SCN Ckp TimeName ---- -- ---- ---------- --------- ---- 10 Incr 113804217-SEP-15 +DATA/min/datafile/system.256.854775095 40 Incr 113804217-SEP-15 +DATA/min/datafile/users.259.854775097
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11Full9.36M DISK 00:00:0117-SEP-15 BP Key: 11Status: EXPIRED Compressed: NO Tag: TAG20150917T221650 Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00 SPFILE Included: Modificationtime: 17-SEP-15 SPFILE db_unique_name: MIN Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15
BS KeySize Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 122.00K DISK 00:00:0017-SEP-15 BP Key: 12Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0dqhdnqq_1_1
BS KeySize Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1330.99M DISK 00:00:0217-SEP-15 BP Key: 13Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0cqhdnqq_1_1
specification does not match any archived log in the repository
RMAN> report obsolete;
RMAN retention policy will be applied to the command RMAN retention policy is settorecoverywindowof7days no obsolete backups found
RMAN> list expired backup;
List of BackupSets ===================
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Incr 0433.65M DISK 00:01:1017-SEP-15 BP Key: 9Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_0aqhdno6_1_1 ListofDatafilesinbackupset9 File LV Type Ckp SCN Ckp TimeName ---- -- ---- ---------- --------- ---- 20 Incr 113804317-SEP-15 +DATA/min/datafile/sysaux.257.854775097 30 Incr 113804317-SEP-15 +DATA/min/datafile/undotbs1.258.854775097
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Incr 0638.84M DISK 00:01:1017-SEP-15 BP Key: 10Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_09qhdno6_1_1 ListofDatafilesinbackupset10 File LV Type Ckp SCN Ckp TimeName ---- -- ---- ---------- --------- ---- 10 Incr 113804217-SEP-15 +DATA/min/datafile/system.256.854775095 40 Incr 113804217-SEP-15 +DATA/min/datafile/users.259.854775097
BS KeyType LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11Full9.36M DISK 00:00:0117-SEP-15 BP Key: 11Status: EXPIRED Compressed: NO Tag: TAG20150917T221650 Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00 SPFILE Included: Modificationtime: 17-SEP-15 SPFILE db_unique_name: MIN Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15
BS KeySize Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 122.00K DISK 00:00:0017-SEP-15 BP Key: 12Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0dqhdnqq_1_1
BS KeySize Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1330.99M DISK 00:00:0217-SEP-15 BP Key: 13Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0cqhdnqq_1_1
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 14 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN 15 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 16 B 0 A DISK 17-SEP-15 1 1 NO DB_RMAN 17 B A A DISK 17-SEP-15 1 1 NO ARC_RMAN
--你会发现这是老的控制文件，是我们0级备份里的，其中关于redo的状态记录，当前redo还停留在序列号22 SQL> selectGROUP#,SEQUENCE# ,STATUS,ARCHIVED from v$log;
GROUP# SEQUENCE# STATUS ARC ---------- ---------- ---------------- --- 122CURRENTNO 321 ACTIVE YES 220 ACTIVE YES
SQL> desc v$logfile; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3)
--redo的记录还是源库的信息，一会儿恢复的时候要setnewname SQL> col member for a80 SQL> set line 200 SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER ---------- ------- -------------------------------------------------------------------------------- 3 +DATA/min/onlinelog/group_3.266.854775193 3 +DATA/min/onlinelog/group_3.267.854775195 2 +DATA/min/onlinelog/group_2.264.854775189 2 +DATA/min/onlinelog/group_2.265.854775191 1 +DATA/min/onlinelog/group_1.262.854775185 1 +DATA/min/onlinelog/group_1.263.854775187
RMAN> RUN 2> { 3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK; 4> ALLOCATE CHANNEL c2 DEVICE TYPE DISK; 5> set archivelog destination to "/u01/app/oracle/archived_log"; 6> set newname for datafile 1 to "/u01/app/oracle/oradata/min/system01.dbf"; 7> set newname for datafile 2 to "/u01/app/oracle/oradata/min/sysaux01.dbf"; 8> set newname for datafile 3 to "/u01/app/oracle/oradata/min/undotbs01.dbf"; 9> set newname for datafile 4 to "/u01/app/oracle/oradata/min/users01.dbf"; 10> set newname for tempfile 1 to "/u01/app/oracle/oradata/min/temp01.dbf"; 11> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.262.854775185'' to ''/u01/app/oracle/oradata/min/redo1a.log'' "; 12> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_1.263.854775187'' to ''/u01/app/oracle/oradata/min/redo1b.log'' "; 13> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.264.854775189'' to ''/u01/app/oracle/oradata/min/redo2a.log'' "; 14> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_2.265.854775191'' to ''/u01/app/oracle/oradata/min/redo2b.log'' "; 15> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.266.854775193'' to ''/u01/app/oracle/oradata/min/redo3a.log'' "; 16> SQL "ALTER DATABASE RENAME FILE ''+DATA/min/onlinelog/group_3.267.854775195'' to ''/u01/app/oracle/oradata/min/redo3b.log'' "; 17> RESTORE DATABASE; 18> SWITCH DATAFILE ALL; 19> switch tempfile all; 20> }
released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 released channel: ORA_DISK_4 allocated channel: c1 channel c1: SID=1146 device type=DISK