Friday, September 8, 2017

DataGuard FastSync : Define the Redo Transport Mode SYNC NOAFFRIM

FASTSYNC is a new DataGuard capability available with Oracle Database 12c. The FASTSYNC configures redo transport services for this configuration member using the SYNC and NOAFFIRM attributes of the LOG_ARCHIVE_DEST_n initialization parameter. The attribute NOAFFIRM specifies that the standby acknowledge receipt of redo without waiting for the write to the standby redo log file to complete. This mode is only available in maximum availability protection mode.

Remark:
FASTSYNC transport mode uses the NOAFFIRM attribute of the LOG_ARCHIVE_DEST_n parameter, data loss is possible.

The following attributes of LOG_ARCHIVE_DEST_N initialization parameter define the redo transport mode that is used by primary database to send redo to the standby database:


  1. SYNC
    Specifies that redo data generated by a transaction must have received at a destination that has this attribute before the transaction can commit.  In the configuration with multiple SYNC destinations, the redo must be processes as described here for every SYNC destination.
  2. ASYNC (default)
    Specifies that redo data generated by a transaction need not have been received at a destination that has this attribute before the transaction can commit.
  3. AFFIRM Specifies that redo transport destination acknowledge received redo data after writing it to the standby redo log.
  4. NOAFFIRM Specifies that a redo transport destination acknowledge received redo data before writing it to the standby redo log

If neither the AFFIRM or NOAFFIRM attribute is specified, the default is AFFIRM.
  • When the SYNC attribute is specified and the NOAFFIRM 
  • When the ASYNC attribute is specified.

Configuration Zero Data Loss 

A Zero data loss protection requires synchronous communication between a production database (primary) and the remote database (standby) used for data protection and availability. The DataGuard favorite configuration for  zero data loss is Maximum Availability protection mode with synchronous redo transport (SYNC). With FAR SYNC instances and the usages of the FASTSYNC attribute for the redo data stream we can setup a Oracle (Active) Data Guard Far Sync Zero Data Loss at Any Distance

Far Sync Configuration

Configures redo transport services to use FASTSYNC

  • DGMGRL
    [oracle@oel7 Desktop]$ dgmgrl sysdg@oel7/cdb1_CFG
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Sep 8 02:14:20 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Password:
    Connected to "cdb2"
    Connected as SYSDG.
    DGMGRL> show configuration
    
    Configuration - DRSolution
    
      Protection Mode: MaxAvailability
      Members:
      cdb1   - Primary database
        cdb1fs - Far sync instance 
          cdb2   - Physical standby database 
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    SUCCESS   (status updated 25 seconds ago)
    
    DGMGRL> show database verbose cdb1 RedoRoutes
      RedoRoutes = '(cdb1:cdb1fs sync)'
    
    DGMGRL> show far_sync verbose cdb1fs RedoRoutes
      RedoRoutes = '(cdb1:cdb2 async)'
    
    DGMGRL> show database verbose cdb2 RedoRoutes
      RedoRoutes = '(cdb2:cdb1 sync)'
    
    DGMGRL> show database verbose cdb1 LogXptMode
      LogXptMode = 'SYNC'
    
    DGMGRL> show far_sync verbose cdb1fs LogXptMode
      LogXptMode = 'ASYNC'
      
    DGMGRL> show database verbose cdb2 LogXptMode
      LogXptMode = 'SYNC'
    
    SQL> show parameter log_archive_dest_2
    NAME         TYPE  VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_2   string  service="cdb1fs", SYNC AFFIRM
           delay=0 optional compression=d
           isable max_failure=0 max_conne
           ctions=1 reopen=300 db_unique_
           name="cdb1fs" net_timeout=30,
           valid_for=(online_logfile,all_
           roles)
    
    
  • 
    
  • Configure FASTSYNC

    DGMGRL> edit database cdb1 set property 'redoroutes'='(cdb1:cdb1fs fastsync)';
    
    DGMGRL> show database verbose cdb1 RedoRoutes
      RedoRoutes = '(cdb1:cdb1fs fastsync)'
    
    DGMGRL> show database verbose cdb1 LogXptMode
      LogXptMode = 'SYNC'
    
    
  • Alertlog
    ==> /home/oracle/app/diag/rdbms/cdb1/cdb1/trace/alert_cdb1.log <==
    Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
    ALTER SYSTEM SET log_archive_dest_2='service="cdb1fs"','SYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="cdb1fs" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
    ALTER SYSTEM SWITCH ALL LOGFILE start (cdb1)
    
    
  • Settings LOG_ARCHIVE_DEST_n
    • Before change => log_archive_dest_2 setting
      SQL> show parameter log_archive_dest_2
      
      NAME         TYPE  VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_2       string  service="cdb1fs", SYNC AFFIRM 
                                                       delay=0 optional compression
             =disable max_failure=0 max_con
             nections=1 reopen=300 db_uniqu
             e_name="cdb1fs" net_timeout=30
             , valid_for=(online_logfile,al
             l_roles)
      
      
    • After change => log_archive_dest_2 setting

      SQL> show parameter log_archive_dest_2
      
      NAME         TYPE  VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_2       string  service="cdb1fs", SYNC NOAFFIRM 
                                                       delay=0 optional compression
             =disable max_failure=0 max_con
             nections=1 reopen=300 db_uniqu
             e_name="cdb1fs" net_timeout=30
             , valid_for=(online_logfile,al
             l_roles)