Backup, Restore and Recovery with Oracle

A database backup is is a copy of the data stored in a database. There are two types of backups: physical backups and logical backups.

A physical backup is a copy of the files that belong to a database at a specific point in type. It can either be a cold backup or hot backup.

A logical backup is does not copy the files, instead, it extracts the data into a specific file format (for example with exp.)

A backup is needed to restore the data in a database if it gets damaged.

Some structural changes as well as direct path load require a backup.

Oracle differentiates between consistent and inconsistent backups.

A consistent backup exhibits the following three properties:

All headers of datafiles that belong to a writable tablespaces have the same checkpoint SCN.

These datafiles don't have any changes past this checkpoint SCN. That is: it is not a fuzzy.

Lastly, The SCNs of the datafile headers match the checkpoint information in the controlfiles.

An inconsistent backup is (almost by definition) in which at least on of the mentioned properties are not exhibited. That is, some files contain changes that were made after the files were checkpointed. A recovery is needed in order to make the backup consistent.

An inconsistent backup is created by a hot backup. Also, if the database crashed (or was shutdown abort) and then the backup was made.

A restore is, if you want, the opposite of a backup: the backed up files (that is: the backup) is copied back to their original location.

Usually, a file is restored after a media failure. However, there are other situations where a file is restored, for example a point in time recovery.

Since most probably, the files that were backed up (not the backup itself) have changed between a backup and a restore, the restored files must be recovered.

During a recovery, all changes made to the restored file since the time of the backup are re-done. That's what the redo log and the archived redo logs are for.

It is also possible to (at least incompletely) recover if the current redo log is lost or if the controlfiles are lost.

There are four types of recovery:

• Datafile media recovery (also called media recovery)
• Crash recovery
• Instance recovery
• Disaster recovery

A recovery (if it is not a crash recovery) can either be a

• Complete recovery, or an
• Incomplete recovery (Also known as Point in time recovery (PITR), or database point in time recovery (DBPITR))

Backups and recovery can be performed either

• with RMAN (the recovery manager) or
• using SQL*Plus and operating system file copy commands (such as cp on Unix and copy on Windows).