Backing Up Oracle Databases

This section outlines the backup options available to DBAs and makes recommendations for backing up the databases used by the server configurations. Be aware that these are just recommendations. Any finalized disaster recovery plan must be created by your organization in consultation with its IT infrastructure staff.

An application backup consists of backing up both the database and the application archive files and you have a choice between online and offline backups. If all of your archive files are in Native-II format, you can back up a server configuration online, without shutting it down or locking it.

An online or hot backup is a backup performed while the database is online and available for read/write operations. Except for Oracle exports, you can only perform online backups when running in ARCHIVELOG mode. An offline or cold backup is a backup performed while the database is offline and unavailable to its users.

Typically an Oracle DBA uses one or more of the following options to back up an Oracle database.

Logical Backups (Export/Import)

Oracle exports are “logical” database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back up the physical data files. One of the advantages of exports is that you can selectively re-import tables. However, you cannot roll forward from a restored export file. To completely restore a database from an export file, you almost need to recreate the entire database. Logical backups takes a snapshot of the database schema as it was at a particular time.

Offline/Cold Backups

A backup performed when the database is shut down is known as an offline or cold backup. You must copy the data files, control file and online redo log files using an OS copy utility. This is a considered a complete backup of the database. Any changes made after this backup are unrecoverable if the database is running in NOARCHIVELOG mode. All transactions are recorded in online redo log files whether the database is archiving or not. When redo logs are archived (ARCHIVELOG mode), Oracle allows you to apply these transactions after restoring files that were damaged (assuming that an active redo log file was not among the files damaged).

Whenever the schema of the database is changed, such as when you add a new data file, rename a file, or create or drop a tablespace, you must shut down the database and copy at least the control file and the newly added data file. A complete backup of the database is preferred.

Before performing a cold backup, it is essential to get a list of all the Oracle files that need to be backed up. Running the following queries will provide a list of all the files.

select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Shut down the database from SQL*Plus or Server Manager. Back up all the files to secondary storage (for example, tapes). Ensure that you back up all data files, all control files, and all log files. When completed, restart your database.

Online/Hot Backups

A backup performed when the database instance is running is known as online or hot backup. Online backups are very important at customer sites where a database instance must operate 24-hours per day and offline backups are not feasible. During the duration of an online backup, the database remains available for both reading and updating. For this kind of backup, the database must be in ARCHIVELOG mode. Only data files and current control files need to be backed up. Unlike offline backups, the unit of a online backup is a tablespace, and any or all tablespaces can be backed up whenever needed. Different data files can be backed up at different times.

To perform an online backup, you switch the tablespace into “backup mode” before copying the files as shown in the following example:

ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;

It is better to backup individual tablespaces than to put all tablespaces in backup mode at the same time. Backing them up separately incurs less overhead. After completing the tablespace backups, it is important to back up the control files as shown in the following example.

ALTER SYSTEM SWITCH LOGFILE; --Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/directory_name/control.dbf';

The frequency of online backups is inversely proportional to the time taken for recovery in case of a media failure. The older your backup, the more redo log files need to be applied, and the recovery times increases. Backup strategies should be tested before being used to protect a production database.

We strongly recommend that you run online backups at times when the database is least accessed, during non-peak hours. Oracle writes complete database blocks instead of the normal deltas to redo log files while in backup mode. This leads to excessive database archiving and could lock up the database.

RMAN Backups

Recovery Manager (RMAN) is an Oracle tool that lets the DBA back up and recover Oracle databases. RMAN lets you perform full backups (with the database online or offline), incremental backups on the block level, and backups of online redo logs and control files. The SYSDBA privilege is required to run RMAN on a database. The other benefits of RMAN backups are that you can:

• Keep track of all backup and recovery operations performed against the database.
• Manage centralized backup and recovery procedures for the enterprise.
• Identify corrupt blocks.
• Back up only those blocks that actually contain data. This can lead to significant savings in backup space requirements.
• Have support for incremental backups. Incremental backups back up only those blocks that have changed since a previous backup. This helps with the disk space usage and reduces the backup times significantly. The Oracle 10g feature called “block change training” provides significant improvement for incremental backups. Contact your DBA about how to implement this feature.

The following examples of RMAN backup and restore are extremely simplistic and are included on to illustrate basic concepts. By default, Oracle uses the database control files to store information about backups. Normally, you prefer to set up an RMAN catalog database to store RMAN metadata. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.

rman target sys/*** nocatalog
 run {
  allocate channel t1 type disk;
  backup
   format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
    ( database );
   release channel t1;
 }
Example RMAN restore:
  rman target sys/*** nocatalog
  run {
   allocate channel t1 type disk;
   restore tablespace users;
   recover tablespace users;
   release channel t1;
}

Export/Import Data Pump

Oracle introduced the export/import data pump in the 10g release. The import pump is twenty times faster than the conventional import utility. Export/Import data pump utilities are “logical” database backups (not physical) as they extract data and logical definitions from the database into a file. Export/Import data pump utilities do not fit into 24/7 model because they do not offer roll-forward capabilities. Export data pump provides a snapshot of the database schema as it was at a particular time.

Recommendations

We strongly recommend the use of RMAN backups if your enterprise wants to run an Atlas Hub instance in a 24/7 environment. RMAN has evolved over the last few years and Oracle continues to add features that make disaster recovery easier, more reliable, and faster.