ENABLE REALTIME APPLY IN ORACLE DATAGUARD
Log apply services in dataguard plays a major role in recovery of logs from primary to standby and keep them in sync
Dataguard default mode : Redo apply (physical standby) and SQL apply (Logical standby)
As in most of cases, customers often use physical standby(PS).
Real time apply is a nice feature used in dataguard which directly apply the logs from standby redologs(SRL) to standby database and skips waiting for archival of redo part .
- Here RFS receive the redo from primary and store in SRL.
- MRP(physical standby) or LSP(Logical standby) directly recover the redo from SRL
Things required for Real time apply : Standby redolog files
Advantages: Fast switchover and failover due to log apply from SRL
Check the database role,open mode on both primary and standby database
kish@PRIM>select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBX00 READ WRITE PRIMARY
kish@STDBY>select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DBX00 MOUNTED PHYSICAL STANDBY
Verify if real time apply is enabled on your primary database. If standby redologs(SRL column) is set to NO, then real time apply is not enabled
kish@PRIM>col DEST_ID for 99999
kish@PRIM>col DEST_NAME for a20
kish@PRIM>select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
------- -------------------- --------- ---------------- --- -----------------------
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL NO MANAGED
kish@STDBY>col DEST_ID for 99999
kish@STDBY>col DEST_NAME for a20
kish@STDBY>select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
------- -------------------- --------- ---------------- --- -----------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL NO MANAGED
By default, you will see the message from logs saying ‘RFS[1]: No standby redo logfiles created – Managed Standby Recovery not using Real Time Apply’ . This confirm that realtime apply is not active
kish@STDBY>select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the active heartbeat ARCH
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process (PID:3522)
RFS[1]: No standby redo logfiles created
RFS[2]: Assigned to RFS process (PID:3524)
RFS[3]: Assigned to RFS process (PID:3526)
RFS[4]: Assigned to RFS process (PID:3528)
RFS[5]: Assigned to RFS process (PID:3530)
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_04/o1_mf_1_82_jonn4yj4_.arc
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_04/o1_mf_1_83_jonn4ynq_.arc
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_04/o1_mf_1_84_jonn4yoz_.arc
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_04/o1_mf_1_85_jonn4zl3_.arc
Check the logfile groups and their members on primary and standby
kish@PRIM>select GROUP#,count(MEMBER) from v$logfile group by GROUP# order by 1;
GROUP# COUNT(MEMBER)
-------------------------------------------------- --------------------------------------------------
1 2
2 2
3 2
4 2
5 1
6 1
7 1
8 1
9 2
10 2
11 2
12 2
12 rows selected.
kish@STDBY>select GROUP#,count(MEMBER) from v$logfile group by GROUP# order by 1;
GROUP# COUNT(MEMBER)
---------- -------------
1 2
2 2
3 2
4 2
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
12 rows selected.
Check if there are standby logfile added
kish@PRIM>col Redologs for a70
kish@PRIM>select ' |' ||a.group#||' | '||a.member||' | '||b.bytes/1024/1024||' | ' as Redologs from v$logfile a, v$standby_log b WHERE a.group# = b.group#;
no rows selected
kish@STDBY>col Redologs for a70
kish@STDBY>select ' |' ||a.group#||' | '||a.member||' | '||b.bytes/1024/1024||' | ' as Redologs from v$logfile a, v$standby_log b WHERE a.group# = b.group#;
no rows selected
If standby logfiles are not there, then add them both on primary and standby
kish@PRIM>alter database add standby logfile group 9 size 50M;
Database altered.
kish@PRIM>alter database add standby logfile group 10 size 50M;
Database altered.
kish@PRIM>alter database add standby logfile group 11 size 50M;
Database altered.
kish@PRIM>alter database add standby logfile group 12 size 50M;
Database altered.
kish@PRIM>select group#,bytes/1024/1024 "MB",status from v$standby_log;
GROUP# MB STATUS
-------------------------------------------------- -------------------------------------------------- ----------
9 50 UNASSIGNED
10 50 UNASSIGNED
11 50 UNASSIGNED
12 50 UNASSIGNED
On standby add standby redologs
kish@STDBY>alter database recover managed standby database cancel;
Database altered.
kish@STDBY>alter database add standby logfile group 9 size 50M;
Database altered.
kish@STDBY>alter database add standby logfile group 10 size 50M;
Database altered.
kish@STDBY>alter database add standby logfile group 11 size 50M;
Database altered.
kish@STDBY>alter database add standby logfile group 12 size 50M;
Database altered.
kish@STDBY>select group#,bytes/1024/1024 "MB",status from v$standby_log;
GROUP# MB STATUS
---------- ---------- ----------
9 50 UNASSIGNED
10 50 UNASSIGNED
11 50 UNASSIGNED
12 50 UNASSIGNED
Enable real time apply using below syntax
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
Verify if SRL column on primary is YES. This says that Real time apply is active.
kish@PRIM>select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
------- -------------------- --------- ---------------- --- -----------------------
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL YES MANAGED REAL TIME APPLY
kish@STDBY>select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
------- -------------------- --------- ---------------- --- -----------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL NO MANAGED REAL TIME APPLY
Validate if standby redologs are used by switching the logfile on primary and check standby log status in standby database
kish@PRIM>alter system switch logfile;
System altered.
kish@STDBY>select group#,bytes/1024/1024 "MB",status,FIRST_CHANGE#,NEXT_CHANGE# from v$standby_log;
GROUP# MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ------------- ------------
9 50 ACTIVE 4196962
10 50 UNASSIGNED
11 50 UNASSIGNED
12 50 UNASSIGNED
In logfile, you will find this message saying ‘Managed Standby Recovery starting Real Time Apply’
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Media Recovery Waiting for thread 1 sequence 86 (in transit)
Media Recovery Log /apps01/FRA/DBX01/archivelog/2021_10_04/o1_mf_1_86_jonn4x96_.arc
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Assigned to RFS process (PID:5549)
Media Recovery Waiting for thread 1 sequence 87 (in transit)
