先说一下为什么要写这个异机完全恢复的blog，作为一个年轻的DBA，相信大多数都是备份长做而恢复不常做，我会经常担心我的生产库的服务器挂了怎么办，我的备份完好，但我们又没有容灾，我能否保证我的数据不丢失呢，所以就要rman利用备份重建，而如果此时我能找到完好无损的在线日志，我是不是可以完全恢复了呢，理论上大家都说可以，确从未在网上见到过完全恢复的事例（完全其实很简单，只是网上阐述了太多的不完全恢复，会让我觉得set until time等等是必备的其中一步），甚至oracle的官方网文档上也使用了“SET UNTIL SCN 123456;” 的案例，有鉴于此，我做了这个实验，并记录下来，跟大家分享，不见得这篇原创有多高深的技术和见解O(∩_∩)O
--手动0级全库备份 run { CONFIGURE RETENTION POLICY TO RECOVERY WINDOWOF7 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO 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'ALTER SYSTEM ARCHIVE LOG CURRENT'; 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-1511NO DB_RMAN 10 B 0 A DISK 17-SEP-1511NO DB_RMAN 11 B F A DISK 17-SEP-1511NO TAG20150917T221650 12 B A A DISK 17-SEP-1511NO ARC_RMAN 13 B A A DISK 17-SEP-1511NO ARC_RMAN 14 B F A DISK 17-SEP-1511NO TAG20150917T221703
startup failed: ORA-01078: failure in processing system parameters LRM-00109: could notopenparameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmin.ora'
starting Oracle instance withoutparameter 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-1511NO DB_RMAN 10 B 0 A DISK 17-SEP-1511NO DB_RMAN 11 B F A DISK 17-SEP-1511NO TAG20150917T221650 12 B A A DISK 17-SEP-1511NO ARC_RMAN 13 B A A DISK 17-SEP-1511NO ARC_RMAN
Starting implicit crosscheck copyat18-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 copyat18-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: found to be 'EXPIRED' backup piece handle=+DATA/backup/db_0aqhdno6_1_1 RECID=9 STAMP=890691334 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/backup/db_09qhdno6_1_1 RECID=10 STAMP=890691334 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/backup/ctl_atuo_c-2496627597-20150917-00 RECID=11 STAMP=890691411 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/backup/arc_0dqhdnqq_1_1 RECID=12 STAMP=890691418 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/backup/arc_0cqhdnqq_1_1 RECID=13 STAMP=890691418 Crosschecked 5 objects
RMAN> list backup;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Incr 0433.65M DISK 00:01:1017-SEP-15 BP Key: 9 Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_0aqhdno6_1_1 List of Datafiles in backup set9 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 20 Incr 113804317-SEP-15+DATA/min/datafile/sysaux.257.854775097 30 Incr 113804317-SEP-15+DATA/min/datafile/undotbs1.258.854775097
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Incr 0638.84M DISK 00:01:1017-SEP-15 BP Key: 10 Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_09qhdno6_1_1 List of Datafiles in backup set10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 10 Incr 113804217-SEP-15+DATA/min/datafile/system.256.854775095 40 Incr 113804217-SEP-15+DATA/min/datafile/users.259.854775097
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11Full9.36M DISK 00:00:0117-SEP-15 BP Key: 11 Status: EXPIRED Compressed: NO Tag: TAG20150917T221650 Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00 SPFILE Included: Modification time: 17-SEP-15 SPFILE db_unique_name: MIN Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 122.00K DISK 00:00:0017-SEP-15 BP Key: 12 Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0dqhdnqq_1_1
List of Archived Logs in backup set12 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 121113836417-SEP-15113837217-SEP-15
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1330.99M DISK 00:00:0217-SEP-15 BP Key: 13 Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0cqhdnqq_1_1
List of Archived Logs in backup set13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 120112722017-SEP-15113836417-SEP-15
RMAN> list archivelog all;
specification does notmatchany archived log in the repository
RMAN> report obsolete;
RMAN retention policy will be applied to the command RMAN retention policy issetto recovery windowof7 days no obsolete backups found
RMAN> list expired backup;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Incr 0433.65M DISK 00:01:1017-SEP-15 BP Key: 9 Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_0aqhdno6_1_1 List of Datafiles in backup set9 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 20 Incr 113804317-SEP-15+DATA/min/datafile/sysaux.257.854775097 30 Incr 113804317-SEP-15+DATA/min/datafile/undotbs1.258.854775097
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Incr 0638.84M DISK 00:01:1017-SEP-15 BP Key: 10 Status: EXPIRED Compressed: NO Tag: DB_RMAN Piece Name: +DATA/backup/db_09qhdno6_1_1 List of Datafiles in backup set10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 10 Incr 113804217-SEP-15+DATA/min/datafile/system.256.854775095 40 Incr 113804217-SEP-15+DATA/min/datafile/users.259.854775097
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11Full9.36M DISK 00:00:0117-SEP-15 BP Key: 11 Status: EXPIRED Compressed: NO Tag: TAG20150917T221650 Piece Name: +DATA/backup/ctl_atuo_c-2496627597-20150917-00 SPFILE Included: Modification time: 17-SEP-15 SPFILE db_unique_name: MIN Control File Included: Ckp SCN: 1138347 Ckp time: 17-SEP-15
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 122.00K DISK 00:00:0017-SEP-15 BP Key: 12 Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0dqhdnqq_1_1
List of Archived Logs in backup set12 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 121113836417-SEP-15113837217-SEP-15
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1330.99M DISK 00:00:0217-SEP-15 BP Key: 13 Status: EXPIRED Compressed: NO Tag: ARC_RMAN Piece Name: +DATA/backup/arc_0cqhdnqq_1_1
List of Archived Logs in backup set13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 120112722017-SEP-15113836417-SEP-15
RMAN>delete noprompt expired backup;
using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4
List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 9911 EXPIRED DISK +DATA/backup/db_0aqhdno6_1_1 101011 EXPIRED DISK +DATA/backup/db_09qhdno6_1_1 111111 EXPIRED DISK +DATA/backup/ctl_atuo_c-2496627597-20150917-00 121211 EXPIRED DISK +DATA/backup/arc_0dqhdnqq_1_1 131311 EXPIRED DISK +DATA/backup/arc_0cqhdnqq_1_1 deleted backup piece backup piece handle=+DATA/backup/db_0aqhdno6_1_1 RECID=9 STAMP=890691334 deleted backup piece backup piece handle=+DATA/backup/db_09qhdno6_1_1 RECID=10 STAMP=890691334 deleted backup piece backup piece handle=+DATA/backup/ctl_atuo_c-2496627597-20150917-00 RECID=11 STAMP=890691411 deleted backup piece backup piece handle=+DATA/backup/arc_0dqhdnqq_1_1 RECID=12 STAMP=890691418 deleted backup piece backup piece handle=+DATA/backup/arc_0cqhdnqq_1_1 RECID=13 STAMP=890691418 Deleted 5 EXPIRED objects
RMAN> list backup;
specification does notmatchany backup in the repository
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 14 B A A DISK 17-SEP-1511NO ARC_RMAN 15 B 0 A DISK 17-SEP-1511NO DB_RMAN 16 B 0 A DISK 17-SEP-1511NO DB_RMAN 17 B A A DISK 17-SEP-1511NO ARC_RMAN
RMAN-06139: WARNING: control file isnotcurrentfor REPORT SCHEMA Report of database schema for database with db_unique_name MIN
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 10SYSTEM***+DATA/min/datafile/system.256.854775095 20 SYSAUX ***+DATA/min/datafile/sysaux.257.854775097 30 UNDOTBS1 ***+DATA/min/datafile/undotbs1.258.854775097 40 USERS ***+DATA/min/datafile/users.259.854775097
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 120 TEMP 32767+DATA/min/tempfile/temp.268.854775211
--你会发现这是老的控制文件，是我们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 memberfor a80 SQL>set line 200 SQL>selectGROUP#,STATUS,MEMBERfrom 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 1to "/u01/app/oracle/oradata/min/system01.dbf"; 7>set newname for datafile 2to "/u01/app/oracle/oradata/min/sysaux01.dbf"; 8>set newname for datafile 3to "/u01/app/oracle/oradata/min/undotbs01.dbf"; 9>set newname for datafile 4to "/u01/app/oracle/oradata/min/users01.dbf"; 10>set newname for tempfile 1to "/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
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure ofalter db command at09/18/201501:04:41 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--看一下此时控制文件中的日志序列号，会发现没有前推，必须用resetlogs打开数据库 SQL>selectgroup#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE# ---------- ---------------- ---------- 1CURRENT22 3 ACTIVE 21 2 ACTIVE 20 RMAN>alter database open resetlogs;