Tablespace Backups


It is important to keep the volume of data to be backed up or restored as small as possible, especially when backing up large Oracle databases. One way to do this is by backing up specific tablespaces.


You do not have to back up pure index tablespaces during a database backup. Index definitions are stored in the SYSTEM tablespace as a matter of course. Therefore, indexes can always be recreated.

As it takes longer to recreate indexes than to restore and recover index tablespaces, we do not generally recommend a backup strategy based on the BRBACKUP function –m all_data as described below. Only use this function for a good reason.

To back up all tablespaces except index tablespaces, perform one of the following:

·        Start BRBACKUP with the command option -m all_data.

For more information, see -m|-mode.

·        Set the profile parameter backup_mode = all_data in init<DBSID>sap.

For more information, see backup_mode.

During the backup, BRBACKUP indicates all pure index tablespaces, so that BRRECOVER and BRRESTORE recognize these. In the restore process, which uses a complete backup (brrestore -m all), you can exclude the pure index tablespaces with the command brrestore -m all_data. For more information, see -m|-mode.

After restoring the backed-up data tablespaces, both the index tablespaces and the indexes must be newly created with the definitions in the SYSTEM tablespace. BRRECOVER automatically performs this during the recovery.

BRRECOVER can now automatically handle the situation where the old index tablespaces are not restored. After the database is opened, BRRECOVER creates new index tablespaces called <OLD_TABLESPACE_NAME>I with the same space attributes as the old tablespaces:

·         Oracle 10g: the tablespaces are renamed back to their old names

·         Oracle 9i: you can trace them back to their old names by subsequently creating new index tablespaces with the old names and executing an online index rebuild at tablespace level ( this is “idrebuild” in BRSPACE).

After new indexes have been created, you must update optimizer statistics on these indexes. For example, you can use brconnect -f stats -t <tablespace name> to do this. For more information, see -f stats. This requires extra time, possibly canceling the expected time reduction from recreating indexes.

We recommend you to test the complete recovery procedure to find out whether you can indeed expect a time reduction of this approach.