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.
The remainder of this topic explains the types of backups supported by Oracle and provides recommendations about performing Oracle backups.
Typically an Oracle DBA uses one or more of the following options to back up an Oracle database.
Exports are logical database backups that extract logical definitions and data from the database to a file.
Export backups are cross-platform and can be easily moved from one operating system to the other.
These backups require shutting down the database instance and copying all the data, log, and control files.
These backups are taken when the database is available and running in ARCHIVELOG mode. To perform a backup of this type, the tablespaces need to be in backup mode and all the data files associated with the tablespace must be backed up. It is essential to backup the control files and archived redo log files.
While the database is offline or online, DBAs can use the RMAN utility to back up the database.
Export pump and import pump are new for Oracle 10g. Expdp and Impdp are cross-platform and can be easily moved from one operating system to the other.
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.
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 is created, 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.
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 file 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 even database freezes.
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:
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 will 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;
}
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.
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 Corporation continues to add features that make disaster recovery easier, more reliable, and faster.