Setting Up Replication

The following task summary provides instructions on how to deploy Genesys Info Mart in the Business Continuity architecture.

In the description and in the following procedure, the term "replication source" is used to refer to the Info Mart database at the active site (the source of data replication) while the term "replication target" is used to refer to the Info Mart database at the standby site (the target of data replication).

The following three major processes are used in Oracle GoldenGate for data replication between the source and target databases:

• Extract
• DataPump
• Replicat

Use GoldenGate data-definitions files to configure each of these processes (refer to Oracle® GoldenGate Windows and UNIX Administrator's Guide for instructions). The following Task Summary provides Genesys-specific recommendations.

Important

The instructions for the replication setup and Disaster Recovery do not cover Genesys Info Mart tenant views. If it is important for your environment to preserve tenant views for the purposes of Disaster Recovery, contact Genesys for recommendations.

Deployment Prerequisites

The following instructions assume that the Genesys Info Mart deployment at the active site is operational. The active Genesys Info Mart server can continue to run until the step in which you copy the Info Mart database to the standby site.

As a prerequisite, you need to do the following:

• Deploy the standby Genesys Info Mart Server application at the standby site. To avoid the synchronization task for Application objects between the active and standby sites, use the same Application configuration object for the standby Genesys Info Mart Server application as already configured for the active Genesys Info Mart Server application. It is recommended to stop Genesys Info Mart Server at the active site for the period while the standby Genesys Info Mart Server is being deployed. Once the deployment is complete at the standup site, check that the Application object in the Configuration Layer references the host on which the active Genesys Info Mart Server application must be running and start the active Genesys Info Mart Server. You will change the host association to point to the standby Genesys Info Mart Server application as part of the Disaster Recovery procedure.
• Configure Database Access Points (DAPs) to extract IDB data after Disaster Recovery.
• Install the Genesys Info Mart Administration Console at the standby site.

With these considerations in mind, follow the deployment instructions in the Genesys Info Mart 8.1 Deployment Guide to deploy the Genesys Info Mart components at the standby site before you proceed with the replication configuration.

Configuring Database Replication for Info Mart Database

Task Summary

Set up the Genesys Info Mart database at the standby site.

Refer to the Genesys Info Mart 8.1 Deployment Guide for general instructions. Keep in mind that the standby Info Mart database must have a schema identical to the active database.

Deploy Oracle GoldenGate at both sites.

Oracle GoldenGate must be deployed at both sites, on the hosts where the Info Mart database instances reside, according to the Oracle documentation.

Configure the Extract, DataPump, and Replicat modules.

The following Info Mart-specific samples demonstrate how to exclude Info Mart tables from replication.
Substitute the following variables in the configuration file examples:

[+] See variables

• <Info_Mart> with the name of your Info Mart schema.
• <Extract_name> with the name of your Extract file.
• <DataPump_name> with the name of your DataPump file.
• <Replicat_name> with the name of your Replicat file.
• <GGate_schema> with the name of your GoldenGate schema.
• <Password> with the password of the GoldenGate database user.
• <Target_Host> with the machine name of the database host for the replication target.

For configuration instructions, refer to the Oracle GoldenGate documentation.

Extract

[+] See configuration

EXTRACT <Extract_name>

EXTTRAIL ./dirdat/em

USERID <GGate_schema>, PASSWORD <Password>

TRACE ./trace/<Extract_name>.trc

WILDCARDRESOLVE DYNAMIC

DBOPTIONS ALLOWUNUSEDCOLUMN

DDL INCLUDE OBJNAME <Info_Mart>.* &

EXCLUDE OBJNAME <Info_Mart>.TMP* &

EXCLUDE OBJNAME <Info_Mart>.DBMS_TAB* &

EXCLUDE OBJNAME <Info_Mart>.GIDB_G_* &

EXCLUDE OBJNAME <Info_Mart>.GIDB_GM* &

EXCLUDE OBJNAME <Info_Mart>.GIDB_GO* &

EXCLUDE OBJNAME <Info_Mart>.AGR_JUNK_NOTIFICATION &

EXCLUDE OBJNAME <Info_Mart>.AGR_LOCK &

EXCLUDE OBJNAME <Info_Mart>.AGR_NOTIFICATION &

EXCLUDE OBJNAME <Info_Mart>.G_CALL &

EXCLUDE OBJNAME <Info_Mart>.G_IR &

EXCLUDE OBJNAME <Info_Mart>.G_IS_LINK &

EXCLUDE OBJNAME <Info_Mart>.STG_ACW &

EXCLUDE OBJNAME <Info_Mart>.STG_ACTIVE_ACW &

EXCLUDE OBJNAME <Info_Mart>.STG_SM_RES_ST_REASON_FACT_MM &

EXCLUDE OBJNAME <Info_Mart>.STG_SM_RES_ST_REASON_FACT_V &

EXCLUDE OBJNAME <Info_Mart>.STG_SM_RES_STATE_FACT_MM &

EXCLUDE OBJNAME <Info_Mart>.STG_SM_RES_STATE_FACT_V &

EXCLUDE OBJNAME <Info_Mart>.STG_TRANSFORM_DISCARDS &

EXCLUDE OBJNAME <Info_Mart>.STG_UDH_* &

EXCLUDE OBJNAME <Info_Mart>.CTL_TRANSFORM_HISTORY &

EXCLUDE OBJNAME <Info_Mart>.CTL_EXTRACT_HISTORY &

EXCLUDE OBJNAME <Info_Mart>.CTL_PURGE_HISTORY &

EXCLUDE OBJNAME <Info_Mart>.CTL_SCHEMA_INFO

DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 20 MAXRETRIES 60 REPORT

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLEEXCLUDE <Info_Mart>.TMP*;

TABLEEXCLUDE <Info_Mart>.DBMS_TAB*;

TABLEEXCLUDE <Info_Mart>.GIDB_G_*;

TABLEEXCLUDE <Info_Mart>.GIDB_GM*;

TABLEEXCLUDE <Info_Mart>.GIDB_GO*;

TABLEEXCLUDE <Info_Mart>.AGR_JUNK_NOTIFICATION;

TABLEEXCLUDE <Info_Mart>.AGR_LOCK;

TABLEEXCLUDE <Info_Mart>.AGR_NOTIFICATION;

TABLEEXCLUDE <Info_Mart>.G_CALL;

TABLEEXCLUDE <Info_Mart>.G_IR;

TABLEEXCLUDE <Info_Mart>.G_IS_LINK;

TABLEEXCLUDE <Info_Mart>.STG_ACW;

TABLEEXCLUDE <Info_Mart>.STG_ACTIVE_ACW;

TABLEEXCLUDE <Info_Mart>.STG_IDB_FK_VIOLATION;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_MM;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_V;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_MM;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_V;

TABLEEXCLUDE <Info_Mart>.STG_TRANSFORM_DISCARDS;

TABLEEXCLUDE <Info_Mart>.STG_UDH_*;

TABLEEXCLUDE <Info_Mart>.CTL_TRANSFORM_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_EXTRACT_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_PURGE_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_SCHEMA_INFO;

SEQUENCE <Info_Mart>.*;

TABLE <Info_Mart>.*;

DataPump

[+] See configuration

EXTRACT <DataPump_name>

RMTHOST <Target_Host>, MGRPORT 7809, COMPRESS

RMTTRAIL ./dirdat/dp

TRACE ./trace/<DataPump_name>.trc

PASSTHRU

TABLEEXCLUDE <Info_Mart>.TMP*;

TABLEEXCLUDE <Info_Mart>.DBMS_TAB*;

TABLEEXCLUDE <Info_Mart>.GIDB_G_*;

TABLEEXCLUDE <Info_Mart>.GIDB_GM*;

TABLEEXCLUDE <Info_Mart>.GIDB_GO*;

TABLEEXCLUDE <Info_Mart>.AGR_NOTIFICATION;

TABLEEXCLUDE <Info_Mart>.G_CALL;

TABLEEXCLUDE <Info_Mart>.G_IR;

TABLEEXCLUDE <Info_Mart>.G_IS_LINK;

TABLEEXCLUDE <Info_Mart>.STG_ACW;

TABLEEXCLUDE <Info_Mart>.STG_ACTIVE_ACW;

TABLEEXCLUDE <Info_Mart>.STG_IDB_FK_VIOLATION;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_MM;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_V;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_MM;

TABLEEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_V;

TABLEEXCLUDE <Info_Mart>.STG_TRANSFORM_DISCARDS;

TABLEEXCLUDE <Info_Mart>.STG_UDH_*;

TABLEEXCLUDE <Info_Mart>.CTL_TRANSFORM_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_EXTRACT_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_PURGE_HISTORY;

TABLEEXCLUDE <Info_Mart>.CTL_SCHEMA_INFO;

SEQUENCE <Info_Mart>.*;

TABLE <Info_Mart>.*;

Replicat

[+] See configuration

REPLICAT <Replicat_name>

HANDLECOLLISIONS

USERID <GGate_schema>, PASSWORD <Password>

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/<Replicat_name>.dsc, purge

TRACE ./trace/<Replicat_name>.trc

STATOPTIONS RESETREPORTSTATS

DDL &

INCLUDE ALL

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)

MAPEXCLUDE <Info_Mart>.TMP*;

MAPEXCLUDE <Info_Mart>.DBMS_TAB*;

MAPEXCLUDE <Info_Mart>.GIDB_G_*;

MAPEXCLUDE <Info_Mart>.GIDB_GM*;

MAPEXCLUDE <Info_Mart>.GIDB_GO*;

MAPEXCLUDE <Info_Mart>.AGR_JUNK_NOTIFICATION;

MAPEXCLUDE <Info_Mart>.AGR_LOCK;

MAPEXCLUDE <Info_Mart>.AGR_NOTIFICATION;

MAPEXCLUDE <Info_Mart>.G_CALL;

MAPEXCLUDE <Info_Mart>.G_IR;

MAPEXCLUDE <Info_Mart>.G_IS_LINK;

MAPEXCLUDE <Info_Mart>.STG_ACW;

MAPEXCLUDE <Info_Mart>.STG_ACTIVE_ACW;

MAPEXCLUDE <Info_Mart>.STG_IDB_FK_VIOLATION;

MAPEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_MM;

MAPEXCLUDE <Info_Mart>.STG_SM_RES_ST_REASON_FACT_V;

MAPEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_MM;

MAPEXCLUDE <Info_Mart>.STG_SM_RES_STATE_FACT_V;

MAPEXCLUDE <Info_Mart>.STG_TRANSFORM_DISCARDS;

MAPEXCLUDE <Info_Mart>.STG_UDH_*;

MAPEXCLUDE <Info_Mart>.CTL_TRANSFORM_HISTORY;

MAPEXCLUDE <Info_Mart>.CTL_EXTRACT_HISTORY;

MAPEXCLUDE <Info_Mart>.CTL_PURGE_HISTORY;

MAPEXCLUDE <Info_Mart>.CTL_SCHEMA_INFO;

MAP <Info_Mart>.*, TARGET <Info_Mart>.*;

(For Genesys Info Mart release 8.1.0 or 8.1.1 only) Create an additional index on the CTL_EXTRACT_HWM table on both Info Mart database schemas.

If you are using Genesys Info Mart release 8.1.0 or 8.1.1, perform this step before you execute the ADD TRANDATA command in the following step.
To create the index, use the following commands:

[+] See commands

create unique index I_C_EXTR_HWM_ID ON CTL_EXTRACT_HWM (

TABLE_NAME ASC,

DATA_SOURCE_KEY ASC,

MAX_TS ASC

);

Configure the replication source according to the Oracle GoldenGate documentation.

Configure the Oracle RDBMS that hosts the active Info Mart database as a replication source.

In particular, enable supplemental logging at the table level for all tables that are to be replicated to the target database. Use the following command within the GoldenGate SCI, substituting <Info_Mart> with the name of your Info Mart schema:
ggsci> ADD TRANDATA <Info_Mart>.*

Note: It is essential that the ADD TRANDATA command captures all of the changes in the Info Mart database before replication is started. If you have to upgrade Genesys Info Mart in the time window between performing this step and starting replication, repeat this command after you upgrade Genesys Info Mart and before you start replication.

Before you start Oracle GoldenGate processing, copy the Info Mart database from the replication source to the target.

To do so, use the following steps as an example, substituting <Info_Mart> with the name of your Info Mart schema:

[+] See steps

1. Stop the Genesys Info Mart server.

For instructions, refer to the Genesys Info Mart 8.1 Deployment Guide.

2. Retrieve the current SCN number from the replication source by using the following commands:

>sqlplus SYSTEM as sysdba

SQL> select current_scn from v$database;

3. Create the exp-gim.par file:

LOGFILE=gim-exp.log

PARALLEL=2

SCHEMAS=<Info_Mart>

flashback_scn=<value of current_scn>

STATUS=100000

4. Execute the export script:

expdp system parfile=exp-gim.par

5. Start the Genesys Info Mart server at the active site.

For instructions, refer to the Genesys Info Mart 8.1 Deployment Guide.

6. Transfer the export file to the system that is the replication target.

7. Create the imp-gim.par file:

DUMPFILE=gim.dmp

LOGFILE=gim-imp.log

SCHEMAS=<Info_Mart>

STATUS=100000

TABLE_EXISTS_ACTION=REPLACE

8. Remove the sequence values from the target Info Mart database, to facilitate the import of the sequence values from the source Info Mart database. To do so, execute the following script at the target database using the credentials of the appropriate Info Mart database user:

SET SERVEROUTPUT ON;

BEGIN

FOR rec IN (

select 'drop sequence ' || SEQUENCE_NAME as V

from user_sequences

)

LOOP

dbms_output.put_line(TO_CHAR(SYSTIMESTAMP , 'mm.dd.yyyy hh24:mi:ss.ff3') || ' Running ' || rec.V || '...');

execute immediate rec.V;

END LOOP;

END;

/

9. Execute the import script:

impdp system parfile=imp-gim.par

10. Check the gim-imp.log file.

Ignore any error messages regarding the tables that already exist.

Re-create any missing indexes on the target database.

Note: Certain versions of Oracle may have specific requirements to the patch level and settings in order to enable proper creation of indexes. For example, Oracle v11.2.0.1.0 requires patch p8795792_112010_Generic.

Start Replicat on the target database.

Use the following commands, substituting the following variables:

[+] See variables

• <User_ID> with the name of the GoldenGate database user.
• <Password> with the password of the GoldenGate database user.
• <Replicat_name> with the name of your Replicat file.
• <Value of current_scn> with the current SCN number.

[+] See commands

ggsci> dblogin userid <User_ID> password <Password>

ggsci> ADD REPLICAT <Replicat_name>, EXTTRAIL ./dirdat/dp

ggsci> START REPLICAT <Replicat_name>, AFTERCSN <Value of current_scn>

Start Extract and DataPump on the replication source.

Use the following commands, substituting the following variables:

[+] See variables

• <User_ID> with the name of the GoldenGate database user.
• <Password> with the password of the GoldenGate database user.
• <Extract_name> with the name of your Extract file.
• <DataPump_name> with the name of your DataPump file.

[+] See commands

ggsci> dblogin userid <User_ID> password <Password>

ggsci> ADD EXTRACT <Extract_name>, TRANLOG, THREADS 1, BEGIN NOW

ggsci> ADD EXTTRAIL ./dirdat/em, EXTRACT <Extract_name>, MEGABYTES 2000

ggsci> ADD EXTRACT <DataPump_name>, EXTTRAILSOURCE ./dirdat/em

ggsci> ADD RMTTRAIL ./dirdat/DP, EXTRACT <DataPump_name>, MEGABYTES 2000

ggsci> START EXTRACT <Extract_name>

ggsci> START EXTRACT <DataPump_name>

Check the GoldenGate ggserr.log file for any reported issues and correct them, if necessary.

After completion of the above tasks, all Info Mart tables that are mapped for replication will be synchronized regularly between the replication source and target databases. For the tables that are excluded from replication, neither data nor database schema changes will be replicated.