We're here to help!

Replicate & DataGuard with VIP failover

Follow

How to normalize Dbvisit Standby configuration with Oracle DataGuard and/or Dbvisit Standby using VIP failover.

Manual VIP failover is done by simple remote ssh by any other 3rd party cluster software is preferred to create HA (VCS, Oracle  CRS, Linux-HA, Heartbeat, ...)

 

Pre-requisites

Node 1 - dbvlin216.dbvisit.co.nz DB_UNIQUE_NAME SRCDB, SID SRCDB
Node 2 - dbvlin217.dbvisit.co.nz DB_UNIQUE_NAME SRCDB_STBY, SID SRCDB

Oracle RDBMS installed
Oracle DataGuard or Dbvisit Standby installed https://oracle-base.com/articles/12c/data-guard-setup-using-broker-12cr1

OS VIP configuration

VIP1 - dbvlin-dg-vip.dbvisit.co.nz (this VIP will be used by dedicated Oracle listener and running always on primary node)
VIP2 - dbvlin-sb-vip.dbvisit.co.nz (this VIP will be used by MINE process and running always on standby node)

#/etc/hosts

172.16.1.176    dbvlin216.dbvisit.co.nz dbvlin216
172.16.1.177    dbvlin217.dbvisit.co.nz dbvlin217
172.16.1.2    dbvlin-dg-vip.dbvisit.co.nz dbvlin-dg-vip
172.16.1.4    dbvlin-sb-vip.dbvisit.co.nz dbvlin-sb-vip

#/etc/sysconfig/network-scripts/ifcfg-eth0:1

DEVICE="eth0:1"
ONBOOT=no
BOOTPROTO=static
IPADDR=172.16.1.2
NETMASK=255.255.255.0
GATEWAY=172.16.1.1
TYPE=Ethernet
NM_CONTROLLED="yes"
IPV6INIT="no"

#/etc/sysconfig/network-scripts/ifcfg-eth0:2

DEVICE="eth0:2"
ONBOOT=no
BOOTPROTO=static
IPADDR=172.16.1.4
NETMASK=255.255.255.0
GATEWAY=172.16.1.1
TYPE=Ethernet
NM_CONTROLLED="yes"
IPV6INIT="no"

User Equivalence

Set user equivalence on dbvlin216 & dbvlin217 for users oracle and root

https://oracle-base.com/articles/linux/user-equivalence-configuration-on-linux#manual

Oracle Stack Configuration

Listeners

Static listener listener_vip will be reallocated together with VIP dbvlin-dg-vip during switchover/failover operation

#dbvlin216

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin216.dbvisit.co.nz)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER_VIP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin-dg-vip.dbvisit.co.nz)(PORT = 1522))
    )
  )

SID_LIST_LISTENER_VIP =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = SRCDB)
    )
  )

SID_LIST_LISTENER = 
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = SRCDB_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = SRCDB)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

#dbvlin217

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin217.dbvisit.co.nz)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )

LISTENER_VIP =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin-dg-vip.dbvisit.co.nz)(PORT = 1522))
 )
 )

SID_LIST_LISTENER_VIP =
 (SID_LIST =
 (SID_DESC =
 (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
 (SID_NAME = SRCDB)
 )
 )

SID_LIST_LISTENER = 
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = SRCDB_STBY_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
 (SID_NAME = SRCDB)
 )
 )

ADR_BASE_LISTENER = /u01/app/oracle

Tnsnames

tnsnames.ora file is common on all (source & target servers)

SRCDB_STBY =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin217.dbvisit.co.nz)(PORT = 1521))
    (CONNECT_DATA =
      (SID = SRCDB)
    )
  )

SRCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin216.dbvisit.co.nz)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRCDB)
    )
  )

SRCDB_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin-dg-vip.dbvisit.co.nz)(PORT = 1522))
    (CONNECT_DATA =
      (SID = SRCDB)
    )
  )

 

Redo & Archive Logs Location Unification

We need to have the same name and physical location of online redo and archive logs on both primary & standby site. Database are configured as follows

log_archive_dest_1 is local and pointed to FRA
log_archive_dest_2 is used by DG Broker to and pointed to standby database
log_archive_dest_3 is local and will be used by MINE process as the absolute path of archive log will be the same on primary and standby using following format: /u01/app/oracle/oradata/SRCDB/archivelogs/%t_%s_%r.dbf

SRCDB

SQL> sho parameter log_archive_dest_1

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1             string     location=USE_DB_RECOVERY_FILE_
                         DEST valid_for=(all_logfiles,a
                         ll_roles) db_unique_name=SRCDB

SQL> sho parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     service="srcdb_stby", SYNC AFF
                         IRM delay=0 optional compressi
                         on=disable max_failure=0 max_c
                         onnections=1 reopen=300 db_uni
                         que_name="srcdb_stby" net_time
                         out=30, valid_for=(online_logf
                         ile,all_roles)

SQL> sho parameter log_archive_dest_3

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3             string     LOCATION=/u01/app/oracle/orada
                         ta/SRCDB/archivelogs

SQL> col GROUP# for 99
SQL> col TYPE for a10
SQL> col MEMBER for a50
SQL> select GROUP#, TYPE, MEMBER from v$logfile;

GROUP# TYPE      MEMBER
------ ---------- --------------------------------------------------
     3 ONLINE      /u01/app/oracle/oradata/SRCDB/redo03.log
     2 ONLINE      /u01/app/oracle/oradata/SRCDB/redo02.log
     1 ONLINE      /u01/app/oracle/oradata/SRCDB/redo01.log
     4 STANDBY      /u01/app/oracle/oradata/SRCDB/standby_redo01.log
     5 STANDBY      /u01/app/oracle/oradata/SRCDB/standby_redo02.log
     6 STANDBY      /u01/app/oracle/oradata/SRCDB/standby_redo03.log

6 rows selected.

 

Dbvisit Replicate configuration

Use SRCDB_DG as the source database SID (the same SID will be used as the target in this configuration)

Use dbvlin-sb-vip.dbvisit.co.nz as the interface of MINE process.

Set REDO_READ_METHOD=ARCHONLY before starting processes.

Switchover

Custom shell script is used from switchover operation

readonly NODE1=dbvlin216
readonly NODE2=dbvlin217
readonly SCRIPT_PATH=/home/oracle/DG
readonly DB1=SRCDB
readonly DB2=SRCDB_STBY

PATH=/sbin:$PATH
LOCAL_NODE=$(hostname -s)

get_db_unique_name() {
  echo "set echo off feed off pages 0
        select db_unique_name from v\$database;" | sqlplus -s ${1}

}

PRIMARY_DB=$(get_db_unique_name "system/manager@SRCDB_DG")

case ${PRIMARY_DB} in
  ${DB1}) STANDBY_DB=${DB2};;
  ${DB2}) STANDBY_DB=${DB1};;
esac

case ${LOCAL_NODE} in
  ${NODE1}) REMOTE_NODE=${NODE2};;
  ${NODE2}) REMOTE_MODE=${NODE1};;
esac

echo "Primary node is ${LOCAL_NODE}, db_unique_name: ${PRIMARY_DB}"
echo "Standby node is ${REMOTE_NODE}, db_unique_name: ${STANDBY_DB}"

# stop replication on standby
ssh ${REMOTE_NODE} '/home/oracle/DG/start-console.sh "shutdown immediate all"'

# sync MINE plogs directory from old MINE to new MINE server
rsync -au ${REMOTE_NODE}:/home/oracle/DG/mine/ /home/oracle/DG/mine/

# sync APPLY plogs directory from old APPLY to new APPLY server
rsync -au rsync -au ${REMOTE_NODE}:/home/oracle/DG/apply/ /home/oracle/DG/apply/

# stop listener running on primary
lsnrctl stop listener_vip

# reallocate inferface used by listener_vip
sudo ifdown eth0:1
sudo ssh ${REMOTE_NODE} ifup eth0:1

# reallocate interface used by MINE
sudo ssh ${REMOTE_NODE} ifdown eth0:2
sudo ifup eth0:2

# switchover database role
echo "switchover to ${STANDBY_DB};" | dgmgrl sys/manager@${PRIMARY_DB}

# start listener_vip on new primary
ssh ${REMOTE_NODE} /home/oracle/start_lsnr_vip.sh

# start replication on new standby
${SCRIPT_PATH}/DG-run-dbvlin-sb-vip.dbvisit.co.nz.sh

Example output

oracle@dbvlin216[/home/oracle]: ./switchover.sh 
Initializing......done
DDC loaded from database (424 variables).
Dbvisit Replicate version 2.9.00_unreleased_4_609_g41788ae
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/DG/DG-MINE.ddc loaded.
Dbvisit Replicate MINE process shutting down.
Dbvisit Replicate APPLY process shutting down.
OK-0: Completed successfully.
100.plog                                                                                            100%  384     0.4KB/s   00:00    
101.plog                                                                                            100%  576KB 575.9KB/s   00:00    
[...]                                                                                 
98.plog.gz                                                                                          100%   84KB  84.3KB/s   00:00    
99.plog.gz                                                                                          100% 2244KB   2.2MB/s   00:00    

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-APR-2017 10:59:19

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbvlin-dg-vip.dbvisit.co.nz)(PORT=1522)))
The command completed successfully
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> Performing switchover NOW, please wait...
Operation requires a connection to instance "SRCDB" on database "srcdb_stby"
Connecting to instance "SRCDB"...
Connected as SYSDBA.
New primary database "srcdb_stby" is opening...
Operation requires start up of instance "SRCDB" on database "srcdb"
Starting instance "SRCDB"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "srcdb_stby"
DGMGRL> DGMGRL> The Oracle base has been set to /u01/app/oracle

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-APR-2017 11:00:08

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbvlin217/listener_vip/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.2)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbvlin-dg-vip.dbvisit.co.nz)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener_vip
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                06-APR-2017 11:00:09
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbvlin217/listener_vip/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.2)(PORT=1522)))
Services Summary...
Service "SRCDB" has 1 instance(s).
  Instance "SRCDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Initializing......done
DDC loaded from database (424 variables).
Dbvisit Replicate version 2.9.00_unreleased_4_609_g41788ae
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/DG/DG-APPLY.ddc loaded.
Starting process APPLY...started
Initializing......done
DDC loaded from database (424 variables).
Dbvisit Replicate version 2.9.00_unreleased_4_609_g41788ae
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/DG/DG-MINE.ddc loaded.
Starting process MINE...started


 

Have more questions? Submit a request

Comments