Now we'll see how to resolve an UNNAMED
datafile issue in a Data Guard configuration:
SQL> SELECT * FROM V$RECOVER_FILE WHERE ERROR LIKE '%MISSING%'; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------- ---------- ---------- 10 ONLINE ONLINE FILE MISSING 0
10
in the primary database:SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10; FILE# NAME ---------- ----------------------------------------------- 536 /u01/app/oracle2/datafile/ORCL/users03.dbf
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10; FILE# NAME ---------- ------------------------------------------------------- 536 /u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010
UNNAMED
file is disk capacity or a nonexistent path, fix the issue by creating the datafile in its original place.STANDBY_FILE_MANAGEMENT
to MANUAL
:SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; System altered.
ALTER DATABASE CREATE DATAFILE
statement: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.
STANDBY_FILE_MANAGEMENT
to AUTO
and 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.
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.