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
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#;
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#;
