Complete recovery after lost of all control files and data files.

Assuming all of the control files and one or more data files are lost during media failure, but the online redo logs are intact, can we do a complete recovery? In a previous post, I have demonstrated that it is possible when I have backup control files though I have to open database by resetlogs. In this post, I will demonstrate in this case we can do complete recovery without resetlogs by re-creating the control files.

Note: the scripts used see previous post.

Steps

1. perform hot backup

#> hot_backup.sh

2. Create a table with one row and switch logfile

SQL> drop table system.t;
SQL> create table system.t as select * from all_tables where rownum <=1; SQL> alter system switch logfile;

3. Insert the second row and switch logfile
SQL> insert into system.t select * from all_tables where rownum <=1; SQL> commit;
SQL> alter system switch logfile;

4. Insert the third row and switch logfile
5. Insert the forth row WITHOUT switch logfile

At this point, we should see total 4 rows in the table t:

sys@DBWRKEV1> select count(*) from system.t ;

COUNT(*)
----------
4

6. Delete all data files to simulate media failure, shutdown instance

rm /db1/u01/oradata/dbwrkev1/system01.dbf
rm /db1/u04/oradata/dbwrkev1/undotbs01.dbf
rm /db1/u02/oradata/dbwrkev1/sysaux01.dbf
rm /db1/u02/oradata/dbwrkev1/users01.dbf

7. Restore data files

#> restore_data.sh

8. Re-create control file

idle> conn / as sysdba
Connected to an idle instance.
idle> startup nomount;
ORACLE instance started.
 
Total System Global Area  473956352 bytes
Fixed Size                  1979520 bytes
Variable Size             314575744 bytes
Database Buffers          150994944 bytes
Redo Buffers                6406144 bytes
idle> @ctl
 
Control file created.

Below is the content of ctl.sql

CREATE CONTROLFILE REUSE DATABASE "DBWRKEV1" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/db1/u05/archive/dbwrkev1/redo01a.log',
    '/db1/u06/archive/dbwrkev1/redo01b.log'
  ) SIZE 50M,
  GROUP 2 (
    '/db1/u05/archive/dbwrkev1/redo02a.log',
    '/db1/u06/archive/dbwrkev1/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/db1/u05/archive/dbwrkev1/redo03a.log',
    '/db1/u06/archive/dbwrkev1/redo03b.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/db1/u01/oradata/dbwrkev1/system01.dbf',
  '/db1/u04/oradata/dbwrkev1/undotbs01.dbf',
  '/db1/u02/oradata/dbwrkev1/sysaux01.dbf',
  '/db1/u02/oradata/dbwrkev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

Then I did:

idle> recover database;
ORA-00279: change 10467469165231 generated at 01/27/2010 13:36:56 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_7_709302414.arc
ORA-00280: change 10467469165231 for thread 1 is in sequence #7
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
ORA-00279: change 10467469165563 generated at 01/27/2010 13:44:35 needed for thread 1
ORA-00289: suggestion : /db1/archive/dbwrkev1/dbwrkev1_1_8_709302414.arc
ORA-00280: change 10467469165563 for thread 1 is in sequence #8
ORA-00278: log file '/db1/archive/dbwrkev1/dbwrkev1_1_7_709302414.arc' no longer needed for this recovery
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
Log applied.
Media recovery complete.
idle> alter database open;
 
Database altered.
 
idle> select count(*) from system.t;
 
  COUNT(*)
----------
         4
 

Updated Feb 3, 2010 - It seems RMAN recovery can automatically apply changes in the online redo logs. See: http://oracle-study-notes.blogspot.com/2007/07/exercise-using-control-file-autobackup.html