Backup with Oracle

Purpose

You must always have a recent and consistent copy of Oracle database data that can be used to recover the database in the event of failure involving data loss.

See BR*Tools for Oracle Database Administration for more information about backup options, including a description of integration with the Oracle Enterprise Manager (OEM) and Recovery Manager (RMAN) and of support for incremental backups.

The Oracle database management system (DBMS) supports the following types of backup:

Oracle Backups

Type of Backup

What Is Backed Up

Database

Full backup, all data files are saved

Tablespace

Partial backup, data files of one or more tablespaces are saved

Data file

One or more data files are saved

You can perform offline or online backups, as follows:

·        Offline

During an offline backup the whole database, or parts of it, are unavailable for use. Offline backups are usually done at the database level. Offline backups at the tablespace or data file level are not advisable in an integrated environment like the SAP system, because the application modules require access to data in several tablespaces. If one of the main tablespaces is not available, most application modules cannot continue.

For an offline backup at the database level, you must close the database. Then you should do a full backup, that is, you must back up all data files, all online redo log files, and at least one control file.

The advantage of such a full backup is consistency. This backup could be restored and the database could then be opened without needing to do a recovery.

The major disadvantage is, obviously, that the database cannot be used while the backup is running.

BRBACKUP (the SAP backup tool) supports offline backups at all levels.

Oracle Offline Backup with BRBACKUP

·        Online

An online backup is more appropriate for large databases. An online backup can be a full or a partial backup. The backup includes all or some of the data files and a control file. For the backup to be usable, the offline redo log files generated while the backup was done also need to be available.

The advantage is that the database is available for normal use while the backup is taken. Online backup has the following disadvantages:

Ў        Performance deteriorates

The DBMS performance worsens because more redo log information is generated while the backup is going on. Therefore you should do online backups during times of low system load. For example, an online backup running alongside a background job with batch input causes the run time of the background job to increase considerably.

Ў        Backed up data files are inconsistent without the redo log files

If used for a recovery, the online backup must be supplemented with the redo log files archived during the backup. To make sure that the last online redo log file from the backup is safely archived, you should perform a redo log switch immediately after the backup. An online backup done with SAP’s BRBACKUP tool does this automatically.

You could perform an online backup by first running BRBACKUP to save data files and then running BRARCHIVE immediately afterwards, to save the archived redo log files belonging to the backup.

Oracle Online Backup with BRBACKUP

Process Flow

...

       1.      You decide what kind of backups to do, as follows:

Ў        Whether to use offline or online backups or both

Only offline backups cause system downtime. Use online backup if downtime cannot be tolerated or if your database is very large and would take too long to back up offline.

Ў        What level of backup to use

If you have decided that a complete backup takes too long, use backups at the tablespace level. For example, if the database is comprised of 20 tablespaces, back up four of them every night Monday to Friday (that is, equivalent to one full backup a week). Then the worst case would be that a tablespace damaged on Sunday would have to be recovered from last Monday’s backup.

       2.      You consider the frequency of backups, if possible increasing the frequency.

More frequent backups lead to shorter recovery time and therefore shorter downtime.  A good compromise is to make less frequent full backups, (for example, one full backup on Sunday) and more frequent partial backups (for example, back up one third of all tablespaces Monday, Tuesday, Wednesday and again Thursday, Friday, Saturday). Then the worst case would be to use a backup that is three days old. The following diagram shows an example of this approach:

Oracle: Example of Partial Backup Schedule

Increase backup frequency of heavily used tablespaces

SAP recommends backing up heavily used tablespaces more often, for example, by including them twice in a backup cycle. The reason is that heavily used tablespaces put more load on disk drives, so increasing the probability of disk failure. The more often such tablespaces are backed up, the more likely a recent backup is available to be used in a recovery.

       3.      You consider parallel online or offline backups to increase throughput.

For example, if partial online backups of tablespaces are done (see example above), you can also schedule the backups of several (groups of) tablespaces to run in parallel, utilizing multiple devices. BRBACKUP supports parallel backups.

       4.      You adjust the backup frequency by performing tests.

There is no rule of thumb to determine the backup frequency. Suppose, for example, a test showed that to apply 3 redo log files to a restored full backup took 15 minutes (that is, 5 minutes recovery time per archived redo log file) and assuming 20 redo log files are archived on average a day, then a recovery from a 3 day old full backup would take 3 x 20 x 5 minutes = 300 minutes (or five hours). If, as in this example, a recovery time of five hours is too long, more frequent backups can be taken (or other techniques such as mirrored disks can be used).

       5.      You consider backing up to disk first if you have enough disk space.

A disk backup is usually faster than a tape backup because disk devices are generally faster. You can then copy your backups from disk to tape without incurring downtime. If possible, retain the disk backup copy since a restore from disk is faster than from tape. Note that this assumes that the disks are not mirrored. Other options are available with mirrored disks (see below).

       6.      You consider using mirrored disks to reduce the downtime for an offline backup.

       7.      You consider using hardware compression with backup.

This cuts down backup time by as much as 50%. If the backup must go to tape directly and be done online, consider using multiple tape drives for parallel backups to shorten backup time.

       8.      You assess your tape devices.

Think about what kind of tape devices you are using for backing up your database since this determines the downtime in the case of offline backups. To give you some idea of this, the capacity of tapes currently ranges from 2 to 30 GB and the speed of data transfer ranges from 1 to 10 GB per hour.

       9.      You verify that your backup tapes are readable for a restore.

It is best to use a separate system from the live production system. To perform a test, you might want to restore the data files of the system tablespace, rollback segment tablespace, temporary tablespace and a tablespace of your choice, plus control files, online redo log files, and archived redo log files. Mount the database, take offline all data files that were not restored, recover the database, and then open it. If successful, this is proof that the restored files can be used.

   10.      If you use the Oracle backup tool RMAN, you consider using incremental backups

Incremental backups are available only possible if you use RMAN. They can help you to dramatically reduce the size of backups. You should start with a level 0 incremental backup, because this backs up all data blocks of the data files. Then you can use incremental backups with a level greater than 0 to back up only data blocks that have changed since the last incremental backup of the same or a lower level.

A recommendation made in earlier versions of this documentation was to skip index tablespaces during backups, because it might be faster to rebuild an index tablespace and the indexes than to restore and recover an index tablespace. This recommendation is no longer valid.

SAP now uses Oracle8's cost-based optimizer. This means that one more step is now necessary after the rebuild of an index tablespace and its indexes. The extra step requires executing the command analyze index <name> … to calculate the statistics required by the cost-based optimizer. With this the total time is longer than the time for restore and recovery

Result

You always have an up-to-date backup of your database. This allows you to quickly recover the database in the event of a failure involving data loss. Therefore, the availability of your SAP system is increased because downtime due to the absence of a suitable database backup is kept to a minimum.

See also:

BR*Tools to Back Up the Oracle Database on SAP Service Marketplace at:

service.sap.com/dbaora