Recovering Through an Added Datafile with a Backup Control File

If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.

In this example I illustrate this behavior and how to solve the problem.

A)Backup The Data file.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
----------------------------------------
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/user01.dbf

SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.

SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/user01.dbf /oradata2/arju/

SQL> ALTER DATABASE END BACKUP;
Database altered.


B)Backup the control file.

SQL> alter database backup controlfile to '/oradata2/arju/control.ctl';
Database altered.

Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

C)Create a Tablespace with two datafiles.

SQL> CREATE TABLESPACE TBS_AFTER_BACKUP DATAFILE '/oradata2/data1/dbase/datafile01.dbf' size 1M, '/oradata2/data1/dbase/datafile02.dbf' size 1M;

Tablespace created.

D)Remove the All copies of control files.
SQL> show parameter control_files

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

SQL> !rm /oradata1/arju/control02.ctl


E)Shutdown and Restore Control file from Backup

SQL> shutdown abort
ORACLE instance shut down.
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl

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

Since these restored control files are before adding tablespace so the datafile information of the latest added tablespace will not be here. So when it will try to apply redo of the new datafile it will generate error.

F)Startup the instance and Try to recover database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.

Here I am performing automatic recovery.

SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradata2/data1/dbase/datafile02.dbf'
ORA-01110: data file 4: '/oradata2/data1/dbase/datafile01.dbf'

G)View the files added by selecting from V$DATAFILE.

SQL> SELECT FILE#, NAME FROM V$DATAFILE;
FILE# NAME
--------------------------------------------------------------------------
1 /oradata2/data1/dbase/system01.dbf
2 /oradata2/data1/dbase/undotbs01.dbf
3 /oradata2/data1/dbase/sysaux01.dbf
4 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004
5 /oradata2/data1/dbase/user01.dbf
6 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.

H)Rename unnamed datafiles to original names.


If multiple versions of unnamed file exists the identify them from alert log or derive the original file location of each unnamed file from the error message and V$DATAFILE.


SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004' TO '/oradata2/data1/dbase/datafile01.dbf';
Database altered.


SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' TO '/oradata2/data1/dbase/datafile02.dbf';
Database altered.


I)Now perform recovery operations
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo01.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
ORA-00310: archived log contains sequence 3; sequence 4 required
ORA-00334: archived log: '/oradata2/data1/dbase/redo01.log'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'

As one member fail so provide another member.

SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo03.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
Log applied.
Media recovery complete.

J)Open the database with RESETLOGS option
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