Wednesday, August 30, 2017

MaxAvailability DataGuard environment : Adding a far sync instance to the environment

An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo
from the primary database and then ships that redo to other members of the Oracle Data Guard configuration. A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with  standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.

Far Sync is a lightweight Oracle instance that has only a control file, spfile, password file
and standby log files, there are no database files or online redo logs

Oracle Far Sync provides the ability to perform a zero data loss failover to a remote standby database without requiring a second standby database or complex operation. Far Sync enables this by deploying a Far Sync instance at a distance that is within an acceptable range of the primary for SYNC transport. The Far Sync instance receives redo from the primary via SYNC transport. and immediately forwards the redo to the standby databases via ASYNC transport.

The Far Sync instance can also forward redo to the new Oracle Database Backup, Logging, and Recovery Appliance

First we create a DataGuard environment in Max Aviability mode.

To create the environment follow the steps of  "Using DBCA to create a standby database"
This will setup an environment of cdb1 and cdb2 database with MaxAviablility.

DGMGRL connfiguration overview Dataguard status :
DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1 - Primary database
    cdb2 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

We can create manualy the far sync instance. In this blog we do it with the new RMAN command "RMAN "DUPLICATE TARGET DATABASE FOR FARSYNC FROM ACTIVE DATABASE"

Setup and prepare the farsync environment cdb1fs instance:
  1. Setup and prepare the farsync environment cdb1fs instance:
  2. Add cdb1fs to the /etc/oratab
  3. Setup a tnsnames entry to the networking 
  4. Prepare a dedicated service name for the farsync Database in the listener configuration
  5. Create a inital far sync instance cdb1fs with pfile and state nomount
  6. Create the duplicate RMAN script for creating the far sync instance cdb1fs 
  7. Add the far sync instance cdb1fs to the dataguard environment.
1. Add cdb1fs to the /etc/oratab
echo "cdb1fs:/home/oracle/app/oracle/product/12.2.0/db_1:N" >> /etc/oratab

2. Setup the tnsnames for cdb1fs far sync instance 
echo "CDB1FS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb1fs)
   (ur=a)
    )
  )
" >> $ORACLE_HOME/network/admin/tnsnames.ora

3. Prepare a dedicated service name for the farsync Database in the listener configuration 
Listener.ora  SID_LIST_LISTENER  
===============================
SID_LIST_LISTENER=
   (SID_LIST=
 (SID_DESC=
          (GLOBAL_DBNAME=cdb1_dgmgrl)
          (SID_NAME=cdb1)   
          (ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/db_1)
         )
 (SID_DESC=
          (GLOBAL_DBNAME=cdb2_dgmgrl)
          (SID_NAME=cdb2)   
          (ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/db_1)
         )
 (SID_DESC=
          (GLOBAL_DBNAME=cdb1fs)
          (SID_NAME=cdb1fs)   
          (ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/db_1)
         )
        )

And execute a reload of the listener so that the service is registerd as a service into the listener
lsnrctl reload

4. Create a inital far sync instance cdb1fs with pfile and state nomount  
mkdir -p /home/oracle/app/admin/cdb1fs/adump

Create and startup the cdb1fs instance
echo "db_unique_name=cdb1fs
db_name=cdb1
sga_target=325M
pga_aggregate_target=10M" >> $ORACLE_HOME/dbs/initcdb1fs.ora

cd $ORACLE_HOME/dbs
orapwd file='orapwcdb1fs' entries=10 dbuniquename=cdb1fs password=xxxxx


. oraenv [cdb1fs]
sqlplus / as sysdba << EOF
startup force nomount
exit 
EOF


5a. Create the duplicate RMAN script for creating the farsync  
echo "run {allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE FOR FARSYNC
FROM ACTIVE DATABASE
SPFILE
  SET DB_UNIQUE_NAME='cdb1fs'
  SET SGA_TARGET='352M'
  RESET CONTROL_FILES
  SET LOG_ARCHIVE_DEST_2=''
;}" > $HOME/active_standby_farsync.rcv 

5b. execute the duplicate RMAN script for creating the farsync 
rman auxiliary sys/oracle@cdb1fs  target  sys/oracle@cdb1 cmdfile=/home/oracle/active_standby_farsync.rcv log=/home/oracle/cre_actv_stndby_farsync.log

6. Add the farsync cdb1fs to the dataguard environment
dgmgrl sysdg@oel7/cdb1_CFG

DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1 - Primary database
    cdb2 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

Add the far sync instance to the dataguard connfiguration
==========================================================

DGMGRL>  ADD FAR_SYNC cdb1fs as connect identifier is cdb1fs;
far sync instance "cdb1fs" added

DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1   - Primary database
    cdb2   - Physical standby database 

  Members Not Receiving Redo:
  cdb1fs - Far sync instance (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 40 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.

DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Members:
  cdb1   - Primary database
    cdb2   - Physical standby database 

  Members Not Receiving Redo:
  cdb1fs - Far sync instance (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

DGMGRL> edit database cdb1    set property 'redoroutes'='(cdb1:cdb1fs sync)';

DGMGRL> edit far_sync cdb1fs  set property 'redoroutes'='(cdb1:cdb2 async)';

DGMGRL> edit far_sync cdb1fs  set property 'redoroutes'='(cdb1:cdb2 async)';

DGMGRL> show configuration    

Configuration - DRSolution

  Protection Mode: MaxPerformance
  Members:
  cdb1   - Primary database
    cdb1fs - Far sync instance (disabled)
      cdb2   - Physical standby database 
        Error: ORA-16685: database does not receive redo data

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 12 seconds ago)

DGMGRL> enable far_sync cdb1fs;
Enabled.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
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 19 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)'

How and what with a switchover Dataguard command
Lets play what will happen if we do a switch over of the database. The FAR SYNC instance is only operational for the cdb1 instance the cdb2 instance in this setup is communication directly to the cdb1.

DGMGRL> switchover to cdb2;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb2"
Connecting ...
Connected to "cdb2"
Connected as SYSDG.
New primary database "cdb2" is opening...
Operation requires start up of instance "cdb1" on database "cdb1"
Starting instance "cdb1"...
ORACLE instance started.
Database mounted.
Connected to "cdb1"
Switchover succeeded, new primary is "cdb2"

DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb2   - Primary database
    cdb1   - Physical standby database 

  Members Not Receiving Redo:
  cdb1fs - Far sync instance 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

DGMGRL> switchover to cdb1
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb1"
Connecting ...
Connected to "cdb1"
Connected as SYSDG.
New primary database "cdb1" is opening...
Operation requires start up of instance "cdb2" on database "cdb2"
Starting instance "cdb2"...
ORACLE instance started.
Database mounted.
Connected to "cdb2"
Switchover succeeded, new primary is "cdb1"

DGMGRL> show configuration

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1   - Primary database
    cdb1fs - Far sync instance 
      Warning: ORA-16778: redo transport error for one or more members

      cdb2   - Physical standby database 
        Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 12 seconds ago)

= ============================ =
= Be patience  After some time =
= ============================ =

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 42 seconds ago)

Problems with the rollout of FAR SYNC instance 
  •  ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
    • DGMGRL command ADD FAR_SYNC cdb1fs as connect identifier is cdb1fs;
      Results into an Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set.
      Solution => Use in the duplicate command RMAN a reset of the log_archive_dest_state_2  "SPFILE SET LOG_ARCHIVE_DEST_2=''"
  • ORA-16900: change of LogXptMode or RedoRoutes property violates overall protection mode
    • DGMGRL command edit database cdb1 set property 'redoroutes'='(cdb1:cdb1fs sync)';
      Result into an Error: ORA-16900: change of LogXptMode or RedoRoutes property violates overall protection mode.
      Solution => Temporary change the protection mode to MAXPERFORMANCE. Execute the add and redoroutes properties. When finished protection mode to MAXAVIABLILITY