RMAN-(recovery scenario):lost all the data files, control files, redo log file and spfile

Restore and Recover database in Noarchivelog Mode

Before proceed we need to know while restoring data in noarchivelog mode that,

1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.

2)Media recovery is not possible, because there are no archived logs.

3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.

In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.

A)Find the DBID of the Database from Backup Piece.
bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,

3386862614, MAXVALUE,

We got DBID here 3386862614. For more details have a look How to find DBID

B)Connect to target database with RMAN

bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 12:50:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

C)Set the DBID.

RMAN> set dbid 3386862614

executing command: SET DBID

D)Start the database in nomount stage with dummy Parameter.

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 byte

E)Restore spfile

RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';

Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08

F)Mount the Database.

RMAN> startup force nomount;
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 100667112 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes

G)Restore controlfile.
RMAN> restore controlfile from autobackup;

Starting restore at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /oradata2/flash_recovery_area
database name (or database unique name) used for search: DBASE1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata2/data1/dbase1/control01.ctl
output filename=/oradata2/data1/dbase1/control02.ctl
output filename=/oradata2/data1/dbase1/control03.ctl
Finished restore at 06-MAY-08

H)Mount the Database.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

I)Restore Database.

As we have flash recovery area configured so it automatically cataloged backup sets.
RMAN> restore database;

Starting restore at 06-MAY-08
Starting implicit crosscheck backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 06-MAY-08

Starting implicit crosscheck copy at 06-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-08

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
restoring datafile 00002 to /oradata2/data1/dbase1/undotbs01.dbf
restoring datafile 00003 to /oradata2/data1/dbase1/sysaux01.dbf
restoring datafile 00004 to /oradata2/data1/dbase1/users01.dbf
restoring datafile 00005 to /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
restoring datafile 00006 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
restoring datafile 00007 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
restoring datafile 00008 to /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19870: error reading backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19612: datafile 1 not restored due to missing or corrupt data
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp tag=LEVEL_0_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-MAY-08

J)Recover Database:
If the current online logs contain all changes since the last backup , then you can run RECOVER DATABASE without specifying NOREDO. Otherwise you have to specify RECOVER DATABASE NOREDO.

RMAN> recover database;

Starting recover at 06-MAY-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 11 is already on disk as file /oradata2/data1/dbase1/redo01.log
archive log thread 1 sequence 12 is already on disk as file /oradata2/data1/dbase1/redo02.log
archive log thread 1 sequence 13 is already on disk as file /oradata2/data1/dbase1/redo03.log
archive log filename=/oradata2/data1/dbase1/redo01.log thread=1 sequence=11
archive log filename=/oradata2/data1/dbase1/redo02.log thread=1 sequence=12
archive log filename=/oradata2/data1/dbase1/redo03.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:12
Finished recover at 06-MAY-08

K)Open the database with RESETLOGS options.

RMAN> alter database open resetlogs;
database opened.