Now we'll see how to resolve an UNNAMED datafile issue in a Data Guard configuration:
- Check for the datafile number that needs to be recovered from the standby database:
SQL> SELECT * FROM V$RECOVER_FILE WHERE ERROR LIKE '%MISSING%'; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------- ---------- ---------- 10 ONLINE ONLINE FILE MISSING 0
- Identify datafile
10in the primary database:SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10; FILE# NAME ---------- ----------------------------------------------- 536 /u01/app/oracle2/datafile/ORCL/users03.dbf
- Identify the dummy filename created in the standby database:
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10; FILE# NAME ---------- ------------------------------------------------------- 536 /u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010
- If the reason for the creation of the
UNNAMEDfile is disk capacity or a nonexistent path, fix the issue by creating the datafile in its original place. - Set
STANDBY_FILE_MANAGEMENTtoMANUAL:SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; System altered.
- Create the datafile in its original place with the
ALTER DATABASE CREATE DATAFILEstatement:SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS '/u01/app/oracle2/datafile/ORCL/users03.dbf'; Database altered.
If OMF is being used, we won't be allowed to create the datafile with the preceding statement. We'll come across the following error:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS '/u01/app/oracle2/datafile/ORCL/users03.dbf'; * ERROR at line 1: ORA-01276: Cannot add file /u01/app/oracle2/datafile/ORCL/users03.dbf. File has an Oracle Managed Files file name.
In order to avoid the error, run the following command:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS NEW; Database altered.
- Set
STANDBY_FILE_MANAGEMENTtoAUTOand start Redo Apply:SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH; System altered. SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT NAME TYPE VALUE ----------------------------------- ----------- ------------------ standby_file_management string AUTO SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
- Check the standby database's processes, or the alert log file, to monitor Redo Apply:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
We've fixed a datafile creation error in the standby database by using the ALTER DATABASE CREATE DATAFILE statement. Usage of this statement varies depending on the use of Oracle-managed files.
Simulate the datafile creation error in your test environment. In the primary database, you can create a datafile in a path that the Oracle user doesn't have privilege to on a standby server, or fill the disk on the standby database server where datafiles reside and create a new datafile in the primary database. Then fix the datafile creation error with the method mentioned previously.