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

 

Recently I asked one of my junior colleague to add a datafile in a tablespace. The database had a physical standby database running. When she added the datafile , the parameter “standby_file_management” in standby database server was set to manual. So after addition of the datafile the file was created like “UNNAMED00006”

And since then the  dataguard synchronization was broken.

In alert log the event of datafile addition was showing like this

2024-04-05T05:48:11.121082-07:00 File #6 added to control file as 'UNNAMED00006' because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file should be manually created to continue. PR00 (PID:5572): MRP0: Background Media Recovery terminated with error 1274 2024-04-05T05:48:11.183592-07:00 Errors in file E:\APP\diag\rdbms\edwprddr\edwprdint\trace\edwprdint_pr00_5572.trc: ORA-01274: cannot add data file that was originally created as 'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF' PR00 (PID:5572): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovery stopped due to failure in applying recovery marker (opcode 17.30).

 

By checking the dataguard broker , we found

DGMGRL> show configuration   Configuration - my_dg_config     Protection Mode: MaxPerformance   Members:   edwprd_int - Primary database     edwprddr   - Physical standby database       Error: ORA-16810: multiple errors or warnings detected for the member   Fast-Start Failover:  Disabled   Configuration Status: ERROR   (status updated 48 seconds ago)

 

 

Solution: To resolve this issue, we took following actions

First we needed to identify which file id was not created properly.

So login to primary database and look for file# 6 which we retrieved from alert log.

SQL>Select file#, name from v$datafile where file#=6;      FILE# NAME ---------- -------------------------------------------------------          6 E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF

 

So , now we will have to recreate the data file in proper location as it was not created in proper location because standby_file_management parameter set to manual.

Login to Standby database

SQL> SQL> alter database recover managed standby database cancel; alter database recover managed standby database cancel * ERROR at line 1: ORA-16136: Managed Standby Recovery not active   This command we used to cancel the MRP but , it failed as MRP process was not active .   Now create a datafile in proper location by referring current 'UNNAMED00006' file   SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\19.3.0\DBHOME_1\DATABASE\UNNAMED00006' as 'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.dbf';   Database altered.   SQL>alter system set standby_file_management='AUTO';   Now start the MRP process, it should start applying all the archives and put the database back in sync SQL> Alter database recover managed standby database disconnect from session; Database altered.

 

Now after checking the dataguard broker , status showed success

DGMGRL> show configuration   Configuration - my_dg_config     Protection Mode: MaxPerformance   Members:   edwprd_int - Primary database     edwprddr   - Physical standby database   Fast-Start Failover:  Disabled   Configuration Status: SUCCESS   (status updated 35 seconds ago)