Tales From A Lazy Fat DBA

What Are the Types of Recovery?

There are three basic types of recovery: instance recovery, crash recovery, and media recovery. Oracle performs the first two types of recovery automatically at instance startup; only media recovery requires you to issue commands.

Instance Recovery
Instance recovery, which is only possible in an OPS (Oracle Parallel Server) 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 recovery is complete.
Crash Recovery
Crash recovery occurs when either a 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. Media recovery is a process of restoring the physical backups and making all the restored datafiles consistent with each other by having same SCN’s in their header’s.

If you do not use Recovery Manager (RMAN) to perform recovery, then you should use the SQL*Plus RECOVER command. It is also possible to use the SQL statement ALTER DATABASE RECOVER, but it is highly recommended that you use the SQL*Plus RECOVER command instead.

When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:

• Issuing SET AUTORECOVERY ON before issuing the RECOVER command – SET AUTORECOVERY ON  – >  RECOVER DATABASE
• Specifying the AUTOMATIC keyword as an option of the RECOVER command   – RECOVER AUTOMATIC DATABASE

what are the types of media recovery in oracle database?

1. Complete recovery
2. Incomplete recovery

Though it is complete recovery or incomplete recovery, the recovery types classified based on what type of failure we are going to recover.

1. Full database recovery
2. Tablespace recovery
3. Datafile recovery

What is complete media recovery?

Complete media recovery is process of restoring full database, a tablespace or a datafile from backup based upon media failure and applying the redo log files to the most current time with of loosing any data’s.

To determine which files to recover:

SQL> SELECT * FROM v$recover_file;

FILE#      ONLINE  ERROR              CHANGE#    TIME     
---------- ------- ------------------ ---------- ---------
        14 ONLINE                              0          
        15 ONLINE  FILE NOT FOUND              0          
        21 OFFLINE OFFLINE NORMAL              0

When you perform complete recovery, you can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later. Types of recoveries comes under Complete Media Recovery:

• Performing Closed Database Recovery
• Performing Open Database Recovery

How complete full database media recovery are made (Performing CLOSED DATABASE Recovery)?

Following way of complete full database recovery is used in both operating system command along with sqlplus recovery command or RMAN utility.

• Shutdown your database using SHUTDOWN ABORT option
• Mount the Database using STARTUP MOUNT
• Restore all the datafiles from the backup and make all datafiles online.
• Apply online redo log files or archived redo log files or both.
• open the database with below command.

sql>alter database open;

How complete tablespace or datafile recovery made (Performing  OPEN DATABASE recovery) ?

Following methods applies to both operating system commands along with Sqlplus RECOVERY command or RMAN utility method to recover datafile or tablespace.

• During database open take tablespace or datafile which need recovery offline.
• Restore the backup tablespace or datafile.
• Apply the online redo log files or archive redo log files or both.
• Open the database with below command.

sql>alter database open;

How incomplete recovery done in oracle database?

Incomplete recovery in oracle database are done using following option when applying archived redo log files and online redo log files.

1. Time based recovery
2. Cancel based recovery
3. Change based recovery
4. Log sequence recovery

The recover database until  command supports three clauses that will abort the recovery process at a specified point:

• recover database until time  ‘YYYY-MM-DD:HH24:MI:SS’ recovers the database to a specified point in time.
• recover database until cancel recovers the database applying one log file at a time until the recovery process is manually canceled
• recover database until change <scn>recovers the database to a specified system change number ( SCN)

After using any one option open the database with following commands,

sql>alter database open resetlogs;

The above statement reset the log sequence to 0 and this command is used when we need incomplete recovery.

What are the methods of media recovery available in oracle database?

1. Recovery using operating system.
2. RMAN Recovery Manager.