Thinking Out Loud

February 23, 2012

My Bi-directional Replication Using GoldenGate Adventure, Ep5

Filed under: 11g,GoldenGate,oracle — mdinh @ 4:02 am

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>
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: