MRP0: Detected orphaned datafiles!Recovery will possibly be retried after flashback…
I got a weird message on a monday morning in the physical standby database displaying message as ‘MRP0: Detected orphaned datafiles!’. I wonder what MRP detected with datafiles
kish@STDBY>select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process (PID:10578)
RFS[1]: No standby redo logfiles created
ARC1: Becoming the active heartbeat ARCH
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
MRP0: Background Media Recovery process shutdown
18 rows selected.
When i checked the consistency in standby database, i can see some datafiles require media recovery and one fuzzy datafile where datafile header SCN is lower than controlfile header SCN
kish@STDBY>col file# for 9999999
select
a.file#,
case
when a.CHECKPOINT_CHANGE# > b.CHECKPOINT_CHANGE# then 'Fuzzy header!'
kish@STDBY> 2 3 4 5 when a.CHECKPOINT_CHANGE# = b.CHECKPOINT_CHANGE# then 'ACID complaint!'
when a.CHECKPOINT_CHANGE# < b.CHECKPOINT_CHANGE# then 'Media recovery required!'
end Consistency_check
from
v$datafile a
inner join v$datafile_header b on (a.FILE#=b.FILE#)
order by 1;
6 7 8 9 10 11
FILE# CONSISTENCY_CHECK
-------- ------------------------
1 Media recovery required!
3 Media recovery required!
4 Media recovery required!
5 ACID complaint!
6 Media recovery required!
7 ACID complaint!
8 ACID complaint!
9 ACID complaint!
10 ACID complaint!
11 ACID complaint!
12 ACID complaint!
13 ACID complaint!
14 Fuzzy header!
13 rows selected.
I checked the datafiles in standby and compared with primary for missing files
kish@STDBY>select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/apps01/base/oradata/dbx01/datafile/system.268.1083714775
/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
........
/apps01/base/oradata/dbx01/datafile/users.279.1083903263
MRP is not running because the incarnation in which the standby database run is an orphaned version. In simple terms , standby database SCN is higher than primary due to some inconsistent recovery or human errors
kish@STDBY>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS CLOSING 42
6 rows selected.
I bounced the database to mount state
kish@STDBY>shu immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
kish@STDBY>startup nomount;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 989859744 bytes
Database Buffers 503316480 bytes
Redo Buffers
Restored the standby controlfile using service feature in 12c and mounted the database. Thanks to oracle for introducing this “service” feature. God bless you !
RMAN> restore standby controlfile from service dbx00;
Starting restore at 26-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service dbx00
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/apps01/FRA/DBX01/controlfile/o1_mf_jnl5gscz_.ctl
Finished restore at 26-SEP-21
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Restored the database itself using service instead of specific datafiles which needs media recovery.
As we know that RMAN has the intelligence to skip the datafiles which are already consistent
RMAN> run
{
set newname for datafile '+DATAC1/DBX00/DATAFILE/system.268.1083714775' to '/apps01/base/oradata/dbx01/datafile/system.268.1083714775';
set newname for datafile '+DATAC1/DBX00/DATAFILE/sysaux.273.1083714719' to '/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719';
set newname for datafile '+DATAC1/DBX00/DATAFILE/undotbs1.266.1083714851' to '/apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851';
set newname for datafile '+DATAC1/DBX00/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.260.1083714939' to '/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.260.1083714939';
set newname for datafile '+DATAC1/DBX00/DATAFILE/users.257.1083714849' to '/apps01/base/oradata/dbx01/datafile/users.257.1083714849';
set newname for datafile '+DATAC1/DBX00/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.267.1083714939' to '/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.267.1083714939';
set newname for datafile '+DATAC1/DBX00/CC5EB83E86C4281FE053CD38A8C0DB1B/DATAFILE/system.270.1083716807' to '/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/system.270.1083716807';
set newname for datafile '+DATAC1/DBX00/CC5EB83E86C4281FE053CD38A8C0DB1B/DATAFILE/sysaux.277.1083716807' to '/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/sysaux.277.1083716807';
set newname for datafile '+DATAC1/DBX00/CC5EB83E86C4281FE053CD38A8C0DB1B/DATAFILE/users.274.1083716893' to '/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/users.274.1083716893';
set newname for datafile '+DATAC1/DBX00/CC5EBF37A8122A8BE053CD38A8C0EFDF/DATAFILE/system.269.1083716919' to '/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/system.269.1083716919';
set newname for datafile '+DATAC1/DBX00/CC5EBF37A8122A8BE053CD38A8C0EFDF/DATAFILE/sysaux.272.1083716919' to '/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/sysaux.272.1083716919';
set newname for datafile '+DATAC1/DBX00/CC5EBF37A8122A8BE053CD38A8C0EFDF/DATAFILE/users.278.1083717009' to '/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/users.278.1083717009';
set newname for datafile '+DATAC1/DBX00/DATAFILE/users.279.1083903263' to '/apps01/base/oradata/dbx01/datafile/users.279.1083903263';
set newname for datafile '+DATAC1/DBX00/TEMPFILE/temp.258.1083714929' to '/apps01/base/oradata/dbx01/tempfile/temp.258.1083714929';
set newname for datafile '+DATAC1/DBX00/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012021-09-19_11-56-29-pm.dbf' to '/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012021-09-19_11-56-29-pm.dbf';
set newname for datafile '+DATAC1/DBX00/CC5EB83E86C4281FE053CD38A8C0DB1B/TEMPFILE/temp.276.1083716869' to '/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/tempfile/temp.276.1083716869';
set newname for datafile '+DATAC1/DBX00/CC5EBF37A8122A8BE053CD38A8C0EFDF/TEMPFILE/temp.261.1083716969' to '/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/tempfile/temp.261.1083716969';
restore database from service dbx00 using compressed backupset;
switch datafile all;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22>
executing command: SET NEWNAME
Starting restore at 26-SEP-21
Starting implicit crosscheck backup at 26-SEP-21
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 26-SEP-21
Starting implicit crosscheck copy at 26-SEP-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnkzn8dc_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnkzsny1_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl09ko2_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl1tr77_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl26k15_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl3dclh_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl3wl1c_.ctl
File Name: /apps01/FRA/DBX01/controlfile/o1_mf_jnl4mn2c_.ctl
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_18_jnl4qw44_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_19_jnl4s1m3_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_20_jnl4s3fh_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_21_jnl4v0mr_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_22_jnl4vxfj_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_23_jnl4x2t7_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_21/o1_mf_1_24_jnl4xwfl_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_25_jnnd9613_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_26_jnnd96lo_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_27_jnnd972v_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_28_jnnd97f1_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_30_jnnd9b4p_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_31_jnnd9b5c_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_32_jnnd9b7k_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_33_jnnd9bns_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_34_jnnd9c6s_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_29_jnnd9d9n_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_35_jnndm8lk_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_36_jnnm5vr2_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_37_jnnm6c8p_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_38_jnnnb1kz_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_39_jnnq8nrs_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_40_jnnqd1wg_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_41_jnnqd9px_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_42_jnnshgqb_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_45_jnpwsdy2_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_43_jnpwsfrg_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_22/o1_mf_1_44_jnpwsfv2_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_23/o1_mf_1_46_jnqjml11_.arc
File Name: /apps01/FRA/DBX01/archivelog/2021_09_23/o1_mf_1_47_jnqmcoj1_.arc
File Name: /apps01/FRA/DBX01/autobackup/2021_09_22/o1_mf_s_1083818671_jnnbsr61_.bkp
File Name: /apps01/FRA/DBX01/autobackup/2021_09_22/o1_mf_s_1083890807_jnnf3l1j_.bkp
File Name: /apps01/FRA/DBX01/autobackup/2021_09_22/o1_mf_s_1083902714_jnnqk3fl_.bkp
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 1744270
skipping datafile 7; already restored to SCN 1744270
skipping datafile 8; already restored to SCN 1857203
skipping datafile 9; already restored to SCN 1857203
skipping datafile 10; already restored to SCN 1857203
skipping datafile 11; already restored to SCN 1857214
skipping datafile 12; already restored to SCN 1857214
skipping datafile 13; already restored to SCN 1857214
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service dbx00
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel ORA_DISK_1: restore complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service dbx00
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service dbx00
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851
channel ORA_DISK_1: restore complete, elapsed time: 00:02:39
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service dbx00
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /apps01/base/oradata/dbx01/datafile/users.257.1083714849
channel ORA_DISK_1: restore complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service dbx00
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /apps01/base/oradata/dbx01/datafile/users.279.1083903263
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 26-SEP-21
Then compared the incarnation of the database on primary and standby
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBX00 2195240283 PARENT 1 07-JUL-14
2 2 DBX00 2195240283 CURRENT 1594143 19-SEP-21
Reset the incarnation from 3 to 2 in standby to previous state because of fuzziness(Primary(1594143) < Standby(2137590))
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBX00 2195240283 PARENT 1 07-JUL-14
2 2 DBX00 2195240283 PARENT 1594143 19-SEP-21
3 3 DBX00 2195240283 CURRENT 2137590 22-SEP-21
RMAN> reset database to incarnation 2;
database reset to incarnation 2
Now incarnation 3 become orphan and 2 become current in standby database
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBX00 2195240283 PARENT 1 07-JUL-14
2 2 DBX00 2195240283 CURRENT 1594143 19-SEP-21
3 3 DBX00 2195240283 ORPHAN 2137590 22-SEP-21
Post that enable MRP in standby
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
Check the status of redo apply in standby
kish@STDBY>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 67
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 0
Check the message for any errors. In below output , oracle clears the logfile automatically after SCN reset
kish@STDBY>select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RFS[20]: No standby redo logfiles created
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /apps01/base/oradata/dbx01/onlinelog/group_2.263.1083714913
Oracle then perform media recovery using archivelogs shipped from primary
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /apps01/base/oradata/dbx01/onlinelog/group_3.264.1083714915
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /apps01/base/oradata/dbx01/onlinelog/group_4.265.1083714917
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +REDO/DBX00/ONLINELOG/group_5.256.1083899629
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/apps01/FRA/DBX01/archivelog/2021_09_30/o1_mf_1_61_joczyghx_.arc
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_09_30/o1_mf_1_62_joczygmw_.arc
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_09_30/o1_mf_1_63_joczyh8h_.arc
All good now! We can see a sync between primary and standby
kish@STDBY>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 69
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 65
kish@STDBY>/
PROCESS STATUS SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 69
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 66
9 rows selected.
kish@STDBY>/
PROCESS STATUS SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 69
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 69
9 rows selected.
I finally checked the standby file management and it is set to manual due to some activity and the engineer forget to set the parameter to automatic post changes. Some one had added a new datafile on primary and the datafile was not residing in the original directory
kish@PRIM>show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
I set it auto
kish@PRIM>alter system set standby_file_management=AUTO;
System altered.
Checked consistency of datafiles and it is sync ! Cheers !
kish@STDBY>set lines 200 pages 1000
col file# for 9999999
select
c_h.file#,
case
when c_h.CHECKPOINT_CHANGE# > d_h.CHECKPOINT_CHANGE# then 'Fuzzy header!'
when c_h.CHECKPOINT_CHANGE# = d_h.CHECKPOINT_CHANGE# then 'ACID complaint!'
when c_h.CHECKPOINT_CHANGE# < d_h.CHECKPOINT_CHANGE# then 'Media recovery required!'
end Consistency_check
from
v$datafile c_h
inner join v$datafile_header d_h on (c_h.FILE#=d_h.FILE#)
order by 1;kish@STDBY>kish@STDBY> 2 3 4 5 6 7 8 9 10 11
FILE# CONSISTENCY_CHECK
-------- ------------------------
1 ACID complaint!
3 ACID complaint!
4 ACID complaint!
5 ACID complaint!
6 ACID complaint!
7 ACID complaint!
8 ACID complaint!
9 ACID complaint!
10 ACID complaint!
11 ACID complaint!
12 ACID complaint!
13 ACID complaint!
14 ACID complaint!
13 rows selected.
StAxAlPhA 