Backup Restore And Recovery With Oracle

Backup, Restore and Recovery with Oracle Backup 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.

Consistent and inconsistent backups Oracle differentiates between consistent and inconsistent backups.

Consistent backup 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.

Inconsistent backup 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.

Restore 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.

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.

Types of recovery There are four types of recovery:

• •

Datafile media recovery (also called media recovery) Crash recovery

Oracle performs an automatic crash recovery when the instance is started after a shutdown abort or after a crash. A crash recovery 'only' uses the online redo log to recover online datafiles. Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration.

•

Instance recovery

Instance Recovery is only used in a RAC environment. It is the application of online redo log by one instance that detected the crash of another instance.

•

Disaster recovery

There are at least five options to be considered for disaster recovery when running Oracle:

• • • • •

OPS (Oracle Parallel Server) Replication Standby databasess OS oriented solutions Architectural solutions

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))

Not all redo generated since the last backup is applied in a incomplete recovery. It is also called database point in time recovery (DBPITR). Flashback can (sometimes?) used as an alternative to point in time recovery

Performing backup and recovery 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).

recover [SQL*PLUS] recover database until cancel recover database until time '2004-03-21:22:59:04' recover database until change 123456 recover datafile 'filename' until cancel recover datafile 'filename' until time '2004-03-21:22:59:04' recover datafile 'filename' until change 123456 recover tablespace ts_name until cancel recover tablespace ts_name until time '2004-03-21:22:59:04' recover tablespace ts_name until change 123456 recover database using backup controlfile

Until time Performs a incomplete recovery (=Point in time recovery). The format of the time is 'YYYY-MM-DD:HH24:MI:SS'

Until change Performs a incomplete recovery. The number that follows until change is an SCN. In order to recover up to and including SCN 999, use recover until change 1000.

Standby database Recovers a standby database.

Using backup controlfile Don't use the current controlfiles, use backed up control files instead.