RMAN恢复实践
RMAN> list backup;using target database control file instead of recovery catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------1 Full 964.65M DISK 00:02:07 19-SEP-13 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130919T011640 Piece Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 838482 19-SEP-13 /u01/app/oracle/oradata/august/august/system01.dbf 2 Full 838482 19-SEP-13 /u01/app/oracle/oradata/august/august/sysaux01.dbf 3 Full 838482 19-SEP-13 /u01/app/oracle/oradata/august/august/undotbs01.dbf 4 Full 838482 19-SEP-13 /u01/app/oracle/oradata/august/august/users01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------2 Full 9.36M DISK 00:00:08 19-SEP-13 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130919T011640 Piece Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_ncsnf_TAG20130919T011640_93odvkkc_.bkp SPFILE Included: Modification time: 19-SEP-13 SPFILE db_unique_name: AUGUST Control File Included: Ckp SCN: 838535 Ckp time: 19-SEP-13
2. 归档当前日志
SQL> alter system switch logfile; SQL> select sequence#,first_change#,next_change# ,archived from v$archived_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ARCHIV---------- ------------- ------------ ------ 2 757280 767227 YES 3 767227 780016 YES 4 780016 811911 YES 5 811911 835483 YES 6 835483 840784 YES
3. 在新的日志文件中，进行DML操作。
SQL> create table scott.rman_test1(id int,name char(10));SQL> select * from scott.rman_test1; ID NAME---------- -------------------- 1 log1 1 log1 1 log1 1 log1 1 log1 1 log1
将表scott.rman_test1操作日志归档。
SQL> alter system switch logfile;SQL> select sequence#,first_change#,next_change# from v$archived_log;SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------...... 6 835483 840784 7 840784 841197
在新的日志中，进行scott.rman_test2的创建，不对此日志归档，log sequence 8.
SQL> create table scott.rman_test2 as select table_name from user_tables;
此时在线日志8中有rman_test2,归档日志7中有rman_test1.
4. 使用rman进行不完整恢复到归档日志7.
注：rman restore操作需要在mount下进行，不然会报错。
RMAN> restore database;Starting restore at 19-SEP-13allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=44 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/august/august/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/august/august/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/august/august/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/august/august/users01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkpRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 09/19/2013 20:38:42ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkpORA-19573: cannot obtain exclusive enqueue for datafile 1
关闭数据库，启动到mount
RMAN> shutdown immediate /*RMAN下可以进行shutdown, startup命令*/database closeddatabase dismountedOracle instance shut downRMAN> startup mount;connected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area 422670336 bytesFixed Size 1336960 bytesVariable Size 322963840 bytesDatabase Buffers 92274688 bytesRedo Buffers 6094848 bytes
查看备份集信息概要。
RMAN> list backup summary;List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---1 B F A DISK 19-SEP-13 1 1 NO TAG20130919T0116402 B F A DISK 19-SEP-13 1 1 NO TAG20130919T011640
恢复备份文件
-rw-r-----. 1 Oracle oinstall 9830400 Sep 19 01:18 o1_mf_ncsnf_TAG20130919T011640_93odvkkc_.bkp-rw-r-----. 1 Oracle oinstall 1011515392 Sep 19 01:18 o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkpRMAN> restore database;Starting restore at 19-SEP-13allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/august/august/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/august/august/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/august/august/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/august/august/users01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkp tag=TAG20130919T011640channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:03:45Finished restore at 19-SEP-13
介质恢复（相当于db2中的前滚操作）
RMAN> recover database until;
RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found ";": expecting one of: "scn, sequence, time"RMAN-01007: at line 1 column 23 file: standard input
可以看出RMAN中recover database until 后面可以按三种方式前滚(SCN,SEQUENCE,TIME).
这里我们按照sequence方式恢复（前滚）。
查看归档日志情况。
RMAN> list archivelog all;List of Archived Log Copies for database with db_unique_name AUGUST=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------1 1 2 A 15-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_15/o1_mf_1_2_93cc8thy_.arc2 1 3 A 15-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_15/o1_mf_1_3_93ck0mxm_.arc3 1 4 A 15-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_16/o1_mf_1_4_93ftwxqj_.arc4 1 5 A 16-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_5_93o9xt7k_.arc5 1 6 A 19-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc6 1 7 A 19-SEP-13 Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc
前滚恢复。
RMAN> recover database until sequence 7;Starting recover at 19-SEP-13using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arcarchived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc thread=1 sequence=6media recovery complete, elapsed time: 00:00:06Finished recover at 19-SEP-13
/*好像只恢复到log 7就停止了*/
RMAN> recover database until sequence 8; Starting recover at 19-SEP-13using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arcarchived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc thread=1 sequence=7media recovery complete, elapsed time: 00:00:01Finished recover at 19-SEP-13
/*看来需要指定到需要日志的下一个sequence*/
察看当前日志情况。
SQL> select sequence#,archived,status,first_change#,next_change# from v$log;
SEQUENCE# ARCHIV STATUS FIRST_CHANGE# NEXT_CHANGE#---------- ------ -------------------------------- ------------- ------------ 7 YES INACTIVE 840784 841197 6 YES INACTIVE 835483 840784 8 NO CURRENT 841197 2.8147E+14
我们采用不完全恢复，不应用8号日志，直接打开数据库。
RMAN> alter database open resetlogs;
database opened
数据库完成恢复，由于是resetlogs模式打开的，查看当前日志状态。
SQL> select sequence#,archived,status,first_change#,next_change# from v$log;
SEQUENCE# ARCHIV STATUS FIRST_CHANGE# NEXT_CHANGE#---------- ------ -------------------------------- ------------- ------------ 1 NO CURRENT 841198 2.8147E+14 0 YES UNUSED 0 0 0 YES UNUSED 0 0
日志被reset从1号开始了。
看到恢复后的用户表情况。
SQL> select table_name from user_tables;
TABLE_NAME------------------------------------------------------------RMAN_TEST1T_CT_PSALGRADEBONUSEMPDEPT
看到RMAN_TEST1表已经被恢复回来了。但是里面没有数据，因为我们之前并没有commit这个插入。
