UNNAMED file in standby after adding new file to primary (OR) Background Media Recovery terminated with ORA-1274 after adding a Datafile
on
Get link
Facebook
Twitter
Pinterest
Email
Other Apps
Adding a Tablespace / Datafile in Primary Database causes the MRP in Physical Standby Database to terminate with the Error below. STANDBY_FILE_MANAGEMENT = MANUAL is set on the Standby Database.
MRP0: Background Media Recovery terminated with error 1274 ORA-01274: cannot add datafile '...dbf' - file could not be created
In Unix Environment you will get the below Message when try to restart the MRP
ORA-01111: name for data file 163 is unknown - rename to correct file
CAUSE
This Error occurs if we add a Datafile OR Tablespace in PRIMARY Database and that could not be translated to the Standby Database due to these Reasons:
Standy_file_management is set to MANUAL
Primary & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in Primary and Standby
Insufficient Space or wrong Permissions on the Standby Database to create the Datafile
The Redo Log generated from Primary will have Information about the Tablespace / Datafile added however it could not be created successfully in Physical Standby Database due to the standby_file_management = MANUAL or is not able to find the specified Folder due to a missing / incorrect Filename Conversion. The File Entry is added to Standby Controlfile as "UNNAMED0000n" in /dbs or /database folder depends on the Operating System and eventually the MRP terminates.
Alert Log in Standby Shows MRP is terminated with below error ================================================================= File #5 added to control file as 'UNNAMED00005' 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 Thu Sep 25 19:02:35 2008 Errors in file c:\oracle\product\10.2.0\admin\mystd\bdump\mystd_mrp0_3436.trc: ORA-01274: cannot add datafile 'D:\ORADATA\PRIM\SALES01.DBF' - file could not be created
By default it is AUTO by broker.
SOLUTION
Perform all mentioned Steps on the Standby Database:
Step 1: Ensure the standby_file_management = 'MANUAL'
NOTE : For the parameter db_file_name_convert change if the Data Guard Broker is enabled then edit the Parameters using the Broker,
DGMGRL>edit database '' set property DbFileNameConvert='<>','<>'; DGMGRL>edit database '' set property StandbyFileManagement=manual;
By default StandbyFileManagement is AUTO by broker.
Step 2: Identify the File which is "unnamedn"
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- D:\ORADATA\MYSTD\SYSTEM.DBF D:\ORADATA\MYSTD\UNDO.DBF D:\ORADATA\MYSTD\SYSAUX.DBF D:\ORADATA\MYSTD\SERVICE01.DBF C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005
Step 3: Rename/create the Datafile to the correct Filename
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'D:\oradata\mystd\sales01.dbf';
If the standby is in ASM + OMF then use the below command,
SQL> alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as <'+ASMDISKGROUPNAME'> size ;
or
SQL>alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as new;
Step 4: Verify the Filename is correct
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- D:\ORADATA\MYSTD\SYSTEM.DBF D:\ORADATA\MYSTD\UNDO.DBF D:\ORADATA\MYSTD\SYSAUX.DBF D:\ORADATA\MYSTD\SERVICE01.DBF D:\ORADATA\MYSTD\SALES01.DBF
Step 5: Change the STANDBY_FILE_MANAGMENT to AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
Step 6: Start the MRP (this is using Real Time Apply)
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
Step 7: Verify the MRP is running as expected
SQL> select process, status , sequence# from v$managed_standby;
Moving a Datafile to a different Location on a Physical Standby Database (Doc ID 1543367.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
GOAL
This Note describes how to move a Datafile to a different Location on a Physical Standby Database
SOLUTION
Sometimes it is necessary to move a Datafile to another Location (eg. Mountpoint, ASM Diskgroup,…) due to Space issues, Hardware Replacement or for Performance Reasons. There are several Possibilities to perform this Task explained below:
Physical Standby Database is mounted or opened READ ONLY:
We can copy and rename the Datafile while Managed Recovery is stopped and the Datafile to be copied is OFFLINE if the Physical Standby Database is opened READ ONLY either using OS-Tools and SQL*PLUS, eg.
% cp
SQL> alter database rename file ‘’ to ‘’;
or using RMAN:
RMAN> connect target /
RMAN> backup as copy datafile format ‘’;
RMAN> switch datafile to datafilecopy '';
New in Oracle Database 12c: Physical Standby Database is in Active Data Guard Mode (opened READ ONLY and Managed Recovery is running):
It is now possible to online move a Datafile while Managed Recovery is running, ie. the Physical Standby Database is in Active Data Guard Mode. You can use this Command to move the Datafile
SQL> alter database move datafile to ‘’ [keep];
-> The 'keep'-Option will also keep the original Datafile, without this Option the File gets automatically deleted once the move completed.
The Destination can also be an ASM Diskgroup, of course if you want to move a Datafile to ASM or from one Diskgroup to another.
Comments
Post a Comment
Oracle DBA Information