Vishal desai’s Oracle Blog

January 10, 2014

Golden Gate conditional processing using UPDATEDELETES, TOKENS and FILTER

Filed under: Golden Gate, Replication — vishaldesai @ 4:01 pm

Client had two databases (source and target) and wanted to replicate data as follows:

All transactions generated by OLTP user needs to be replicated (as is) in near real time to target database. ARCHIVER user runs batch operations and deletes data from source database but on Target database it should convert ARCHIVER deletes into updates and there should be column indicator on target database to identify records that were deleted by ARCHIVER user on source database. Eventually these records will be deleted from Target database once its consumed by queue processor.

Test Case:

For demo purpose my Target database is same as Source database.

Source Database Target Database
APPSCHEMA – stores application tables APPSCHEMA – stores application tables
OLTP – web user  
ARCHIVER – user for archiving old data using batch operations  

Scripts to setup demo:

Database schema and table setup:
================================

DROP USER oltp cascade;
DROP USER archiver cascade;
DROP USER appschema cascade;
DROP TABLE appschema.test;
DROP TABLE appschema.test1;

CREATE USER oltp IDENTIFIED BY oltp DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER archiver IDENTIFIED BY archiver DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER appschema IDENTIFIED BY appschema DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CREATE session, RESOURCE, DBA TO oltp,archiver,appschema;
  
CREATE TABLE appschema.test
(
   id      NUMBER PRIMARY KEY,
   name    VARCHAR2(20),
   company VARCHAR2(20)
);

  
CREATE TABLE appschema.test1
(
   id          NUMBER PRIMARY KEY,
   name        VARCHAR2(20),
   company     VARCHAR2(20),
   deleted_row VARCHAR(20)
);

Source:
=======

--Add Extract
dblogin  userid ggadmin@DBTNS, password pass1234
ADD EXTRACT e_test, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/et, EXTRACT e_test

--Extract parameter file
edit params e_test

EXTRACT e_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
DBOPTIONS
USERID ggadmin, PASSWORD pass1234
EXTTRAIL ./dirdat/et
TABLE appschema.test,TOKEN(TK_DBUSER = @GETENV ("TRANSACTION" , "USERNAME"));

--Generate definition file
edit params defgen1

defsfile /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin@DBTNS, PASSWORD pass1234
table appschema.test;

defgen paramfile /opt/oracle/app/gghome/dirprm/defgen1.prm

Copy /opt/oracle/app/gghome/dirdef/test.def to target server.

Target:
=======

--Add Replicat
dblogin  userid ggadmin@DBTNS, password pass1234
ADD CHECKPOINTTABLE ggadmin.CHKPTAB
ADD REPLICAT r_test, EXTTRAIL ./dirdat/et CHECKPOINTTABLE ggadmin.CHKPTAB

--Replicat parameter file
edit params r_test

REPLICAT r_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
SOURCEDEFS /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin, PASSWORD pass1234
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
GETDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = " "), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"OLTP") > 0);
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
UPDATEDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = "ARCHIVER"), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"ARCHIVER") > 0);

Basically we are telling Golden Gate to replicate transactions as is when TOKEN value has OLTP stored in it and convert deletes into updates when TOKEN value has ARCHIVER stored in it.

Testing Transactions:

-- Populate Data and verify INSERTS are replicated to Target

SQL> conn oltp/oltp
Connected.
SQL> insert into appschema.test values (20,'VISHAL','ORA');

1 row created.

SQL> insert into appschema.test values (21,'ERIC','ORA');

1 row created.

SQL> insert into appschema.test values (22,'PHIL','ORA');

1 row created.

SQL> insert into appschema.test values (23,'GARY','ORA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from appschema.test;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as OLTP user

SQL> conn oltp/oltp
Connected.
SQL> delete from appschema.test where id=20;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test  order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as ARCHIVER user

SQL> conn archiver/archiver
Connected.
SQL> delete from appschema.test where id=22;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA                  ARCHIVER
        23 GARY                 ORA

That concludes the demo.

P.S. FILTER (@TOKEN("TK_DBUSER")="OLTP") did not work for some reason. Probably Token was storing blank padding’s. I will update this blog when I have time to verify that. 🙂

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: