Configure Rman Backupset Compression for 10g+

Ref:

http://neeraj-dba.blogspot.com/2011/12/configure-rman-backupset-compression.html

Configure Rman Backupset Compression

RMAN compresses the backup set contents before writing them to disk. No extra uncompression steps are required during recovery when we use RMAN compression. RMAN has two types of compression:  

1.) Null Compression     and
2.) Unused Block Compression

1.) Null Compression :  When backing up datafiles into backup sets, RMAN does not back up the contents of data blocks that have never been allocated. This means RMAN will never backup the blocks that are ever used. For example: We have a tablespace having one datafile of size 100MB and out of 100MB only 50 MB is used. Then RMAN will backup only 50MB. 

2.) Unused Block Compression :  RMAN skips the blocks that do not currently contain data and this is called Unused Block Compression. RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. No extra action is required on the part of the DBA to use this feature. 

Example :  We  have a tablespace having one datafile of size 100MB and out of 100MB, 50MB is used by the user tables. Then user dropped a table belonging to that tablespace which was of 25MB, with Unused Block Compression only 25MB of the files is backed up. In this example if null compression is used then it would have backed up 50MB because Null Compression will consider the blocks that are formatted/ever used. 

Binary Compression : Binary Compression can be done by specifying "AS COMPRESSED" clause in backup command, this compression is called as binary compression. RMAN can apply a binary compression algorithm as it writes data to backup sets. This compression is similar to the compression provided by many tape vendors when backing up data to tape. But we cannot give exact percentage of compression. This binary compression algorithm can greatly reduce the space required for disk backup storage. It is typically 2x to 4x, and greater for text-intensive databases.  The command to take the compressed backup :

RMAN> backup as compressed backupset database ;


There is no special command to restore database from the compressed backupsets. The restore command will be the same as with uncompressed backups.The restore from the compressed backpuset will take more time than uncompressed backupsets.
To use rman compression option, we can run the following RMAN commands to configure compression 


RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
followed by ..
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH' ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM 'LOW' ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC' ; 

Oracle 11g added several compression algorithms to compress data. They can be used for compressing tables, LOBs , compressed data pump exports or even RMAN backups. Unfortunately for some compression algorithms we need to purchase the "Advanced Compression Option". The following table lists the available RMAN compression options, the most likely compression algorithm being used and states if an additional license is required:

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup throughput and the degree of compression afforded. If we have enabled the Oracle Database 11g Release 2 Advanced Compression Option, then we can choose from the following compression levels :

·                     HIGH - Best suited for backups over slower networks where the limiting factor is network speed

·                     MEDIUM - Recommended for most environments. Good combination of compression ratios and speed

·                     LOW - Least impact on backup throughput and suited for environments where CPU resources are the limiting factor.

Note:  The compression ratio generally increases from LOW to HIGH, with a trade-off of potentially consuming more CPU resources.

We can check the compression level by using the command .


SQL> select  *  from V$RMAN_COMPRESSION_ALGORITHM;
Output : 

I found a good scenario on net related to compression level having statistics about the this compression level . Here is the scenario : The environment being used was a freshly created 11g Release 2 database with some smaller tables in it. The total sum of all segments equals to 4.88 GB. All database data files excluding the temporary ones are 7.3 GB total. Excluding temporary and undo data files total size equates to 5.9 GB.


Here is the test results displays of the compression level :
Test results

As we  can see  from the  table  HIGH compression  does an  incredibly high  load on the  machine and  take extremely long but produces the smallest backup set size.Surprisingly BASIC compression (which is available without advanced compression license) does a good job as well and produces the second smallest backup set but takes nearly as long as doing uncompressed backups. But in other environment with faster CPUs this will change . 

In the test environment used either LOW or MEDIUM compression seems to be the best choice. Due to the fact MEDIUM produces a approx. 15% smaller backup set but taking only a few seconds more time to complete i would rank MEDIUM on 1st and LOW on second.

Finally we came to the conclusion that stronger the compression the smaller the backup size but the more CPU-intensive the backup is. If we do not have the advanced compression license BASIC compression will produce reasonable compression rates at moderate Load. If  we have the licence we have a lot more options to suit our needs.

If we want to test and optimize our rman backup, we basically have three major switches to play with :

·                     compression algorithmn

·                     rman parallelism and

·                     data transfer mechanism (SAN or Ethernet [this includes: iSCSI, NFS, CIFS, Backup to tape over Ethernet])

----

以下為我在10g DB 實測的結果 , 在開啟了壓縮功能後 , 170GB的備份時間從27 min 拉長到 40 mins

RMAN>

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

I created a 50MB table , delete the all data and then backup it , the backupset size is compressed to 3.9MB.

Before

-rw-r----- 1 oracle dba 48M Mar 1 09:16 bk_ORCL_datafile_331_1

After

-rw-r----- 1 oracle dba 3.9M Mar 1 09:18 bk_ORCL_datafile_333_1

如果要改為two threads :
RMAN>
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

回復原有設定
RMAN>

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;