User-Managed Recovery Scenario - 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.

For example, suppose the following sequence of events occurs:

You back up the database

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

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 controlfile 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:

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

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.


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';

Continue recovery by issuing the previous recovery statement.

For example:

RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL