In Oracle : Oracle Backup & Recovery Overview

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA

-> Oracle Backup & Recovery Overview

 

 Oracle Backup & Recovery Overview

 

 

Here are the main topics for this article:

 

1.  What is a BACKUP ? What is a RESTORE ? What is  RECOVERY ?

2.  How we can take a physical Backup ?

3.  How can we take a logical backup ?

4.  Which are the steps in recovery process ?

5.  How could I calculate the Database Availability ?

6.  How we can increase the Database Availability ?

7.  Comparison of Backup Methods

8.  What kind of backups are available for Oracle Database ?

9.  What is the difference between a consistent & inconsistent backup ?

1.  What is a BACKUP ? What is a RESTORE ? What is  RECOVERY ?

 

A "backup" is a copy of the database. This copy could be a physical copy of the database (the database files are copied, so this is a physical backup) or a logical copy (the database information is copied in another format which can be "imported" again into the database to get back the old data). A logical copy of the database (or a part of it) is named logical Backup.  

 

When the physical files (from a backup) are copied back to the initial location is named a "restore" operation. 

 

When a database is restored, sometimes we need to apply last changes (from archive log files/ log files) to bring the database data up-to-date. This operation is named "recovery", because the last information is recovered. 

 

 

2.  How we can take a physical Backup ?

    

    a)  At the Operating System level using "copy" command ( cp for UNIX, Solaris, Linux ).  

    b) using Oracle RMAN, or other backup tools.  

 

 

3.  How can we take a logical backup ?

 

    a) using Oracle exp/ imp utility 

    b) the data/ code objects could be copied in a text file using different tools (like TOAD, SQL Developer)

    c) using Oracle Data Pump (10g and + )

 

 

4. Which are the steps in recovery process ?

  • 1st:  Roll forward (cache recovery) : is carried out by applying all (committed and un committed) redo log records to the data files.

  • 2nd: Roll back (transaction recovery) : all uncommitted transactions are reverted to its original state. This is done by reading the rollback (undo) segments.      

All this process is based on the SCN (System Change Number) which acts as an internal clock. 

 

 

5.  How could I calculate the Database Availability ?

 

Database Availability = MTBF/ (MTBF+MTTR), where:

 

MTBF = Mean Time Between Failures

MTTR = Mean Time To Recover

 

 

6.  How we can increase the Database Availability ?

 

This could be done technically by using:

  • a Real Application Cluster (RAC) environment: Many instances on several computers access the same database. If one node or a server fails, other nodes perform a recovery for that node. Such a configuration will have load distribution along with the added advantage of high availability.    

  • an Oracle Standby Database: The Oracle Standby database is a copy of the production database. When the production database fails, the standby database will be used. 

 

7.  Comparison of Backup Methods

 

Backup type RMAN Backup User-Managed Backup Export 
Closed database backups Supported. Requires instance to be mounted. Supported. Not supported.
Open database backups Supported. No need to use BEGIN/END BACKUP statements. Supported. Must use BEGIN/END BACKUP statements. Requires rollback or undo segments to generate consistent backups.
Incremental backups Supported. Not supported. Not supported.
Corrupt block detection Supported. Identifies corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION. Not supported. Supported. Identifies corrupt blocks in the export log.
Automatic record keeping of files in backups Supported. Establishes the name and locations of all files to be backed up (whole database, tablespace, datafile or control file backup). Not supported. Files to be backed up must be specified manually. Supported. Performs either full, user, or table backups.
Recovery catalogs Supported. Backups are recorded inthe RMAN repository, which is contained in the control file and optionally in the recovery catalog database. Not supported. DBA must keep own records of backups. Not supported.
Backups to media manager Supported. Interfaces with a media manager. RMAN also supports proxy copy, a feature that allows the media manager to manage the transfer of data. Supported. Backup to tape is manual or controlled by a media manager. Supported.
Backs up initialization parameter file Supported. Supported. Not supported.
Backs up password and networking files Not supported. Supported. Not supported.
Platform-independent language for backups Supported. Not supported. Supported.

 

 

8.  What kind of backups are available for Oracle Database ?

 

->> Cold  (Off-line Backup) - Shut the database down and backup up ALL data, log, control files, password file, pfile (spfile).

 

->> Hot (On-line Backup) - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. The control files, redo log files are copied as well. 

 

->> logical backup (using Export/Import Utility or Data Pump (10g and +) ): The database is running and a copy of the database (or a part of the database) is exported/ imported into/from a system file.  

 

 

9.  What is the difference between a consistent & inconsistent backup ?

 

In a consistent backup, all headers of datafiles that belong to a WRITABLE tablespaces have the SAME checkpoint SCN.

 

In a inconsistent backup, NOT all headers of datafiles that belong to a WRITABLE tablespaces have the SAME checkpoint SCN. An inconsistent backup is created by a hot backup. A recovery is needed in order to make the backup consistent. Only a consistent database could be up and running

 

 

In-Oracle.com  -> Oracle DBA

-> Oracle Backup & Recovery Overview

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.