How to start MRP process on Dataguard -- standby database

Hi DBA-Mates, Hope you all are doing Great!!!

Usually, we have faced this issue Archive logs not applied on physical standby and this is most important error/issue in our DBA task.

Also, 99% people ask this question in the interviews.

We would like to share our real time scenario here which may useful and helpful to everyone.

Whenever we faced this type of issue, we need to check some important below points:

1. We should check the status of database, which should be mounted status.

SQL> select name, open_mode from v$database;

It may also show in Open (Read-only) mode because it may open for any backup or reporting purpose.

2. Then we need to check the MRP process is running or not as shown below command:

1. Check the MRP process is running or not?

$ ps -ef|grep mrp

$

2. In our case MRP is down. If the MRP process is not running then we need to START the MRP to sync from below command.

As per DBA guidance, we should always cross verify the correct instance and database.

$hostname; id

$echo $ORACLE_SID

PRD02

$sqlplus '/as sysdba'

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

Once you start the MRP then archive log will start to sync.

Alert log error details: Providing below alert details , so we can see how the above command is working...

$ cd /p01/app/oracle/diag/rdbms/ora-data/ORA-DATA/trace

$ view alert_ORA-DATA.log

Fri Nov 02 08:41:14 2018

Archived Log entry 1014432 added for thread 1 sequence 608497 rlc 810827692 ID 0x8c1e6f64 dest 2:

RFS[2]: No standby redo logfiles available for thread 1

RFS[2]: Opened log for thread 1 sequence 608499 dbid -1944187801 branch 810827692

Fri Nov 02 12:42:47 2018

alter database recover managed standby database disconnect

Attempt to start background Managed Standby Recovery process (ORA-DATA)

Fri Nov 02 08:42:47 2018

MRP0 started with pid=54, OS id=7948

MRP0: Background Managed Standby Recovery process started (ORA-DATA)

 started logmerger process

Fri Nov 02 08:42:53 2018

Managed Standby Recovery not using Real Time Apply

Fri Nov 02 08:42:53 2018

Reconfiguration started (old inc 4, new inc 6)

List of instances:

 1 2 (myinst: 1)

 Global Resource Directory frozen

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Fri Nov 02 08:42:53 2018

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Fri Nov 02 08:42:53 2018

 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Fri Nov 02 08:42:53 2018

 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Submitted all GCS remote-cache requests

 Fix write in gcs resources

Reconfiguration complete

Parallel Media Recovery started with 32 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: alter database recover managed standby database disconnect

Fri Nov 02 08:42:57 2018

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608453.131634.991106577

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423209.63201.991105597

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423210.119301.991106405

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608454.137049.991106667

Fri Nov 02 08:43:12 2018

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608455.141399.991107219

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608456.122183.991107343

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423211.111046.991107213

Fri Nov 02 08:43:24 2018

To Stop the MRP process:

$sqlplus '/as sysdba'

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>

Next – How to check Archive Gap and details…(soon)

Some more useful links:

Difference between fnd_conc_clone.setup_clean and cmclean

How to deploy RPD in OBIEE 11g

Concurrent Managers are down error insert_fcp failed due to ORA-01403 no data found, ORA-06512

Oracle Apps DBA Interview Questions and Answers for experienced asked Top MNC company

Regards,