Recovery process on a standby database terminated with the following error in the alert log.
PR00 (PID:1200024): Managed Standby Recovery starting Real Time Apply
2022-12-29T23:12:39.387793+00:00
PR00 (PID:1200024): MRP0: Background Media Recovery terminated with error 1111
2022-12-29T23:12:39.388261+00:00
Errors in file /u01/app/oracle/diag/rdbms/stbydbdg/STBYDB/trace/STBYDB_pr00_1200024.trc:
ORA-01111: name for data file 117 is unknown – rename to correct file
ORA-01110: data file 117: ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117’
ORA-01157: cannot identify/lock data file 117 – see DBWR trace file
ORA-01111: name for data file 117 is unknown – rename to correct file
ORA-01110: data file 117: ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117’
PR00 (PID:1200024): Managed Standby Recovery not using Real Time Apply
Stopping change tracking
2022-12-29T23:12:39.915944+00:00
Recovery Slave PR00 previously exited with exception 1111
2022-12-29T23:12:39.966595+00:00
Errors in file /u01/app/oracle/diag/rdbms/stbydbdg/STBYDB/trace/STBYDB_mrp0_1199992.trc:
ORA-01111: name for data file 117 is unknown – rename to correct file
ORA-01110: data file 117: ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117’
ORA-01157: cannot identify/lock data file 117 – see DBWR trace file
ORA-01111: name for data file 117 is unknown – rename to correct file
ORA-01110: data file 117: ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117’
2022-12-29T23:12:39.966785+00:00
Background Media Recovery process shutdown (STBYDB)
In this article we will show you how to resolve these errors so that the recovery process could proceed so that the physical standby could be in sync with the primary database.
1. Check the standby_file_management parameter on the physical standby.
ON STANDBY
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management string AUTO
2. Obtain the file number and error from v$recover_file on the physical standby.
ON STANDBY
SQL> select FILE#,ERROR from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
117 FILE MISSING
SQL> select file#,name from v$datafile where file#=117;
FILE# NAME
---------- ----------------------------------------------------------------------
117 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117
3. Identify the missing datafile on the primary database.
ON PRIMARY
NB. Take note of the size in the bytes column.
SQL> select file#,name,bytes from v$datafile where file#=117;
FILE# NAME BYTES
---------- -----------------------------------------------------------------
117 +DATA/PRIMDB/DATAFILE/prime_data.513.1156781259 268435456004. Connect to the broker and check the physical standby status.
$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Dec 30 00:01:10 2022
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected to "STBYDBDG"
DGMGRL> show configuration;
Configuration - PRIMDB
Protection Mode: MaxPerformance
Members:
PRIMDB - Primary database
STBYDBDG - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 15 seconds ago)
DGMGRL> show database STBYDBDG;
Database - STBYDBDG
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
STBYDB
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16854: apply lag could not be determined
Database Status:
ERROR
5. Cancel managed recovery on the physical standby.
ON STANDBY
DGMGRL> EDIT DATABASE 'STBYDBDG' SET STATE='APPLY-OFF';
Succeeded.
6. Set the StandbyFileManagement property to MANUAL
ON STANDBY
DGMGRL> EDIT DATABASE 'STBYDBDG' SET PROPERTY 'StandbyFileManagement' = 'MANUAL';
Property "StandbyFileManagement" updated
DGMGRL>
7. Create an empty datafile which same structure as the missing datafile.
In our case it is datafile 117.
NB. It would need all archivelogs from time of creation for recovery.
ON STANDBY
Connect to sqlplus on the physical standby.
Specify the actual size of the datafile from the primary. We obtained that from step 3.
SQL> alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00117' as '+DATA/' size 26843545600;
Database altered.
8. Set the StandbyFileManagement property back to AUTO
ON STANDBY
DGMGRL> EDIT DATABASE 'STBYDBDG' SET PROPERTY 'StandbyFileManagement' = 'AUTO';
Property "StandbyFileManagement" updated9. Start the managed recovery from the broker.
ON STANDBY
DGMGRL> EDIT DATABASE 'STBYDBDG' SET STATE='APPLY-ON';
Succeeded.
DGMGRL>10. On the physical standby database query v$managed_standby and check status of the MRP
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 96682
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 109572
ARCH CLOSING 109577
ARCH CLOSING 109570
RFS IDLE 0
RFS RECEIVING 96683
RFS RECEIVING 109578
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 APPLYING_LOG 96683
RFS IDLE 0
13 rows selected.
SQL> select FILE#,ERROR from v$recover_file;
no rows selected