Data guard (DG) : ORA-01111 - Datafile not created on standby database

 

Intro

In today's data-driven era, protecting data is paramount. Organizations must be prepared to address potential failures by implementing robust disaster recovery strategies. For those hosting critical data, having a disaster recovery (DR) site is essential. Oracle Data Guard offers a comprehensive solution for database disaster recovery, ensuring data protection and availability.

In this article, I will discuss the issues encountered after rebuilding the standby database. Our standby system suddenly stopped due to the restoration of an unnamed data file. This occurred because I forgot to set STANDBY_FILE_MANAGEMENT to AUTO after making changes to the standby redo.

I used the Metalink note "How to Resolve ORA-01111, ORA-01110, ORA-01157 in a Physical Standby Database" (Doc ID 1416554.1) to address this issue.

Error: 

Errors in file /u01/app/oracle/diag/rdbms/fe4prd_ha/TEST_HA/trace/TEST_HA_pr00_16592.trc:
ORA-01111: name for data file 781 is unknown - rename to correct file
ORA-01110: data file 781: '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00781'
ORA-01157: cannot identify/lock data file 781 - see DBWR trace file
ORA-01111: name for data file 781 is unknown - rename to correct file
ORA-01110: data file 781: '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00781'
Completed: alter database recover managed standby database disconnect from session
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (TEST_HA)

This error message indicates the data file number required to retrieve the name from the primary database. In this example, our primary datafiles are in ACFS, and standby datafiles are located within ASM.

# Get the datafile name 

select FILE#,name from v$datafile where FILE#=&datafile_number;

select FILE#,name from v$datafile where FILE#=781;

     FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
       781 /u02/app/oracle/oradata/TEST_PRD/TEST_PRD/datafile/o1_mf_fleetedg_m51xj74z_.dbf

Solution : 

When you are creating in file system you do not need to specify the data file size, But in ASM specify the file size exactly the same as prod. Once you created the file change the STANDBY_FILE_MANAGEMENT='AUTO' and start the MRP.

SQL> Alter database create datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00781' as '+DATA/TEST_HA/DATAFILE/o1_mf_fleetedg_m51xj74z_.dbf' size 34358689792;
Database altered.

-- Change 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;						

Conclusion

Proper disaster recovery planning is crucial for Oracle databases, and Oracle Data Guard offers numerous features to facilitate this. One such feature is the ability to test your disaster recovery plan by putting the database in snapshot standby mode. This allows you to test the application in read-write mode, and once testing is complete, you can seamlessly convert the database back to physical standby mode. As a database engineer, it is your responsibility to ensure proper alerting and maintenance on the standby database to guarantee its readiness and reliability.