UNNAMED Datafile | ORA-01111: name for data file is unknown | Oracle Standby

ORA-01111 ORA-01110 ORA-01157 is Oracle Standby Alert Log | Datafile created with UNNAME#### in Oracle Standby after adding new datafile to Primary

UNNAME#### in Oracle Standby after adding new datafile to Primary

One of our clients faced this issue after adding a new datafile in the Primary Database. It's a Maximum Performance Architecture where we are having 2 Node Primary Database with ASM and a 2 Node Physical Standby with ASM and a single node Physical Standby with Local File System. So the tablespace got full in Primary Database and a new datafile was added to the same. After the logs got shifted to the DR Sites, there was no issue with the 2 Node DR but the recovery on Single Node Physical Standby got stopped with the below mentioned errors:

Errors in file /u01/app/oracle/diag/DR/rdbms/DR/trace/DR_mrp0_47282.trc:
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00054'
ORA-01157: cannot identify/lock data file 24 - see DBWR trace file
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00054'
Thu Sep 15 20:21:11 EAT 2018
MRP0: Background Media Recovery process shutdown (DR)
Thu Sep 15 20:21:11 EAT 2018
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Thu Sep 15 20:23:25 EAT 2018
Primary database is in MAXIMUM PERFORMANCE mode

A new datafile was added to the Database in DR but at a wrong location with a wrong name. Below mentioned steps helped us resolve this issue.

Solution:
=========

1. Stop the recovery process immediately if hasn't already.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Check for STANDBY_FILE_MANAGEMENT parameter value, set to MANUAL if it's AUTO (the value is meant to be AUTO only in STANDBY Database)

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

For RAC
---------

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';

3. If the file is physically at correct location and the entry in Controlfile is incorrect, then modify the entry as

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00054' to '</path/datafile_real_name>';

or if the file is not there like it wasn't in our case then you need to create one from the already created file but this will need all archivelogs from time of creation for recovery

For filesystem
----------------

Alter database create datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00054' as '</path/datafile_real_name>' ;

For ASM
----------

Alter database create datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00054' as '+DATADG/' size <exact_size_of_datafile_on_Primary>;

4. After the datafile is created at correct location and with correct name, take the database bounce (not required necessarily, just for verification).

5. Start the MRP

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Or if using REAL TIME APPLY
-----------------------------

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

6. Set the STANDBY_FILE_MANAGEMENT to AUTO again.

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

For RAC
---------

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

At this point, your Physical Standby should have started applying the shipped logs from Primary Database.

I hope this helps !!