Monday, November 19, 2012

Oracle Backup Types

A database backup is a copy of data that can be used to reconstruct the data.
Oracle backup types can be divided into below categories.

1.1. Physical and Logical Backups
  • Physical backups.       
    Backup of physical files such as data-files, control-files and archive-log files
  • Logical backups.         
    Backup of logical data such as tables or stored procedures exported from DB

1.2. Consistent and Inconsistent Backups
A physical backup can be classified by being a consistent or an inconsistent backup.
Consistent backup
  • All datafiles have the same SCN; in other words, all changes in the redo logs have been applied to the datafiles.
  • It is rare that an open database backup can be considered consistent.
  • Consistent backups are taken when the database is shut down normally or in a MOUNT state.
Inconsistent backup
  • Is performed while the database is open and users are accessing the database.
  • Typically SCNs of the datafiles do not match when an inconsistent backup is taking place, a recovery operation performed using an inconsistent backup must rely on both archived and online redo log files to bring the database into a consistent state before it is opened.
  • Database must be in ARCHIVELOG mode to use an inconsistent backup method.

1.3. Full and Incremental Backups
Full backups
  • Include all blocks of every datafile within a tablespace or a database;
  • It is essentially a bit-for-bit copy of one or more datafiles in the database.
  • Can be created with RMAN or with OS-level file copy commands
Incremental backups
Backup of block level changes to database made after previous incremental/full backup. Backup can be level 0 or level 1.
  • ‘Level 0’ Incremental backup - This backs up all blocks in database. This is equivalent to full backup.
  • ‘level 1’ Incremental backup - This backs up database block changes after previous incremental backup. If there is no level 0 incremental backup and you run level 1 incremental backup, RMAN will automatically make level 0 incremental backup.
1.      Cumulative incremental backup - level 1 incremental backup which includes all blocks changed since most recent level 0 incremental backup. Cumulative backups may take more time and space than non- cumulative, however only one cumulative backup from any level will be needed for recovery.

2.      Differential incremental backup - level 1 incremental backup which includes only block changed since most recent incremental backup. By default, incremental backups are differential
  • A distinct advantage to using an incremental backup in a recovery strategy is that archived and online redo log files may not be necessary to restore a database or tablespace to a consistent state
  • The incremental backups may have some or all of the blocks needed.
  • Incremental backups can only be performed within RMAN.
  • Incremental backups can only be performed on datafiles, and not on control files or archived redo logs.
  • if the incremental keyword is not included, by default recovery manager will perform a full backup.
  • During recovery, incremental backups are chosen by recovery manager, and applied automatically
  • In the case that archived redo logs were not successfully backed up (or were corrupted), incremental backups offer a way of making the datafile newer without needing this redo. Conversely, if the tape that an incremental backup resides on is corrupt, archived redo logs can be used to roll forward.
  • Incremental backups can be applied in parallel to multiple datafiles concurrently.
  • Much less redo is applied during recovery.

1.4. Online (Hot) and Offline (cold) backups
·         Online Database Backup (HOT BACKUP)
An online backup or also known as an open backup or Hot backup, is a backup in which all read-write datafiles and control files have not been check pointed with respect to the same SCN. For example, one read-write datafile header may contain an SCN of 100 while other read-write datafile headers contain an SCN of 95 or 90. Oracle cannot open the database until all of these header SCNs are consistent, that is, until all changes recorded in the online redo logs have been saved to the datafiles on disk. If the database must be up and running 24 hours a day, 7 days a week, then you have no choice but to perform online backups of a whole database which is in ARCHIVELOG mode.

·         Offline Database Backup (COLD BACKUP)

Backup taken while Oracle database is down or in mount state NOT OPEN.

In this backup, all datafiles and control files are consistent to the same point in time - consistent with respect to the same SCN, for example. The only tablespaces in a consistent backup that are allowed to have older SCNs are read-only and offline-normal tablespaces, which are consistent with the other datafiles in the backup. This type of backup allows the user to open the set of files created by the backup without applying redo logs, since the data is already consistent. The only way to perform this type of backup is to shut down the database cleanly and make the backup while the database is closed. A consistent whole database backup is the only valid backup option for databases running in NOARCHIVELOG mode.


1.5. Image Copies
  • Image copy is a bit-for-bit identical copy of a DB file
  • Image copies are full backups created by operating system commands or RMAN backup as copy commands.
  • Image copies are the default backup file format in RMAN.
  • By creating image copies using RMAN, all datafiles will automatically be included in the backup.

1.6. Backupsets and Backup Pieces
  • Backup set is a collection of files called backup pieces, each of which may contain the backup of one or several database files
  • Backupsets can be created and restored only with RMAN.
  • Each backup piece belongs to only one backupset.
  • All backupsets and pieces are recorded in the RMAN repository.

1.7. Compressed Backups
  • Compression is available to reduce the amount of disk space or tape needed to store the backup.
  • Compressed backups are only usable by RMAN
  • Need no special processing when used in a recovery operation;
  • RMAN automatically decompresses the backup.
  • Creating compressed backups is as easy [as compressed backupset]

1.8. Whole Database Backup
  • The most common type of backup, a whole database backup contains the control file along with all database files that belong to a database. If operating in ARCHIVELOG mode, the DBA also has the option of backing up different parts of the database over a period of time, thereby constructing a whole database backup piece by piece.


1.9. Tablespace Backups
  • A tablespace backup is a subset of the database. Tablespace backups are only valid if the database is operating in ARCHIVELOG mode. The only time a tablespace backup is valid for a database running in NOARCHIVELOG mode is when that tablespace is read-only or offline-normal.


1.10. Datafile Backups
  • A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups and are only valid if the database is run in ARCHIVELOG mode. The only time a datafile backup is valid for a database running in NOARCHIVELOG mode is if that datafile is the only file in a tablespace. For example, the backup is a tablespace backup, but the tablespace only contains one file and is read-only or offline-normal.


1.11. Control File Backups
  • A control file backup is a backup of a database's control file. If a database is open, the user can create a valid backup by issuing the following SQL statement: ALTER DATABASE BACKUP CONTROLFILE to 'location'; or use Recovery Manager (RMAN).


1.12. Archived Redo Log Backups
  •  Archived redo logs are the key to successful media recovery. Depending on the disk space available and the number of transactions executed on the database, you want to keep as many days of archive logs on disk and you want to back them up regularly to ensure a more complete recovery.


1.13. Configuration Files Backups
  • Configuration files may consist of spfile or init.ora, password file, tnsnames.ora, and sqlnet.ora. Since these files do not change often, then they require a less frequent backup schedule. If you lost a configuration file it can be easily recreated manually. When restore time is a premium, it will be faster to restore a backup of the configuration file then manually creating a file with a specific format.


No comments: