Start & Stop MRP on Standby database

Stop log apply service (MRP) on standby :-

alter database recover managed standby database cancel;

shutdown immediate;

stop log shipping from primary and shutdown primary database:-

Note:- Run this on primary--> to get log_archive_dest location for standby db's please use below query:-

col dest_name for a30
col status for a10
col destination for a50
select dest_name,status,destination from gv$archive_dest_status where destination is not null;

then :-

show parameter log_archive_dest_state_&n; ------> n is number from above query 

alter system set log_archive_dest_state_&n='DEFER';

shutdown immediate;

To start primary database & enable log shipping use below steps:-

steps to be performed on PRIMARY DB:-

startup;

alter system set log_archive_dest_state_&n='ENABLE';

steps to be performed on STANDBY DB:-

startup nomount;

alter database mount standby database; 

alter database recover managed standby database disconnect from session;

--------- to check status of mrp on standby and (lgwr or lns) on Primary -----

on standby:-

SQL > ! ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep

or 

$ ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep

SQL> 

-------------------------- Final all in one query for MRP DR ARCHIVE LOG GAP --------------

select a.thread# thread,
 k.host_name,
 k.instance_name,
 m.process,
 m.status,
 a.arch_seq arch,
 b.appl_seq appl,
(a.arch_seq-b.appl_seq)
 difference from (select thread#, max(sequence#) 
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
 (select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b,gv$instance k,gv$managed_standby m
 where a.thread# = b.thread# (+) and b.thread# = k.thread# (+) and m.thread#<>0  and m.process like '%MRP%' order by a.thread#, a.arch_seq, b.appl_seq;

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

--------------------------------------------------------------------------------------------------------------------------------------------
    THREAD HOST_NAME                 INSTANCE_NAME    PROCESS   STATUS             ARCH       APPL DIFFERENCE
---------- ---------------------------------------------------------------- ---------------- --------- ------------ ---------- ---------- ----------
         1 host1                                                          instance1        MRP0      APPLYING_LOG     418336     418336          0
         2 host2                                                          instance2        MRP0      APPLYING_LOG     512681     512681          0
         3 host3                                                          instance3        MRP0      APPLYING_LOG     564134     564134          0
         4 host4                                                          instance4        MRP0      APPLYING_LOG     549583     549583          0

**************** The Best Option is Search For Oracle Support Help ***************************************

OPEN STANDBY DB IN READONLY (ACTIVE DATAGUARD)

1. check status of dataguard

select name,open_mode from gv$dataguard;

NAME        OPEN_MODE

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

PUNE            MOUNTED

2. Cancel MRP on standby db

alter database recover managed standby database cancel;

3. Open standby db in readonly mode:-

Hint:- db should be in mount mode already.

alter database open read only;

4. Start MRP with real-time log apply on standby db:-

alter database recover managed standby database using current logfile disconnect from session;

5. Verify the standby db status:-

select name, open_mode from gv$database;

NAME        OPEN_MODE

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

PUNE        READ ONLY WITH APPLY 

6. Check MRP process is running :-

select process, status, sequence# from gv$managed_standby where process like '%MRP%';

PROCESS        STATUS        SEQUENCE#

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

MRP0            APPLYING LOG        2000

=========================================================================

************************** If you need to do manual recovery ********************************

**copy archive logs from  primary db to standby db and perform below steps:-

alter database recover managed standby database cancel;

alter database register logfile  '/u01/oradata/redo03.log';

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database disconnect from session;

check gap :-

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#

FROM V$ARCHIVE_GAP;