Category - DATAGUARD

Options for Redo Transport - Oracle dataguard 19c

Redo Transport options for Oracle dataguard 19c

Redo transport services performs the automated transfer of redo data between members of an Oracle Data Guard configuration and other databases.
Each redo transport destination is individually configured to receive redo data via one of two redo transport modes:

=> Synchronous Transport(SYNC)

The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment.
A transaction cannot commit until all redo generated by that transaction has been successfully sent to every enabled redo transport destination 
that uses the synchronous redo transport mode.
This transport mode is used by the Maximum Protection and Maximum Availability data protection modes described in Oracle Data Guard Protection Modes.

=> Asynchronous Transport (ASYNC)

The asynchronous redo transport mode transmits redo data asynchronously with respect to transaction commitment.
A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport 
destination that uses the asynchronous redo transport mode.
This transport mode is used by the Maximum Performance data protection mode described in Oracle Data Guard Protection Modes.

Redo Transport Authentication Using a Password File

In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database.
The copy of the password file is automatically refreshed whenever an administrative privilege (SYSDG, SYSOPER, SYSDBA, and so on) is granted or revoked, 
and after the password of any user with administrative privileges is changed.

The only exception to this is far sync instances. Updated password files must still be manually copied to far sync instances because far 
sync instances receive redo, but do not apply it.
Once the password file is up-to-date at the far sync instance the redo containing the password update at the primary is automatically 
propagated to any standby databases that are set up to receive redo from that far sync instance.
The password file is updated on the standby when the redo is applied.

When a password file is used for redo transport authentication, the password of the user account used for redo transport authentication is compared between the database initiating 
a redo transport session and the target database.
The password must be the same at both databases to create a redo transport session.

By default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used.
The REDO_TRANSPORT_USER database initialization parameter can be used to select a different user password for redo transport authentication by setting 
this parameter to the name of any user who has been granted the SYSOPER privilege.
For administrative ease, Oracle recommends that the REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination.
Testing-Time.

Configuring an Oracle Database to Receive Redo Data

Redo transport destination must be configured to receive and to archive redo data from a redo source database.

Managing Standby Redo Logs

The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log.

A standby redo log is used to store redo received from another Oracle database.

Standby redo logs are structurally identical to redo logs, and are created and managed using the same SQL statements used to create and manage redo logs.

Redo received from another Oracle database via redo transport is written to the current standby redo log group by a remote file server (RFS) foreground process.

When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, 
and the previously used standby redo log group is archived by an ARCn background process.

The process of sequentially filling and then archiving redo log file groups at a redo source database is 
mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups.

Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database.
For administrative ease, Oracle recommends that all redo log files in the redo log at the redo source database and the 
standby redo log at a redo transport destination be of the same size.

The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database.

Cases Where Redo Is Written Directly To an Archived Redo Log File

Redo received by a standby database is written directly to an archived redo log file if
a standby redo log group is not available or
if the redo was sent to resolve a redo gap.

When this occurs, redo is written to the location specified by the LOCATION attribute of one LOG_ARCHIVE_DEST_n parameter that is valid for archiving redo received from another database.
The LOG_ARCHIVE_DEST_n parameter that is used for this purpose is determined when the standby database is mounted, 
and this choice is reevaluated each time a LOG_ARCHIVE_DEST_n parameter is modified.

=> Validating a Configuration

To validate an Oracle Data Guard configuration after you create it, query the V$DATAGUARD_CONFIG view from any database in the configuration.

The view displays the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters.

On Primary

SQL> set lines 5000
SQL> show parameter db_uniq
NAME				     TYPE	 VALUE
------------------------------------ ----------- -------
db_unique_name			     string	 DBTAR

SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME		       PARENT_DBUN		      DEST_ROLE 	CURRENT_SCN	CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
DBTAR			       NONE			      PRIMARY DATABASE	    3834443	     0
DBSET			       DBTAR			      PHYSICAL STANDBY	    3834459	     0

SQL> 

On Standby

SQL> set lines 5000
SQL> show parameter db_uniq
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------
db_unique_name			     string	 DBSET

SQL> select * from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME		       PARENT_DBUN		      DEST_ROLE 	CURRENT_SCN	CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
DBSET			       DBTAR			      PHYSICAL STANDBY	    3834013	     0
DBTAR			       NONE			      PRIMARY DATABASE		  0	     0


=> Viewing Attributes of Destinations With V$ARCHIVE_DEST

The V$ARCHIVE_DEST view can be queried to see the current settings and status for each redo transport destination.

On Primary

SQL> @name

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

SQL>  COL DEST_NAME FOR A20
 COL DESTINATION FOR A10
 SET LINES 5000
 select DEST_ID,DEST_NAME,STATUS,ARCHIVER,DESTINATION,DELAY_MINS,NET_TIMEOUT,PROCESS,TRANSMIT_MODE,AFFIRM,VALID_TYPE,VALID_ROLE,DB_UNIQUE_NAME from V$ARCHIVE_DEST where DEST_ID=1;
SQL> SQL> SQL> 
   DEST_ID DEST_NAME		STATUS	  ARCHIVER   DESTINATIO DELAY_MINS NET_TIMEOUT PROCESS	  TRANSMIT_MOD AFF VALID_TYPE	   VALID_ROLE	DB_UNIQUE_NAME
---------- -------------------- --------- ---------- ---------- ---------- ----------- ---------- ------------ --- --------------- ------------ ------------------------------
	 1 LOG_ARCHIVE_DEST_1	VALID	  ARCH	     USE_DB_REC 	 0	     0 ARCH	  SYNCHRONOUS  NO  ALL_LOGFILES    ALL_ROLES	DBTAR
						     OVERY_FILE
						     _DEST

SQL> select DEST_ID,DEST_NAME,STATUS,ARCHIVER,DESTINATION,DELAY_MINS,NET_TIMEOUT,PROCESS,TRANSMIT_MODE,AFFIRM,VALID_TYPE,VALID_ROLE,DB_UNIQUE_NAME from V$ARCHIVE_DEST where DEST_ID=2;

   DEST_ID DEST_NAME		STATUS	  ARCHIVER   DESTINATIO DELAY_MINS NET_TIMEOUT PROCESS	  TRANSMIT_MOD AFF VALID_TYPE	   VALID_ROLE	DB_UNIQUE_NAME
---------- -------------------- --------- ---------- ---------- ---------- ----------- ---------- ------------ --- --------------- ------------ ------------------------------
	 2 LOG_ARCHIVE_DEST_2	VALID	  LGWR	     TNSSET		 3	    30 LGWR	  PARALLELSYNC YES ONLINE_LOGFILE  PRIMARY_ROLE DBSET

On Standby

SQL> COL DEST_NAME FOR A20
SQL> COL DESTINATION FOR A10
SQL> SET LINES 5000
SQL> select DEST_ID,DEST_NAME,STATUS,ARCHIVER,DESTINATION,DELAY_MINS,NET_TIMEOUT,PROCESS,TRANSMIT_MODE,AFFIRM,VALID_TYPE,VALID_ROLE,DB_UNIQUE_NAME from V$ARCHIVE_DEST where DEST_ID=1;

   DEST_ID DEST_NAME		STATUS	  ARCHIVER   DESTINATIO DELAY_MINS NET_TIMEOUT PROCESS	  TRANSMIT_MOD AFF VALID_TYPE	   VALID_ROLE	DB_UNIQUE_NAME
---------- -------------------- --------- ---------- ---------- ---------- ----------- ---------- ------------ --- --------------- ------------ ------------------------------
	 1 LOG_ARCHIVE_DEST_1	VALID	  ARCH	     USE_DB_REC 	 0	     0 ARCH	  SYNCHRONOUS  NO  ALL_LOGFILES    ALL_ROLES	DBSET
						     OVERY_FILE
						     _DEST

SQL> select DEST_ID,DEST_NAME,STATUS,ARCHIVER,DESTINATION,DELAY_MINS,NET_TIMEOUT,PROCESS,TRANSMIT_MODE,AFFIRM,VALID_TYPE,VALID_ROLE,DB_UNIQUE_NAME from V$ARCHIVE_DEST where DEST_ID=2;

   DEST_ID DEST_NAME		STATUS	  ARCHIVER   DESTINATIO DELAY_MINS NET_TIMEOUT PROCESS	  TRANSMIT_MOD AFF VALID_TYPE	   VALID_ROLE	DB_UNIQUE_NAME
---------- -------------------- --------- ---------- ---------- ---------- ----------- ---------- ------------ --- --------------- ------------ ------------------------------
	 2 LOG_ARCHIVE_DEST_2	VALID	  LGWR	     TNSTAR		 0	    30 LGWR	  ASYNCHRONOUS NO  ONLINE_LOGFILE  PRIMARY_ROLE DBTAR

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> 

Setting Archive Tracing

Using LOG_ARCHIVE_TRACE 
LOG_ARCHIVE_TRACE enables and controls the generation of comprehensive trace information for log archiving and redo transport activity.
The additional tracing that is output when setting LOG_ARCHIVE_TRACE to a non-zero value can appear in trace files for an archive process,
RFS process, LGWR process, SYNC process, ASYNC process, foreground process, MRP process, recovery process, log apply process, startup process,
shutdown process, and other processes that use redo transport services.

Let we trace & Tracks real-time apply as below on Standby Database Side.

SQL > alter system set log_archive_trace=4096

Go to trace folder and look for RFS Trace file as below.

[oracle@racnoden1 trace]$ tai -100f DBSET1_pr00_15591.trc

*** 2022-07-03T12:34:57.127788+05:30
MRP: LAG_TIMING: RTA woke up due to 
MRP: Read(2) thr 2 seq 87 start 89 read 1 req 1 min_cnt 0 nab -1 status 0
MRP: Reading thread 1 seq 134 bno 73 max_cnt 0 min_cnt 0
MRP: entering loop for logical end of file [thr=1 seq=134 bno=73 min_cnt=0 max_cnt=0]
MRP: Reading control file to determine SRL completion for [thr=1,seq=134]
krsm_find_in_srl: Checking completion of T-1.S-134 LNO:6
krsm_find_in_srl: B-1107118395.T-1.S-134 rhp B-1107118395.T-1.S-134  le_seq 134 le_lot 1109075626
krsm_find_in_srl: RTA Re-read SRL header T-1.S-134 LNO:6. Found NAB -1 LOS 0x00000000003aa40c NXS 0xffffffffffffffff
  SRL valid
MRP: LAG_TIMING: sleeping and waiting to be posted or for timeout
MRP: LAG_TIMING: RTA woke up due to
MRP: Read(2) thr 1 seq 134 start 73 read 1 req 1 min_cnt 0 nab -1 status 0
MRP: Reading thread 2 seq 87 bno 90 max_cnt 0 min_cnt 0
MRP: entering loop for logical end of file [thr=2 seq=87 bno=90 min_cnt=0 max_cnt=0]
MRP: Reading thread 1 seq 134 bno 74 max_cnt 0 min_cnt 0
MRP: entering loop for logical end of file [thr=1 seq=134 bno=74 min_cnt=0 max_cnt=0]
MRP: Reading thread 1 seq 134 bno 74 max_cnt 0 min_cnt 0
MRP: entering loop for logical end of file [thr=1 seq=134 bno=74 min_cnt=0 max_cnt=0]
MRP: Reading control file to determine SRL completion for [thr=1,seq=134]
krsm_find_in_srl: Checking completion of T-1.S-134 LNO:6
krsm_find_in_srl: B-1107118395.T-1.S-134 rhp B-1107118395.T-1.S-134  le_seq 134 le_lot 1109075626
krsm_find_in_srl: RTA Re-read SRL header T-1.S-134 LNO:6. Found NAB -1 LOS 0x00000000003aa40c NXS 0xffffffffffffffff
  SRL valid
MRP: LAG_TIMING: sleeping and waiting to be posted or for timeout

Redo Transport Wait Events

Wait Event     Description

LNS wait on ATTACH      -> Total time spent waiting for redo transport sessions to be established to all ASYNC and SYNC redo transport destinations

LNS wait on SENDREQ  -> Total time spent waiting for redo data to be written to all ASYNC and SYNC redo transport destinations

LNS wait on DETACH    -> Total time spent waiting for redo transport connections to be terminated to all ASYNC and SYNC redo transport destinations

Monitoring Apply Sessions as below

Perform the following query on the redo source database to determine the most recently archived sequence number for each thread:

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 

GROUP BY THREAD#;

Perform the following query on the redo source database to determine the most recently archived redo log file at each redo transport destination:

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#  FROM V$ARCHIVE_DEST_STATUS  WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

Perform a query at a redo source database to find out if an archived redo log file has been received at a particular redo transport destination.

Each destination has an ID number associated with it.

You can query the DEST_ID column of the V$ARCHIVE_DEST view on a database to identify each destination's ID number.

Assume that destination 1 points to the local archived redo log and that destination 2 points to a redo transport destination.

Perform the following query at the redo source database to find out if any log files are missing at the redo transport destination:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM -
> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) -
> LOCAL WHERE -
> LOCAL.SEQUENCE# NOT IN -
> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND -
> THREAD# = LOCAL.THREAD#);