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)