RMAN does (not?) backup empty blocks | what is NULL COMPRESSION ? | what is BINARY COMPRTESSION ?| what is UNUSED BLOCK COMPRESSION ?

In my class this week I have pointed out an important point about RMAN which is commonly misunderstood by many DBAs:
Some believe that RMAN does not backup empty blocks at all.
Others believe that RMAN does not backup empty block above the HIGH WATER MARK.
TRUE IS:
RMAN in ORACLE 9i and before does not backup empty blocks which have never been used!!!
in other words: RMAN does not backup empty blocks above the high watermark which are not formated!!
This is called NULL COMPRESSION.

In Oracle 10g Release 2 RMAN does not backup unused blocks (empty blocks below the High watermark).
This new feature is called UNUSED BLOCK COMPRESSION, see online doku 10gr2:
RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. In previous releases, RMAN only supported NULL compression, which skipped space in datafiles that had never been allocated. No extra action is required on the part of the DBA to use this feature.

AS of ORACLE 10g RMAN can even compress backupsets. (Binary backupset compression), which can reduce the space needed for the backup to one fifth of size of an uncompressed backup.

RMAN> configure device type disk backuptype to compressed backupset;# make it the default!orRMAN> backup as compressed backupset database;-- only compress this single one


Here is my demo for NULL COMPRESSION from my class:
1. I create a new tablespace with one datafile of size 10m.
2. I backup the datafile with RMAN, => very small backupset!
3. I create a table in this datafile and insert 1000000 rows.
4. I take another backup with RMAN, => big backupset.
5. I delete the rows and commit;
6. I take another backup with RMAN, => still big backupset.
7. I truncate the table, high watermark is down to first block in first extent!!
8. I take another backup with RMAN, => still big backupset.

Here is the demo-code:

sys@orcl9iR2 SQL> CREATE TABLESPACE lutz_test
2 DATAFILE ‘/opt/oracle/oradata/orcl/lutz_test_01.dbf’ SIZE 10m
sys@orcl9iR2 SQL> SELECT file#
2 FROM v$datafile
3 WHERE name = ‘/opt/oracle/oradata/orcl/lutz_test_01.dbf’ ;
FILE#
———-
9
sys@orcl9iR2 SQL> SELECT bytes, blocks, user_bytes, user_blocks
2 FROM dba_data_files
3 where FILE_ID=9;
BYTES BLOCKS USER_BYTES USER_BLOCKS
———- ———- ———- ———–
10485760 1280 10420224 1272
sys@orcl9iR2 SQL> SELECT (10485760-10420224)/1024 FROM dual;
(10485760-10420224)/1024
————————
64
# 64K contain extent bitmap for tablespace
[oracle@KR11-KL1 oracle]$ rman target /
Recovery Manager: Release 9.2.0.7.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1108329042)
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ll /home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman
-rw-r—– 1 oracle oinstall 98304 Jan 27 06:50 /home/oracle/BACKUP/RMAN/0nh9sopv_1_1.rman
sys@orcl9iR2 SQL> SELECT 98304/1024 FROM dual;
98304/1024
———-
96
# size of backupset with empty datafile

sys@orcl9iR2 SQL> ! ls -l /opt/oracle/oradata/orcl/lutz_test_01.dbf
-rw-r—– 1 oracle oinstall 10493952 Jan 27 06:50 /opt/oracle/oradata/orcl/lutz_test_01.dbf
sys@orcl9iR2 SQL> SELECT 10493952/1024 FROM dual;
10493952/1024
————-
10248
# size of empty datafile on disk

sys@orcl9iR2 SQL> CREATE TABLE lutz_tab
2 ( sp1 NUMBER) TABLESPACE lutz_test;
sys@orcl9iR2 SQL> BEGIN
2 FOR i IN 1..1000000 LOOP
3 INSERT INTO lutz_tab values(i);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
sys@orcl9iR2 SQL> COMMIT;
Commit complete.

RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman
-rw-r—– 1 oracle oinstall 9535488 Jan 27 07:02 /home/oracle/BACKUP/RMAN/0ph9spfo_1_1.rman
sys@orcl9iR2 SQL> SELECT 9535488/1024 FROM dual;
9535488/1024
————
9312
# size of backupset with datafile after inserts

sys@orcl9iR2 SQL> DELETE FOM lutz_tab;
1000000 rows deleted.
sys@orcl9iR2 SQL> COMMIT;
Commit complete.
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman
-rw-r—– 1 oracle oinstall 9535488 Jan 27 07:05 /home/oracle/BACKUP/RMAN/0rh9spll_1_1.rman
# size of backupset with datafile AFTER DELETING all rows

sys@orcl9iR2 SQL> TRUNCATE TABLE lutz_tab;
Table truncated.
RMAN> BACKUP DATAFILE 9;
channel ORA_DISK_1: finished piece 1 at 27-JAN-06
piece handle=/home/oracle/BACKUP/RMAN/0th9spok_1_1.rman comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-JAN-06
[oracle@KR11-KL1 oracle]$ ls -l /home/oracle/BACKUP/RMAN/0th9spok_1_1.rman
-rw-r—– 1 oracle oinstall 9535488 Jan 27 07:07 /home/oracle/BACKUP/RMAN/0th9spok_1_1.rman
# size of backupset with datafile AFTER TRUNCATING
!!! empty formatted blocks backed up!!!

Related Metalink Notes:
Note: 247705.1 RMAN BACKUP COMPRESSION
Note: 255973.1 Oracle10g Recovery Manager – Backup Compression Methods

connect lutz hartmann as sysdba

Possibly related posts: (automatically generated)

This entry was posted on Friday, January 27th, 2006 at 6:13 am and is filed under RMAN. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

7 Responses to “RMAN does (not?) backup empty blocks | what is NULL COMPRESSION ? | what is BINARY COMPRTESSION ?| what is UNUSED BLOCK COMPRESSION ?”

  1. Anonymous Says:
    November 4, 2006 at 10:17 pm | Reply

    This is very useful. None of the Oracle Docs points this out as clear as you do. Thanks.

  2. an ou instructor Says:
    March 30, 2007 at 7:36 am | Reply

    Great stuff!

  3. Lutz Hartmann Says:
    March 30, 2007 at 8:43 am | Reply

    Hi Marielle,
    thanks for the feedback!
    =;-)
    Lutz

  4. vaibhav Says:
    June 30, 2007 at 4:25 pm | Reply

    Hi Lutz,

    I was checking RMAN 10g feature “Unused Block Compression”

    Following steps were performed ( Oracle Database 10g R2 10.2.0.3.0)

    1) Create tablespace USERS of size 40M

    2) Create table TEST in tablespace USERS

    3) Take RMAN backup of tablespace users
    SQL> select blocks/datafile_blocks from v$backup_datafile where file#=4 ;
    BLOCKS/DATAFILE_BLOCKS
    ———————-
    .976757813

    4) Delete all rows from table TEST

    5) Take RMAN backup of tablespace users
    SQL> select blocks/datafile_blocks from v$backup_datafile where file#=4 ;
    BLOCKS/DATAFILE_BLOCKS
    ———————-
    .976757813

    6) Check the size of backup pieces generated in step 3 and 5.
    The size is same – 39.1 M

    So it seems that RMAN is taking backup of the blocks which don’t contain the user data.

    Thanks and Regards,
    Vaibhav

  5. Alan Says:
    August 13, 2007 at 9:39 pm | Reply

    Vaibhav,
    You may only see the result of ‘Unused block compression” if you do a full (or level 0) backup, except for other restrictions.
    Regards,
    Alan

  6. Vaibhav Says:
    August 21, 2007 at 7:42 pm | Reply

    Hi Alan,

    Thank you for the information.

    Eventhough the incremental level 0 backup is taken, the blocks (empty/unused) which are allocated get backed up.

    RMAN skips the blocks which are currently not allcoated. I request going through following testcase.

    SQL> select * from v$version;
    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod
    PL/SQL Release 10.2.0.3.0 – Production
    CORE 10.2.0.3.0 Production
    TNS for 32-bit Windows: Version 10.2.0.3.0 – Production
    NLSRTL Version 10.2.0.3.0 – Production

    Took backup (incremental level 0) of the database ==> size 773M

    SQL> select blocks,empty_blocks from user_tables where table_name=’TEST’;
    BLOCKS EMPTY_BLOCKS
    ———- ————
    61097 343

    SQL> truncate table test reuse storage;
    Table truncated.

    SQL> analyze table test compute statistics;
    Table analyzed.

    SQL> select blocks,empty_blocks from user_tables where table_name=’TEST’;
    BLOCKS EMPTY_BLOCKS
    ———- ————
    0 61440

    SQL> declare
    2 vtotal_blocks number;
    3 vtotal_bytes number;
    4 vunused_blocks number;
    5 vunused_bytes number;
    6 vlast_used_extent_file_id number;
    7 vlast_used_extent_block_id number;
    8 vlast_used_block number;
    9 begin
    10 DBMS_SPACE.UNUSED_SPACE (‘SCOTT’,'TEST’,'TABLE’,vtotal_blocks,vtotal_bytes,vunused_blocks,vunused_bytes,v
    last_used_extent_file_id,
    11
    12 vlast_used_extent_block_id,vlast_used_block);
    13 dbms_output.put_line(‘ Unused blocks =>’||vunused_blocks);
    14 end;
    15 /
    Unused blocks =>61437

    PL/SQL procedure successfully completed.

    Took backup (incremental level 0) of the database ==> size 773M

    SQL> truncate table test ;
    Table truncated.

    SQL> analyze table test compute statistics;
    Table analyzed.

    SQL> select blocks,empty_blocks from user_tables where table_name=’TEST’;
    BLOCKS EMPTY_BLOCKS
    ———- ————
    0 8

    Took backup (incremental level 0) of the database ==> size 300 M

    Thanks and Regards,
    Vaibhav

  7. Raghu Says:
    September 24, 2009 at 7:45 am | Reply

    Hii..
    Easy to understand…very informative…
    Thanks
    Raghu

Leave a Reply