MRP: Background Media Recovery process shutdown or NO MRP0 EXISTS !

MRP: Background Media Recovery process shutdown 

The main problem was standby db not applying the archive redo received from production DB. The MRP0 process was missing. When we query the following SQL it returned no rows.

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

no rows returned.

Suspicious list of errors showing on Standby Server alert:

ORA-16401: archive log rejected by Remote File Server (RFS)
MRP: Background Media Recovery process shutdown 

To Drill down check the error message in dataguard status:

select severity,
       error_code,
       message,
       to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') "TIMESTAMP"
  from v$dataguard_status
 order by timestamp desc;

You will find an error like following:
MRP0: Detected orphaned datafiles! 

This could be for two reason.

1. There may me UNNAMED datafiles

To Check:

select file#,name from v$datafile where name like '%UNNAMED%';

if no. 1 returns no row then try no. 2.

2. The problem may be for mismatch in Database Incarnation between production and standby.

To check:

Issue this RMAN command in production to check production DB incarnation:

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       ORCL 1771320492       PARENT  1          24-AUG-13

2       2       ORCL 1771320492       CURRENT 925702     23-MAR-14

and the production incarnation is:

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL 1771320492       PARENT  1          24-AUG-13

2       2       ORCL 1771320492       PARENT 925702     23-MAR-14

3       3       ORCL 1771320492       CURRENT  1932062    02-APR-14

Now reset the standby incarnation to match production incurnation:

RMAN> reset database to incarnation 2;

Now check again:

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL 1771320492       PARENT  1          24-AUG-13

2       2       ORCL 1771320492       CURRENT 925702     23-MAR-14

3       3       ORCL 1771320492       ORPHAN  1932062    02-APR-14

So, its now same.

The next task is to recover the standby database:

RMAN> recover database;

After recovering the standby start the MRP0 process again:

SQL> alter database recover managed standby database disconnect;

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS

---------

MRP0

Else, If no. 1 returns row then try below steps.

I have checked below query outputs:

a.) select file#, error from v$recover_file;

70 FILE MISSING
b.) select file#, name, status from v$datafile;

70 /ora....oracle/product/11.2.0/db_1/dbs/UNNAMED00070 RECOVER

Steps:

1. Backup related file at primary

On primary:
——————–
$ rman target /

RMAN> backup datafile 70 format '/orasoft/cbs_rman/oracle-backup/st1/backdata.bk'  tag 'PRIMARY_70';

Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc

3. At the standby site, catalog the backuppiece and confirm it’s available for use:

On standby:
——————–
$ rman target /

RMAN> catalog backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backup of datafile 70;

Stop redo apply on the physical standby database

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

In My case redo apply has been already stopped.

On the standby site restore the datafile:

run below command for restoring datafile 70:

RUN {
SET NEWNAME FOR DATAFILE 70 to '/orasoft/oracle-backup/UNTOTBS00001.dbf';
RESTORE DATAFILE 70;
SWITCH DATAFILE 70;
}

Check status of files:Restart redo apply on the physical standby database

On standby:
——————–
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

So the log apply process is now working again.