Skip to content
Advertisements

Archive for

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.

Advertisements

RMAN-06004: ORACLE error from recovery catalog database

Using a recovery catalog makes it startightforward to restore the controlfile.When you issue the restore controlfile command, RMAN will retieve from teh recovery catalog the location and name of the file that contains the control file backup and restores the control file approproately.

Because the recovery catalog knows the location of the RMAN backups piece, It doesn’t matter whether the backup piece is in a flash recovery area or in a configured channel location.

When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in nomount mode. To list backups of your controlfiles ,use the lsit command as shown here.

RMAN>connect target /
RMAN>connect catalog backup/backup@recover
RMAN>startup nomount
RMAN>list backup of ocntrolfile;

If you have registered two database in the recovery datalog with the same name, then you might receive an error such as this when you attempt to list backups or restore the control file

RMAN-06004: ORACLE error from recovery catalog database
RMAN-20005: target database name is ambiguous

In this situation, you will need to first set your database identifier(dbid) before you can restore your control file.

How to determine your DBID

You can verify that you have multiple databases with the same name in your recovery catalog by querying the recovery catalog RC_DATABASE view

sql>connect backup/backup@recover;
sql> select db_key,dbid,name from rc_database;
DB_KEY       DBID            NAME
——-           —–                 ——-
1                1124743449      TEST
4241        1140772490      TEST