Resolving the ‘UNNAMED File Issue/Error’ in Oracle Data Guard: A Comprehensive Guide

Oracle Data Guard is an essential feature for disaster recovery, ensuring high availability and data integrity. However, one common challenge in maintaining synchronization between the Primary and Standby databases is the ‘UNNAMED File Issue/Error.’

In this blog post, we’ll explore what causes this error, how to resolve it effectively, and tips to avoid encountering it in the future.

What is the ‘UNNAMED File Issue/Error’?

The error typically arises when:

A new datafile is added to the Primary Database.
There is insufficient space on the Standby Database server.
Improper configuration of the STANDBY_FILE_MANAGEMENT parameter.

This results in Oracle errors such as:

ORA-01111: Name for data file is unknown.
ORA-01110: Data file cannot be identified.
ORA-01157: Cannot identify/lock the data file.

Consequently, the Managed Recovery Process (MRP) halts, leaving the Standby Database out of sync with the Primary.

Understanding the Cause

When the STANDBY_FILE_MANAGEMENT parameter is not set to AUTO and a new datafile is added on the Primary Database, Oracle creates a placeholder file on the Standby Database. This placeholder file is stored as an unnamed file (e.g., /dbs/UNNAMED00011) in the $ORACLE_HOME/dbs directory. Also it is occured when the space on standby is exhausted and we add datafiles in primary.

Environment Details

Primary DB Host/Instance	`ERPPRD/ORCLPRD`
Standby DB Host/Instance	`ERPSTDY/ORCLSTDY`
Database Version	Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Error Log on Standby Database

The following log sample highlights the issue on Saturday, January 11, 2025:


Sat Jan 11 15:05:13 2025
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'

Steps to Resolve the Issue

1. Verify the Standby Database
Run the following query on the Standby Database to confirm its status:


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

Check the Managed Recovery Process (MRP):


SQL> select process, thread#, sequence#, status from 
v$managed_standby where process = 'MRP0';

2. Identify the UNNAMED File

Locate the unnamed file on the Standby Database:


SQL> select * from v$recover_file where error like '%UNNAMED%';

Output example:

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

3. Find the Corresponding Datafile on the Primary Database

On the Primary Database, run:


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

Output example:

FILE#	Name
11	/u02/oradata/datafile/orclprd/users01.dbf

4. Rename the File on the Standby Database

Temporarily set STANDBY_FILE_MANAGEMENT to MANUAL:


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

Rename the unnamed file:


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

Revert STANDBY_FILE_MANAGEMENT to AUTO:


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

5. Restart and Verify the MRP

Restart the Managed Recovery Process:


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

Verify that the logs are being applied:

When the difference is 0, synchronization between Primary and Standby is restored.

Tips to Avoid This Issue

Always set STANDBY_FILE_MANAGEMENT to AUTO in Data Guard environments:

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

Ensure sufficient filesystem space is available on both Primary and Standby servers.
Follow Oracle’s best practices for Data Guard configuration.

Conclusion

The ‘UNNAMED File Issue/Error’ is a common but easily resolvable problem in Oracle Data Guard setups. By following the steps outlined in this guide, you can quickly restore synchronization between Primary and Standby databases. Proactively ensuring the proper configuration and sufficient storage space can help you avoid this error altogether.

For more tips, troubleshooting guides, and Oracle Database best practices, keep visiting FunOracleApps.com! 😊

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Resolving the ‘UNNAMED File Issue/Error’ in Oracle Data Guard: A Comprehensive Guide