Condition:
All control files have been deleted
After backup of the control file, the newly created data file was deleted
The control file has a binary backup
.
To test this situation, we first get the binary backup of the control file and then delete all the control files:
SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';
Database altered.
Create new tablespace
SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;
Tablespace created.
SQL> !rm /home/oracle/backups/controlfiles/*.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info
To mount the DB, copy the control file backup to the location of the deleted files and rename it. Then we try to recover using the backup file:
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/
control01.ctl control02.ctl control03.ctl
SQL> alter database mount;
Database altered.
SQL> recover database using backup controlfile;
::::::::::::::output trimmed::::::::::::::
ORA-00280: change 13917957 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'
ORA-01112: media recovery not started
ORA-01244: From the error we can see that we have an unnamed datafile. Now we need to find the name of the new datafile created after backup of the control file and create a database based on it:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005
10 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';
Database altered.We will try to restore it and make it open
SQL> recover database using backup controlfile;
ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select tbs.name,dt.name from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;
NAME NAME
------------------------------ -------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2 /u01/app/oracle/oradata/DB11G/tbs_test2.dbf
10 rows selected.
Situation:
- All control files have been lost
- After creating backup of control file new created datafile lost
- Control file binary backup is available
.
Take binary backup of the control file and remove all control files to simulate lost of all control files:
SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';
Database altered.
Create new tablespace
SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;
Tablespace created.
SQL> !rm /home/oracle/backups/controlfiles/*.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info
--To mount Database copy backup of control file to the original control file locations and rename to original control file names.Then recover database using backup control file
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/
control01.ctl control02.ctl control03.ctl
SQL> alter database mount;
Database altered.
SQL> recover database using backup controlfile;
::::::::::::::output trimmed::::::::::::::
ORA-00280: change 13917957 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf'
ORA-01112: media recovery not startedORA-01244 says that we have unnamed datafile. Now we must find this datafile and create datafile based on it with name of datafile that were created after taking backup of control file:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005
10 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';
Database altered.Now recover the database again and then open the database:
SQL> recover database using backup controlfile;
ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select tbs.name,dt.name from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;
NAME NAME
------------------------------ -------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2 /u01/app/oracle/oradata/DB11G/tbs_test2.dbf
10 rows selected.
