Configuring Disaster Recovery Using DNS Failover and Oracle GoldenGate

This section describes the configuration of a Disaster Recovery / Business Continuity architecture, as described in Disaster Recovery Architecture. The configuration is based on the Oracle GoldenGate software.

Operation

System Startup Procedure and Normal Operating Mode

1. Start the replication process. [+] Show steps Tip For reference, use the Oracle® GoldenGate Windows and UNIX Administrator’s Guide 11g Release 1 (11.1.1) E17341-01 (Ref 1.) On the MAIN and SECONDARY systems, run the script that removes INSERT, UPDATE, and DELETE permissions to CFG_DB and MS_DB users. Use the START MANAGER command to start manager processes at both sites. At the SECONDARY site, using GGSCI, start REPLICATs in preparation to receive changes from the Configuration and Log Databases on the live MAIN system. START REPLICAT CSP START REPLICAT MSP At the MAIN site, using GGSCI, start REPLICAT MSS in preparation to receive changes from the log Database on the live SECONDERY system. START REPLICAT MSS On the MAIN site system, alter the primary Extract to begin capturing data based on the current timestamp. Otherwise, Extract will spend unnecessary time looking for operations that date back to the time that the group was created using the ADD EXTRACT command. ALTER EXTRACT CSP, BEGIN NOW ALTER EXTRACT MSP, BEGIN NOW On the SECONDARY site system, alter the secondary Extract to begin capturing data based on the current timestamp. Otherwise, Extract will spend unnecessary time looking for operations that date back to the time that the group was created with the ADD EXTRACT command. ALTER EXTRACT MSS, BEGIN NOW On the MAIN system, start the primary Extracts so they are ready to capture transactional changes. START EXTRACT CSP START EXTRACT MSP On the SECONDARY system, start the secondary Extract so it is ready to capture transactional changes at the secondary Log Database. START EXTRACT MSS On the MAIN system, do the following: Run the script that grants INSERT, UPDATE, and DELETE permissions to CFG_DB and MS_DB users. Run the script that enables triggers and cascade delete constraints. On the SECONDARY system, do the following: Run the script that grants INSERT, UPDATE, and DELETE permissions to MS_DB users. Run the script that enables triggers and cascade delete constraints. At this point, the database system is ready for normal operation.

2. Start the system. [+] Show steps Run the scripts that switchover cfgmaster host name IP resolution to a MAIN live system. Launch the MAIN live Master Configuration Server primary/backup pair at Site 1. Launch the MAIN live Master Solution Control Server to control the main Master Configuration Server pair at Site 1. Launch the MAIN Message server at Site 1 to support communications for Solution Control Servers controlling site components. Launch Solution Control Server at Sites 1 and 2. Using Solution Control Server, start the Configuration Server Proxy pair at Sites 1 and 2. Using Solution Control Server, start the Framework site components.

Disaster Recovery Switchover

At this point, all system components residing at the MAIN site on Host 3 are lost and not running:

• MAIN live Master Configuration Server primary/backup pair
• MAIN live Master Solution Control Server
• MAIN Message server at Site 1
• Oracle database
• Oracle GoldenGate

Operations on other sites can be continued non-stop in limited mode without a configuration change using Configuration Server Proxies running in Read-Only mode until the SECONDARY Master Configuration Server is brought on-line.

Perform the following steps to move activity to the SECONDARY live Master Configuration Server primary/backup pair.

1. On the SECONDARY live standby system, using GGSCI, issue the LAG REPLICAT command until it returns At EOF (end of file) to confirm that REPLICAT applied all of the data from the trail to the database. LAG REPLICAT CSP LAG REPLICAT MSP

2. Stop the REPLICAT processes. STOP REPLICAT CSP STOP REPLICAT MSP

3. On the SECONDARY system, run the script that grants INSERT, UPDATE, and DELETE permissions to the CFG_DB and MS_DB users.

4. Run the script that enables triggers and cascade delete constraints.

5. Launch the SECONDARY live Master Configuration Server primary/backup pair at Site 2.

6. Launch the SECONDARY live Master Solution Control Server to control the MAIN Master Configuration server pair at Site 2.

7. Launch the SECONDARY Message Server at Site 2 to support communication for Solution Control Servers controlling site components.

8. Run the dnscmd script that switches over cfgmaster host name IP resolution to a MAIN live system.

9. On the host running Configuration Server Proxies, run the switch over script to flush the DNS cache.

Communication Server Proxies reconnect to the SECONDARY live Master Configuration Server primary/backup pair and resume normal operation.

Warning

Do not start the data EXTRACTOR on the SECONDARY system. The user transactions must accumulate in the SECONDARY system database until the MAIN system is to be restored. Use the Secondary CSS replication group for database replication from SECONDARY to MAIN system before switching back to the MAIN system.

The diagram below shows the system in completed failover mode.

Multi-Site Disaster Recovery Architecture

Configuration Example

Configuration for Oracle GoldenGate Replication Processes

1. At the MAIN and SECONDARY Oracle databases, create a user CFG_DB for the Configuration Server database, and user MS_DB for the Log Message Server database.

2. Using the initialization scripts in the Installation Package, create the database objects for the Configuration and Log Message Server Databases.

3. Use Oracle® GoldenGate Oracle Installation and Setup Guide11g Release 1 (11.1.1) E17799-01 (Ref 1.) and the examples of Parameter files below to configure the EXTRACT and REPLICAT processes. [+] Show details Process Table (EXTRACT) / Map (REPLICAT) Parameters EXTRACT CSP SEQUENCE CFG_DB.*; TABLE CFG_DB.*; TABLEEXCLUDE CFG_DB.cfg_refresh; EXTRACT CSS EXTRACT MSP SEQUENCE MS_DB_1.*; TABLE MS_DB_1.*; TABLEEXCLUDE MS_DB_1.G_LOG_SCHEMA_INFO; EXTRACT MSS SEQUENCE MS_DB_2.*; TABLE MS_DB_2.*; TABLEEXCLUDE MS_DB_2.G_LOG_SCHEMA_INFO; REPLICAT CSS MAP CFG_DB.*,TARGET CFG_DB.*; MAPEXCLUDE CFG_DB.cfg_refresh; REPLICAT CSP REPLICAT MSP MAP MS_DB_1.*,TARGET MS_DB_1.*; MAPEXCLUDE MS_DB_1.G_LOG_SCHEMA_INFO; REPLICAT MSS MAP MS_DB_2.*,TARGET MS_DB_2.*; MAPEXCLUDE MS_DB_2.G_LOG_SCHEMA_INFO;

4. Register Oracle GoldenGate EXTRACT and REPLICAT using GGSCI. [+] Show steps On the MAIN live system: dblogin userid gg_user, password gg_password register extract CSP, LOGRETENTION register extract MSP, LOGRETENTION On the SECONDARY live standby system: dblogin userid gg_user, password gg_password register extract CSS, LOGRETENTION register extract MSS, LOGRETENTION

Extract Group CSP at Primary Site Configuration Example

1. At MAIN system, start GGSCI.

2. Use the ADD EXTRACT command to create an Extract group CSP.      ADD EXTRACT CSP , TRANLOG, BEGIN NOW Use TRANLOG as the data source option.

3. Use the ADD RMTTRAIL command to specify a remote trail to be created on the target system.      ADD RMTTRAIL ./CS, EXTRACT CSP Use the EXTRACT argument to link this trail to the Extract group.

4. Use the EDIT PARAMS command to create a parameter file for the Extract group. Include the following parameters plus any others that apply to your database environment.      EDIT PARAMS CSP

CSP EXTRACT Parameters File Example: [+] Show file EXTRACT CSP RMTHOST <Secondary host name>, MGRPORT 7809 RMTTRAIL ./dirdat/CP USERID gg_user PASSWORD gg_password TRACE ./trace/cfg_db.trc --Only use if DDL is configured WILDCARDRESOLVE DYNAMIC DDL INCLUDE MAPPED OBJNAME cfg_db.* DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 20 MAXRETRIES 60 REPORT -- TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 1024000 -- TRANLOGOPTIONS DBLOGREADERBUFSIZE 1024000 STATOPTIONS RESETREPORTSTATS REPORT AT 00:01 REPORTROLLOVER AT 00:01 REPORTCOUNT EVERY 60 SECONDS, RATE -- SEQUENCE cfg_db.*; TABLE cfg_db.*; TABLEEXCLUDE cfg_db.cfg_refresh;

REPLICAT Group CSP at Secondary Site Configuration Example

1. At the SECONDARY system, start GGSCI.

2. Use the ADD REPLICAT command to create a Replicat group CSP.      ADD REPLICAT CSP, EXTTRAIL CSP, BEGIN NOW Use the EXTTRAIL argument to link the Replicat group to the remote trail.

3. Use the EDIT PARAMS command to create a parameter file for the Replicat group. Include the following parameters plus any others that apply to your database environment:      EDIT PARAMS CSP

CSP REPLICAT Parameters File Example: [+] Show file MACRO #exception_handler BEGIN , TARGET ggate.exceptions , COLMAP ( rep_name = "rep" , table_name = @GETENV ("GGHEADER", "TABLENAME") , errno = @GETENV ("LASTERR", "DBERRNUM") , dberrmsg = @GETENV ("LASTERR", "DBERRMSG") , optype = @GETENV ("LASTERR", "OPTYPE") , errtype = @GETENV ("LASTERR", "ERRTYPE") , logrba = @GETENV ("GGHEADER", "LOGRBA") , logposition = @GETENV ("GGHEADER", "LOGPOSITION") , committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")) , INSERTALLRECORDS , EXCEPTIONSONLY; END; -- This ends the macro REPLICAT CSP HANDLECOLLISIONS --END RUNTIME USERID gg_user, PASSWORD gg_password ASSUMETARGETDEFS DISCARDFILE ./dirrpt/CP.dsc, purge TRACE ./trace/CSP.trc -- INCLUDE ALL -- & -- STATOPTIONS RESETREPORTSTATS DDL INCLUDE ALL --INCLUDE MAPPED -- & -- DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST DBOPTIONS DEFERREFCONST REPORT AT 00:01 REPORTROLLOVER AT 00:01 REPORTCOUNT EVERY 60 SECONDS, RATE --- DDLOPTIONS REPORT DDLERROR DEFAULT IGNORE REPERROR (DEFAULT, EXCEPTION) REPERROR (DEFAULT2, ABEND) REPERROR (-1, EXCEPTION) MAP CFG_DB.*,TARGET CFG_DB.*; MAPEXCLUDE CFG_DB.cfg_refresh; --MAP CFG_DB.* #exception_handler();

Configuration of Genesys Components

1. Start the replication process (P.3.3.1.1).

2. Run the dnscmd script that switches over cfgmaster host name IP resolution to a MAIN live system.

3. Install the MAIN live Master Configuration Server primary/backup pair at Site 1.

4. Install the SECONDARY dormant Configuration Server primary/backup pair at Site 2.

5. Launch the MAIN live Master Configuration Server primary/backup pair at Site 1.

6. Using Genesys Administrator connected to the Primary Master Configuration Server at Site 1, configure Master Solution Control Server, Message Server for distributed SCS, and Backup Master Configuration Server.

7. Install Master Solution Control Server, Message Server for distributed SCS, and Master Backup Configuration Server on Site 1.

8. Copy Master Solution Control Server, Message Server for distributed SCS, and Master Backup Configuration Server working directories to Site 2.

9. Launch Master Solution Control Server and Message Server for distributed SCS.

10. Using Genesys Administrator connected to the Primary Master Configuration Server at Site 1, configure and install Configuration Server Proxies, Solution Control Servers, and Message Servers for network logging for Sites 1 and 2.

11. Start Configuration Server Proxies at Sites 1 and 2.

12. Start Solution Control Servers at Sites 1 and 2.

13. Start Message Servers for network logging at Sites 1 and 2.

14. Install Framework Components at Site 1 using the Configuration Server Proxy host and port at Site 1.

15. Install Framework Components at Site 2 using the Configuration Server Proxy host and port at Site 2.

File and Script Examples

EXTRACT Parameters File

[+] Show file

EXTRACT <extract_name>
RMTHOST <target databse host name>, MGRPORT 7809
RMTTRAIL ./dirdat/<rmttrail_name>
USERID <golden_gate_user> PASSWORD <golden_gate_password>
TRACE ./trace/<oracle_user_name>.trc
--Only use if DDL is configured
WILDCARDRESOLVE DYNAMIC
DDL INCLUDE MAPPED OBJNAME <oracle_user_name>.*
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 20 MAXRETRIES 60 REPORT
-- TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 1024000
-- TRANLOGOPTIONS  DBLOGREADERBUFSIZE 1024000

STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 60 SECONDS, RATE
--
SEQUENCE <oracle_user_name>.*;
TABLE <oracle_user_name>.*;
TABLEEXCLUDE <exclude_filter>;

REPLICAT Parameters File

[+] Show file

MACRO #exception_handler
BEGIN
, TARGET ggate.exceptions
, COLMAP ( rep_name = "rep"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- This ends the macro
REPLICAT <replicat_name>
HANDLECOLLISIONS
--END RUNTIME
USERID <golden_gate_user>, PASSWORD <golden_gate_password>
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/<discard_file_name>.dsc, purge
TRACE ./trace/<traice_file_name>.trc
-- INCLUDE ALL -- &
-- STATOPTIONS RESETREPORTSTATS
DDL INCLUDE  ALL
--INCLUDE MAPPED  --  &

-- DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DBOPTIONS  DEFERREFCONST
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 60 SECONDS, RATE
---
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE 
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
MAP <oracle_user_name>.*,TARGET <oracle_user_name>.*;
MAPEXCLUDE <exclude_filter>;
--MAP CFG_DB.* #exception_handler();

dnscmd Scripts

[+] Show scripts

Switch to SECONDARY Master Server

rem DNSCMD DELETE command  
dnscmd 135.17.36.102 /RecordDelete mst.lab cfgmaster A /f
rem DNSCMD ADD command
dnscmd 135.17.36.102 /RecordAdd mst.lab cfgmaster A 135.17.36.140

Switch to MAIN Master Server

 
rem DNSCMD DELETE command  
dnscmd 135.17.36.102 /RecordDelete mst.lab cfgmaster A /f
rem DNSCMD ADD command
dnscmd 135.17.36.102 /RecordAdd mst.lab cfgmaster A 135.17.36.139

Switch over Script

[+] Show script

ipconfig /flushdns
ping cfgmaster.mst.lab