Skip to main content

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

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:

  1. A new datafile is added to the Primary Database.
  2. There is insufficient space on the Standby Database server.
  3. 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/InstanceERPPRD/ORCLPRD
Standby DB Host/InstanceERPSTDY/ORCLSTDY
Database VersionOracle 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

  1. Always set STANDBY_FILE_MANAGEMENT to AUTO in Data Guard environments:
    SQL> alter system set standby_file_management = AUTO scope=both;
  2. Ensure sufficient filesystem space is available on both Primary and Standby servers.
  3. 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
Labels:

Comments

Post a Comment

Popular posts from this blog

WebLogic migration to OCI using WDT tool

WebLogic migration to OCI using WDT tool Oracle WebLogic Deploy Tool (WDT) is an open-source project designed to simplify and streamline the management of Oracle WebLogic Server domains. With WDT, you can export configuration and application files from one WebLogic Server domain and import them into another, making it a highly effective tool for tasks like migrating on-premises WebLogic configurations to Oracle Cloud. This blog outlines a detailed step-by-step process for using WDT to migrate WebLogic resources and configurations. Supported WLS versions Why Use WDT for Migration? When moving Oracle WebLogic resources from an on-premises environment to Oracle Cloud (or another WebLogic Server), WDT provides an efficient and reliable approach to: Discover and export domain configurations and application binaries. Create reusable models and archives for deployment in a target domain. Key Pre-Requisites Source System: An Oracle WebLogic Server with pre-configured resources such as: Applica...
Post a Comment
Read more

Rename a PDB in Oracle Database Multitenant Architecture in TDE and Non TDE Environment

Rename a PDB in Oracle Database Multitenant Architecture I am sharing a step-by-step guide to help you rename a PDB. This approach uses SQL commands. Without TDE or encryption Wallet Initial Check Check the Current Database Name and Open Mode: SQL > SELECT NAME, OPEN_MODE FROM V$DATABASE; NAME OPEN_MODE --------- -------------------- BEECDB READ WRITE List Current PDBs: SQL > SHOW PDBS; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FUAT READ WRITE NO We need to RENAME FUAT to BEE  Steps to Rename the PDB Step 1: Export ORACLE_SID Set the Oracle SID to the Container Database (CDB): export ORACLE_SID=BEECDB Step 2: Verify Target PDB Name Availability If the target PDB name is different from the current PDB name, ensure no service exists with the target PDB name. Run SQL to Check Exi...
Post a Comment
Read more

How to make flash work on IE or Edge with IE Compatibility

How to make flash work on IE or Edge with IE Compatibility With flash been ended from 1-Jan-2021, many applications using flash have stopped working the way they were built (like OEM, OBIEE, etc). The option you have to upgrade.  This method will give a workaround in IE or Edge in IE compatibility mode without upgrading. 1. Verify you have installed flash.ocx on your desktop. Typically, flash.ocx file is located in C:\Windows\System32\Macromed\Flash or C:\Windows\SysWOW64\Macromed\Flash according to your laptop OS system. 2. Edit mms.cfg from the location as a System Administrator In the case of the 64bit system, it might have mms.cfg under C:\Windows\SysWOW64\Macromed\Flash 3. Add below options into mms.cfg file. Note: AllowListUrlPattern list is an example. The URLs need to be replaced to your hosts those have Flash pages. SilentAutoUpdateEnable=0 AutoUpdateDisable=1 EOLUninstallDisable=1 EnableAllowList=1 AllowListUrlPattern=http://www.google.com/ AllowListUrlPattern=http://goo...
Post a Comment
Read more