Oracle Data Guard 11gR2 Administration : Beginner's Guide

Time for action – resolving UNNAMED datafile errors

Now we'll see how to resolve an UNNAMED datafile issue in a Data Guard configuration:

1. 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
   

2. Identify datafile 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
   

3. Identify the dummy filename created in the standby database:

   SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10; ...