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) |
Leave a Reply