RMAN 备份“Unused Block Compression”测试

Rman一个重要特性“空块不作备份”(Unused Block Compression)一直都深受广大DBA的喜爱，该特性不但节约空间而且能大大提高备份效率。关于"Unused Block Compression"相关官方文档如下：

Unused Block Compression Of Datafile Backups to Backup Sets

When backing up datafiles into backup sets, RMAN does not back up the contents of data blocks that have never been allocated. (In previous releases, this behavior was referred to as NULL compression.)

RMAN also skips other datafile blocks that do not currently contain data, if all of the following conditions apply:

l  The COMPATIBLE initialization parameter is set to 10.2

l  There are currently no guaranteed restore points defined for the database

l  The datafile is locally managed

l  The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup

l  The backup set is being created on disk.
http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta009.htm#RCMRF98765

 RMAN also skips other datafile blocks that do not currently contain data,根据此说法通过delete、truncate等方案来提高备份的效率。那么具体情况是否和官方文档描述的一样呢？

实验环境：

为便于展示实验效果，创建一新表空间

点击(此处)折叠或打开

1. create tablespace rman_test datafile '/u01/app/oracle/oradata/orcl/rman_test01.dbf' size 500M EXTENT MANAGEMENT LOCAL;
   
2. SELECT FILE_NAME,FILE_ID FROM dba_data_files WHERE TABLESPACE_NAME='RMAN_TEST';
   
3. FILE_NAME FILE_ID
   
4. ------------------------------------------------------------ ----------
   
5. /u01/app/oracle/oradata/orcl/rman_test01.dbf 6

1.新建数据文件的备份

对6号数据文件进行备份，输出日志内容

点击(此处)折叠或打开

1. RMAN> backup datafile 6 format '/setup/datafile6_%U';
2. Starting backup at 24-MAR-17
   
3. using channel ORA_DISK_1
   
4. channel ORA_DISK_1: starting full datafile backupset
   
5. channel ORA_DISK_1: specifying datafile(s) in backupset
   
6. input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
   
7. channel ORA_DISK_1: starting piece 1 at 24-MAR-17
   
8. channel ORA_DISK_1: finished piece 1 at 24-MAR-17
   
9. piece handle=/setup/datafile6_0crvuqil_1_1 tag=TAG20170324T161621 comment=NONE
   
10. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
    
11. Finished backup at 24-MAR-17

生成文件情况：

点击(此处)折叠或打开

1. RMAN> list backup of datafile 6;
   
2. List of Backup Sets
   
3. ===================
4. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
5. ------- ---- -- ---------- ----------- ------------ ---------------
   
6. 9 Full 88.00K DISK 00:00:11 24-MAR-17
   
7.         BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20170324T161621
   
8.         Piece Name: /setup/datafile6_0crvuqil_1_1
   
9.   List of Datafiles in backup set 9
   
10.   File LV Type Ckp SCN Ckp Time Name
    
11.   ---- -- ---- ---------- --------- ----
    
12.   6 Full 1564797 24-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf

2.创建对象后数据文件备份

点击(此处)折叠或打开

1. create table t tablespace rman_test as select * from dba_objects;
   
2. insert into t select * from t;
   
3. commit;
   
4. //反复执行上述命令
   
5. SELECT sum(BLOCKS)*8/1024 FROM user_segments WHERE SEGMENT_NAME='T';
   
6. SUM(BLOCKS)*8/1024
   
7. ------------------
   
8. 352

T表占用空间大约为350M，再次进行rman备份

点击(此处)折叠或打开
RMAN> backup datafile 6 format '/setup/datafile6_%U';

1. 
   
2. Starting backup at 24-MAR-17
3. using channel ORA_DISK_1
   
4. channel ORA_DISK_1: starting full datafile backupset
   
5. channel ORA_DISK_1: specifying datafile(s) in backupset
   
6. input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
   
7. channel ORA_DISK_1: starting piece 1 at 24-MAR-17
   
8. channel ORA_DISK_1: finished piece 1 at 24-MAR-17
   
9. piece handle=/setup/datafile6_0drvur3g_1_1 tag=TAG20170324T162520 comment=NONE
   
10. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
    
11. Finished backup at 24-MAR-17

生成文件情况：

点击(此处)折叠或打开

1. RMAN> list backup of datafile 6;
2. List of Backup Sets
   
3. ===================
4. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
5. ------- ---- -- ---------- ----------- ------------ ---------------
   
6. 10 Full 348.09M DISK 00:00:33 24-MAR-17
   
7.         BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20170324T162520
   
8.         Piece Name: /setup/datafile6_0drvur3g_1_1
   
9.   List of Datafiles in backup set 10
   
10.   File LV Type Ckp SCN Ckp Time Name
    
11.   ---- -- ---- ---------- --------- ----
    
12.   6 Full 1566150 24-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf

3.delete后数据文件备份

     对T表delele全部数据后，进行RMAN备份

点击(此处)折叠或打开

1. RMAN> backup datafile 6 format '/setup/datafile6_%U';
2. Starting backup at 24-MAR-17
   
3. using target database control file instead of recovery catalog
   
4. allocated channel: ORA_DISK_1
   
5. channel ORA_DISK_1: sid=141 devtype=DISK
   
6. channel ORA_DISK_1: starting full datafile backupset
   
7. channel ORA_DISK_1: specifying datafile(s) in backupset
   
8. input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
   
9. channel ORA_DISK_1: starting piece 1 at 24-MAR-17
   
10. channel ORA_DISK_1: finished piece 1 at 24-MAR-17
    
11. piece handle=/setup/datafile6_0ervurpv_1_1 tag=TAG20170324T163719 comment=NONE
    
12. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
    
13. Finished backup at 24-MAR-17

生成文件情况：

点击(此处)折叠或打开

1. RMAN> list backup of datafile 6;
2. List of Backup Sets
   
3. ===================
4. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
5. ------- ---- -- ---------- ----------- ------------ ---------------
   
6. 11 Full 348.09M DISK 00:00:20 24-MAR-17
   
7.         BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20170324T163719
   
8.         Piece Name: /setup/datafile6_0ervurpv_1_1
   
9.   List of Datafiles in backup set 11
   
10.   File LV Type Ckp SCN Ckp Time Name
    
11.   ---- -- ---- ---------- --------- ----
    
12.   6 Full 1570537 24-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf

4.truncate后备份
    对表进行重建，并且插入步骤2中相同的记录数，然后执行truncate操作，进行rman备份

点击(此处)折叠或打开

1. RMAN> backup datafile 6 format '/setup/datafile6_%U';
2. Starting backup at 24-MAR-17
   
3. using target database control file instead of recovery catalog
   
4. allocated channel: ORA_DISK_1
   
5. channel ORA_DISK_1: sid=139 devtype=DISK
   
6. channel ORA_DISK_1: starting full datafile backupset
   
7. channel ORA_DISK_1: specifying datafile(s) in backupset
   
8. input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
   
9. channel ORA_DISK_1: starting piece 1 at 24-MAR-17
   
10. channel ORA_DISK_1: finished piece 1 at 24-MAR-17
    
11. piece handle=/setup/datafile6_0frvus6l_1_1 tag=TAG20170324T164404 comment=NONE
    
12. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
    
13. Finished backup at 24-MAR-17

生成文件情况：

点击(此处)折叠或打开

1. RMAN> list backup of datafile 6;
2. List of Backup Sets
   
3. ===================
4. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
5. ------- ---- -- ---------- ----------- ------------ ---------------
   
6. 12 Full 348.09M DISK 00:00:19 24-MAR-17
   
7.         BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20170324T164404
   
8.         Piece Name: /setup/datafile6_0frvus6l_1_1
   
9.   List of Datafiles in backup set 12
   
10.   File LV Type Ckp SCN Ckp Time Name
    
11.   ---- -- ---- ---------- --------- ----
    
12.   6 Full 1575161 24-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf

【总结】               

	备份集大小	备份时间（秒）
全新数据文件	88K	16
创建T表后(350M)	348.09M	36
Delete from t	348.09M	25
Truncate table t	348.09M	25

通过上述数据不难发现：
在10.2.0.1环境下，delete、truncate表都不会改变生成备份集大小，其对应的备份时间也无明显改善。因此可推断"Unused Block Compression"对该版本并不适用。为了解答心中的疑惑，又打开mos查找相关文档，找到了关于此问题的答案：
unused block 压缩 对 10.2.0.1版本是不适用的。
只有当在打10.2.0.2patch之后建立的tablespace 才能使用unused block 压缩
The cause of this problem has been identified and verified in an 'unpublished' Bug 4720762 - Fixed in Product Version 11.0

计划抽空再对11g版本进行测试。

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

转载于:http://blog.itpub.net/29827284/viewspace-2136106/