SETUP : All scenarios are in archivelog mode.
Database version is 10.2.0.1.0
1. Loss of all controlfiles when all redos are available.
SQL> startup
SQL> alter database begin backup;
SQL> select * from v$backup;
$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/.
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 482215 05-DEC-11
2 ACTIVE 482215 05-DEC-11
3 ACTIVE 482215 05-DEC-11
4 ACTIVE 482215 05-DEC-11
5 ACTIVE 482215 05-DEC-11
SQL> alter database end backup;
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 482215 05-DEC-11
2 NOT ACTIVE 482215 05-DEC-11
3 NOT ACTIVE 482215 05-DEC-11
4 NOT ACTIVE 482215 05-DEC-11
5 NOT ACTIVE 482215 05-DEC-11
SQL> select NAME,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc 1 482026 482026 482412
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc 2 482026 482412 482414
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc 3 482026 482414 482416
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 NO CURRENT 482416 05-DEC-11
2 1 2 52428800 1 YES INACTIVE 482412 05-DEC-11
3 1 3 52428800 1 YES INACTIVE 482414 05-DEC-11
Now, delete all the control files.
SQL> shutdown abort
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 71305484 bytes
Database Buffers 88080384 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info
-> Restore controlfile from backup to all the locations mentioned in initialization parameter file.
$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control01.ctl
$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control02.ctl
$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control03.ctl
SQL> alter database mount;
Database altered.
SQL> recover database using backup controlfile [until cancel];
ORA-00279: change 482216 generated at 12/05/2011 11:42:39 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc
ORA-00280: change 482216 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
-> It requires archived log related to seq1. Suggestion is pointing to current location, and press enter to accept the suggestion
ORA-00279: change 482412 generated at 12/05/2011 11:50:04 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc
ORA-00280: change 482412 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
-> It requires archived log related to seq2. Suggestion is pointing to current location, and press enter to accept the suggestion
ORA-00279: change 482414 generated at 12/05/2011 11:50:05 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_%u_.arc
ORA-00280: change 482414 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
-> It requires archived log related to seq3. Suggestion is pointing to current location, and press enter to accept the suggestion
ORA-00279: change 482416 generated at 12/05/2011 11:50:11 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_%u_.arc
ORA-00280: change 482416 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
-> It requires archived log related to seq4. Suggestion is pointing to an archived location, but required redo is available in redo01.log(it is clear from the output of redo log) so,specify the file name and location and press enter.
/u01/app/oradata/orcl1/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
-> And, you will get all the data back.
**********************************************************************************
2. Loss of all controlfiles and one of the redo is missing
SQL> startup
SQL> alter database begin backup;
$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/
SQL> alter database end backup;
*****Perform some changes*************
SQL> Alter system switch logfile;
/
/
/
SQL> select name,sequence# from v$archived_log;
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc 1
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc 2
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7fs1j3o5_.arc 3
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_7fs1j40o_.arc 4
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 1 NO CURRENT 485153 05-DEC-11
2 1 3 52428800 1 YES ACTIVE 485148 05-DEC-11
3 1 4 52428800 1 YES ACTIVE 485151 05-DEC-11
->Remove archived log sequence#2
$ rm /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc
-> remove all controlfiles
$ rm /u01/app/oradata/orcl1/control*.ctl
SQL> shutdown abort
SQL> startup
Error-> error in identifying controlfile
Database is in nomount state.
-> copy all the controlfile and datafiles from backup location to target location
$ cp /u01/app/backup/orcl1/*.* /u01/app/oradata/orcl1/*.*
SQL> alter database mount
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 484998 generated at 12/05/2011 14:15:41 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc
ORA-00280: change 484998 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 485146 generated at 12/05/2011 14:21:10 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc
ORA-00280: change 485146 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
--> Since redo sequenc#2 does not exists.Issue cancel.
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
**********************************************************************************
3. Recovery from loss of all controlfiles by creating controlfile.
-> Same procedure will work whether all the archivelogs is available or not.
SQL> SQL> startup
SQL> alter database begin backup;
$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/
SQL> alter database end backup;
SQL> alter database backup controlfile to trace as '/u01/app/control.bak' noresetlogs;
*****Perform some changes*************
SQL> Alter system switch logfile;
/
/
/
SQL> select name,sequence# from v$archived_log;
1
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_1_7fv6mo17_.arc
SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
2
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_2_7fv6zzz6_.arc
3
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_3_7fv700xt_.arc
4
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_4_7fv70466_.arc
SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
5
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_5_7fv70532_.arc
6
/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_6_7fv70b3f_.arc
SQL> select * from v$log;
delete archive log related to seq 2 and delete all the controlfiles
SQL> shutdown abort
SQL> startup
error in identifying contorlfile.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oradata/orcl1/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oradata/orcl1/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oradata/orcl1/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oradata/orcl1/system01.dbf',
'/u01/app/oradata/orcl1/undotbs01.dbf',
'/u01/app/oradata/orcl1/sysaux01.dbf',
'/u01/app/oradata/orcl1/users01.dbf',
'/u01/app/oradata/orcl1/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
Controlfuile created
SQL> recover database;
SQL> alter database open;
**********************************************************************************
