Analysis on the reason why the RMAN backup file is much larger than the database size

Analysis on the reason why the RMAN backup file is much larger than the database size

When talking with the customer, the customer proposed that the database size is only 40 GB, but the file backed up by RMAN is 80 GB, And the DUMP file exported by expdp is less than 40 GB, I feel a little surprised. Isn't RMAN only backing up data blocks? How can backup not exceed 40 GB? Is it related to the high level line? With these questions, I read some articles on MOS and found that there is indeed a possibility that the RMAN backup file is much larger than the space occupied by the database.

First, understand the compression type of RMAN:

By default RMAN has three types of compression:

1. Null compression

2. Unused block compression

3. Binary compression <this is the most common compression type, backup as compressed backupset ....

Till Oracle Version 10.1 only 'null' compression is done by default but from Oracle Version 10.2 'null' and 'unused Block' compression is done. the types of compression are done automatically, no special command is required.

'Null' and 'unused Block' compression are filtering which bloich are sent to the backups. 'binary 'compression is an additional compression on the blocks send to the backup.

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. (In previous releases, this behavior was referred to as NULL compression ). this means RMAN will never backup the blocks that are ever used. RMAN, through Oracle version 9i and forward has stored med null compression.

Example: You have a tablespace having one datafile of size 100 MB and out of 100 MB only 50 MB is used. then RMAN will backup only 50 MB. <in versions earlier than 10.2 (accurate to versions earlier than 10.2.0.2), Oracle adopts this compression type by default. RMAN does not back up data blocks that have never been allocated. In other words, RMAN does not back up blocks that have never been used. For example, if a tablespace has a data file of MB, this file uses 50 MB, RMAN only backs up 50 MB of data (it does not matter whether it is an empty block under high water level or whether the data block has been allocated ).

NOTE:
Null Compression also applies to SE and SE ONE

2. Unused Block Compression:

From Oracle version 10.2 forward, RMAN skips the blocks that do no 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. 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.

Example: You have a tablespace having one datafile of size 100 MB and out of 100 MB, 50 MB is used by the user tables. then user dropped a table belonging to that tablespace which was of 25 MB, with the new unused block compression on 25 MB of the files is backed up. in this example if null compression is used then it wocould have backed up 50 MB because null compression will consider the blocks that are Formatted/ever used. <from Version 10.2 (accurate to version 10.2.0.2), a new Compression type-Unused Block Compression, this Backup Type skips blocks that do not currently contain data (even if this block has been used). This is the default compression method for the new version of RMAN. For example, if a tablespace has a data file of MB and 50 MB is used for the user table, the user drops a table of 25 MB in the space, the new default unused block compression type backs up only 25 MB of data blocks. If the COMPRESSION type is null compression, 50 MB of data blocks will be backed up, because the COMPRESSION type of null compression will consider whether the blocks have been formatted or used.

Unused Block Compression is done, if all of the following conditions apply: <although the unused block compression is 10.2 of the default Compression type, but it does not mean that versions 10.2 must use this compression type after being backed up through RMAN. The following five conditions must be met when using this compression type.

+ The COMPATIBLE initialization parameter is set to 10.2 <even if The Oracle database is upgraded to version 10.2.0.2 or later, The value of The COMPATIBLE initialization parameter is null by default, to use the unused block compression function, you must manually set this parameter.

+ There are currently no guaranteed restore points defined for the database <no force restore point in FLASHBACK.

+ The datafile is locally managed <local data file management method. The default value is.

+ The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup <data file as part of The full database backup, or Incremental Backup Based on zero-level backup.

+ The backup set is being created on DISK <The backup set is created on The DISK.
Or
Backup is done to TAPE using "OSB" (Oracle Secure Backup )! <Or use OSB to back up data to tape. other third-party backup software cannot use this COMPRESSION type. It can only be null compression.

 

Note:
Unused Block Compression is NOT used if backup done to tape using a third party backup software!
The Unused block compression IS executed in Standard Edition. <Standard Edition can also be compressed.

 

Skipping unused data blocks where possible enables RMAN to back up datafiles using less space, and can make I/O more efficient.

The above content is extracted from A Complete Understanding of RMAN Compression (Document ID 563427.1).

The following content indicates that the unused block compression type is unavailable in 10.2.0.1:

Unused block compression is NOT available with the Base Release of 10gR2 (10.2.0.1 ).

It only became available in the first Patch Set Release (10.2.0.2) for NEW tablespaces only.

So for tablespaces created:

• Before 10.2.0.2 was applied
• Before compatible was set to 10.2

We cannot use Unused Block Compression.

The cause of this problem has been identified and verified in an 'unpublished' Bug 4720762-Fixed in Product Version 11.0

The above content is taken from: Why is RMAN not using Unused Block Compression during backup? (Documentation ID 798844.1)

Conclusion: After confirmation with the customer, the customer database version is 10.2.0.4, So we boldly speculate that although the customer's version is higher than 10.2.0.2, however, because the database initialization parameter COMPITABLE is null by default or does not meet other conditions, the unused block compression type cannot be used in RMAN, therefore, all data blocks that have been used in RMAN will be backed up (null compression). 80 GB may be the size of the space that the customer has used, and then the corresponding space is released, therefore, the current backup shows that the 40 GB database RMAN backup has 80 GB.

-- End --