🖥️ Database Environment:
🔹 Operating System: Red Hat Enterprise Linux (RHEL) 8
🗄️ Database Environment: Oracle Real Application Clusters (RAC)
⚙️ Database Version: Oracle Database 19c Enterprise Edition (19.30)
🎯 Problem Statement : A customer reported an unexpected Data Guard synchronization issue after adding approximately 30 new datafiles to Production DB. Adding datafiles is a routine operational activity performed regularly as application data grows. The Data Guard environment is configured with: ✔️ STANDBY_FILE_MANAGEMENT = AUTO ✔️ Oracle ASM storage on both Primary and Standby databases ✔️ Same ASM disk group names on both sites ✔️ DB_FILE_CREATE_DEST = +DATA configured on both Primary as well as on Standby database Given these settings, the customer expected Oracle Data Guard to automatically create corresponding datafiles on the Standby database whenever new datafiles were added on the Primary. However, after the latest batch of datafile additions, MRP process on the Standby database stopped and multiple datafiles appeared as UNNAMED files. As a result, redo apply halted, causing a significant archive log gap between Primary and Standby databases. The following errors were observed on the Standby database: ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' ORA-01157: cannot identify/lock data file 455 - see DBWR trace file ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' The unexpected behavior raised several questions: ❓ Why did Oracle create an UNNAMED datafile despite STANDBY_FILE_MANAGEMENT=AUTO? ❓ Why was the datafile not automatically created in the ASM disk group specified by DB_FILE_CREATE_DEST? ❓ What internal conditions cause Oracle Data Guard to generate UNNAMED00nnn placeholder files? ❓ How can DBAs identify the root cause, resolve the issue, and resume redo apply without rebuilding the Standby database? This article investigates the underlying reasons for UNNAMED datafiles in Oracle Data Guard environments, explains the internal mechanisms involved, and provides practical troubleshooting and recovery steps. |
Step 1: Before proceeding with the investigation, let's verify the current Data Guard configuration, even though the customer has confirmed that all relevant parameters are configured correctly. #Check the DB configuration parameters in both Primary and Standby DBs. On Primary: SQL> show parameter DB_CREATE_FILE_DEST NAME VALUE --------------------- ------- DB_CREATE_FILE_DEST +DATA On Standby: SQL> show parameter standby NAME VALUE ------------------------- ------- standby_file_management AUTO SQL> show parameter DB_CREATE_FILE_DEST NAME VALUE ----------------------- ------- DB_CREATE_FILE_DEST +DATA From the above output, it is evident that the customer was correct. The Data Guard configuration parameters were properly configured on the Standby database. The key parameters responsible for automatic file creation, such as STANDBY_FILE_MANAGEMENT=AUTO and DB_FILE_CREATE_DEST=+DATA, were set correctly, indicating that the root cause of the UNNAMED datafile issue requires further investigation. Step 2: Let's check the MRP process errors in Standby DB alert log. 2026-06-03T18:38:10.730630+05:30 PR00 (PID:1384428): MRP0: Background Media Recovery terminated with error 1111 2026-06-03T18:38:10.731200+05:30 Errors in file /oracle_home/app/oracle/diag/rdbms/testprod_dr/testprod1/trace/testprod1_pr00_1384428.trc: ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' ORA-01157: cannot identify/lock data file 455 - see DBWR trace file ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' 2026-06-03T18:38:10.734251+05:30 .... (PID:3117772): Managed Standby Recovery not using Real Time Apply 2026-06-03T18:38:11.198094+05:30 Completed: ALTER DATABASE RECOVER managed standby database disconnect from session 2026-06-03T18:38:11.481707+05:30 Stopping change tracking 2026-06-03T18:38:11.589537+05:30 Recovery Slave PR00 previously exited with exception 1111. 2026-06-03T18:38:11.616467+05:30 Errors in file /oracle_home/app/oracle/diag/rdbms/testprod_dr/testprod1/trace/testprod1_mrp0_1384223.trc: ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' ORA-01157: cannot identify/lock data file 455 - see DBWR trace file ORA-01111: name for data file 455 is unknown - rename to correct file ORA-01110: data file 455: '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' 2026-06-03T18:38:11.616759+05:30 Background Media Recovery process shutdown (testprod1) The MRP logs clearly indicate that recovery stopped when Oracle attempted to access Datafile 455. Instead of a valid ASM file name, the file was recorded as UNNAMED00455, causing Oracle to raise ORA-01111 and ORA-01157 errors. As a result, the recovery slave process (PR00) exited with exception 1111, forcing the Managed Recovery Process (MRP0) to shut down. ❓What Next? What Could Be the Cause? At this point, we have verified that the Data Guard configuration is correct and identified that MRP stopped due to the presence of an UNNAMED datafile. The next step is to determine why Oracle created the UNNAMED placeholder file despite the required configuration being in place. Step 3: Let's try to manually create a data file in Standby database. To further investigate the issue, let's attempt to manually create the UNNAMED datafile on the Standby database and observe whether Oracle is able to create the file successfully in the configured ASM disk group. Before manually creating the datafile, the STANDBY_FILE_MANAGEMENT parameter must be temporarily changed from AUTO to MANUAL. Oracle does not allow manual file management operations on a physical standby database while automatic standby file management is enabled. After successfully creating the required datafile, it is important to revert the STANDBY_FILE_MANAGEMENT parameter back to AUTO. SQL> alter system set standby_file_management=MANUAL; System altered. SQL> ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' AS '+DATA' size 33285996544; Database altered. SQL> alter system set standby_file_management=AUTO; System altered. DB Alert Log: 2026-06-03T18:44:53.783154+05:30 ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' AS '+DATA' size 33285996544 2026-06-03T19:01:38.802217+05:30 Completed: ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00455' AS '+DATA' size 33285996544 One thing we noticed that the manual file creation completed successfully on the standby database without any issues. However, the command completion time was approx. 16 minutes and 45 seconds, which is unusually long for this operation. As shown above, the datafile creation started at 18:44:53 and completed at 19:01:38, resulting in a total duration of about 16 minutes and 45 seconds, which is significantly longer than expected. Let's start the MRP again and observe the behaviour. After starting the MRP process process again terminated with same error , but this time for the next datafile 456. Perform the same steps to create datafile 456 as below: SQL> alter system set standby_file_management=MANUAL; System altered. SQL> ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00456' AS '+DATA' size 33285996544; Database altered. SQL> alter system set standby_file_management=AUTO; System altered. DB Alert Log: 2026-06-03T19:12:24.362633+05:30 ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00456' AS '+DATA' size 33285996544 2026-06-03T19:28:09.689040+05:30 Completed: ALTER DATABASE CREATE DATAFILE '/oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00456' AS '+DATA' size 33285996544 For datafile 456 as well, the datafile creation command took approx. 15 minutes and 45 seconds to complete. This closely matches the duration observed for the previous datafile creation, indicating that the delay is consistently occurring during the datafile creation process itself rather than being specific to a single datafile. These findings indicate that the excessive time taken for datafile creation could be a key factor behind the MRP terminations. Further analysis of the standby environment, particularly storage performance, ASM operations, and I/O latency, is required to determine the root cause of the delay. ❓What should be our next step now? Step 4: Let's manually add a single datafile in the production database for testing purposes to estimate the datafile creation time on Primary DB. On Production: SQL> alter tablespace TESTDATA add datafile '+DATA' size 33285996544; Tablespace altered. Elapsed: 00:00:23.14 As shown above, the datafile was added to the tablespace on the Primary database in just 23 seconds, which is significantly faster than the 15–16 minutes observed on the Standby database. This clearly indicates that the delay is specific to the Standby environment and not related to the datafile creation operation itself. Let's check the Standby database session logs and alert log. On Standby: SQL> select file#,name,status from v$datafile where file#=457; FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 /oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00457 RECOVER 19:38:36 SQL> / FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 /oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00457 RECOVER 19:38:37 SQL> / FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 /oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00457 RECOVER 19:40:43 SQL> / FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 /oracle_home/app/oracle/product/19.0/dbhome/dbs/UNNAMED00457 RECOVER 19:40:43 SQL> / FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 +DATA/TESTPROD_DR/DATAFILE/testdata.734.1234985377 RECOVER 19:40:44 SQL> / FILE# NAME STATUS ----- ------------------------------------------------------------- ------- 457 +DATA/TESTPROD_DR/DATAFILE/testdata.734.1234985377 ONLINE The output from Standby DB shows that the datafile initially remained in RECOVER state for a while with the placeholder name UNNAMED00457. After some time, it was assigned the actual ASM path "+DATA/TESTPROD_DR/DATAFILE/testdata.734.1234985377" and then finally changed to ONLINE. This indicates that the datafile creation and recovery process completed successfully, but there was a noticeable delay before the file became fully available. Step 4: Let's start the MRP now and check for any error messages. After starting the MRP, the process came up successfully without any errors. This clearly indicates that there was a delay in datafile creation on the standby storage. In this case, the issue occurred because the DBA attempted to start the MRP while the newly added datafile was still being created on the standby database. During this period, the datafile existed as an UNNAMED file and had not yet been fully created at the target ASM location. As a result, when MRP attempted to access the datafile, it was unable to locate it because the creation process was still in progress. Consequently, the MRP process terminated with an error. Once the datafile creation completed and the file transitioned from the UNNAMED state to the actual ASM file name and became ONLINE, MRP started successfully and continued recovery without any issues. Standby DB Alert Log: 2026-06-03T19:40:44.113274+05:30 Successfully added datafile 457 to media recovery Datafile #457: '+DATA/TESTPROD_DR/DATAFILE/TESTdata.734.1234985377' 2026-06-03T19:40:53.471056+05:30 Media Recovery Log +FRA/TESTPROD_DR/ARCHIVELOG/2026_06_03/thread_2_seq_59265.5518.1234968857 2026-06-03T19:41:02.878312+05:30 Media Recovery Log +FRA/TESTPROD_DR/ARCHIVELOG/2026_06_03/thread_1_seq_136903.4693.1234964439 🏁 Final Conclusion: 🔹 Here in our case, the "UNNAMED datafile" message is not an issue; rather, it is an expected behavior on the standby (DR) database during the initial phase after a datafile is added on the primary database. Once the datafile is successfully created on the standby database, it is automatically converted to an ASM OMF file and brought online. 🔹 Our analysis shows that adding a 31 GB datafile on the primary database completes in approximately 23 seconds, whereas the corresponding datafile creation on the standby database takes approximately 15–19 minutes. 🔹 During this interval, the file remains in the UNNAMED state within the standby database data dictionary. If the Managed Recovery Process (MRP) attempts to access the file before the creation process completes, it may fail to locate the file and terminate, resulting in the observed apply lag. 🔹 Once the file creation process completes and the datafile is converted from UNNAMED to its ASM OMF name, the file becomes ONLINE, and MRP starts and runs successfully without any issues. 🔹 At present, both the Primary and Standby databases are fully synchronized, and redo apply is functioning normally. ⚠️ Similar apply lag behavior may be observed in the future whenever a new datafile is added on the primary database, until the corresponding datafile creation process is completed on the standby side. 👉 Based on the findings, we strongly recommended the customer that the concern Storage team investigate the prolonged file creation time on the ASM raw devices at the DR site. The observed delay appears to be related to the standby storage layer rather than the database recovery process itself. Step 5: The similar observation was shared with the concern Storage team to check any bottelneck for Standby DB Server. 🔹 As confirmed by the Storage Team, the underlying storage is hosted on a virtualized environment. They have identified a hardware-related issue on the storage side, which is contributing to the observed delay in datafile creation. Based on their assessment, the behavior seen during this investigation is expected under the current conditions. 🔹 The Storage Team is aware of the issue and will be taking the necessary corrective actions on their end to resolve the underlying hardware problem and improve storage performance. 🎯 Key Takeaway: 🔹 Not every Data Guard apply lag or MRP termination is caused by a database issue. In this case, the investigation revealed that the root cause was delayed datafile creation on the standby storage infrastructure rather than any problem with Data Guard itself. 🔹 In our case, the UNNAMED datafile state was a normal and expected phase while the standby database was creating the new datafile. However, the unusually long file creation time on the standby storage introduced a window during which MRP could not access the file, leading to recovery interruptions and apply lag. 🏁 Lesson Learned: When investigating Data Guard apply lag associated with datafile additions, always validate standby storage performance before concluding that the issue lies within the database recovery process. 🎉 Enjoy the troubleshooting journey!!! 📝 Stay tuned for a detailed blog post on this case !!! |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
