ora-01244: unnamed datafile(s) added to control file by media recovery

Cause: Media recovery with a backup control file or a control file that was rebuilt, encountered the creation of a datafile that was not in the control file. An entry has been added to the control file for the new datafiles, but with the file name UNNAMEDnnnn, where nnnn is the file number. Attached errors describe the file names that were originally used to create the files.

Action: Rename the files to valid file names and resume recovery. If necessary the command ALTER DATABASE CREATE DATAFILE may be used to create a file suitable for recovery and do the rename. If the file is not going to be recovered then take it offline with the FOR DROP option.

You'll encouter below error in alert log:

Errors in file /u01/app/11.2.0.3/diag/rdbms/odsprod/odsprod/trace/odsprod_pr00_16095.trc:

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 684: '/OSP/Data45/osp/data/tblspc_osp_686.dbf'
Slave exiting with ORA-283 exception

And your database recovery will abort

Now there can be a couple of scenarios, where in you may encounter this error, and their respective solutions .

1.You restored a full database and are recovering it through archives from source

Here, you are recovering after a full restore and hence the error says that the required data file doesn't exist.

To over come this issue, you simply have to execute below SQL:

alter database create datafile file#_from_error as '/new/path/to/data/file.dbf';

So, in our case that would be:

SQL> alter database create datafile 684 as '/bkp_loc/OSP/data/tblspc_osp_686.dbf';

The path is where you want the data file to be created. This can be similar to the one reported in error (if you have same path as source) or could be different, as in this case.

Also, if you are using ASM or to create Oracle managed datafile name

SQL> alter database create datafile 684 as new;

Now, you can continue with your recovery.

2.You lost a control file and restored it from a backup after which you had added data files to the database

Here, the data files already exist, (but they didn't at the time you backed up control file) but just that the restored control file is from time before the data files were added and hence not aware of them.

So, in this case, we can just rename the file to its existing file:

SQL> alter database rename file '/u01/app/product/11.2.0/dbhome_1/dbs/UNNAMED00684' to '/OSP/Data45/osp/data/tblspc_osp_686.dbf';

While renaming or recreating, just ensure that the target file being created doesn't already exist or if renaming, you aren't renaming it to other existing data files.

In any case if you know that you dont need that file, you can mark the same offline

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!