• Arun Kumar

Oracle Golden Gate Functions

Consider below source and target tables


Client Requirement


The client requirements are below:

  1. If HOURLY_PRICE is above 75 then EMP_LEVEL = Senior, if HOURLY_PRICE is above 50 then EMP_LEVEL= Junior else EMP_LEVEL = fresher

  2. Give a 10% bonus to all employees and set target BONUS = 10% of SALARY

  3. If the source DEPT_NAME is missing, set it default ‘IT’

  4. TRNX on target must contain the exact time at which transaction has been executed

  5. Combine the FIRST_NAME and LAST_NAME on target as FULL_NAME

  6. Convert target FULL_NAME into upper case


Assumptions: Map all other columns with matching names by default

Setup Replication With Functions


Create GG_FUN table on proddb

On Proddb:
==========
Conn fox/fox
CREATE TABLE gg_fun
(
 EMPID NUMBER PRIMARY KEY,
 FIRST_NAME Varchar2(10),
 LAST_NAME Varchar2(10),
 SALARY Number,
 HOURLY_PRICE Number,
 DEPT_NAME Varchar2(4),
 PHONE Number,
 COUNTRY Varchar2(20)
);
 

Create GG_FUN_TEST on devdb

On Devdb:
=========
Conn tom/tom
CREATE TABLE gg_fun_test
(
 EMP_ID Number PRIMARY KEY,
 FULL_NAME Varchar2(20),
 SALARY Number,
 BONUS Number,
 EMP_LEVEL Varchar2(20),
 DEPT_NAME Varchar2(4),
 PHONE Varchar2(10),
 TRNX Date,
 COUNTRY Varchar2(20)
);
 

Connect to database via Golden Gate and add table level supplemental logging

On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI> add trandata FOX.GG_FUN
Logging of supplemental redo data enabled for table FOX.GG_FUN.
TRANDATA for scheduling columns has been added on table 'FOX.GG_FUN'.

CREATE SOURCE DEFINITIONS FILE


First, we need to create a parameter file for DEFGEN utility. You can create it via GG prompt or manually via vi editor

On proddb:
==========
GGSCI> edit params defgen3
DEFSFILE ./dirdef/FoxGG_FUN.def
USERID ogg PASSWORD ogg
TABLE FOX.GG_FUN;

Exit the GG prompt and initiate defgen utility to generate definitions file

On proddb:
==========
cd $GG_HOME
./defgen paramfile ./dirprm/defgen3.prm

Copy the definitions file on target server under $GG_HOME/dirdef location


Create extract on source

Copy the definitions file on target server under $GG_HOME/dirdef location

Create data pump process

GGSCI> Add extract PFOXGND, EXTTRAILSOURCE ./dirdat/tn
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/rn, extract PFOXGND
GGSCI> edit param PFOXGND
EXTRACT PFOXGND
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rn
TABLE FOX.GG_FUN;

Let us create replicat process as per the client requirement on target

On Devdb:
==========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat DTOMGNR, integrated exttrail /u01/app/oracle/product/gg/dirdat/rn
GGSCI> edit param DTOMGNR
REPLICAT DTOMGNR
USERID ogg, PASSWORD ogg
SOURCEDEFS ./dirdef/FoxGG_FUN.def
MAP fox.gg_fun TARGET tom.gg_fun_test, &
COLMAP (EMP_ID=EMPID,
FULL_NAME=@STRUP(@STRCAT(FIRST_NAME,' ',LAST_NAME)),
SALARY=SALARY,
BONUS=@COMPUTE(0.1*SALARY),
EMP_LEVEL=@EVAL(HOURLY_PRICE > 75, 'SENIOR', HOURLY_RATE > 50, 'JUNIOR', 'FRESHER'),
Dept_name=@IF(@COLTEST(DEPT_NAME, NULL, MISSING),'IT',DEPT_NAME) ,
Phone=@NUMSTR(PHONE),
Trnx=@DATENOW(),
Country=country
);

Start the Extract, Pump and Replicat process

On proddb:
==========
GGSCI> start PFOXGNE
GGSCI> start PFOXGND

On devdb:
=========
GGSCI> start DTOMGNR

Let us test our replication

On proddb:
==========
INSERT INTO gg_fun VALUES (1001,'John', 'Doe', 2000, 70, 'HR', 987654321, 'USA');
INSERT INTO gg_fun VALUES (1002,'Daniel', 'David', 5000, 110, NULL, 4444444444, 'UK');
INSERT INTO gg_fun VALUES (1003,'Robin', 'Lee', 1000, 10, 'FIN', 123123212, 'CAN');
COMMIT;



Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is same on both source and target database. Create Sample Table Create Fox user on both pr

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Cr

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis