oracle rman增量恢复测试记录

1、RMAN 增量恢复测试RMAN 增量恢复测试2012 年 3 月 29 日版本：1.01 / 88RMAN 增量恢复测试目录一、概述：51.1 、增量备份的基本概念51.1.1 、概念51.1.2 、备份策略及恢复51.1.3 、增量备份命令6二、备份过程72.1、第一次全备份：72.1.1、数据.72.1.2 、当时的归档日志序列号：72.1.3 、数据库当时的时间：82.1.4 、备份前的归档日志状态82.1.4 、开始第一次全备份82.1.4.1 、RMAN 配置选项：82.1.4.2 、开始备份数据库及归档日志：92.1.5 、第一次备份完成后的时间：112.1.6 、查看 RMAN

2、备份集112.1.7、测试汇总132.2、第一次增量备份142.2.1、数据：.142.2.2 、归档日志序列号142.2.3 、数据库当时的时间152.2.4 、开始第一次增量备份152.2.5 、备份后的归档日志状态172.2.6 、第一次增量备份后的时间172.2.7 、查看第一次增量备份后的 RMAN 备份集172.2.8、测试汇总192.3、第二次增量备份202.3.1 、数据2.3.1.1 、先2.3.1.2 、再2.3.1.3 、继续.20两条数据20两条数据：21数据：222.3.2 、归档日志序列号232.3.3 、数据库当时的时间232.3.4 、开始第二次增量备份232.

3、3.5 、备份后的归档日志序列号252.3.6 、第二次增量备份后的时间262.3.7 、查看第二次增量备份 RMAN 备份集262.3.8、第二次增量备份后系统的 SCN282.3.9、测试汇总293.4 、第三次增量备份293.4.1 、数据分布293.4.2 、归档日志序列号313.4.3 、数据库当前的时间322 / 88RMAN 增量恢复测试3.4.4 、开始第三次增量备份323.4.5 、备份后的归档日志序列号343.4.6 、第三次增量备份后的时间353.4.7 、查看第三次增量备份的 RMAN 备份集363.4.8、测试汇总38三、恢复过程393.1 、进行任意时间点的恢复39

4、测试目的：39测试步骤：393.1.1 、模拟故障。393.1.2 、发现故障393.1.3 、恢复参数文件403.1.3.1 、连接 RMAN,设置 DBID403.1.3.2 、通过 Oracle 默认的参数文件启动403.1.3.3 、从第一次全备份中恢复 spfile413.1.3.4 、重新从恢复后的参数文件启动413.1.4、恢复文件423.1.5 、恢复第二次增量备份时的数据文件423.1.6 、查看数据库的相关 SCN437、Recover 数据库463.1.8 、打开数据库验证数据473.1.9 、恢复测试小结483.2 、丢失某个增量备份集的恢复（基于时间点）48测试目的：

5、48测试步骤：493.2.1、恢复文件493.2.1.1、恢复前，确认数据库已经处在 nomount 状态了493.2.1.2、恢复第三次增量备份时的文件493.2.2、恢复第一次全备份时的数据文件502.2.2.1 、首先，要把数据库启动到 mount 状态502.2.2.2 、恢复第一次全备时的数据文件503.2.3 、删除第二次增量备份时的数据片，模拟第二次增备损坏513.2.4 、恢复数据到第三次增量备份时的状态523.2.5 、修复数据库533.2.6 、恢复第一次增量备份后到第二次此增量备份完成这段时间的归档日志。.543.2.7 、再次执行恢复数据库553.2.8 、执行备份检查

6、563.2.9 、再次执行恢复数据库613.2.10 、打开数据库验证数据633.2.11 、恢复测试小结643.3 、基于 SCN 的恢复64测试目的：64测试步骤：643.3.1、恢复并修复数据库653 / 88RMAN 增量恢复测试3.3.2 、验证数据683.3.3 、恢复测试小结693.4 、丢失中间某个增量备份归档日志的恢复（基于 SCN）70测试目的：70测试步骤：703.4.1、恢复第二次增量备份的文件703.4.2 、删除第一次增量备份的归档日志713.4.3 、恢复数据文件723.4.4 、恢复数据库到第二次增量备份时的 SCN743.4.5 、验证数据763.4.6 、恢

7、复测试小结773.5 、基于日志序列号的恢复77测试目的：77测试步骤：773.5.1、恢复第二次增量备份的文件773.5.2、恢复数据文件783.5.3、恢复时间所对应的归档日志793.5.4 、按日志序列号恢复归档日志813.5.5 、按日志序列号恢复数据库823.5.6 、打开数据库，并验证数据833.5.7 、恢复测试小结83补充测试：831）归档日志 10 所对应的时间842） 恢复并修复数据库843） 验证数据87三、测试总结884 / 88RMAN 增量恢复测试一、概述：在 rman 增量备份中，有差异增量和累积增量的概念，本文主要了在差异备份模式下，如何进行各种方式的恢复，使大

8、家对 Oracle RMAN 的差异备份恢复有个基本的了解。由于知识水平的限制，本文的一些推论或说明可能并全准确，文中的结论大家进行学习和参考。本文所验证的很多内容，都是很难在互联网上搜索到的，所以在测试过程中保留了各种输出的详细，并未对输出进行过任何的更改，所以大家也可以从下面的测试和输出中发现新的结论。文档说明：本文红色字体部分代表是提醒注意的部分；绿色的字体代表是命令；斜体字部分代表的是输出的补充说明部分。1.1、增量备份的基本概念1.1.1、概念差异增量：是备份上级及同级备份以来所有变化的数据块，差异增量是默认增量备份方式累积增量：是备份上级备份以来所有变化的块因为累积增量是备份上级备

9、份以来所有变化的数据块，所以累积增量需要的备份时间，同时需要较小的恢复时间；而差异增量正好相反，它可以备份同级备份以来变化的数据块。所以会减少备份时间，但需要的恢复时间.在 oracle 9 中增量备份需要通过扫描整个数据库的数据块才能知道哪些数据块发生了变化，这是一个代价很大，时间很长的过程，而且由于增量备份形成多个不同的备份集，使恢复变得更加不可靠，所以增量备份在版本 9 中仍然不被推荐使用;在 10g 中，增量备份作了很大的改进，不需要再扫描所有数据块就能得知哪些数据块发生变化，从而大大提升备份效率。但这些却以牺牲磁盘 i/o 为代价， 所以在 oltp 事务系统中还得衡量是否愿意以 i

10、/o 为代价来保证安全及高可用性。10g 还支持增量合并，增量备份可支持 7 级增量。1.1.2、备份策略及恢复以 rman 多级增量备份来作为备份策略例子:增量备份都需要一个 0 级备份来作为基础，0 级备份与全备份的区别在于 0 级可以用来增量恢复，而全备份则不可以。5 / 88RMAN 增量恢复测试备份策略：例如:我们对一星期做如下备份策略，如果周六发生数据库故障，那么差异增量只需要使用星期天的 0 级，星的一级，星期四、五的二级就可以恢复，累积增量则需要星期天的 0 级，星的一级、星期五的二级即可进行恢复。1.1.3、增量备份命令零级备份backup incremental 一级差异增

11、量 backup incremental 一级累计增量level0database;level1database;backupincrementallevel1cumulativedatabase;6 / 88星期差异增量累积增量星期天0 级0 级星期一2 级2 级星期二2 级2 级星1 级1 级星期四2 级2 级星期五2 级2 级2 级2 级星期天0 级0 级RMAN 增量恢复测试二、备份过程2.1、第一次全备份：2.1.1、数据说明：以下所有的测试表全部是在 user1 用户模式下进行的SQL> create table name(id number,name varchar(20)

12、;Table created.SQL> insert into name values(1,'zxk');1 row created.SQL> select * from name;ID NAME- -1 zxkSQL> commit;Commit complete.2.1.2、当时的归档日志序列号：SQL> alter system switch logfile;System altered.SQL> archive log list;Database log mode Automatic archival Archive destination

13、Oldest online log sequenceNext log sequence to archive Current log sequenceArchive ModeEnabled f:arch1227 / 88RMAN 增量恢复测试2.1.3、数据库当时的时间：SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'-12-03-15 12:52:232.1.4、备份前的归档日志状态（在这之前做过一次全备份，非增量 0 级备份，所以下面的归档日志序列号增加

14、了，不过并未更新数据）SQL> archive log list;Database log mode Automatic archival Archive destinationOldest online log sequenceNext log sequence to archive Current log sequenceArchive ModeEnabled f:arch3552.1.4、开始第一次全备份2.1.4.1、RMAN 配置选项：RMAN> show all;using target database controlfile instead of recovery

15、catalogRMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAUEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'f:bakctl-pf%

16、d_ctf_bak_%F'CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;8 / 88RMAN 增量恢复测试CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMATU'

17、CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT U'CONFIGURE MAXSETSIZE TO UNLIMITED; # default'f:bakdatadt1%d_datafile_bak_%'f:bakdatadt2%d_datafile_bak_%CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:ORACLEPRODUCT10.1.0DB_1DATABASESNCFOR

18、CL.ORA' # default2.1.4.2、开始备份数据库及归档日志：RMAN> backup incremental level 0 database plus archivelog;Starting backup at 15-3 月 -12 current log archivedusing channel ORA_DISK_1 using channel ORA_DISK_2channel ORA_DISK_1: starting compressed archive log backupsetchannel ORA_DISK_1: specifying archiv

19、e log(s) in backup setinput archive log th=1 sequence=1 recid=46 stamp=777991779channel ORA_DISK_1: starting piece 1 at 15-3 月 -12channel ORA_DISK_2: starting compressed archive log backupsetchannel ORA_DISK_2: specifying archive log(s) in backup setinput archive log thinput archive log th input arc

20、hive log th=1 sequence=2 recid=47 stamp=777993230=1 sequence=3 recid=48 stamp=777993432=1 sequence=4 recid=49 stamp=777993480channel ORA_DISK_2: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1CN5UFIR_1_1 comment=NONE channe

21、l ORA_DISK_1: backup set complete, elapsed time: 00:00:02channel ORA_DISK_2: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT2ORCL_DATAFILE_BAK_1DN5UFIR_1_1 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02channel ORA_DISK_1: starting compressed archive log backupset

22、channel ORA_DISK_1: specifying archive log(s) in backup setinput archive log th=1 sequence=5 recid=50 stamp=777993819channel ORA_DISK_1: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1EN5UFIT_1_1 comment=NONE9 / 88RMAN 增量恢复

23、测试channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 15-3 月 -12Starting backup at 15-3 月 -12 using channel ORA_DISK_1 using channel ORA_DISK_2channel ORA_DISK_1: starting compressed incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in ba

24、ckupsetinput datafile fno=00001 name=E:ORACLEPRODUCT10.1.0ORADATAORCLSYSTEM01.DBFinput datafile fno=00006 name=F:ORADATA2TABLESPACE1.DBFinput datafile fno=00004 name=E:ORACLEPRODUCT10.1.0ORADATAORCLUSERS01.DBF channel ORA_DISK_1: starting piece 1 at 15-3 月 -12channel ORA_DISK_2: starting compressed

25、incremental level 0 datafile backupset channel ORA_DISK_2: specifying datafile(s) in backupsetinput datafile fno=00003 name=E:ORACLEPRODUCT10.1.0ORADATAORCLSYSAUX01.DBFinput datafile fno=00005 name=E:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBFinput datafile fno=00002 name=E:ORACLEPRODUCT10.1.0ORADAT

26、AORCLUNDOTBS01.DB Fchannel ORA_DISK_2: starting piece 1 at 15-3 月 -12channel ORA_DISK_2: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT2ORCL_DATAFILE_BAK_1GN5UFIU_1_1 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:36channel ORA_DISK_1: finished piece 1 at 15-3 月 -1

27、2piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1FN5UFIU_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:43Finished backup at 15-3 月 -12Starting backup at 15-3 月 -12 current log archivedusing channel ORA_DISK_1 using channel ORA_DISK_2channel ORA_DISK_1: starting compressed

28、 archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log th=1 sequence=6 recid=51 stamp=777993865channel ORA_DISK_1: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1HN5UFK9_1_1 comme

29、nt=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 15-3 月 -12Starting Control File and SPFILE Autobackup at 15-3 月 -1210 / 88RMAN 增量恢复测试piece handle=F:BAKCTL-PFORCL_CTF_BAK_C-1294617127-20120315-02 comment=NONEFinished Control File and SPFILE Autobackup at 15-3

30、 月 -12RMAN>2.1.5、第一次备份完成后的时间：SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'-12-03-15 13:25:192.1.6、查看 RMAN 备份集BS KeySizeDevice Type Elapsed Time Completion Time- - - - -15-3 月 -12402MBP Key: 40DISK00:00:02Status: AVAILABLECompressed: YESTag: TAG20120

31、315T132339Piece Name: F:BAKDATADT2ORCL_DATAFILE_BAK_1DN5UFIR_1_1List of Archived Logs in backup set 40Thrd SeqLow SCNLow TimeNext SCNNext Time- - - - - -15-3 月15-3 月15-3 月15-3 月15-3 月15-3 月111234630832631802632048-12 631802-12 632048-12 632071-12-12-12BS KeySizeDevice Type Elapsed Time Completion Ti

32、me- - - - -15-3 月 -12413MBP Key: 41DISK00:00:02Status: AVAILABLECompressed: YESTag: TAG20120315T132339Piece Name: F:BAKDATADT1ORCL_DATAFILE_BAK_1CN5UFIR_1_1List of Archived Logs in backup set 41Thrd SeqLow SCNLow TimeNext SCN11 / 88Next TimeRMAN 增量恢复测试- - - - - -15-3 月 -12 63083215-3 月 -1211627378BS

33、 KeySizeDevice Type Elapsed Time Completion Time- - - - -15-3 月 -124267KBP Key: 42DISK00:00:00Status: AVAILABLECompressed: YESTag: TAG20120315T132339Piece Name: F:BAKDATADT1ORCL_DATAFILE_BAK_1EN5UFIT_1_1List of Archived Logs in backup set 42Thrd SeqLow SCNLow TimeNext SCNNext Time- - - - - -15-3 月 -

34、12 63224715-3 月 -1215632071BS KeyType LV SizeDevice Type Elapsed Time Completion Time- - - - - - -15-3 月 -1243Incr 0213MBP Key: 43DISK00:00:31Status: AVAILABLECompressed: YESTag: TAG20120315T132342Piece Name: F:BAKDATADT2ORCL_DATAFILE_BAK_1GN5UFIU_1_1List of Datafiles in backup set 43File LV Type Ck

35、p SCNCkp TimeName- - - - - -15-3 月 -1220Incr 632252E:ORACLEPRODUCT10.1.0ORADATAORCLUNDOTBS01.DBF15-3 月 -12 E:ORACLEPRODUCT10.1.0ORADATAORCLSYSAU3X01.DBF50Incr 63225215-3 月 -120Incr 632252E:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBFBS KeyType LV SizeDevice Type Elapsed Time Completion Time- - - - -

36、- -15-3 月 -1244Incr 0330MBP Key: 44DISK00:00:40Status: AVAILABLECompressed: YESTag: TAG20120315T132342Piece Name: F:BAKDATADT1ORCL_DATAFILE_BAK_1FN5UFIU_1_1List of Datafiles in backup set 44File LV Type Ckp SCNCkp TimeName- - - - - -15-3 月 -12 E:ORACLEPRODUCT10.1.0ORADATAORCLSYSTE10Incr 632251M01.DB

37、F12 / 88RMAN 增量恢复测试15-3 月 -12 E:ORACLEPRODUCT10.1.0ORADATAORCLUSERS401.DBF60Incr 63225115-3 月 -12 F:ORADATA2TABLESPACE1.DBF0Incr 632251BS KeySizeDevice Type Elapsed Time Completion Time- - - - -15-3 月 -12457KBP Key: 45DISK00:00:01Status: AVAILABLECompressed: YESTag: TAG20120315T132425Piece Name: F:B

38、AKDATADT1ORCL_DATAFILE_BAK_1HN5UFK9_1_1List of Archived Logs in backup set 45Thrd SeqLow SCNLow TimeNext SCNNext Time- - - - - -15-3 月 -12 63227315-3 月 -1216632247BS KeyType LV SizeDevice Type Elapsed Time Completion Time- - - - - - -15-3 月 -1246Full2MDISK00:00:01BP Key: 46Status: AVAILABLECompresse

39、d: NOTag: TAG20120315T132427Piece Name: F:BAKCTL-PFORCL_CTF_BAK_C-1294617127-20120315-02Ckp time: 15-3 月 -12Controlfile Included: Ckp SCN: 632278SPFILE Included: Modification time: 14-3 月 -12可以看到当时的备份集到达了 46，下一个 SCN 为 632273文件所对应的块为 F:BAKCTL-PFORCL_CTF_BAK_C-1294617127-20120315-022.1.7、测试汇总1、 从数据到第一

40、次全备份结束时产生的归档日志为 2-6，即在恢复这一段时间用到的归档日志为 2-62、当时的 SCN 为 632272（从最后一个备份的归档日志得知）3、时间段为：12-03-15 12:52:23 至 12-03-15 13:25:194、文件的对应的备份片为：F:BAKCTL-PFORCL_CTF_BAK_C-1294617127-20120315-0213 / 88RMAN 增量恢复测试2.2、第一次增量备份2.2.1、数据SQL> insert into name values(2,'wxh');1 row created.SQL> insert into

41、name values(3,'zyx');1 row created.SQL> commit;Commit complete.SQL> select * from name;ID NAME1 zxk2 wxh3 zyx2.2.2、当时的归档日志序列号SQL> alter system switch logfile;System altered.SQL> archive log list;Database log mode Automatic archival Archive destinationOldest online log sequenceNex

42、t log sequence to archive Current log sequenceArchive ModeEnabled f:arch68814 / 88RMAN 增量恢复测试2.2.3、数据库当时的时间SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'12-03-15 13:33:542.2.4、开始第一次增量备份RMAN> backup incremental level 1 database plus archivelog;Startin

43、g backup at 15-3 月 -12 current log archivedusing channel ORA_DISK_1 using channel ORA_DISK_2channel ORA_DISK_1: starting compressed archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log th=1 sequence=1 recid=46 stamp=777991779channel ORA_DISK_1: starting p

44、iece 1 at 15-3 月 -12channel ORA_DISK_2: starting compressed archive log backupsetchannel ORA_DISK_2: specifying archive log(s) in backup setinput archive log thinput archive log th input archive log th input archive log th=1 sequence=2 recid=47 stamp=777993230=1 sequence=3 recid=48 stamp=777993432=1

45、 sequence=4 recid=49 stamp=777993480=1 sequence=5 recid=50 stamp=777993819channel ORA_DISK_2: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1JN5UGVH_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00

46、:00:06channel ORA_DISK_2: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT2ORCL_DATAFILE_BAK_1KN5UGVH_1_1 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:06channel ORA_DISK_1: starting compressed archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in ba

47、ckup setinput archive log thinput archive log th input archive log th=1 sequence=6 recid=51 stamp=777993865=1 sequence=7 recid=52 stamp=777994374=1 sequence=8 recid=53 stamp=777995249channel ORA_DISK_1: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BA

48、KDATADT1ORCL_DATAFILE_BAK_1LN5UGVN_1_1 comment=NONE15 / 88RMAN 增量恢复测试channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 15-3 月 -12Starting backup at 15-3 月 -12 using channel ORA_DISK_1 using channel ORA_DISK_2channel ORA_DISK_1: starting compressed incremental level 1

49、datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=E:ORACLEPRODUCT10.1.0ORADATAORCLSYSTEM01.DBFinput datafile fno=00006 name=F:ORADATA2TABLESPACE1.DBFinput datafile fno=00004 name=E:ORACLEPRODUCT10.1.0ORADATAORCLUSERS01.DBF channel ORA_DISK_1: sta

50、rting piece 1 at 15-3 月 -12channel ORA_DISK_2: starting compressed incremental level 1 datafile backupset channel ORA_DISK_2: specifying datafile(s) in backupsetinput datafile fno=00003 name=E:ORACLEPRODUCT10.1.0ORADATAORCLSYSAUX01.DBFinput datafile fno=00005 name=E:ORACLEPRODUCT10.1.0ORADATAORCLEXA

51、MPLE01.DBFinput datafile fno=00002 name=E:ORACLEPRODUCT10.1.0ORADATAORCLUNDOTBS01.DB Fchannel ORA_DISK_2: starting piece 1 at 15-3 月 -12channel ORA_DISK_1: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT1ORCL_DATAFILE_BAK_1MN5UGVQ_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35channel ORA_DISK_2: finished piece 1 at 15-3 月 -12piece handle=F:BAKDATADT2ORCL_DATAFILE_BAK_1NN5UGVQ_1_1 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35Finished backup at 15-3 月 -12Starting backu