Skip to main content

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.

Comments

Popular posts from this blog

ORACLE FLASH RECOVERY AREA USAGE QUERY

FINDING ORACLE FLASH RECOVERY AREA USAGE SELECT NAME,        (SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,          ((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,        ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL   FROM V$RECOVERY_FILE_DEST;

All Dictionary View Tables:

All Dictionary View Tables: TABLE_NAME COMMENTS ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_APPLY Details about each apply process that dequeues from the queue visible to the current user ALL_APPLY_CONFLICT_COLUMNS Details about conflict resolution on tables visible to the current user ALL_APPLY_DML_HANDLERS Details about the dml handler on tables visible to the current user ALL_APPLY_ENQUEUE Details about the apply enqueue action for user accessible rules where the destination queue exists and is visible to the user ALL_APPLY_ERROR Error transactions that were generated after dequeuing from the queue visible to the current user ALL_APPLY_EXECUTE Details about the apply execute action for all rules visible to the user ALL_APPLY_KEY_COLUMNS Alternative key columns for a STREAMS table visible to the current user ALL_APPLY_PARAME...

How to delete/remove Management Agent from Oracle Enterprise Manager 12C

  1. Before you deinstall a Management Agent, do the following:     a. Stop the Agent using command from Management Agent home:                 cd /u01/oemcc_latest/core/12.1.0.2.0/bin/                 $ emctl stop agent     b. Wait for the Management Agent to go to the unreachable state in the Cloud Control console.     c. It is mandatory to delete the Management Agent and their monitored targets using any of the following methods: Remove the Agent target manually from the console: 1. Login to 12C Cloud Control 2. Navigate to Setup => Manage Cloud Control => Agents 3. Go to the Home page of the Agent that you want to remove 4. Expand the drop-down menu near the " Agent " 5. Expand the " Target Setup " option 6. Select " Remove Target "   ...