How to influence Oracle backup output size?

Question

I have two environment one Prod one Dev. Dev is created by Prod data and both have a similar database size. However, the backup output size varies greatly. Prod is about 300 M while Dev always above 1G. Both using incremental Level 1 backup.

Why would this be happening?

Answer 1

Incremental Level 1 backup in RMAN means all data blocks changed since last Incremental Level 1 or 0 backup (0 is the most basic level, it always contains all data).

If you use CUMULATIVE keyword on Dev, the incremental level 1 contains all data blocks changed since last Incremental Level 0 backup. Which means more of them. One possible explanation.

RMAN only cares whether the block was written to, there are no efforts here to de-duplicate basing on the data. Maybe the Prod received less of such block writes, and Dev more. In particular, if you import into Dev, this definitely counts here. Another possible reason.

Manual says, that unused-never-written-to blocks are always skipped but, in contrast, the unused blocks are skipped only in a very specific set of circumstances:

• The COMPATIBLE initialization parameter is set to 10.2 or higher.

• Note that if COMPATIBLE is 10.2, then only tablespaces created with 10.2 compatibility will be optimized to exclude blocks that do not currently contain data. If COMPATIBLE is 11.0.0 or higher, however, then the first backup that produces backup sets after COMPATIBLE is set to 11.0.0 or higher will update the headers of all locally managed datafiles so that all locally managed datafiles can be optimized.

• There are currently no guaranteed restore points defined for the database.

• The datafile is locally managed.

• (sic!) The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup.

• The backup set is created on disk or Oracle Secure Backup is the media manager. Thus, when backing up to a media manager other than Oracle Secure Backup, RMAN does not skip datafile blocks that do not currently contain data.

So, Dev could simply back up unused blocks, and there are plenty of reasons why Prod would have no symmetry here. Another possible reason.

RMAN will do Level 0 (i.e. full) if it believes you erroneously requested Level 1. Manual says:

The database performs checks when attempting to create a level 1 incremental backup to ensure that the incremental backup is usable by a subsequent RECOVER command. Among the checks performed are:

• A level 0 backup must exist for each datafile in the BACKUP command as the base backup for an incremental strategy. Level 0 backups must not have status UNAVAILABLE. If no level 0 backup exists, then RMAN makes a level 0 backup automatically.

• Sufficient incremental backups taken since level 0 must exist and be available such that the incremental backup to be created is usable.

Especially the last point may be tricky. Another possible reason.

Answer 2

Well it's questionable what happened to your dev. database. Check total sizes of tablespaces vs. sizes of database segments. Oracle does distinguish "used blocks", "unused blocks" and "never used blocks". Surprisingly even unused blocks are being backed up. So for example if somebody tried to rebuild (or recreate) all the database indexes then our tablespaces got fragmented and size of RMAN backups grown. Try to check column LAST_DDL_TIME on DBA_OBJECTS table.