Backup and 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.  What kind of backups are available for Oracle Database ?

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

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