User Managed database recovery when all database files lost.

In this example , you have only hot backup of the database. You have lost all datafiles , control files and redo log files. But you have archived redo log file. The backup of the database is taken as in example, User managed Hot Backup. Now you want to perform recovery of whole database. This is discussed in the following sections.

1)Restore the spfile: If you have backup then restore from that. If you don't have then I have shown different ways in order to create spfile in my topic. Search around my blog.

2)Start the database in nomount stage.
SQL>STARTUP NOMOUNT

3)Edit the spfile by Alter system if any modification needed.

show the control_files parameter in the spfile by,

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata2/data1/dbase/control0
1.ctl, /oradata2/data1/dbase/c
ontrol02.ctl, /oradata2/data1/
dbase/control03.ctl

Copy the backup of control files to these location. If there is disk corruption then you can copy it in another place and change the spfile accordingly. Like I copied spfile to the location /oradata1/arju and then point that place in spfile.

SQL> !mkdir /oradata1/arju

Copy from backup location to /oradata1/arju/*
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl

SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl

Point spfile about the new location of control files.
SQL> alter system set control_files='/oradata1/arju/control01.ctl', '/oradata1/arju/control02.ctl' scope=spfile;
System altered.

Since it is static parameter so start the database with modified spfile in order to make affect.
SQL> startup force nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl

4)Mount the database and see the location of the datafile.


SQL> alter database mount
2 ;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/7.dbf

6 rows selected.

5)Copy the backup datafile to these location. However you can change any location. Suppose here I am changing /oradata2/data1/dbase/users01.dbf to /oradata1/arju/user.dbf

SQL> !scp /oradata2/arju/system01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/sysaux01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/undotbs01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/users01.dbf /oradata1/arju/user.dbf

SQL> !scp /oradata2/arju/7.dbf /oradata2/7.dbf

SQL> ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' to '/oradata1/arju/user.dbf';
Database altered.

6)At time time restore is complete. So recovery is needed. As we have no online redo logs so need to do incomplete recovery.

SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123


Specify log: {=suggested | filename | AUTO | CANCEL}
-----Here I pressed ENTER.
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

If you don't have backup control file then you can do as follows,

CREATE CONTROLFILE REUSE DATABASE "DBASE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/data1/dbase/redo01.log' SIZE 4M,
GROUP 3 '/oradata2/data1/dbase/redo03.log' SIZE 50M
DATAFILE
'/oradata2/data1/dbase/system01.dbf',
'/oradata2/data1/dbase/undotbs01.dbf',
'/oradata2/data1/dbase/sysaux01.dbf',
'/oradata1/arju/user.dbf',
'/oradata2/7.dbf'
CHARACTER SET WE8ISO8859P1
;

SQL> recover database until cancel using BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123


Specify log: {=suggested | filename | AUTO | CANCEL}
---JUST I PRESS ENTER HERE
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

Related Documents:
---------------------------
User Managed Restore Operation

What you will do if spfile lost Look at Solution of The problem Section

How to solve problem of inconsistent control file.

How to re-create Control file