Feb 22, 2014

Oracle 11gR2 RMAN Backup Compression : with Case study

RMAN Backup Compression : Oracle 11g Release 2 

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN  backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands:

Binary compression creates some performance overhead during backup and restore operations. Binary compression consumes CPU resources, so compressed backups should not be scheduled when CPU usage is already high. However, the following circumstances may warrant paying the performance penalty:

  • You are using disk-based backups when disk space in your flash recovery area or other disk-based backup destination is limited.
  • You are performing your backups to some device over a network when reduced network bandwidth is more important than CPU usage.
  • You are using some archival backup media such as CD or DVD, where reducing backup sizes saves on media costs and archival storage.


Commands to be used:

Step-1: Set RMAN configuraton for backup type:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

Step-2: Set RMAN configuraton for compresion algorithim type:

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Step-3: Chnage your bacup script :
rman > backup as compressed backupset incremental level 0 database;
OR
connect target `/'
run
{
allocate channel ch1 device type disk;
backup as compressed backupset incremental level 0 database;
release channel ch1;
}

Example:

RMAN> configure device type disk backup type to compressed backupset;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
configure device type disk backup type to compressed backupset parallelism 1;
new RMAN configuration parameters are successfully stored

RMAN> show all;
configure device type disk backup type to compressed backupset parallelism 1;

RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

It is possible to compress archived redo log and incremental backupsas follows:

RMAN> backup as compressed backupset archivelog all;
RMAN> backup as compressed backupset incremental level 0 database;
RMAN> backup as compressed backupset incremental level 1 database;
RMAN> backup as compressed backupset incremental level 1 cumulative database;

To de-configure:

By changing this parameter, all backups will be compressed.  To return to the default non-compressed configuration, clear the configuration to make future backups be non-compressed:

RMAN> configure device type disk clear;
old RMAN configuration parameters:
configure device type disk backup type to compressed backupset parallelism 1;
RMAN configuration parameters are successfully reset to default value
RMAN> show all;
configure device type disk parallelism 1 backup type to backupset; # default

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of

LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size: 276.55M
time: 00:00:50

To summaries we can conclude:

LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

Note : Binary compression can be CPU-intensive. You can choose which compression algorithm that RMAN uses for backups. By default, RMAN uses BZIP2, which has a very good compression ratio. ZLIB compression, which requires a COMPATIBLE setting of 11.0.0 or higher, is very fast but has a lower compression ratio than other algorithms.

As shown, the compressed backup is smaller in size than the uncompressed backup. To identify which files were compressed, query v$backup_files as follows:

sql> column fname format a90;
sql> set lines 180;
sql> set pages 500;
SQL> select fname, compressed from v$backup_files; 

FNAME                     COMPRESSED
-----------               -----------
C:\uncompressed_sysaux.bkp       NO
C:\copmressed_sysaux.bkp           YES

I have tested in one of my production database environment with following configurations:
-----------------------------------------------------------------------------------------------
-- Before compression method ( When CPU% was <10%)
a) Backup size :  20.68G
b) Elapsed Time : 6 min ( max)
-- Applied following commands and see the statistics
1) RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
2) CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
3) backup as compressed backupset incremental level 0 database;

-- After applied compression method ( When CPU% was <10%)
a) Backup size :  4.03G ( around 80% reduced)
b) Elapsed Time : 8 min ( around 30% time consumption increased)

So based on calculation and test scenarios, you decide what type of compression method you will follow. But It is best practice when CPU utilization is very very less for longer duration in off peak hours in OLTP database environments, you can go for any compression method.

In case of doubts / clarification, please write to me. Please feel free to write comments.

No comments:

Post a Comment

Translate >>