Recovering Through an Added Datafile with a Backup Control File: Scenario

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.

For example, suppose the following sequence of events occurs:

1. You back up the database

2. You create a new tablespace containing two datafiles: /oracle/oradata/trgt/test01.dbf and /oracle/oradata/trgt/test02.dbf.

3. You later restore a backup control file and perform media recovery through the CREATE TABLESPACE operation.

You may see the following error when applying the CREATE TABLESPACE redo data:

ORA-00283: recovery session canceled due to errors 
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: '/oracle/oradata/trgt/test02.dbf'
ORA-01110: data file 10: '/oracle/oradata/trgt/test01.dbf'

To recover through an ADD DATAFILE operation, use the following procedure:

1. View the files added by selecting from V$DATAFILE. For example:

   SELECT FILE#,NAME 
   FROM V$DATAFILE;
   
   FILE#           NAME
   --------------- ----------------------
   1               /oracle/oradata/trgt/system01.dbf
   .
   .
   .
   10               /oracle/oradata/trgt/UNNAMED00001
   11               /oracle/oradata/trgt/UNNAMED00002
   
   

2. If multiple unnamed files exist, then determine which unnamed file corresponds to which datafile by using one of these methods:

   • Open the alert_SID.log, which contains messages about the original file location for each unnamed file.

   • Derive the original file location of each unnamed file from the error message and V$DATAFILE: each unnamed file corresponds to the file in the error message with the same file number.

3. Issue the ALTER DATABASE RENAME FILE statement to rename the datafiles. For example, enter:

   ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO
                              '/oracle/oradata/trgt/test01.dbf';
   ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO
                              '/oracle/oradata/trgt/test02.dbf';
   
   

4. Continue recovery by issuing the previous recovery statement. For example:

   RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL