Why is RMAN not using Unused Block Compression during backup?
Last updated on NOVEMBER 21, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 18.104.22.168 [Release 10.2 to 11.2]
Information in this document applies to any platform.
RMAN database is running at release 10.2 and meets all the criteria for use of Unused Block Compression:
The COMPATIBLE initialization parameter is set to 10.2
There are currently no guaranteed restore points defined for the database
The datafile is locally managed
The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup
The backup set is being created on disk.
Oracle Database Backup and Recovery Reference
11g Release 2 (11.2)
Part Number E10643-04
If COMPATIBLE is set to 10.2, then only tablespaces created with 10.2 compatibility will be 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 will update the headers of all locally managed datafiles so that all locally managed datafiles can be optimized.
However, the following query from v$backup_datafile shows that all the datafiles are fully scanned during backup (%READ is 100):
SQL> set lines 120
SQL> select file# fno, used_change_tracking BCT,
incremental_level INCR, datafile_blocks BLKS,
block_size blksz, blocks_read READ,
round((blocks_read/datafile_blocks) * 100,2) "%READ",
blocks WRTN, round((blocks/datafile_blocks)*100,2) "%WRTN"
where completion_time between
to_date('25-jan-09 01:30:00', 'dd-mon-rr hh24:mi:ss') and
to_date('26-jan-09 12:35:26', 'dd-mon-rr hh24:mi:ss')
order by file#;
-- Change the dates as necessary to encompass the backup start and end times
-- and check the %READ value for each datafile.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms