When we add a new datafile to the primary environment of a dataguard setup, we need to add the datafile to the standby environment as well if we want the manged recover to continue working. We can do that manually if we want – but fortunately for us Oracle comes with a built in feature for automatically creating the file in the standby as well. All we need to do to set it up is to set the parameter “standby_file_management” to AUTO and create a conversion map for the files location using DB_FILE_NAME_CONVERT parameter.

All of this should be running well, until some Junior DBA (hey D., you know I’m talking about you.. 🙂 ) decides to create a new datafile in the non-default location.
Once that happens, the dataguard is unable to create the new datafile and the managed recovery will stop working.

This is a short guide I created years ago for how to identify and solve the problem.

Identify the problem

Our apply service has stopped working, the sequence numbers for our applied logs are getting far apart, and the V$DATAGUARD_STATS shows there is a problem. When we look in the standby alert log, we see this error message:

Errors in file /dwhinf/app01/oracle/admin/dwrepdg/bdump/dwrepdg_mrp0_18127.trc:
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/dwhinf/app01/oracle/product/11.2.0.4/dbs/UNNAMED00007'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/dwhinf/app01/oracle/product/11.2.0.4/dbs/UNNAMED00007'

Okay, so we know that the MRP (apply service) is broken and the database is reporting that he created a dummy file named “UNAMED00007”.

Fixing the problem

First of all, we need to help the standby, and create the file manually.

1. Identify the dummy name in the standby:

   select name from v$datafile where name like '%UNNAMED%';
   

2. We’ll identify the original file name from the primary database. We would like to create the file with the same name – but maybe in a different location.
   We will also might want to consider adding the file directory to the mapping:

   alter system set db_file_name_convert=('/prod/dwhinf/rep/'    ,'/dwhinf/rep/',
                                          '/prod/dwhinf/rep_new/','/dwhinf/rep/') scope=both;
   

3. Now we want to create the manually – but we can’t. The database is in automatic mode so we’ll change it to manual:

   alter system set standby_file_management='manual';
   

4. Now we can create the file in the new location:

   alter database create datafile '/dwhinf/app01/oracle/product/11.2.0.4/dbs/UNNAMED00007' as '/dwhinf/rep/bo6_data_new06.dbf';
   

5. And set the database to work with automatic standby file managment:

   alter system set standby_file_management='auto';
   

6. Resume the recovery process:

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;