http://blog.itpub.net/29827284/viewspace-2136106/博文中已经对10g(10.2.0.1)环境进行了测试,下面接着对11g查看Unused Block Compression的情况。
官方相关文档http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta007.htm#RCMRF107
Unused block compression is turned on automatically when all of the following five conditions are true:
1. The COMPATIBLE initialization parameter is set to 10.2 or higher.
Note: If COMPATIBLE is set to 10.2, then only tablespaces created with 10.2 compatibility are optimized to exclude blocks that do not currently contain data. If COMPATIBLE is set to 11.0.0 or higher, however, then the first backup that produces backup sets after COMPATIBLE is set to 11.0.0 or higher updates the headers of all locally managed data files so that all locally managed data files can be optimized.
2. There are currently no guaranteed restore points defined for the database.
3. The data file is locally managed
4. The data file is being backed up to a backup set as part of a full backup or a level 0 incremental backup
5. The backup set is created on disk or Oracle Secure Backup is the media manager.
Note: When backing up to a media manager that is not Oracle Secure Backup, RMAN copies all the blocks regardless of whether they contain data or not.
Note: A corrupt unused block is not harmful. This is because when a block is corrupt and RMAN does not read it because of unused block compression, RMAN does not detect the corruption.
实验环境:
同10g的测试一样,新建表空间,分别进行全新数据文件备份、创建表后的备份、delete备份以及truncate备份,具体过程如下:
点击(此处)折叠或打开
- create tablespace rman_test datafile '/u01/app/oracle/oradata/orcl/rman_test01.dbf' size 500M;
- SELECT FILE_NAME,FILE_ID FROM dba_data_files WHERE TABLESPACE_NAME='RMAN_TEST';
- FILE_NAME FILE_ID
- ------------------------------------------ -----
- /u01/app/oracle/oradata/orcl/rman_test01.dbf 5
1.新建数据文件的备份
点击(此处)折叠或打开
- RMAN> backup datafile 5 format '/archivelog/datafile5_%U';
- Starting backup at 26-MAR-17
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
- channel ORA_DISK_1: starting piece 1 at 26-MAR-17
- channel ORA_DISK_1: finished piece 1 at 26-MAR-17
- piece handle=/archivelog/datafile5_0os04nu0_1_1 tag=TAG20170326T220800 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
- Finished backup at 26-MAR-17
-
- RMAN> list backup of datafile 5;
- List of Backup Sets
- ===================
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 6 Full 1.03M DISK 00:00:00 26-MAR-17
- BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20170326T220800
- Piece Name: /archivelog/datafile5_0os04nu0_1_1
- List of Datafiles in backup set 6
- File LV Type Ckp SCN Ckp Time Name
- ---- -- ---- ---------- --------- ----
- 5 Full 8438803 26-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf
2.创建对象后数据文件备份
点击(此处)折叠或打开
- create table t tablespace rman_test as select * from dba_objects;
- insert into t select * from t;
- commit;
- //反复执行上述命令
- SELECT sum(BLOCKS)*8/1024 FROM user_segments WHERE SEGMENT_NAME='T';
- SUM(BLOCKS)*8/1024
- ------------------
- 416
T表占用空间大约为410M,再次进行rman备份
点击(此处)折叠或打开
- RMAN> backup datafile 5 format '/archivelog/datafile5_%U';
- Starting backup at 26-MAR-17
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
- channel ORA_DISK_1: starting piece 1 at 26-MAR-17
- channel ORA_DISK_1: finished piece 1 at 26-MAR-17
- piece handle=/archivelog/datafile5_0ps04obu_1_1 tag=TAG20170326T221526 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
- Finished backup at 26-MAR-17
-
- RMAN> list backup of datafile 5;
- List of Backup Sets
- ===================
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 7 Full 415.30M DISK 00:00:10 26-MAR-17
- BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20170326T221526
- Piece Name: /archivelog/datafile5_0ps04obu_1_1
- List of Datafiles in backup set 7
- File LV Type Ckp SCN Ckp Time Name
- ---- -- ---- ---------- --------- ----
- 5 Full 8441133 26-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf
3.delete后备份
点击(此处)折叠或打开
- RMAN>backup datafile 5 format '/archivelog/datafile5_%U';
- Starting backup at 26-MAR-17
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=1 device type=DISK
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
- channel ORA_DISK_1: starting piece 1 at 26-MAR-17
- channel ORA_DISK_1: finished piece 1 at 26-MAR-17
- piece handle=/archivelog/datafile5_0qs04olb_1_1 tag=TAG20170326T222026 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
- Finished backup at 26-MAR-17
-
- RMAN> list backup of datafile 5;
- List of Backup Sets
- ===================
-
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 8 Full DISK 00:00:11 26-MAR-17
- BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20170326T222026
- Piece Name: /archivelog/datafile5_0qs04olb_1_1
- List of Datafiles in backup set 8
- File LV Type Ckp SCN Ckp Time Name
- ---- -- ---- ---------- --------- ----
- 5 Full 8494438 26-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf
点击(此处)折叠或打开
- RMAN> backup datafile 5 format '/archivelog/datafile5_%U';
- Starting backup at 26-MAR-17
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=43 device type=DISK
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/rman_test01.dbf
- channel ORA_DISK_1: starting piece 1 at 26-MAR-17
- channel ORA_DISK_1: finished piece 1 at 26-MAR-17
- piece handle=/archivelog/datafile5_0rs04p4i_1_1 tag=TAG20170326T222834 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
- Finished backup at 26-MAR-17
-
- RMAN> list backup of datafile 5;
- List of Backup Sets
- ===================
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 9 Full 1.53M DISK 00:00:00 26-MAR-17
- BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20170326T222834
- Piece Name: /archivelog/datafile5_0rs04p4i_1_1
- List of Datafiles in backup set 9
- File LV Type Ckp SCN Ckp Time Name
- ---- -- ---- ---------- --------- ----
- 5 Full 8496079 26-MAR-17 /u01/app/oracle/oradata/orcl/rman_test01.dbf
| 备份集大小(M) | 备份时间(秒) |
全新数据文件 | 1.03 | 1 |
创建T表后(410M) | 415.30 | 16 |
Delete from t | 415.30 | 15 |
Truncate table t | 1.53 | 1 |
【总结】相比10g(10.2.0.1)版本,可以看到
1.truncate后,备份效率已经得到了改善,基本和全新数据文件差别不大。
2.delete表后,备份效率依然和10g一样,备份机大小以及备份时间与delete前基本相同。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29827284/viewspace-2136113/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29827284/viewspace-2136113/