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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s