ENABLE REALTIME APPLY IN ORACLE DATAGUARD

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 createdManaged 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)

Leave a Reply

%d bloggers like this: