Friday, January 27, 2006

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 backup
type to compressed backupset;
# make it the default!
or
RMAN> 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

3 Comments:

At Sat Nov 04, 06:11:00 PM, Anonymous Anonymous said...

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

 
At Mon Jul 02, 11:04:00 PM, Anonymous vaibhav said...

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

 
At Mon Jul 02, 11:05:00 PM, Anonymous vaibhav said...

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

 

Post a Comment

<< Home