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#);
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444