Oracle GoldenGate Data Transformation

Oracle GoldenGate Replication supports data transformation of data as it is read from the trail file and before it is put inside the database. This data mapping and manipulation is enabled by using the MAPPING parameter options.

Transformation of the data can be done in the EXTRACT or the REPLICAT process. Here we will see how configure the processes to perform a simple transformation on the REPLICAT side.

Oracle GoldenGate Transformations

Environment Details

Environment SOURCE TARGET
OGG Version 11.2.1.0.6 11.2.1.0.6
Oracle DB Version 11.2.0.3 11.2.0.3
Schema SCOTT SCOTT

Prepare SOURCE and TARGET Database
Create the tables below on the SOURCE and TARGET.

On SOURCE
SQL> CREATE TABLE SCOTT.CUSTOMER_DETAIL
(OWNER_ID VARCHAR2(20) NOT NULL,
CUST_GROUP_ID NUMBER NOT NULL,
CUST_GROUP_DETAIL VARCHAR2(30),
DELETED_FLAG VARCHAR2(1) DEFAULT 'N' NOT NULL,
MODIFIED TIMESTAMP(6) DEFAULT systimestamp NOT NULL);

Table created. 

On TARGET
SQL> CREATE TABLE SCOTT.CUST_DETAIL
(GROUP_CUST_ID NUMBER NOT NULL,
POS_GROUP_CUST_ID VARCHAR2(20) NOT NULL,
GROUP_TYPE_ID NUMBER,
DESCRIPTION VARCHAR2(40),
DEFAULT_LOCALE VARCHAR2(10) DEFAULT 'en-US' NOT NULL,
MODIFIED_DT TIMESTAMP(6) DEFAULT SYSDATE NOT NULL,
OWNER_BUS VARCHAR2(10) NOT NULL,
MODIFIED_USER VARCHAR2(10) NOT NULL);

Table created.

SQL> CREATE SEQUENCE SCOTT.GROUP_CUST_ID_SEQ START WITH 1 INCREMENT BY 2 CACHE 20 NOCYCLE;

Sequence created

SQL> SELECT SCOTT.GROUP_CUST_ID_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
1

Prepare the Transformation
The MAPPING below will be used in our Replicat process. This is used in the EXTRACT or the REPLICAT process parameter file when configuring the EXTRACT or REPLICAT process.

SCOTT.CUSTOMER_DETAIL SCOTT.CUST_DETAIL
OWNER_ID => OWNER_BUS
CUST_GROUP_ID => GROUP_CUST_ID
CUST_GROUP_DETAIL => DESCRIPTION
DELETED_FLAG  => (NO COLUMN MAPPING) DO NOT REPLICAT IF DELETE_FLAG=Y
MODIFIED   =>  MODIFIED_DT
=> POS_GROUP_CUST_ID (POPULATED WITH SEQ)
=> GROUP_TYPE_ID (ALWAYS ONE '1')
=> MODIFIED_USER (ALWAYS ZERO 'A')
=> DEFAULT_LOCALE (ALWAYS 'US')

Enable Supplemental Logging
Oracle GoldenGate requires that you enable supplemental logging at the database level and all the tables that are part of replication. Verify and add the supplemental logging if it is not already enabled.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES

Add supplementallogging either from GGSCI or SQLPLUS.

From GGSCI
GGSCI (Production) 1> dblogin userid ggs, password oracle
Successfully logged into database.

GGSCI (Production) 2> ADD TRANDATA SCOTT.CUSTOMER_DETAIL , COLS (OWNER_ID, CUST_GROUP_ID, CUST_GROUP_DETAIL, DELETED_FLAG, MODIFIED)
Logging of supplemental redo data enabled for table SCOTT.CUSTOMER_DETAIL.

From SQL*PLUS
sqlplusL > alter table scott.customer_detail add supplemental log data (all) columns;

Table altered

Create Oracle GoldenGate Processes

MANAGER Process
Create the MANAGER process on both SOURCE and TARGET.

GGSCI > edit params mgr
PORT 7809
(Save and Exit the file)

GGSCI (Production) 3> info mgr
Manager is DOWN!

GGSCI (Production) 4> start mgr
Manager started.

GGSCI (Production) 5> info mgr
Manager is running (IP port Production.7809).

EXTRACT on SOURCE
Create the EXTRACT Process on the SOURCE. Here for this demo, the EXTRACT will write directly to the remote server.

GGSCI> edit params ecust01
Extract ecust01
UserID gguser, Password oracle
RmtHost 192.168.1.30, MgrPort 7809
RmtTrail ./dirdat/et
GetUpdateBefores
Table SCOTT.CUSTOMER_DETAIL;

(Save and Exit the file)

GGSCI (Production) 4> Add Extract ecust01, TranLog, Begin Now
EXTRACT added.

GGSCI (Production) 5> Add rmtTrail ./dirdat/et, Extract ecust01, Megabytes 100
RMTTRAIL added.

GGSCI (Production) 6> info ecust01
EXTRACT ECUST01        Initialized 2014-04-26 08:23 Status STOPPED
Checkpoint Lag         00:00:00 (updated 00:00:20 ago)
Log Read Checkpoint    Oracle Redo Logs
2014-04-26 08:23:05 Seqno 0, RBA 0
SCN 0.0 (0)

GGSCI (Production) 7> start ecust01
Sending START request to MANAGER ...
EXTRACT ECUST01 starting

GGSCI (Production) 8>   info ecust01
EXTRACT     ECUST01      Last Started 2014-04-26 08:23 Status RUNNING
Checkpoint Lag           00:00:00 (updated 00:00:35 ago)
Log Read Checkpoint      Oracle Redo Logs
2014-04-26 08:23:05 Seqno 88, RBA 6119952
SCN 0.0 (0)

GGSCI (Production) 9> info all
Program  Status   Group    Lag at Chkpt Time Since Chkpt
MANAGER  RUNNING
EXTRACT  RUNNING  ECUST01  00:00:38   00:00:04

Create DEFGEN File
Since SOURCE and TARGET table definitions are different we must create a SOURCE table definition file and copy it to TARGET server. In our Demo we are mapping the columns on the TARGET side.

GGSCI> edit params defgen
defsfile ./dirdef/customer_detail.def
userid ggs, password oracle
TABLE SCOTT.CUSTOMER_DETAIL;

-- (Save and exit the file)

$ cd $GGS_HOME
$ ./defgen paramfile ./dirprm/defgen.prm reportfile ./dirrpt/defgen.rpt
$ view ./dirrpt/defgen.rpt

Verify the report and make sure there are no errors. On successful run of the defgen utility you will see the following line in the report file at the end.

Definitions generated for 1 table in ./dirdef/customer_detail.def

Goto ./dirdef and open the customer_detail.def file, this contains the table definition for customer_detail.def

$ view ./dirdef/customer_detail.def

Copy this file to TARGET server and use it in the replicat parameter file as SOURCEdefs for mapping dissimilar columns and data types.

$ scp $GGS_HOME/dirdef/customer_group.def ggs@192.168.1.30:/u01/app/ggs/dirdef/

REPLICAT Process on Target
Create the Replicat process on the TARGET server.

GGSCI> EDIT PARAMS RCUST01
Replicat RCUST01
UserID gguser, Password oracle_4U
DiscardFile ./dirrpt/RCUST01.dsc, Purge
SOURCEDEFS ./dirdef/customer_detail.def
DBOPTIONS SUPPRESSTRIGGERS
Map SCOTT.CUSTOMER_DETAIL, TARGET SCOTT.CUST_DETAIL,
KEYCOLS(GROUP_CUST_ID), WHERE (DELETED_FLAG = "N"), &
SQLEXEC (ID LOOKUP_SEQ, &
QUERY " SELECT SCOTT.GROUP_CUST_ID_SEQ.NEXTVAL FROM DUAL ", NOPARAMS), &
COLMAP (OWNER_BUS = OWNER_ID, &
GROUP_CUST_ID = CUST_GROUP_ID , &
DESCRIPTION = CUST_GROUP_DETAIL, &
MODIFIED_DT = MODIFIED, &
POS_GROUP_CUST_ID = @GETVAL(LOOKUP_SEQ.EAST.GROUP_CUST_ID_SEQ.nextval), &
GROUP_TYPE_ID = 1, &
MODIFIED_USER = “A”, &
DEFAULT_LOCALE = "US");

GGSCI (Production) 9> Add Replicat RCUST01, ExtTrail ./dirdat/et
REPLICAT added.

GGSCI (Production) 13> start rcust01
Sending START request to MANAGER ...
REPLICAT RCUST01 starting

Generate Transactions and Verify Replication Process

On SOURCE
SQL> insert into scott.customer_detail values (1, 1, 'abc', 'N', sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> insert into scott.customer_detail values (2, 2, 'xyz', 'N', sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> update scott.customer_detail set cust_group_detail='pqrs' where owner_id=1;
1 row updated.

SQL> commit;
Commit complete.

SQL> update scott.customer_detail set cust_group_detail='uvw' where owner_id=2;
1 row updated.

SQL> commit;
Commit complete.

SQL> select count(*) from scott.customer_detail;
COUNT(*)
----------
2

Then from GGSCI find the statistics for the rows processed by the process.

GGSCI (Production) 1> info ecust01
EXTRACT ECUST01 Last Started 2014-04-26 09:00 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2014-04-26 09:26:07 Seqno 88, RBA 7762944
SCN 0.2217127 (2217127)

GGSCI (Production) 2> stats ecust01, total
Sending STATS request to EXTRACT ECUST01 ...
Start of Statistics at 2014-04-26 09:26:19.
Output to ./dirdat/et:
Extracting from SCOTT.CUSTOMER_DETAIL to SCOTT.CUSTOMER_DETAIL:
*** Total statistics since 2014-04-26 09:00:25 ***
Total inserts        4.00
Total updates      4.00
Total befores     4.00
Total deletes        0.00
Total discards     0.00
Total operations    12.00

End of Statistics.

On TARGET
SQL> select count(*) from scott.cust_detail;
COUNT(*)
----------
2

GGSCI (Production) 1> info rcust01
REPLICAT RCUST01 Last Started 2014-04-26 09:24 Status RUNNING
Checkpoint Lag      00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File ./dirdat/et000001
2014-04-26 09:26:03.096336 RBA 3649

GGSCI (Production) 32> stats rcust01, total
Sending STATS request to REPLICAT RCUST01 ...
Start of Statistics at 2014-04-26 09:28:50.
Replicating from SCOTT.CUSTOMER_DETAIL to SCOTT.CUST_DETAIL:
*** Total statistics since 2014-04-26 09:24:06 ***
Total inserts         2.00
Total updates         2.00
Total deletes         0.00
Total discards       0.00
Total operations      4.00

End of Statistics.

All done. Enjoy!

Advanced Replication Series

Configure GoldenGate DDL Replication
GoldenGate Integrated Extract 12c
Upgrade Classic Extract to Integrated Capture
Oracle GoldenGate 11gr2 Upgrade

Leave a Reply

Your email address will not be published.