Recover if all controlfiles,datafiles,spfile,redologs are lost

Problem Generation  


i. Shutdown Database.
SQL>  SHUTDOWN immediate;




ii. Delete all the datafiles ,controlfiles ,redo logfiles ,spfile ,initocmdb.ora files from Operating System




iii. Startup Database
SQL> STARTUP;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initocmdb.ora’




Make sure you have lost all the files not just spfile, if only spfile is lost , then too you will get above error you need to create only spfile.
Solution




i. Using RMAN connect to target database using catalog




[oracle@odb]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 30 06:02:44 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)
connected to recovery catalog database




ii. Set Database DBID
RMAN> set dbid — dbid 4083722939




iii. Startup Database in Nomount State
RMAN> startup nomount;




iv. Restore spfile from Autobackup
RMAN> restore spfile from autobackup;




v. Again start the Database in Nomount State
RMAN> startup force nomount;




vi. Restore controlfile from Autobackup
RMAN> restore controlfile from autobackup;




vii. Mount Database
RMAN> alter database mount;




viii. Open a terminal in OCMDB machine and check archive log completion time
[oracle@odb ~]$ sqlplus / as sysdba
SQL> SELECT thread#,
        resetlogs_change#,
        archived,
        sequence#,
        TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
  completion_time
  FROM v$archived_log
  WHERE archived = ‘YES’
  AND completion_time = (SELECT MAX (completion_time)
                            FROM v$archived_log
                           WHERE archived = ‘YES’);
  THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———-   —————–     —    ———-        ——————-
1    519078     YES       1        2011-01-29:23:18:17




ix. By executing the command below we may not be able to recover database because control file is from older incarnation. This is because we have not taken backup of controlfile after reset logs and immediately crashed the database after recovery in previous scenario.
RMAN> run
{
restore database;
recover database until  sequence 1 thread 1;
sql ‘ alter database open resetlogs’;
}




x. Now, we have to reset database to older incarnation and recover the database.




xi. List Database Incarnation.
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID         STATUS  Reset SCN  Reset Time
——- ——- ——– —————- — ———- ———-
1    2    OCMDB 4083722939    PARENT  1       15-JAN-11
1    1415 OCMDB 4083722939    PARENT  503961 29-JAN-11
1    1867 OCMDB 4083722939    CURRENT 519078 29-JAN-11




xii. Open a terminal in OCMDB machine and check archive log completion time.
SQL> SELECT thread#,
        resetlogs_change#,
        archived,
        sequence#,
        TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
      completion_time
FROM v$archived_log
WHERE archived = ‘YES’
      ORDER BY completion_time;
 THREAD# RESETLOGS_CHANGE# ARC  SEQUENCE# COMPLETION_TIME
———-  —————–   —  ———- ——————-
1         503961   YES       4 2011-01-29:22:18:18
Add 1 to the latest sequence of last incarnation i.e. 4+1 =5




xiii. Reset the Database to old incarnation and recover.
RMAN> reset DATABASE TO incarnation 1415;
RMAN> run
{
restore database;
recover database until  sequence 5 thread 1;
sql ‘ alter database open resetlogs’;
}