top
logo


Unnamed data file with standby_file_management=manual PDF Print E-mail
Written by Martin   
Friday, 16 May 2008 16:56

Tried to solve the following problem the other day on my standby database:

File #7 added to control file as 'UNNAMED00007' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Fri Apr 8 10:00:22 2005
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail

I had done this before, but - couldn't remember how to do it. The same problem can occur when you are recovering your database and a data file has been added to it which was not part of your backup set.

So, here is the solution! I came close to it while looking up the Oracle SQL reference:

On the standby, use "ALTER DATABASE CREATE DATAFILE" as shown in this example:

  1. RECOVER MANAGED STANDBY DATABASE CANCEL;
  2. alter system set standby_file_management = 'manual';
  3. select file#,name from v$datafile where name like '%UNNAMED%';
  4. ALTER DATABASE CREATE DATAFILE 'unnamed datafile' AS 'proper filename';
    An example would be:

    alter database create datafile
    '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/UNNAMED00005'
    as '/u02/oradata/STDBY/data007.ora';

  5. alter database set standby_file_management = 'auto';

"Unnamed data file" in this conectxt is not the fully qualified file name, only UNNAMED00007.

Done! Enable recovery again:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [USING CURRENT LOGFILE]
   2    DISCONNECT FROM SESSION;

If your database is on ASM, then please use the following syntax instead in step 2:

SQL> alter database create datafile 'unnamed datafile' as '+TargetDiskGroupName' size xM;

It's important to specify the size, or the command will fail. An example should clarify this:

SQL> ALTER DATABASE CREATE DATAFILE 
  2   '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005'
  3   AS '+DATA' size 500M;

Last Updated on Thursday, 23 December 2010 12:08
 

bottom

Powered by Joomla!. Designed by: enterprise hosting solutions joomla hosting Valid XHTML and CSS.