1. Deploy Active Data Guard Far Sync to protect data across Oracle Database@Azure regions
  2. Deploy Active Data Guard Far Sync Instances

Deploy Active Data Guard Far Sync Instances

Deploy an Active Data Guard far sync instance on the OCI compute virtual machine in each region to ensure zero data loss across regions.

  1. Create a far sync control file.

    Create a control file for the far sync instance from the primary database.

    SQL> alter database create far sync instance controlfile as '/home/oracle/controlfs.ctl';

    Copy the newly created control file to the far sync instance hosts.

    scp -p controlfs.ctl farsync-toronto:/u01/app/oracle/oradata/fstor/controlfs/
scp -p controlfs.ctl farsync-sydney:/u01/app/oracle/oradata/fssyd/controlfs/
  2. Copy the password file.

    Copy the password file of the primary database to the far sync instance hosts using the name of the far sync instances. In this example, the far sync instance in the Toronto region will be named FSTOR. Hence the name of the password file is orapwFSTOR. 

    #check the location of the password file
SQL> select file_name from v$passwordfile_info;
  
#as grid user, copy the file from ASM to the local filesystem
asmcmd -p
cp +DATAC2/DB_TORONTO/PASSWORD/pwddb_toronto.421.1185102507 /home/grid/orapw
  
#as root user, change the owner of the password file to oracle user
cp /home/grid/orapw /home/oracle/
chown oracle:oinstall /home/oracle/orapw
 
#copy the files to the Far Sync instance hosts using the new names "orapwFSTOR" and "orapwFSSYD"
scp -p /home/oracle/orapw farsync-toronto:/u01/app/oracle/product/19c/dbhome_1/dbs/orapwFSTOR                    
scp -p /home/oracle/orapw farsync-sydney:/u01/app/oracle/product/19c/dbhome_1/dbs/orapwFSSYD
  3. Create a parameter file.

    Create a parameter file for the far sync instance from the primary database, copy it to the far sync instance hosts, and adjust the parameters to reflect your parameter values and directory paths for the far sync instance.

    Alternatively, you can start with a new parameter file on the far sync instance hosts (for example, in the Toronto region).

    #create a parameter file
vi /u01/app/oracle/product/19c/dbhome_1/dbs/init_FSTOR.ora
*.db_name='DBTOR'
*.db_unique_name='FSTOR'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/fstor/controlfs/controlfs.ctl'
*.diagnostic_dest='/u01/app/oracle/oradata/fstor/diag/'
*.core_dump_dest='/u01/app/oracle/oradata/fstor/coredump/'
*.audit_file_dest='/u01/app/oracle/oradata/fstor/audit/'
*.audit_trail='DB'
*.db_recovery_file_dest='/u01/app/oracle/oradata/fstor/fra/'
*.db_recovery_file_dest_size=250g
*.dg_broker_config_file1='/u01/app/oracle/oradata/fstor/dg_broker/fstor_01.dat'
*.dg_broker_config_file2='/u01/app/oracle/oradata/fstor/dg_broker/fstor_02.dat'
*.dg_broker_start=TRUE*.dispatchers='(PROTOCOL=TCP) (SERVICE=FSTORXDB)'
*.standby_file_management='AUTO'

#start the instance with the parameter file
export ORACLE_SID=FSTOR
sqlplus / as sysdba
startup pfile='/u01/app/oracle/product/19c/dbhome_1/dbs/init_FSTOR.ora' nomount;

#create an spfile
create spfile='/u01/app/oracle/product/19c/dbhome_1/dbs/spfileFSTOR.ora' from pfile='/u01/app/oracle/product/19c/dbhome_1/dbs/init_FSTOR.ora';

#restart the instance with the spfile
shutdown immediate;
startup mount;
select name, db_unique_name, database_role, open_mode from v$database;

    In the Sydney region:

    #create a parameter file
vi /u01/app/oracle/product/19c/dbhome_1/dbs/init_FSSYD.ora
*.db_name='DBTOR'
*.db_unique_name='FSSYD'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/fssyd/controlfs/controlfs.ctl'
*.diagnostic_dest='/u01/app/oracle/oradata/fssyd/diag/'
*.core_dump_dest='/u01/app/oracle/oradata/fssyd/coredump/'
*.audit_file_dest='/u01/app/oracle/oradata/fssyd/audit/'
*.audit_trail='DB'
*.db_recovery_file_dest='/u01/app/oracle/oradata/fssyd/fra/'
*.db_recovery_file_dest_size=250g
*.dg_broker_config_file1='/u01/app/oracle/oradata/fssyd/dg_broker/fssyd_01.dat'
*.dg_broker_config_file2='/u01/app/oracle/oradata/fssyd/dg_broker/fssyd_02.dat'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FSSYDXDB)'
*.standby_file_management='AUTO'
 
#start the instance with the parameter file
export ORACLE_SID=FSSYD
sqlplus / as sysdba
startup pfile='/u01/app/oracle/product/19c/dbhome_1/dbs/init_FSSYD.ora' nomount;
 
#create an spfile
create spfile='/u01/app/oracle/product/19c/dbhome_1/dbs/spfileFSSYD.ora' from pfile='/u01/app/oracle/product/19c/dbhome_1/dbs/init_FSSYD.ora';
 
#restart the instance with the spfile
shutdown immediate;
startup mount;
select name, db_unique_name, database_role, open_mode from v$database;
  4. Create a database listener.

    On the far sync hosts, create and start a database listener.

    In the Toronto region:

    vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = farsync-toronto.hubsubnettoront.hubvcntoronto.oraclevcn.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
lsnrctl start
lsnrctl services

    In the Sydney region:

    vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = farsync-sydney.hubsubnetsydney.hubvcnsydney.oraclevcn.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
lsnrctl start
lsnrctl services
  5. Add primary and standby TNS entries to the far sync hosts. 
    vi $ORACLE_HOME/network/admin/tnsnames.ora
 
DB_TORONTO=
        (DESCRIPTION=
          (ADDRESS=(PROTOCOL=TCP)(HOST=vmc1ca-4vzoi-scan.ocidbdelegated.ocivnoffsitedb.oraclevcn.com)(PORT=1521))
          (CONNECT_DATA=
            (SERVER=DEDICATED)
            (SERVICE_NAME=db_toronto.ocidbdelegated.ocivnoffsitedb.oraclevcn.com)
            )
          )
 
DB_SYDNEY=
        (DESCRIPTION=
          (ADDRESS=(PROTOCOL=TCP)(HOST=abvmc1auseh-glcfo-scan.ociabvnet5subn.ociabvirtualne.oraclevcn.com)(PORT=1521))
          (CONNECT_DATA=
            (SERVER=DEDICATED)
            (SERVICE_NAME=db_sydney.ociabvnet5subn.ociabvirtualne.oraclevcn.com)
            )
          )
  6. Add the far sync TSN entries to the primary and standby database hosts.

    Add the TNS connection strings of the far sync instances to the tnsname.ora file on the primary and standby database hosts (on all hosts of the Exadata VM cluster). 

    vi $ORACLE_HOME/network/admin/dbtor/tnsnames.ora
FSTOR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = farsync-toronto)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FSTOR)
    )
  )
 
FSSYD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = farsync-sydney)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FSSYD)
    )
  )
 
#test the connectivity
sqlplus SYS@FSTOR as sysdba
show parameter instance_name
 
sqlplus SYS@FSSYD as sysdba
show parameter instance_name
 
#if the connection fails, make sure port 1521 is open on the Far Sync instance hosts
sudo firewall-cmd --permanent --add-port=1521/tcp
sudo firewall-cmd --reload
  7. Add and enable the far sync instances to the Active Data Guard configuration.

    Use the Active Data Guard broker to add the far sync instance to the existing Active Data Guard configuration while connected to the primary database. 

    dgmgrl SYS as sysdba
DGMGRL> ADD FAR_SYNC FSTOR AS CONNECT IDENTIFIER IS FSTOR;
DGMGRL> ENABLE FAR_SYNC fstor;
 
DGMGRL> ADD FAR_SYNC FSSYD AS CONNECT IDENTIFIER IS FSSYD;
DGMGRL> ENABLE FAR_SYNC fssyd;
  8. Edit the Active Data Guard broker's RedoRoutes properties.

    Use the Active Data Guard broker’s RedoRoutes property to configure the primary database to transport the redo to the local far sync instance in FastSync mode and the far sync instance to forward the redo to the remote standby database in ASYNC mode. 

    EDIT DATABASE 'db_toronto' SET PROPERTY RedoRoutes = '( LOCAL : ( fstor FASTSYNC PRIORITY=1, db_sydney ASYNC PRIORITY=2 ))';
EDIT FAR_SYNC 'fstor' SET PROPERTY RedoRoutes = '( db_toronto : db_sydney ASYNC )';
 
EDIT DATABASE 'db_sydney' SET PROPERTY RedoRoutes = '( LOCAL : ( fssyd FASTSYNC PRIORITY=1, db_toronto ASYNC PRIORITY=2 ))';
EDIT FAR_SYNC 'fssyd' SET PROPERTY RedoRoutes = '( db_sydney : db_toronto ASYNC )';
    The database log_archive_dest_n parameters for the primary, standby, and Far Sync instances are now set accordingly.
  9. Change the Active Data Guard configuration protection mode.

    Increase the protection mode of the Active Data Guard configuration to Maximum Availability. 

    DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
  10. Check the Active Data Guard configuration.

    Check the configuration.

    DGMGRL> show configuration
 
Configuration - dbtor_dgconf
 
  Protection Mode: MaxAvailability
  Members:
  db_toronto - Primary database
    fstor      - Far sync instance
      db_sydney  - Physical standby database
 
  Members Not Receiving Redo:
  fssyd      - Far sync instance
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 57 seconds ago)

    Note:

    In the beginning, the remote far sync instance will show a warning "Warning: ORA-16789: standby redo logs configured incorrectly" until it receives redo data for the first time, either after a role switch or by temporarily changing the redo routes configuration to send data to the remote far sync instance.

    Check the configuration after the switchover.

    DGMGRL> show configuration
 
Configuration - dbtor_dgconf
 
  Protection Mode: MaxAvailability
  Members:
  db_sydney  - Primary database
    fssyd      - Far sync instance
      db_toronto - Physical standby database
 
  Members Not Receiving Redo:
  fstor      - Far sync instance
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 54 seconds ago)