My Bi-directional Replication Using GoldenGate Adventure, Ep6

Even though the configuration is bi-directional, the directive is write to ONLY one database at any given time.

Given the the requirements, it was feasible to use swingbench to test the replication. Screen shot is below.

The following tables were created. Database link ha02 created to second database for verification.

To accurately verify both data sets are identical (A MINUS B) UNION ALL (B MINUS A)

Since this is a control environment, I skipped (B MINUS A).

demo_app@HA01> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CUSTOMERS TABLE
INVENTORIES TABLE
LOGON TABLE
ORDERENTRY_METADATA TABLE
ORDERS TABLE
ORDER_ITEMS TABLE
PRODUCTS VIEW
PRODUCT_DESCRIPTIONS TABLE
PRODUCT_INFORMATION TABLE
PRODUCT_PRICES VIEW
WAREHOUSES TABLE
11 rows selected.
demo_app@HA01> select * from CUSTOMERS minus select * from CUSTOMERS@ha02;
no rows selected
demo_app@HA01> demo_app@HA01> select * from ORDERS minus select * from ORDERS@ha02;
no rows selected
demo_app@HA01> demo_app@HA01> select * from ORDER_ITEMS minus select * from ORDER_ITEMS@ha02;
no rows selected
demo_app@HA01> demo_app@HA01> select count(*) from ORDER_ITEMS;
COUNT(*)
----------
 13502809
demo_app@HA01> select count(*) from ORDERS;
COUNT(*)
----------
 4500000
demo_app@HA01> select count(*) from CUSTOMERS;
COUNT(*)
----------
 4000000
demo_app@HA01>

Here are some commands  I used to monitor the replication process.

info all
SEND replicat rha02, STATUS
STATS replicat rha02, totalsonly *, reportrate min
SEND extract xha01, SHOWTRANS
SEND extract xha01, STATUS
STATS extract xha01, totalsonly *, reportrate sec

Here are output:

GGSCI (goldengate02) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PHA02 00:00:00 00:00:06
EXTRACT RUNNING XHA02 00:00:00 00:00:09
REPLICAT RUNNING RHA02 00:44:05 00:00:00 <- Replicat Lag Time

GGSCI (goldengate02) 2> SEND replicat rha02, STATUS
Sending STATUS request to REPLICAT RHA02 ...
 Current status: Processing data
 Sequence #: 7
 RBA: 306804655
 510 records in current transaction

GGSCI (goldengate02) 3> !
SEND replicat rha02, STATUS
Sending STATUS request to REPLICAT RHA02 ...
 Current status: Processing data
 Sequence #: 7
 RBA: 319996218
 324 records in current transaction

GGSCI (goldengate02) 4> STATS replicat rha02, totalsonly *, reportrate min
Sending STATS request to REPLICAT RHA02 ...
Start of Statistics at 2012-02-24 13:51:39.
DDL replication statistics:
*** Total statistics since replicat started ***
 Operations 22.00
 Mapped operations 22.00
 Unmapped operations 0.00
 Other operations 0.00
 Excluded operations 0.00
 Errors 0.00
 Retried errors 0.00
 Discarded errors 0.00
 Ignored errors 0.00
Cumulative totals for specified table(s):
*** Total statistics since 2012-02-24 12:40:19 ***
 Total inserts/minute: 236945.76
 Total updates/minute: 0.00
 Total deletes/minute: 0.00
 Total discards/minute: 0.00
 Total operations/minute: 236945.76
*** Daily statistics since 2012-02-24 12:40:19 ***
 Total inserts/minute: 236945.76
 Total updates/minute: 0.00
 Total deletes/minute: 0.00
 Total discards/minute: 0.00
 Total operations/minute: 236945.76
*** Hourly statistics since 2012-02-24 13:00:00 ***
 Total inserts/minute: 210912.95
 Total updates/minute: 0.00
 Total deletes/minute: 0.00
 Total discards/minute: 0.00
 Total operations/minute: 210912.95
*** Latest statistics since 2012-02-24 12:40:19 ***
 Total inserts/minute: 236945.76
 Total updates/minute: 0.00
 Total deletes/minute: 0.00
 Total discards/minute: 0.00
 Total operations/minute: 236945.76
End of Statistics.

GGSCI (goldengate01) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PHA01 00:44:24 00:00:07
EXTRACT RUNNING XHA01 00:44:25 00:00:06 <- Extract Lag Time
REPLICAT RUNNING RHA01 00:00:00 00:00:09

GGSCI (goldengate01) 2> SEND extract xha01, SHOWTRANS
Sending SHOWTRANS request to EXTRACT XHA01 ...

Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 143, RBA 97627152
------------------------------------------------------------
XID: 9.13.2847
Items: 8
Extract: XHA01
Redo Thread: 1
Start Time: 2012-02-24:13:08:18
SCN: 0.2630096 (2630096)
Redo Seq: 143
Redo RBA: 97627152
Status: Running

GGSCI (goldengate01) 3> !
SEND extract xha01, SHOWTRANS
Sending SHOWTRANS request to EXTRACT XHA01 ...

Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 144, RBA 1247760
------------------------------------------------------------
XID: 15.16.1643
Items: 146
Extract: XHA01
Redo Thread: 1
Start Time: 2012-02-24:13:08:20
SCN: 0.2630659 (2630659)
Redo Seq: 144
Redo RBA: 1247760
Status: Running

------------------------------------------------------------
XID: 2.9.2845
Items: 48
Extract: XHA01
Redo Thread: 1
Start Time: 2012-02-24:13:08:20
SCN: 0.2630663 (2630663)
Redo Seq: 144
Redo RBA: 1247760
Status: Running

------------------------------------------------------------
XID: 12.33.1658
Items: 84
Extract: XHA01
Redo Thread: 1
Start Time: 2012-02-24:13:08:20
SCN: 0.2630707 (2630707)
Redo Seq: 144
Redo RBA: 1247760
Status: Running

------------------------------------------------------------
XID: 1.33.2812
Items: 131
Extract: XHA01
Redo Thread: 1
Start Time: 2012-02-24:13:08:20
SCN: 0.2630720 (2630720)
Redo Seq: 144
Redo RBA: 1247760
Status: Running

My Bi-directional Replication Using GoldenGate Adventure, Ep5

First, I made an amendment to by first post – My Bi-directional Replication Using GoldenGate Adventure, Ep1

I was trying to be creative by practicing the least privilege principle and it turned out to be a nightmare with unintended consequences.

My recommendation is to follow the manual.

One of many criteria for a well designed bi-directional replication is to avoid conflict; otherwise, resolve conflict.

The saying goes, “An ounce of prevention is worth a pound of cure.”

One alternate to avoid conflict is to use artificial key with sequence alternating between odd and even.

I have created a stored procedure that will automatically do this.

The prerequisite for using the stored procedure in bi-directional replication is the db_name must contain a numeric to represent odd and even database.

Here is the code:

CREATE OR REPLACE PACKAGE pkg_create IS
--
-- mdinh: 2012-Jan-19
--
-- Create ODD and EVEN sequences for bi-directional replication based on DBnn
--
 PROCEDURE seq (p_table VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_create
AS
PROCEDURE seq(p_table VARCHAR2)
IS
 l_start PLS_INTEGER := TO_NUMBER(REGEXP_SUBSTR(SYS_CONTEXT('USERENV','DB_NAME'), '\d+'));
BEGIN
 EXECUTE IMMEDIATE 'create sequence '||p_table||'_seq start with '||l_start||' increment by 2';
EXCEPTION WHEN OTHERS THEN RAISE;
END;
END;
/

Here is an example of how it is used:

A sequence is created by appending SEQ to the table name, the starting value is determined based on the numeric portion of db_name.

demo_app@HA01> start pkg_create.sql
Package created.

Package body created.
demo_app@HA01> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CUSTOMERS TABLE
INVENTORIES TABLE
LOGON TABLE
ORDERENTRY_METADATA TABLE
ORDERS TABLE
ORDER_ITEMS TABLE
PRODUCTS VIEW
PRODUCT_DESCRIPTIONS TABLE
PRODUCT_INFORMATION TABLE
PRODUCT_PRICES VIEW
WAREHOUSES TABLE
11 rows selected.
demo_app@HA01> exec pkg_create.seq('WAREHOUSES');
PL/SQL procedure successfully completed.
demo_app@HA01> select WAREHOUSES_seq.nextval from dual;
NEXTVAL
----------
 1
demo_app@HA01> /
NEXTVAL
----------
 3
demo_app@HA01> /
NEXTVAL
----------
 5
demo_app@HA01>
demo_app@xgoldengate02> start pkg_create.sql
Package created.

Package body created.
demo_app@xgoldengate02> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CUSTOMERS TABLE
INVENTORIES TABLE
LOGON TABLE
ORDERENTRY_METADATA TABLE
ORDERS TABLE
ORDER_ITEMS TABLE
PRODUCTS VIEW
PRODUCT_DESCRIPTIONS TABLE
PRODUCT_INFORMATION TABLE
PRODUCT_PRICES VIEW
WAREHOUSES TABLE
11 rows selected.
demo_app@xgoldengate02> exec pkg_create.seq('WAREHOUSES');
PL/SQL procedure successfully completed.
demo_app@xgoldengate02> select WAREHOUSES_seq.nextval from dual;
NEXTVAL
----------
 2
demo_app@xgoldengate02> /
NEXTVAL
----------
 4
demo_app@xgoldengate02> /
NEXTVAL
----------
 6
demo_app@xgoldengate02>

My Bi-directional Replication Using GoldenGate Adventure, Ep4

https://docs.google.com/spreadsheet/ccc?key=0AmlTdsDfc8ZmdEo4SUhUVjJrTHY0UXRDSHFaYjRQLWc

The above link contains all the configurations for GoldenGate.

The parameter files (*.prm) should reside at GG_HOME/dirprm

The macro file (macrolib.mac) should reside at GG_HOME/dirmac

Don’t forget to execute ADD TRANDATA or ADD SCHEMATRANDATA before starting the manager, extract, datapump extract, replicat.

Next step, create tables and test data.

Stay tuned and I will share with you the stored procedure used to create PK sequences to avoid collisions.

My Bi-directional Replication Using GoldenGate Adventure, Ep3

GG Server: goldengate01, goldengate02

DB Server: ha01 (goldengate01), ha02(goldengate02)

CONFIGURATION at goldengate01:

CREATE PRIMARY EXTRACT:

Extract starts with x and is named after the database instance.

Extract xha01 writes to trail file (xa) locally on the server.

— Primary Extract to local trail xa
ADD EXTRACT xha01, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs/dirdat/xa, EXTRACT xha01, MEGABYTES 500

CREATE DATAPUMP EXTRACT:

DataPump Extract starts with p and is named after the database instance.

DataPump Extract pha01 reads from Primary Extract trail file (xa) locally on the server.

DataPump Extract pha01 sends trail file (pa) to REMOTE SERVER (goldengate02).

— DataPump Extract reads from local trail xa; pump to remote trail pa
ADD EXTRACT pha01, EXTTRAILSOURCE /u01/app/ggs/dirdat/xa
ADD RMTTRAIL /u01/app/ggs/dirdat/pa, EXTRACT pha01, MEGABYTES 500

CREATE REPLICAT:

Replicat starts with r and is named after the database instance.

Replicat rha01 reads from trail (pb), received from REMOTE SERVER (goldengate02)

— Replicat reads from datapump trail pb from pha02
ADD REPLICAT rha01, EXTTRAIL /u01/app/ggs/dirdat/pb

CONFIGURATION at goldengate02:

— Primary Extract to local trail xb
ADD EXTRACT xha02, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs/dirdat/xb, EXTRACT xha02, MEGABYTES 500

— DataPump Extract reads from local trail xb; pump to remote trail pb
ADD EXTRACT pha02, EXTTRAILSOURCE /u01/app/ggs/dirdat/xb
ADD RMTTRAIL /u01/app/ggs/dirdat/pb, EXTRACT pha02, MEGABYTES 500

— Replicat reads from datapump trail pb from pha01
ADD REPLICAT rha02, EXTTRAIL /u01/app/ggs/dirdat/pa

CREATE obey script to CONFIGURE both GG Servers:

oracle@goldengate01:ha01:/u01/app/ggs
> cat config_ha01.obey
dblogin userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
allownested
versions
-- Primary Extract to local trail xa
ADD EXTRACT xha01, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs/dirdat/xa, EXTRACT xha01, MEGABYTES 500
-- DataPump Extract reads from local trail xa; pump to remote trail pa
ADD EXTRACT pha01, EXTTRAILSOURCE /u01/app/ggs/dirdat/xa
ADD RMTTRAIL /u01/app/ggs/dirdat/pa, EXTRACT pha01, MEGABYTES 500
-- Replicat reads from datapump trail pb from pha02
ADD REPLICAT rha01, EXTTRAIL /u01/app/ggs/dirdat/pb
oracle@goldengate02:ha02:/u01/app/ggs
> cat config_ha02.obey
dblogin userid gguser, pbssword AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
allownested
versions
-- Primary Extract to local trail xb
ADD EXTRACT xha02, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs/dirdat/xb, EXTRACT xha02, MEGABYTES 500
-- DataPump Extract reads from local trail xb; pump to remote trail pb
ADD EXTRACT pha02, EXTTRAILSOURCE /u01/app/ggs/dirdat/xb
ADD RMTTRAIL /u01/app/ggs/dirdat/pb, EXTRACT pha02, MEGABYTES 500
-- Replicat reads from datapump trail pb from pha01
ADD REPLICAT rha02, EXTTRAIL /u01/app/ggs/dirdat/pa

EXECUTE obey script at both GG Servers:

Here is an example for the execute at goldengate01

oracle@goldengate01:ha01:/u01/app/ggs
> ggsci < config_ha01.obey
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Solaris, x64, 64bit (optimized), Oracle 11g on Oct 4 2011 23:54:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (goldengate01) 1> Successfully logged into database.
GGSCI (goldengate01) 2> Nested OBEY scripts allowed.
GGSCI (goldengate01) 3> Operating System:
SunOS
Version Generic_141445-09, Release 5.10
Node: goldengate01
Machine: i86pc
Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
GGSCI (goldengate01) 4>
GGSCI (goldengate01) 5> EXTRACT added.
GGSCI (goldengate01) 6> EXTTRAIL added.
GGSCI (goldengate01) 7>
GGSCI (goldengate01) 8> EXTRACT added.
GGSCI (goldengate01) 9> RMTTRAIL added.
GGSCI (goldengate01) 10>
GGSCI (goldengate01) 11> REPLICAT added.
GGSCI (goldengate01) 12>
oracle@goldengate01:ha01:/u01/app/ggs

Create checkpoint table at both GG Server:

Here’s the obey script

> cat add_checkpointtable.obey
dblogin userid gguser, password AACAAAAAAAAAAAJAXHHGIEDCVGTJXDDEPHZEFEMDPEEGEJMH, encryptkey key1
add checkpointtable
info checkpointtable
exit