Primary node No Longer Transmits Archive Log Files To the physical standby database, MRP Stuck and No RFS Process on Standby

Issue:

Recently I faced strange issue where I observed that one of the standby node was not showing the RFS process, checked in V$MANAGED_STANDBY
V$MANAGED_STANDBY  - showing only ARCH and MRP processes.

Archive logs not shipping from Primary to Standby Node and there are no errors observed in primary and standby alert logs.

Tried to copy Archive logs manually from Primary and Standby but MRP recovery process not applying the further archive logs on Standby.

Cause:

This caused due to outage on the standby server for the network activity.

Observations:

On Primary:

ARCH process stuck on Primary instance

SELECT PROCESS, PID, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

DB Mode Showing UNKNOWN

select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs",
standby_logfile_active ACTIVE,
archived_seq#
from v$archive_dest_status
where dest_id =2;

On Standby:
No RFS Process showing when query below:

SELECT PROCESS, PID, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Solution:

1. On Primary:

- Set log transport state to DEFER status:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

2. On standby server:

- Shutdown Database Listener

- Cancel Managed Recovery

SQL> alter database recover managed standby database cancel;

- Shutdown the Standby Database

SQL> shutdown immediate

3. On the Primary

Kill the ARCx Processes and the Database will respawn them automatically immediately without harming it.

ps -ef | grep -i arc

kill -9 <ospid of ARC process> <another ospid of ARC process> ...

4. On standby server

- Startup Standby Database and resume Managed Recovery

SQL> startup mount;

SQL> alter database recover managed standby database disconnect from session;

- Start Database Listener


5. Set log transport state to ENABLE status:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

6. Monitor the alert logs at each site and ensure log shipping and
apply is occurring again.

Also, verify the DB Mode on Primary -

select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs",
standby_logfile_active ACTIVE,
archived_seq#
from v$archive_dest_status
where dest_id =2;

For Information: In case after Terminating ARCH process on primary node,
if it doesn't restart automatically, you can do the following :

On Primary:

SQL> alter system set log_archive_max_processes=4;

(increase log_archive_max_processes accordingly)

Query to get the Discoverer Scheduled jobs with User details

Oracle Discoverer Reports Schedule details with User for SOX Audit

You may want to be able to audit log which user has scheduled a specific workbook/ Discoverer report.

If you want to query information about user that has scheduled a report / discoverer workbook:

Query:

SELECT ebr.br_job_id, dj.job "DBA_JOB", ebr.br_created_by
      ,CASE
         WHEN INSTR (ebr.br_created_by, '#') = 0
           THEN ebr.br_created_by
         WHEN INSTR (ebr.br_created_by, '#') > 0 AND INSTR (ebr.br_created_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_created_by, 2, 9))
         ELSE NULL
       END "BR Owner/Creator - USER"
      ,dj.what,dj.broken, ebr.br_name, ebr.br_workbook_name, ebr.br_description, ebr.br_created_date, ebr.br_next_run_date, dj.next_date "DBA_JOB_NEXT_DATE", ebr.br_completion_date, ebr.br_updated_by
      ,CASE
         WHEN INSTR (ebr.br_updated_by, '#') = 0
           THEN ebr.br_updated_by
         WHEN INSTR (ebr.br_updated_by, '#') > 0 AND INSTR (ebr.br_updated_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_updated_by, 2, 9))
         ELSE NULL
       END "BR Updated by - USER"
      ,ebr.br_updated_date
  FROM EUL_OWNER.eul5_batch_reports ebr, dba_jobs dj
WHERE ebr.br_job_id = dj.job AND dj.broken = 'N';

Note : Replace EUL_OWNER with corresponding EUL owner of your environment in above query.