Thinking Out Loud

February 26, 2012

My Bi-directional Replication Using GoldenGate Adventure, Ep6

Filed under: 11g,GoldenGate,oracle — mdinh @ 4:22 pm

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
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: