Oracle Recovery : Restoring the database

To recover a restored datafile is to update it using redo records, i.e., records of changes made to the database after the backup was taken.

If you use Oracle Recovery Manager (RMAN), you can also recover restored datafiles with an incremental backup, which is a backup of a datafile that contain only changed data blocks.

Oracle performs crash recovery and instance recovery automatically after an instance failure.

Instance recovery is an automatic procedure that involves two distinct operations: rolling forward the backup to a more current time by applying online redo records and rolling back all changes made in uncommitted transactions to their original state.

The question is: What are the various methods to perform an Oracle recovery that can be used by the DBA?

There are three basic types of recovery:

• Instance recovery
• Crash recovery
• Media recovery.

Oracle performs the first two types of recovery automatically at instance startup and only media recovery requires you to issue commands.

Instance Recovery:

Instance recovery, which is only possible in an OPS configuration, occurs in an open database when one instance discovers that another instance has crashed.

A surviving instance automatically uses the redo log to recover the committed data in the database buffers that was lost when the instance failed. Further, Oracle undoes any transactions that were in progress on the failed instance when it crashed and then clears any locks held by the crashed instance after the Oracle recovery is complete.

Crash Recovery:

Crash recovery occurs when either single-instance database crashes or all instances of a multi-instance database crash.

In crash recovery, an instance must first open the database and then execute recovery operations. In general, the first instance to open the database after a crash or SHUTDOWN ABORT automatically performs crash recovery.

Media Recovery:

Unlike crash and instance recovery, media recovery is executed on your command.

In media recovery, you use online and archived redo logs and (if using RMAN) incremental backups to make a restored backup current or to update it to a specific time. It is called media recovery because you usually perform it in response to media failure.

As we know, recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time, which is before the disaster occurred, or roll-forward until the last transaction recorded in the log files, so the basic command used for recovery is:

sql: connect SYS as SYSDBA
sql: RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

The main tool used for Oracle recovery is Recovery Manager, which is a command line interface (CLI) that directs an Oracle server process to back up, restore or recover the database it is connected to.

The Recovery Manager program issues commands to an Oracle server process. The Oracle server process reads the datafile, control file or archived redo log being backed up, or writes the datafile, control file or archived redo log being restored or recovered.

When an Oracle server process reads datafiles, it detects any split blocks and re-reads them to get a consistent block.

Hence, you should not put tablespaces in hot backup mode when using Recovery Manager to perform open backups

Recovery Manager provides a way to:

• Configure frequently executed backup operations
• Generate a printable log of all backup and recovery actions
• Use the recovery catalog to automate both media restore and recovery operations
• Perform parallel and automatic backups and restores
• Find datafiles that require a backup based on user-specified limits on the amount of redo that must be applied
• Back up the database, individual tablespaces or datafiles

To use RMAN, a recovery catalog is not necessary. RMAN will always use the control file of the target database to store backup and recovery operations. To use an Oracle recovery catalog, you will first need to create a recovery catalog database and create a schema for it.

The catalog (database objects) will be located in the default tablespace of the schema owner. The owner of the catalog cannot be the SYS user.

The Oracle recovery catalog database should be created on a different host, on different disks, and in a different database from the target database on which the backup is taken.

The first step is to create a database for the Oracle recovery catalog. Before proceeding, the database should have the following files installed:

• You have access to the SYS password for the database.
• A normal tablespace named TOOLS exists and will be used to store the Oracle recovery catalog.
• The database is configured in the same way as all normal databases, for example, catalog.sql and catproc.sql have been successfully run.

The DBA will start by creating a database schema usually called rman. Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. The commands which are used for this procedure are:

sqlplus sys
SQL: CREATE USER rman IDENTIFIED BY rman;
SQL: ALTER USER rman DEFAULT TABLESPACE tools TEMPORARY TABLESPACE temp;
SQL: ALTER USER rman QUOTA UNLIMITED ON tools;
SQL: GRANT CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER TO rman;
SQL: exit;

Next, log in to rman and create the catalog schema.

rman catalog rman/rman
RMAN: create catalog tablespace tools;
RMAN: exit;

The DBA will now continue by registering the databases in the catalog:

rman catalog rman/rman target backdba/backdba
RMAN: register database;

Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with privileges to maintain and query the recovery catalog:

SQL: GRANT RECOVERY_CATALOG_OWNER TO rman;

Grant other desired privileges to the RMAN user:

SQL: GRANT CONNECT, RESOURCE TO rman;

After creating the catalog owner, now create the catalog itself by using the CREATE CATALOG command within the RMAN interface.

This command will create the catalog in the default tablespace of the catalog owner.

rman catalog rman/rman@catdb

RMAN: create catalog;

Before letting RMAN use a recovery catalog, register the target database(s) in the recovery catalog.

RMAN will obtain all information it needs to register the target database from the database itself. As long as each target database has a distinct DBID, it is possible to register more than one target database in the same recovery catalog.

Each database registered in a given catalog must have a unique database identifier (DBID), but not necessarily a unique database name.

It is also possible to remove or unregister a target database from the recovery catalog. It can be done by running the following procedure from the while logged into the recovery catalog:

SQL: execute dbms_rcvcat.unregisterdatabase(db_key, db_id)

OR

To unregister a database, do the following:

1. Identify the database that you want to unregister. Run the following query from the recovery catalog using Server Manager or SQL*Plus (connected as the RMAN user):
2. SQL: SELECT * FROM rc_database;
3. Remove the backup sets that belong to the database that you want to unregister:
   • Find the backup sets of the database that you want to unregister: RMAN: list backup set of database;
   • Remove the backup sets that belongs only to the database you want to unregister.
   • RMAN: allocate channel for delete type disk; RMAN: change backup set XXX delete;

Return to Oracle DBA