Crash Recovery Overview

An instance(crash) failure occurs when your database isn’t able to shutdown normally.When this happens, your datafiles could be in an inconsistent state meaning they may not contain all committed changes and may contain uncommitted changes. Instance failures occur when the isntance terminates abnormally. A sudden power failure or a shutdown abort are two common causes of isntance failure.

Oracle uses crash recovery to return the database to a consistent committed state after an instance failure. Crash recovery guarntees that when your database is opened, it will contain only transactions that were committted before the instance failure occured. Oracle system monitor will automatically detect whether crash recovery is required.
Crash recovery has two pahses : rollforward and rollback

The system monitor will first roll forward and apply to the datafiles any transactions in the online redo files that occured after the most recent checkpoint. Crash Recovery uses redo information found in the online redo log files onlu . After rolling forward, Oracle will rollback any of those transactions that were never committed. Oracle uses information stored in the undo segments to rollback (undo) any uncommitted transactions.

When you start your database, Oracle uses the SCN information in the control files and datafiles headers to determin which one of the following will occur.

Starting up normally.

Performing crash Recovery

Determining that media reocvery is required.

On start up , Oracle check the instance thread status to determine whether crash recover is required . When the database is open for normal operations ,the thread status is OPEN. When Oracle us shut down normally , a checkpoint takes place and the instance thread status is set to CLOSED.

when your instance abnormally terminates the thread status remains OPEN because Oracle didn’t get a chance to upodate teh status to CLOSED.

On startup , When Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery.

The below query , it will usefull to find out whether crash recovery is required.

select a.thread#,b.open_mode,a.status,
CASE
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery req.’
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recovery Req.’
WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance already open’
ELSE ‘huh?’
END STATUS
FROM v$thread a,
v$database b,
v$instance c
where a.thread#=c.thread#;

Just a Example :

1.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

SQL> ED
Wrote file afiedt.buf

  1  select
  2  a.thread#,b.open_mode,a.status,
  3  CASE
  4  WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery r
eq.’
  5  WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recov
ery Req.’
  6  WHEN ((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alre
ady open’
  7  ELSE ‘huh?’
  8  END STATUS
  9  FROM v$thread a,
 10  v$database b,
 11  v$instance c
 12* where a.thread#=c.thread#
SQL> /

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 READ WRITE           OPEN   Instance already open

2.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.

SQL> select a.thread#,b.open_mode,a.status,
  2  CASE
  3  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery re
q.’
  4  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recove
ry Req.’
  5  WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alrea
dy open’
  6  ELSE ‘huh?’
  7  END STATUS
  8  FROM v$thread a,
  9  v$database b,
 10  v$instance c
 11  where a.thread#=c.thread#;

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 MOUNTED              CLOSED No Crash Recovery Req.

SQL>

3.

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.

SQL> ed
Wrote file afiedt.buf

  1  select a.thread#,b.open_mode,a.status,
  2  CASE
  3  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery re
q.’
  4  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recove
ry Req.’
  5  WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alrea
dy open’
  6  ELSE ‘huh?’
  7  END STATUS
  8  FROM v$thread a,
  9  v$database b,
 10  v$instance c
 11* where a.thread#=c.thread#
SQL> /

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 MOUNTED              OPEN   Crash Recovery req.