Category - DATAGUARD

Using Apply Services on Oracle Dataguard 19c - Using Real-Time & Archived-Apply

Using Real-Time & Delay apply on Oracle Dataguard 19c.

In this article we will practice to use Apply services. These will be in Real-Time apply and ArchiveLog apply Modes.
Apply from Archived Log on Standby - As per oracle dataguard 19c by default, apply services waits for a standby redo log file to be archived before applying the redo that it contains.
Apply from Standby Logfiles on Standby - Where as Real-time apply can be enabled ,which allows apply services to apply the redo in the current standby redo log file as it is being filled.

We can see configuration as per Oracle Doc below,




A=> Using Real-Time Apply to Apply Redo Data Immediately

If the real-time apply feature is enabled, then apply services can apply redo data as it is received, 
without waiting for the current standby redo log file to be archived, which is Default behaviour.

It also enables real-time reporting on an Oracle Active Data Guard standby by keeping it more closely synchronized with the primary database.
Issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement. (As of 12.1, the USING CURRENT LOGFILE clause is deprecated and no longer necessary to start real-time apply.)

As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database, 
apply services can recover redo from standby redo log files as they are being filled.

Demonstrations
Step 1=> Check Primary Database details.

SQL> @name.sql

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> show parameter log_Archive_DEst_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 SERVICE=TNSSET SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET

Step 2=> Check Standby Database details.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 SERVICE=TNSTAR ASYNC NOAFFIRMVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR

Step 3=> Check for existing GAP on Standby Database

SQL> @sync.sql

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
	 1		      112		     95 	17
	 2		       76		     65 	11

Step 4=> Let we start REAL-TIME apply on Standby database as below.

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

Database altered.

Check Apply Status as below on Standby database.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> 

check for GAP if any,

SQL> @sync.sql

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
	 1		      112		     112 	0
	 2		       76		     76 	0

Step 5=> Check Alert log on Standby Side
On Standby Database

Attempt to start background Managed Standby Recovery process (DBSET1)
Starting background process MRP0
2022-07-03T10:42:39.800001+05:30
MRP0 started with pid=97, OS id=27885 
2022-07-03T10:42:39.801084+05:30
Background Managed Standby Recovery process started (DBSET1)
2022-07-03T10:42:44.827407+05:30
Starting single instance redo apply (SIRA) 
 Started logmerger process
2022-07-03T10:42:44.877721+05:30
IM on ADG: Start of Empty Journal 
IM on ADG: End of Empty Journal 
2022-07-03T10:42:44.884263+05:30
.... (PID:6614): Managed Standby Recovery starting Real Time Apply
2022-07-03T10:42:45.302989+05:30

B=> Using Delayed apply to apply ARCHIVED LOG Files on Standby database.

We can Specify a Time Delay for the Application of Archived Redo Log Files
In some cases, you may want to create a time lag between the time when redo data is received from the primary site and when it is applied to the standby database.

You can specify a time interval (in minutes) to protect against the application of corrupted or erroneous data to the standby database.
When you set a DELAY interval, it does not delay the transport of the redo data to the standby database.
Instead, the time lag you specify begins when the redo data is completely archived at the standby destination.

Note:

If you define a delay for a destination that has real-time apply enabled, the delay is ignored.
If you define a delay as described in the following paragraph, then you must start the apply using the USING ARCHIVED LOGFILE clause .

Specifying a Time Delay

Set a time delay on primary and standby databases using the DELAY=minutes attribute of the LOG_ARCHIVE_DEST_n 
initialization parameter to delay applying archived redo log files to the standby database.

By default, there is no time delay. If you specify the DELAY attribute without specifying a value, then the default delay interval is 30 minutes.

Canceling a Time Delay

For physical standby databases, use the NODELAY keyword of the RECOVER MANAGED STANDBY DATABASE clause:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

These commands result in apply services immediately beginning to apply archived redo log files to the standby database, before the time interval expires.

Demonstrations

Step 1=> Check Primary Database details.

SQL> @name.sql

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> show parameter log_Archive_DEst_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 SERVICE=TNSSET SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET

Step 2=> Check Standby Database details.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 SERVICE=TNSTAR ASYNC NOAFFIRMVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR

Step 3=> Check for any GAP on Standby Side.

check for GAP if any,

SQL> @sync.sql

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
	 1		      112		     112 	0
	 2		       76		     76 	0

Step 4=> Stop Real-Time MRP on Standby if Running as below,

SQL> alter database recover managed standby database CANCEL;

Database altered.

Step 5=> Change LOG_ARCHIVE_DEST values on Primary Side as below.

SQL> show parameter db_uniq

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DBTAR

SQL> alter system set log_Archive_dest_state_2='DEFER' scope=both sid='*';

System altered.

SQL> alter system set log_Archive_Dest_2='SERVICE=TNSSET SYNC AFFIRM DELAY=3 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET';

System altered.

SQL>  alter system set log_Archive_dest_state_2='ENABLE' scope=both sid='*';

System altered.

Step 6=> Start NON-Real Time Apply -Archived Log apply on Standby database as below

SQL> alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session;

Database altered.


Step 7=> Check ALert log on Standby Site.

Completed: alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session
2022-07-03T10:49:03.124073+05:30
ALTER SYSTEM SET remote_listener=' racnode-scan:1521' SCOPE=MEMORY SID='DBSET1';
2022-07-03T10:49:03.125215+05:30
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='DBSET1';
2022-07-03T10:49:40.632750+05:30
ARC1 (PID:6871): Archived Log entry 317 added for T-2.S-79 ID 0x2a4b540b LAD:1
2022-07-03T10:49:40.632820+05:30
ARC1 (PID:6871): Archive log for T-2.S-79 available in 3 minute(s)
2022-07-03T10:49:41.054262+05:30
PR00 (PID:30939): Media Recovery Delayed for 3 minute(s) T-2.S-79
2022-07-03T10:49:43.461487+05:30

ARC7 (PID:6883): Archived Log entry 321 added for T-1.S-120 ID 0x2a4b540b LAD:1
2022-07-03T10:50:45.840954+05:30
ARC7 (PID:6883): Archive log for T-1.S-120 available in 3 minute(s)
2022-07-03T10:52:40.567003+05:30
PR00 (PID:30939): Media Recovery Log +FRA/DBSET/ARCHIVELOG/2022_07_03/thread_2_seq_79.463.1109069381
PR00 (PID:30939): Media Recovery Delayed for 3 minute(s) T-1.S-118
2022-07-03T10:52:46.967854+05:30

Step 8=> Now let we insert few records on Primary and Test on Standby as below.
On Primary

11:09:59 SQL>  select * from test_perf;

	ID
----------
	 2
	22
	22
	25
	25
       200
       300
       500

8 rows selected.
11:10:16 SQL> insert into test_perf values (900);
1 row created.

11:10:27 SQL> commit;
Commit complete.

11:10:29 SQL> alter system switch all logfile;
System altered.


ON Standby

11:13:38 SQL> select * from test_perf;
	ID
----------
	 2
	22
	22
	25
	25
       200
       300
       500

8 rows selected.

11:13:42 SQL> /
	ID
----------
	 2
	22
	22
	25
	25
       200
       300
       500
       900

9 rows selected.

11:13:53 SQL> 

So as we can see Redo is being written to StandbyLogfile and then Archived on LOCAL_DESTINATION .
Post archival on Log_archive_dest_1 at Standby Site it's waited for 3 Minutes and then applied on Standby Site.

Alert log on Standby SIde as below.

ARC0 (PID:6863): Archived Log entry 322 added for T-1.S-121 ID 0x2a4b540b LAD:1
2022-07-03T11:10:38.109711+05:30
ARC0 (PID:6863): Archive log for T-1.S-121 available in 3 minute(s)
2022-07-03T11:10:39.842199+05:30
PR00 (PID:30939): Media Recovery Delayed for 3 minute(s) T-2.S-81

2022-07-03T11:13:43.522048+05:30
PR00 (PID:30939): Media Recovery Log +FRA/DBSET/ARCHIVELOG/2022_07_03/thread_2_seq_81.469.1109070639
2022-07-03T11:13:43.701513+05:30
PR00 (PID:30939): Media Recovery Log +FRA/DBSET/ARCHIVELOG/2022_07_03/thread_1_seq_121.468.1109070639
PR00 (PID:30939): Media Recovery Waiting for T-1.S-122 (in transit)

Testing DELAY parameter with REAL-TIME Apply

Let we test what if we enabled REAL-TIME Apply on Standby Side that have DELAY Parameter on PRIMARY Side's LOG_ARCHIVE_DEST.

Step 1=> Check Primary Database details.

SQL> @name.sql

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> show parameter log_Archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 SERVICE=TNSSET SYNC AFFIRM DELAY=3 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET

Step 2=> Check Standby  Database details.

SQL> @name.sql
NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

Check of REAL-TIME apply is running on Standby Database Site.

SQL> 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
As we can see ONLY "MANAGED" returns ,It Means Apply is NOT In REAL-TIME mode, It's in Archived Mode.As we did in last example.

Step 3=> Change Log_archive_Dest setting on Primary as below.

SQL>  alter system set log_Archive_dest_state_2='DEFER' scope=both sid='*';
System altered.

Step 4=> Cancel ARCHIVED-LOG Apply on Standby database as below and start in REAL-TIME APPLY and Check status.

SQL> alter database recover managed standby database CANCEL;
Database altered.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> 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

SQL> 
Step 5=> Enable LOG_ARCHIVE_DEST on Primary Side as below,

SQL> alter system set log_Archive_dest_state_2='ENABLE' scope=both sid='*';

System altered.

Step 6=> Check Alert Logs for Primary and Standby Sides.

On Primary

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
2022-07-03T10:46:03.233926+05:30
LGWR (PID:8121): WARN: Managed Standby Recovery started with REAL TIME APPLY
LGWR (PID:8121): WARN: DELAY 3 minutes specified at primary ignored
LGWR (PID:8121): WARN: Managed Standby Recovery started with REAL TIME APPLY
LGWR (PID:8121): WARN: DELAY 3 minutes specified at primary ignored
LGWR (PID:8121): SRL selected for T-1.S-114 for LAD:2
2022-07-03T10:46:03.276042+05:30

On Standby

PR00 (PID:15591): Media Recovery Log +FRA/DBSET/ARCHIVELOG/2022_07_03/thread_2_seq_81.469.1109070639
PR00 (PID:15591): Managed Standby Recovery started with REAL TIME APPLY
PR00 (PID:15591): Ignoring previously specified DELAY 3 minutes for T-2.S-82

Step 7=> Check after few Minutes if REAL-TIME-QUERY is active Now.

Primary Side

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> 
SQL> 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 WITH QUERY

Standby Side
SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET	  READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>  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

So as we can see in REAL-TIME Apply Mode DELAY parameter got IGNORED on Standby Side.