When Almost Everything of a Database is Missing

I must assume that your Oracle software installed on OS is ready or not damaged before proceeding, otherwise, you should fix the software problem first.

Three major steps to recover a severely damaged database which lost almost everything:

• Restore spfile.
• Restore controlfile.
• Restore and recover database.

Here are the detailed steps:

1. Startup to nomount forcibly.
[oracle@primary01 ~]$ rman target /
...
connected to target database (not started)

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/compdb/spfileprimdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
ORA-15173: entry 'spfileprimdb.ora' does not exist in directory 'compdb'
ORA-06512: at line 4

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

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 96469384 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5455872 bytes
2. Restore spfile from an autobackup or a backup piece.
RMAN> restore spfile from '+data/primdb/autobackup/2012_12_22/s_802730287.978.802730291';

Starting restore at 09-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +data/primdb/autobackup/2012_12_22/s_802730287.978.802730291
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-JAN-13
3. Restart the database to nomount with the restored spfile.
RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2217992 bytes
Variable Size                792725496 bytes
Database Buffers              41943040 bytes
Redo Buffers                   2396160 bytes
4. Restore controlfile from an autobackup or an external file.
RMAN> restore controlfile from '+data/primdb/autobackup/2012_12_22/s_802730287.978.802730291';

Starting restore at 09-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=primdb1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
output file name=+DATA/primdb/controlfile/current.901.804270805
Finished restore at 09-JAN-13
5. Alter the database to mount state with the restored controlfile.
RMAN> alter database mount;

database mounted
6. Restore the database.
RMAN> restore database;

Starting restore at 09-JAN-13
Starting implicit crosscheck backup at 09-JAN-13
allocated channel: ORA_DISK_1
Crosschecked 18 objects
Finished implicit crosscheck backup at 09-JAN-13

Starting implicit crosscheck copy at 09-JAN-13
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 09-JAN-13

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

List of Cataloged Files
=======================
File Name: +data/PRIMDB/AUTOBACKUP/2012_12_22/s_802730287.978.802730291
File Name: +data/PRIMDB/ARCHIVELOG/2013_01_09/thread_1_seq_257.985.804267587
File Name: +data/PRIMDB/ARCHIVELOG/2013_01_09/thread_1_seq_258.982.804267595
...
File Name: +data/PRIMDB/ARCHIVELOG/2012_09_25/thread_1_seq_52.286.794943881
File Name: +data/PRIMDB/ARCHIVELOG/2012_09_25/thread_2_seq_47.287.794944085

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/primdb/datafile/system.897.797943475
channel ORA_DISK_1: restoring datafile 00002 to +DATA/primdb/datafile/sysaux.898.797943569
channel ORA_DISK_1: restoring datafile 00003 to +DATA/primdb/datafile/undotbs1.899.797943687
channel ORA_DISK_1: restoring datafile 00004 to +DATA/primdb/datafile/users.902.797943753
channel ORA_DISK_1: restoring datafile 00005 to +DATA/primdb/datafile/example.900.797943711
channel ORA_DISK_1: restoring datafile 00006 to +DATA/primdb/datafile/undotbs2.901.797943737
channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461
channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461 tag=TAG20121217T113005
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:16
Finished restore at 09-JAN-13
7. Recover the database. Let RMAN go as far as he could.
RMAN> recover database;

Starting recover at 09-JAN-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 75 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917
archived log for thread 1 with sequence 76 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_76.1099.802524949
...
hread_2_seq_254.998.804270043
archived log for thread 2 with sequence 255 is already on disk as file +DATA/primdb/archivelog/2013_01_09/thread_2_seq_255.997.804270051
archived log for thread 2 with sequence 256 is already on disk as file +DATA/primdb/onlinelog/group_4.906.797943793
archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_2_seq_70.1097.802524925 thread=2 sequence=70
archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917 thread=1 sequence=75
...
archived log file name=+DATA/primdb/archivelog/2013_01_09/thread_2_seq_255.997.804270051 thread=2 sequence=                                     255
archived log file name=+DATA/primdb/onlinelog/group_4.906.797943793 thread=2 sequence=256
Finished recover at 09-JAN-13

RMAN>
8. Open the database with resetlogs.
RMAN> alter database open resetlogs;

database opened

More related posts that you may be interested in:

• When the SPFILE is Missing
• When the Controlfile is Missing
• When Some of the Archived Logs are Missing
• When Several Datafiles Are Missing - Restore From Local Backups
• When Several Datafiles Are Missing - Restore From the Standby Database
• When Almost Everything of a Database is Missing
• How Will the Database React to Missing Tempfiles