针对archival backup的backup optimization效果测试

archival backup，又叫历史归档备份，目的是将数据库某一时刻数据生成历史备份，备份里包含了datafile、spfile、controlfile、archivelog，这个历史备份不受retention policy的限制，通过使用backup ... keep ...指定备份的保留时长，archival backup主要应用与历史数据的永久保留以及将数据库完整恢复到另一个环境里。


backup optimization能够对backup命令进行优化，避免对于已存在备份的对象进行重复的备份，那么backup optimization如何作用于archival backup，且看以下测试
/
// backup optimaztion off
/
RMAN> show backup optimization;  


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE BACKUP OPTIMIZATION OFF;


RMAN> show ARCHIVELOG DELETION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default


---先生成一个non-archival archivelog备份
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc';


Starting backup at 20150606 09:00:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:00:54
channel ORA_DISK_1: finished piece 1 at 20150606 09:00:55
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T090053_1k5uOVyhU_.bkp tag=TAG20150606T090053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:00:55


RMAN> list backup of archivelog sequence 8 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
143     B  A  A DISK        20150606 09:00:55 1       1       NO         TAG20150606T090053


---生成第二个archival archivelog备份
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:01:28
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:01:28
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:01:28
channel ORA_DISK_1: finished piece 1 at 20150606 09:01:29
piece handle=/oradata06/vlib2/arc_5jq8q278_1_1 tag=TAG20150606T090128 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:01:29


---生成第三个archival archivelog备份
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:01:39
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:01:39
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:01:39
channel ORA_DISK_1: finished piece 1 at 20150606 09:01:40
piece handle=/oradata06/vlib2/arc_5kq8q27j_1_1 tag=TAG20150606T090139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:01:40


***要避免重复备份必须加上与前面生成的backup相同的tag
RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T090139 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:01:52
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:01:52
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:01:52


---当前archivelog sequence 8共有三个backup，其中RECID=143是non-archival的备份
RMAN> list backup of archivelog sequence 8 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
143     B  A  A DISK        20150606 09:00:55 1       1       NO         TAG20150606T090053
144     B  A  A DISK        20150606 09:01:29 1       1       NO         TAG20150606T090128
145     B  A  A DISK        20150606 09:01:40 1       1       NO         TAG20150606T090139


SYS@tstdb1-SQL> select recid,backup_type,completion_time,keep from v$backup_set where recid in (143,144,145);


     RECID B COMPLETION_TIME   KEE
---------- - ----------------- ---
       143 L 20150606 09:00:55 NO
       144 L 20150606 09:01:29 YES
       145 L 20150606 09:01:40 YES


---指定not backed up 1 times，因为检测到了第一个non-archival archivelog(RECID=143)备份的存在，所有没有生成新的备份
RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:02:11
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:02:11
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:02:11


---把RECID=143的non-archival archivelog备份暂时从catalog里删除，再次进行上面的not backed up 1 times备份后生成了一个新的备份
RMAN> change backuppiece '/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T090053_1k5uOVyhU_.bkp' uncatalog;


uncataloged backup piece
backup piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T090053_1k5uOVyhU_.bkp RECID=175 STAMP=881658054
Uncataloged 1 objects


RMAN>  backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:11:08
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:11:08
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:11:08
channel ORA_DISK_1: finished piece 1 at 20150606 09:11:09
piece handle=/oradata06/vlib2/arc_5lq8q2pc_1_1 tag=TAG20150606T091108 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:11:09


至此archivelog sequence 8的三个backup都是archival属性的
RMAN> list backup of archivelog sequence 8 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
144     B  A  A DISK        20150606 09:01:29 1       1       NO         TAG20150606T090128
145     B  A  A DISK        20150606 09:01:40 1       1       NO         TAG20150606T090139
146     B  A  A DISK        20150606 09:11:09 1       1       NO         TAG20150606T091108


SYS@tstdb1-SQL> select recid,backup_type,completion_time,keep from v$backup_set where recid in (144,145,146);


     RECID B COMPLETION_TIME   KEE
---------- - ----------------- ---
       144 L 20150606 09:01:29 YES
       145 L 20150606 09:01:40 YES
       146 L 20150606 09:11:09 YES


---对archivelog sequence 8进行non-archival的备份，虽然已经有三个archivelog sequence 8的备份，但因这些备份是archival属性的，所以第一次还是生成了新的备份
RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc';


Starting backup at 20150606 09:20:19
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:20:20
channel ORA_DISK_1: finished piece 1 at 20150606 09:20:21
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T092019_1k5vT_mpG_.bkp tag=TAG20150606T092019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:20:21


RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc';


Starting backup at 20150606 09:20:27
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:20:27


/
// backup optimaztion on
/
RMAN> show BACKUP OPTIMIZATION;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE BACKUP OPTIMIZATION ON;


RMAN> show ARCHIVELOG DELETION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default


---清空所有archivelog sequence 8的备份
delete backup of archivelog sequence 8;


---先以non-archival方式备份archivelog sequence 8
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc';


Starting backup at 20150606 09:29:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:29:59
channel ORA_DISK_1: finished piece 1 at 20150606 09:30:02
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T092959_1k5w0X0p3_.bkp tag=TAG20150606T092959 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20150606 09:30:02


RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc';


Starting backup at 20150606 09:30:06
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:30:06


---第一次以archival方式备份archivelog sequence 8，backup optimization生效，提示已经有过备份了，表名backup optimization生效
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:37:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK
backup will be obsolete on date 20150607 09:37:05
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:37:05


---把archivelog sequence 8唯一的备份uncatalog
RMAN> list backup of archivelog sequence 8;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time  
------- ---------- ----------- ------------ -----------------
150     94.76M     DISK        00:00:01     20150606 09:30:00
        BP Key: 182   Status: AVAILABLE  Compressed: NO  Tag: TAG20150606T092959
        Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T092959_1k5w0X0p3_.bkp


  List of Archived Logs in backup set 150
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    8       12723361993717 20150605 06:36:19 12723362000495 20150605 07:40:19


RMAN> change backuppiece '/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T092959_1k5w0X0p3_.bkp' uncatalog;


uncataloged backup piece
backup piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_annnn_TAG20150606T092959_1k5w0X0p3_.bkp RECID=182 STAMP=881659799
Uncataloged 1 objects


---第二次以archival方式备份archivelog sequence 8，生成了tag=TAG20150606T094144的新备份
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:41:44
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:41:44
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:41:44
channel ORA_DISK_1: finished piece 1 at 20150606 09:41:45
piece handle=/oradata06/vlib2/arc_5rq8q4io_1_1 tag=TAG20150606T094144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:41:45


---第三次以archival方式备份archivelog sequence 8，又生成了tag=TAG20150606T094328的新备份,backup optimization没有生效
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:43:28
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:43:28
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:43:28
channel ORA_DISK_1: finished piece 1 at 20150606 09:43:29
piece handle=/oradata06/vlib2/arc_5sq8q4m0_1_1 tag=TAG20150606T094328 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:43:29


---第四次使用not backed up 1 times，以archival方式备份archivelog sequence 8，又生成了tag=TAG20150606T094328的新备份，backup optimization仍然没有生效
RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 09:44:31
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:44:31
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 09:44:31
channel ORA_DISK_1: finished piece 1 at 20150606 09:44:32
piece handle=/oradata06/vlib2/arc_5tq8q4nv_1_1 tag=TAG20150606T094431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 09:44:32


至此archivelog sequence 8有了4个backupset，均为archival属性
RMAN> list backupset of archivelog sequence 8 summary;


using target database control file instead of recovery catalog


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
152     B  A  A DISK        20150606 09:41:45 1       1       NO         TAG20150606T094144
153     B  A  A DISK        20150606 09:43:29 1       1       NO         TAG20150606T094328
154     B  A  A DISK        20150606 09:44:32 1       1       NO         TAG20150606T094431


SYS@tstdb1-SQL> select recid,backup_type,completion_time,keep from v$backup_set where recid in (152,153,154);


     RECID B COMPLETION_TIME   KEE
---------- - ----------------- ---
       152 L 20150606 09:41:45 YES
       153 L 20150606 09:43:29 YES
       154 L 20150606 09:44:32 YES


---第五次以archival方式备份archivelog sequence 8，略有不同的是backup时指定了tag且tag命名与前几次的备份相同，结果均跳过了备份，backup optimization生效
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T094431 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';  


Starting backup at 20150606 09:56:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK
backup will be obsolete on date 20150607 09:56:48
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:56:48


RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T094328 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';  


Starting backup at 20150606 09:57:55
using channel ORA_DISK_1
backup will be obsolete on date 20150607 09:57:55
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 09:57:55


---第六次以archival方式备份archivelog sequence 8，测试一下加入not backed up X times后的效果
RMAN> backup archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T094144 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';    


Starting backup at 20150606 10:14:27
using channel ORA_DISK_1
backup will be obsolete on date 20150607 10:14:27
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 10:14:27


***因为tag=TAG20150606T094144已经有了一个备份，所以backup not backed up 1 times跳过了这个备份，backup not backed up 2 times时才生成新的备份
RMAN> list backupset summary tag=TAG20150606T094144;




List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
152     B  A  A DISK        20150606 09:41:45 1       1       NO         TAG20150606T094144


RMAN> backup not backed up 1 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T094144 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1'; 


Starting backup at 20150606 10:14:47
using channel ORA_DISK_1
backup will be obsolete on date 20150607 10:14:47
archived logs required to recover from this backup will be backed up
skipping archived log of thread 1 with sequence 8; already backed up
Finished backup at 20150606 10:14:47


RMAN> backup not backed up 2 times archivelog like '/oradata06/fra/TSTDB1/archivelog/2015_06_05/o1_mf_1_8_1k4ZQWUms_.arc' tag=TAG20150606T094144 format '/oradata06/vlib2/arc_%U' keep until time 'sysdate+1';


Starting backup at 20150606 10:18:37
using channel ORA_DISK_1
backup will be obsolete on date 20150607 10:18:37
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=32 STAMP=881566820
channel ORA_DISK_1: starting piece 1 at 20150606 10:18:37
channel ORA_DISK_1: finished piece 1 at 20150606 10:18:38
piece handle=/oradata06/vlib2/arc_60q8q6nt_1_1 tag=TAG20150606T094144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 10:18:38




关于backup optimization如何作用于archival backup总结如下
当backup optimization off：
  针对同一个archivelog进行non-archival backup或者archival backup的备份都不会用到backup optimization特性，要避免重复备份对于non-archival backup需要使用not backed up X times或者配置configure archivelog retention policy to backed up X times to devices type ...，对于archival backup除了使用not backed up X times或者配置configure archivelog retention policy to backed up X times to devices type ...外，还必须在backup命令里指定与已经生成的archival backup相同的Tag才行


当backup optimization on：
  一个archivelog有non-archival backup的情况下，后面对该archivelog进行archival backup的时候就可以用到backup optimization，避免重复备份；
  但是当archivelog只有archival backup的情况下，对该archivelog进行non-archival backup的时却不能使用到已有的archival backup，会生成一个新的备份；
  当没有non-archival backup存在的情况下，一个archivelog的多个不同的archival backup间只有tag相同的才能避免重复备份

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1689192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1689192/