Recently I asked one of my junior colleague to add a datafile
in a tablespace. The database had a physical standby database running. When she
added the datafile , the parameter “standby_file_management” in standby database
server was set to manual. So after addition of the datafile the file was
created like “UNNAMED00006”
And since then the dataguard synchronization was broken.
In alert log the event of datafile addition was showing like
this
|
2024-04-05T05:48:11.121082-07:00 File #6
added to control file as 'UNNAMED00006' because the parameter
STANDBY_FILE_MANAGEMENT is set to MANUAL The file
should be manually created to continue. PR00
(PID:5572): MRP0: Background Media Recovery terminated with error 1274 2024-04-05T05:48:11.183592-07:00 Errors in
file E:\APP\diag\rdbms\edwprddr\edwprdint\trace\edwprdint_pr00_5572.trc: ORA-01274:
cannot add data file that was originally created as
'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF' PR00
(PID:5572): Managed Standby Recovery not using Real Time Apply Recovery
interrupted! Recovery
stopped due to failure in applying recovery marker (opcode 17.30). |
By checking the dataguard broker , we found
|
DGMGRL>
show configuration Configuration
- my_dg_config Protection Mode: MaxPerformance Members: edwprd_int - Primary database edwprddr
- Physical standby database Error: ORA-16810: multiple errors or
warnings detected for the member Fast-Start
Failover: Disabled Configuration
Status: ERROR (status updated 48 seconds ago) |
Solution: To resolve this issue, we took following actions
First we needed to identify which file id was not created
properly.
So login to primary database and look for file# 6 which we retrieved
from alert log.
|
SQL>Select
file#, name from v$datafile where file#=6; FILE# NAME ----------
------------------------------------------------------- 6
E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF |
So , now we will have to recreate the data file in proper
location as it was not created in proper location because standby_file_management
parameter set to manual.
Login to Standby database
|
SQL> SQL>
alter database recover managed standby database cancel; alter
database recover managed standby database cancel * ERROR at line
1: ORA-16136:
Managed Standby Recovery not active This command
we used to cancel the MRP but , it failed as MRP process was not active . Now create a
datafile in proper location by referring current 'UNNAMED00006' file SQL> alter
database create datafile
'E:\APP\ORACLE\PRODUCT\19.3.0\DBHOME_1\DATABASE\UNNAMED00006' as
'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.dbf'; Database
altered. SQL>alter
system set standby_file_management='AUTO'; Now start the
MRP process, it should start applying all the archives and put the database
back in sync SQL> Alter
database recover managed standby database disconnect from session; Database
altered. |
Now after checking the dataguard broker , status showed success
|
DGMGRL>
show configuration Configuration
- my_dg_config Protection Mode: MaxPerformance Members: edwprd_int - Primary database edwprddr
- Physical standby database Fast-Start
Failover: Disabled Configuration
Status: SUCCESS (status updated 35 seconds ago) |
