28 January, 2007

Deleting data doesn't reduce the size of the backup

You sometimes may have a fairly large ["fairy large" is relative to a) the average size of database in your organisation b) the disk storage and I/O capacity it is running on c) the backup throughput and tape sizes] database that is taking many hours to backup. You are doing full image backups. You or your users may be tempted to delete data to "reduce the size of the backup" and/or to "reduce the time it takes to backup" and/or to "reduce the duration that datafiles are in backup mode".
Unfortunately, merely deleting rows [even 10% or 99%] in table[s] doesn't really reduce the size or duration of most image backups. The delete operation only adds to the total amount of undo and a very large increase in redo and archivelog generation for which you may have to scramble for disk space.
An image backup [whether by "cp" or "cpio" or "tar"] of the Datafiles will still see the Datafiles as having the same size because the delete doesn't shrink the datafiles.
An RMAN backup will still have to backup as many datablocks as before the delete. This is true _even_ if you are successful in deleting rows in such a manner that some or many blocks are 100% empty. Once a datablock has got formatted it will always be backed-up by RMAN, whether there are rows or no rows in it subsequently.

No comments: