Skip to content

RMAN Backup Compression

November 4, 2013

############################################################################################################
                                                Backup Compression
############################################################################################################
If disk space is at a premium, you have a very large database, and you have some extra CPU capacity, it makes sense to compress the backups to save space. The files are decompressed automatically during a restore or recovery operation.

RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO
COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN>

Compressing backupsets may not be necessary if the operating system’s file system has compression enabled or if the tape device hardware automatically compresses backups;however, RMAN’s compression algorithm is tuned to efficiently back up Oracle data blocks, and as a result it may do a better job of compressing the backupsets

We can either configure backup compression as the default for backupsets

OR Explicitly specify compression in an RMAN backup command.
————————————————————————————————————————————–

RMAN> configure device type disk backup type to compressed backupset;

OR
RMAN> backup as compressed backupset database;
The database files occupy about 2.7GB in the +DATA disk group;
the compressed backupset from RMAN comes in at 241MB, which is well over a 90 percent compression rate.

RMAN> show compression algorithm;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name D041 are:
CONFIGURE COMPRESSION ALGORITHM ‘BASIC‘ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

SQL> desc v$rman_compression_algorithm

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ALGORITHM_NAME                                     VARCHAR2(64)
 INITIAL_RELEASE                                    VARCHAR2(18)
 TERMINAL_RELEASE                                   VARCHAR2(18)
 ALGORITHM_DESCRIPTION                              VARCHAR2(64)
 ALGORITHM_COMPATIBILITY                            VARCHAR2(18)
 IS_VALID                                           VARCHAR2(3)
 REQUIRES_ACO                                       VARCHAR2(3)

col ALGORITHM_NAME for a10
set line 200
select * from v$rman_compression_algorithm;

ALGORITHM_ INITIAL_RELEASE    TERMINAL_RELEASE   ALGORITHM_DESCRIPTION                                            ALGORITHM_COMPATIB IS_ REQ
---------- ------------------ ------------------ ---------------------------------------------------------------- ------------------ --- ---
BASIC      10.0.0.0.0                            good compression ratio                                           9.2.0.0.0          YES NO
LOW        11.2.0.0.0                            maximum possible compression speed                               11.2.0.0.0         YES YES
MEDIUM     11.2.0.0.0                            balance between speed and compression ratio                      11.0.0.0.0         YES YES
HIGH       11.2.0.0.0                            maximum possible compression ratio                               11.2.0.0.0         YES YES

http://kkempf.wordpress.com/2009/09/09/11gr2-rman-you-are-too-dumb-to-pick-a-compression-algorithm/

In reality, these terms map out as follows:

HIGH = unmodified BZIP2
MEDIUM = ZLIB
LOW = LZO
BASIC = BZIP2

We have BASIC and Advanced compression Enabled as part of Oracle Software installation:
===========================================================================================
SQL> select PARAMETER ,VALUE as Value_T_F from v$option where PARAMETER like ‘%Compression%’;

PARAMETER                                                        VALUE_T_F
—————————————————————- ———-
Basic Compression                                                TRUE
Unused Block Compression                                         TRUE
Advanced Compression                                             TRUE
SQL>

SQL>  select round(sum(bytes)/1024/1024/1024) “IN GB ” from v$datafile;
IN GB
———-
1406

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1) When compression method is set to BASIC => CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(a) CPU Utilization               : Rman was using the Average of 25% of CPU
(b) Compression of Backup         : 1 TB of database size when backed up in compressed mode, backup size came to 244G. Approx 83% compressed
(c) Time take for the Backup was     : 6Hrs 20 Mins

==== RMAN Backup started on Tue Jan 25 02:55:38 EST 2011 ====

==== RMAN Backup Ended on Tue Jan 25 09:14:43 EST 2011 ====

SQL> @cpu1  (attached script at bottom of the post)
Enter value for start_date: 2501201100300    (MMDDYYYYHH24MI)
Enter value for end_date: 250120111000

Snap                                                                      Oracle   RMAN     OS
Start                       OS        IO r        IO w        Redo   Sess    CPU    CPU    CPU
Time                      Load      (mb)/s      (mb)/s      (mb)/s  Start      %      %      %
--------------- -------------- ----------- ----------- ----------- ------ ------ ------ ------
01/25/11 09:00            2.71       2.366       0.064       0.015    253      5      4     19
01/25/11 08:00            5.77       0.177       0.131       0.083    254     22     22     36
01/25/11 07:00            6.58       2.665       0.076       0.024    264     27     25     41
01/25/11 06:00            7.28       0.162       0.177       0.197    273     26     25     41
01/25/11 05:00            6.97       0.128       0.094       0.004    270     25     25     40
01/25/11 04:00            7.41       2.950       0.168       0.199    268     27     25     43
01/25/11 03:00            7.13       1.417       0.049       0.015    255     26     25     41

7 rows selected.

mgd041srv:/oradata/d041/backup/hotback: du -hs .
 244G   .

mgd041srv:/oradata/d041/backup/hotback: cat cpu.log

SunOS mgd041srv 5.10 Generic_142900-12 sun4u    01/25/2011

02:10:37    %usr    %sys    %wio   %idle
02:13:37       6       9       0      86
02:16:37       5       9       0      86
02:19:37       5       8       0      87
02:22:38       5       8       0      87
02:25:38       5       8       0      87
02:28:38       6       8       0      86
02:31:38       5       8       0      87
02:34:38       5       8       0      87
02:37:39       8       9       0      83
02:40:39       6       8       0      86
02:43:39       8       9       0      83
02:46:39       5       8       0      88
02:49:39       5       8       0      87
02:52:39       5       8       0      88
02:55:40       5       8       0      87
02:58:40      26       9       0      65
03:01:40      32      10       0      58
03:04:40      31       9       0      60
03:07:40      31       9       0      60
03:10:41      31      10       0      59
03:13:41      30      10       0      60
03:16:41      32      10       0      59
03:19:41      33      10       0      57
03:22:41      31      10       0      59
03:25:42      31      10       0      59
03:28:42      33      10       0      57
03:31:42      32      10       0      58
03:34:42      31       9       0      59
03:37:42      33      10       0      57
03:40:43      32      10       0      59
03:43:43      32      10       0      58
03:46:43      32      10       0      58
03:49:43      31       9       0      60
03:52:43      31       9       0      60
03:55:44      33      11       0      56
03:58:44      32       9       0      59
04:01:44      34      10       0      56
04:04:44      33      10       0      58
04:07:44      34      11       0      55
04:10:44      33      11       0      56
04:13:45      38      12       0      51
04:16:45      36      11       0      53
04:19:45      35      11       0      54
04:22:45      32       9       0      59
04:25:45      31      10       0      59
04:28:46      31       9       0      60
04:31:46      30       9       0      60
04:34:46      30       9       0      61
04:37:46      31      10       0      59
04:40:46      32      11       0      57
04:43:47      32      11       0      57
04:46:47      31      10       0      58
04:49:47      32      11       0      57
04:52:47      31      11       0      58
04:55:47      32      11       0      56
04:58:48      33      11       0      56
05:01:48      33      12       0      55
05:04:48      31      10       0      58
05:07:48      32      11       0      57
05:10:48      32      11       0      57
05:13:49      32      11       0      58
05:16:49      30       9       0      61
05:19:49      30      10       0      61
05:22:49      30       9       0      61
05:25:49      31       9       0      60
05:28:49      31       9       0      60
05:31:50      30       9       0      61
05:34:50      30       9       0      61
05:37:50      30       9       0      61
05:40:50      30       9       0      61
05:43:50      30       9       0      60
05:46:51      30       9       0      61
05:49:51      30       9       0      61
05:52:51      30       9       0      62
05:55:51      31       9       0      60
05:58:51      30       9       0      61
06:01:52      32      10       0      59
06:04:52      30       9       0      61
06:07:52      30       9       0      60
06:10:52      30       9       0      61
06:13:52      30       9       0      61
06:16:52      30       9       0      61
06:19:53      30       9       0      60
06:22:53      30       9       0      61
06:25:53      31       9       0      60
06:28:53      30       9       0      61
06:31:53      32       9       0      59
06:34:54      35       9       0      56
06:37:54      31      10       0      60
06:40:54      31       9       0      60
06:43:54      37      11       0      52
06:46:54      31      10       0      60
06:49:55      31      10       0      59
06:52:55      31      10       0      59
06:55:55      32      10       0      58
06:58:55      31       9       0      60
07:01:55      32      11       0      57
07:04:55      30       9       0      61
07:07:56      30      10       0      60
07:10:56      30       9       0      61
07:13:56      31      10       0      60
07:16:56      30       9       0      61
07:19:56      30       9       0      61
07:22:57      30       9       0      61
07:25:57      31       9       0      59
07:28:57      30       9       0      61
07:31:57      31      10       0      59
07:34:57      33      10       0      57
07:37:58      38      10       0      51
07:40:58      38      10       0      52
07:43:58      32       9       0      59
07:46:58      30       9       0      62
07:49:58      30       9       0      61
07:52:59      30       9       0      61
07:55:59      31       9       0      61
07:58:59      30       9       0      60
08:01:59      31      10       0      59
08:04:59      30       9       0      61
08:08:00      30       9       0      60
08:11:00      30       9       0      61
08:14:00      31       9       0      60
08:17:00      30       9       0      61
08:20:00      30       9       0      61
08:23:01      30      10       0      61
08:26:01      31       9       0      60
08:29:01      30       9       0      61
08:32:01      27       9       0      64
08:35:01      24       9       0      68
08:38:02      24      10       0      66
08:41:02      24       9       0      67
08:44:02      27      10       0      64
08:47:02      24       9       0      67
08:50:02      24       9       0      67
08:53:03      24       9       0      67
08:56:03      25       9       0      66
08:59:03      24       9       0      68
09:02:03      25      10       0      65
09:05:03      24       9       0      67
09:08:03      26      11       0      63
09:11:04      26      11       0      63
09:14:04      20      10       0      70
09:17:04       6       9       0      86

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2) When compression method is set to HIGH => CONFIGURE COMPRESSION ALGORITHM ‘HIGH’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(a) CPU Utilization               : Rman was using the Average of 25% of CPU
(b) Compression of Backup         : 1 TB of database size when backed up in compressed mode, backup size came to 199G. Approx 85% compressed.
(c) Time take for the Backup was     : 13Hrs 15 Mins

==== RMAN Backup started on Fri Jan 28 02:43:44 EST 2011 ====

==== RMAN Backup Ended on Sat Jan 29 15:55:29 EST 2011 ====

Snap                                                                      Oracle   RMAN     OS
Start                       OS        IO r        IO w        Redo   Sess    CPU    CPU    CPU
Time                      Load      (mb)/s      (mb)/s      (mb)/s  Start      %      %      %
--------------- -------------- ----------- ----------- ----------- ------ ------ ------ ------
01/28/11 15:00            8.46       0.437       0.958       0.813    187     32     25     61
01/28/11 14:00            8.45       0.800       0.092       0.113    171     29     25     57
01/28/11 13:00            7.27       1.218       0.623       0.448    172     28     25     55
01/28/11 12:00            8.55       1.433       0.439       0.367    164     32     25     60
01/28/11 11:00            8.61       0.932       0.377       0.360    163     34     25     63
01/28/11 10:00            8.34       0.088       0.038       0.062    163     27     25     55
01/28/11 09:00            7.14       0.123       0.018       0.006    163     26     25     53
01/28/11 08:00            7.30       0.140       0.119       0.085    164     26     25     54
01/28/11 07:00            7.07       0.698       0.378       0.131    178     26     25     57
01/28/11 06:00            8.36       0.036       0.019       0.006    196     25     25     58
01/28/11 05:00            7.66       0.016       0.022       0.004    194     25     25     53
01/28/11 04:00            7.42       0.232       0.104       0.063    191     28     25     57
01/28/11 03:00            7.55       0.708       0.813       0.197    192     26     25     56

mgd041srv:/oradata/d041/backup/hotback: du -hs .
199G   .

Script for CPU Load

Advertisements

From → Rman

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: