ORA-01111: name for data file is unknown in Standby

Many users encountered the issue UNNAMEDnnn file in Standby Database

MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 10:'/home/oracle/11.2.0/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

Uses will encounter this error when standby_file_management is set to auto and file path which is added to the primary database does not exist on standby site. In this situation standby database creates a dummy data file such as UNNAMEDnnnn

If you encounter this error follow the steps
Find the files which are needs to be recovered

SQL> select * from v$recover_file where error=' FILE MISSING';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------------
10 ONLINE ONLINE FILE MISSING 0

Run below command to Identify the file name on Primary as well as on Standby
SQL>select file#,name from v$datafile where file#=10;

Set the standby_file_management to Manual
SQL> ALTER SYSTEM set standby_file_management=MANUAL;

Create the actual data file from dummy filename
SQL> ALTER DATABASE CREATE DATAFILE 'datafile_path/UNNAMEDnnnn' as '{standby_file_path}/datafile_name.dbf';

Users will also encounter the same error when they add a data file on Primary database and STANDBY_FILE_MANAGEMENT on standby set to MANUAL. When MRP (Recovery process) trying to apply the archive logs, it will create an UNNAMEDnnnn file under $ORACLE_HOME/dbs

Run below command to create the data file manually in Standby

SQL> ALTER DATABASE CREATE DATAFILE ‘/home/oracle/11.2.0/dbs/UNNAMED00010'as ‘/dbs01/ORAC/oradata/prd_data10.dbf’;

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com