利用scn增量备份实现数据库增量恢复

数据库版本

SQL>select* fromv$version;   BANNER -------------------------------------------------------------------------------- OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE    11.2.0.3.0      Production TNSforLinux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

全备数据库

RMAN> backup as compressed backupset database  format'/u01/oracle/oradata/tmp/ora11g_0_%U';   Starting backup at 18-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) inbackup set input datafile filenumber=00001 name=/u01/oracle/oradata/ora11g/system01.dbf input datafile filenumber=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf input datafile filenumber=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf input datafile filenumber=00004 name=/u01/oracle/oradata/ora11g/users01.dbf input datafile filenumber=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-12 channel ORA_DISK_1: finished piece 1 at 18-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1tag=TAG20120418T234958 comment=NONE channel ORA_DISK_1: backup setcomplete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) inbackup set including current control filein backup set including current SPFILE inbackup set channel ORA_DISK_1: starting piece 1 at 18-APR-12 channel ORA_DISK_1: finished piece 1 at 18-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1tag=TAG20120418T234958 comment=NONE channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01 Finished backup at 18-APR-12

创建测试数据库验证恢复标准

SQL> conn chf/xifenfei Connected. SQL>droptable xifenfei purge;   Tabledropped.   SQL>createtable xifenfei as   2 select* fromdba_objects;   Tablecreated.   SQL>insertinto xifenfei   2 select* fromdba_objects;   74534rowscreated.   SQL> /         74534rowscreated.   SQL> /   74534rowscreated.   SQL>commit;   Commitcomplete.   SQL>selectcount(*)fromxifenfei;     COUNT(*) ----------     298136   SQL>createtable xifenfei01 as      2 select* fromdba_objects;   Tablecreated.   SQL>altersystem switch logfile;   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL>createtable chf.xifenfei02 as   2 select* fromdba_objects;   Tablecreated.   SQL>altersystem switch logfile;   System altered.

异机恢复库

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';   Starting restore at 19-APR-12 using channel ORA_DISK_1   channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 outputfilename=/u01/oracle/oradata/ora11g/control01.ctl outputfilename=/u01/oracle/oradata/ora11g/control02.ctl Finished restore at 19-APR-12   RMAN> alter database mount;   database mounted released channel: ORA_DISK_1   RMAN> list backup;     List of Backup Sets ===================     BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7       Full    262.79M    DISK        00:01:08     18-APR-12              BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20120418T234958         Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1   List of Datafiles inbackup set7   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   1       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf   2       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf   3       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf   4       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf   6       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf --这里可以发现备份时的scn,增量备份时取这里的最小scn为起点     --为了排除影响,删除所有归档日志记录 RMAN> delete archivelog all;   RMAN> list archivelog all;   specification does not match any archived log inthe repository   RMAN> restore database;   Starting restore at 19-APR-12 using channel ORA_DISK_1   channel ORA_DISK_1: starting datafile backup setrestore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/ora11g/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1tag=TAG20120418T234958 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:36 Finished restore at 19-APR-12   [oracle@xifenfei oradata]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL>selectfile#,to_char(checkpoint_change#,'9999999999999999') "SCN",   2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"   3  from v$datafile_header;        FILE# SCN                                RESETLOGS SCN ---------- ---------------------------------- ----------------------------------          1           1155510                             787897          2           1155510                             787897          3           1155510                             787897          4           1155510                             787897          6           1155510                             787897   SQL>selectfile#,to_char(checkpoint_change#,'999999999999999') "SCN",   2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;        FILE# SCN                              STOP_SCN ---------- -------------------------------- --------------------------------          1          1155510          2          1155510          3          1155510          4          1155510          6          1155510

基于scn增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE 2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U'tag 'XIFENFEI';   Starting backup at 19-APR-12   using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=125 device type=DISK backup will be obsolete on date26-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) inbackup set input datafile filenumber=00001 name=/u01/oracle/oradata/ora11g/system01.dbf input datafile filenumber=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf input datafile filenumber=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf input datafile filenumber=00004 name=/u01/oracle/oradata/ora11g/users01.dbf input datafile filenumber=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-12 channel ORA_DISK_1: finished piece 1 at 19-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1tag=XIFENFEI comment=NONE channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:45   using channel ORA_DISK_1 backup will be obsolete on date26-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) inbackup set including current control filein backup set channel ORA_DISK_1: starting piece 1 at 19-APR-12 channel ORA_DISK_1: finished piece 1 at 19-APR-12 piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1tag=XIFENFEI comment=NONE channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:01 Finished backup at 19-APR-12

增量恢复

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';   searchingforall files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1   List of Files Unknown to the Database ===================================== File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1   Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... catalogingdone   List of Cataloged Files ======================= File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1   RMAN> recover database;   Starting recover at 19-APR-12 using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=125 device type=DISK channel ORA_DISK_1: starting incremental datafile backup setrestore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destinationforrestore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf destinationforrestore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf destinationforrestore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf destinationforrestore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf destinationforrestore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07   starting media recovery   --这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56 archived log filename=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57 unable to findarchived log archived log thread=1 sequence=58 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover commandat 04/19/201200:55:48 RMAN-06054: media recovery requesting unknown archived log forthread 1 with sequence 58 and starting SCN of 1157379   SQL>selectfile#,to_char(checkpoint_change#,'9999999999999999') "SCN",   2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"   3  from v$datafile_header;        FILE# SCN                                RESETLOGS SCN ---------- ---------------------------------- ----------------------------------          1           1157379                             787897          2           1157379                             787897          3           1157379                             787897          4           1157379                             787897          6           1157379                             787897   SQL>selectfile#,to_char(checkpoint_change#,'999999999999999') "SCN",   2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;        FILE# SCN                              STOP_SCN ---------- -------------------------------- --------------------------------          1          1157379          2          1157379          3          1157379          4          1157379          6          1157379   SQL> alter database openresetlogs;   Database altered.   SQL>selectcount(*) from chf.xifenfei;     COUNT(*) ----------     298136   SQL>selectTABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';   TABLE_NAME ------------------------------------------------------------ XIFENFEI02 XIFENFEI01 XIFENFEI

使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.