Oracle Data Guard Resolve UNNAMED File Issue

In a Data Guard configuration it is very common to these the 'UNNAMED File Issue/Error' on Standby Database when you add new datafile on Primary Database and there is no space available on the standby database server or improper parameter settings related to standby file management. This will result in the following Oracle error messages ORA-01111, ORA-01110, ORA-01157 and cause MRP process to crash resulting in Standby database 'out of sync' with primary. 

Also note that, If STANDBY_FILE_MANAGEMENT parameter is not configured as 'AUTO' in your Data Guard environment and a datafile is created on Primary database,  then the file created on standby server will have unnamed file name and it is created under $ORACLE_HOME/dbs directory. 

Environment Details:

Primary DB HostName / DB Instance : PRD101/ ORCLPRD
Standby DB HostName / DB Instance : STD101/ ORCLSTDY
Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Alert log on Standby DB:

Sun May 21 15:05:13 2017
Errors in file /u01/app/oracle/diag/rdbms/orclstdy/orclstdy/trace/orclstdy_dbw0_61177.trc:
ORA-01186: file 11 failed verification tests
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
File 11 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/orclstdy/orclstdy/trace/orclstdy_pr00_11317.trc:
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (orclstdy)

Troubleshooting Steps:

Let us see how we can fix this error and get Standby In-synch again with Primary.

• Run following at Standby side. 

SQL> set lines 300
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCLSTDY  READ ONLY            PHYSICAL STANDBY

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process='MRP0';

no rows selected  --> MRP is not Running

SQL> set sqlblanklines on

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  13817                 13708        109

• Stop MRP if it's not already stopped.

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

Database altered.

SQL> show parameter standby_file_management

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management              string                           AUTO


SQL> col NAME for a70
SQL>  select * from v$recover_file where error like '%UNNAMED%';

     FILE# NAME
---------- ----------------------------------------------------------------------
        11 /u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011

Here the datafile #11 is renamed to UNNAMED00011 on standby database 

• Identify the datafile name on primary database

SQL>  select file#,name from v$datafile where file#=11;

FILE#   NAME
—— ———————————————————————-
11  /u02/oradata/datafile/orclprd/users01.dbf

• Temporary change setting for STANDBY_FILE_MANAGEMENT to MANUAL, we can revert this setting once we finish the activity. 

SQL> alter system set standby_file_management=MANUAL scope=both;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management              string 

• Create a new datafile with same name as of Primary ( Name obtained above)

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011' as '/u02/oradata/datafile/orclstdy/users01.dbf';

Database altered.

SQL> select file#,name from v$datafile where file#=11;

     FILE# NAME
---------- ----------------------------------------------------------------------
        11 /u02/oradata/datafile/orclstdy/users01.dbf

• Revert STANDBY_FILE_MANAGEMENT to AUTO 

  SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

• Check if MRP is up 

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process='MRP0';

no rows selected

• Start MRP process and monitor lag gap. MRP will start applying logs if archives are available at Standby side.

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

Database altered.

• On Primary database switch log or archive log and check the archive logs are being shipped to Standby database and MRP is applying them

SQL> alter system switch logfile;

• Verify standby database 

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  13818                 13713        105


SQL> /

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  13820                 13820          0

You can observe, Standby Database is in synch with Primary and MRP is working perfect. 

Conclusion

In this article we have learned how to fix 'UNNAMED File Issue/Error' on Standby Database when you add new datafile on Primary Database and there is no space available on the standby database server or improper parameter settings related to standby file management. To avoid these kind of errors, also ensure that you have correct Data Guard setup as per best practices and there is sufficient file system space on both Primary and Standby servers