UNNAMED File Error in standby after adding new file to primary

ORA-01111: name for data file 29 is unknown - rename to correct file

There was an issue faced on FNPRDDR (dataguard environment) due standby_file_management=MANUAL and standby was unable to create new datafile for SYSAUX tablespace and MRP process was crashing , Dataguard was out of sync.

MRP0: Background Media Recovery terminated with error 1111

Wed Jan 24 07:55:38 2018

Errors in file /u01/app/oracle/diag/rdbms/fnprddr/FNPRDDR1/trace/FNPRDDR1_pr00_13067.trc:

ORA-01111: name for data file 29 is unknown - rename to correct file

ORA-01110: data file 29: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029'

ORA-01157: cannot identify/lock data file 29 - see DBWR trace file

ORA-01111: name for data file 29 is unknown - rename to correct file

ORA-01110: data file 29: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029'

Inorder to fix this issue below steps was followed

1.    Took  backup of datafile from production FNPRD.

copy datafile '+DATA/FNPRD/DATAFILE/sysaux.1051.966234907'  to '/stage/operation/backup_file.dbf'

2.    Catalog the datafile on the standby database

Catalog datafilecopy '/stage/backup_file.dbf' ;

3.    Moved the datafile to correct location in  ASM

copy datafilecopy '/stage/backup_file.dbf'  to '+DATA';

4.    Rename the datafile to correct location

Alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00029' to '+DATA/FNPRDDR/DATAFILE/sysaux.1329.966238471' ;

5.    Start the MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 16 DISCONNECT FROM SESSION;

Currently FNPRDDR is in sync with Production

   THREAD#   RECEIVED Last Standby Seq Applied        Gap

---------- ---------- ------------------------                              ----------

         1       2018                     2016                                        2

         2       2019                     2019                                          0

Change the standby file standby_file_management from Manual to AUTO

FNPRDDR

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS

---------------- ------------

FNPRDDR1         MOUNTED

FNPRDDR2         MOUNTED