Oracle Golden Gate is a heterogeneous replication tool supporting a number of database technologies. So if your database is not supported (eg. Netezza, Greenplum) you have the option of using the flat file adapter plugin.
Lets go through an example of setting this up. Files can be downloaded from OTN or edelivery.oracle.com.
Here’s a high level view of what it all looks like in real life but we’ll simplify it and just run on single server.
Firstly install Golden Gate on the source database server. I won’t go into the install details as its pretty straight forward with the 12c version using the OUI. After the installation of GG you then need to install the plugin by unzipping the download then untarring the file into the GG Home of the installation.
|
1 2 3 |
unzip V22258-01.zip tar xvf ggs_FileWriter_Linux_x64_v11_1_1_0_0_007.tar -C $GGHOME |
Now lets prepare our database. We need to do a few things so Golden Gate exraction can work. Firstly add supplemental logging:
|
1 2 3 4 5 6 7 8 9 10 |
SQL> alter database add supplemental log data (primary key) columns; SQL> alter system switch logfile; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> ALTER DATABASE FORCE LOGGING; Database altered. |
Create GG User and Role.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> create user ggowner identified by ggowner; User created. SQL> grant connect,resource,dba to ggowner; Grant succeeded. Set up GGUSER role SQL> @$GGHOME/role_setup GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggowner Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes. |
Create our test schema and table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> create user bob identified by bob; User created. SQL> grant connect,resource to bob; Grant succeeded. SQL> create table t1 as 2 select rownum id, 'XXX' mypad 3 from dual connect by level < 1000; Table created. SQL> alter table t1 add constraint t1_pk primary key (id); Table altered. |
Set Flashback Query
|
1 2 3 4 5 |
SQL> alter system set undo_retention=86400; System altered. SQL> GRANT FLASHBACK ANY TABLE TO bob; |
Now its time to go into the GG comand line tool and configure our replication/extraction. Add checkpoint table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (srv1.in.com.au) 1> edit params ./GLOBALS GGSCHEMA ggowner CHECKPOINTTABLE ggowner.checkpointtable GGSCI (srv1.in.com.au) 2> dblogin userid ggowner, password ggowner; Successfully logged into database. GGSCI (srv1.in.com.au) 5> add checkpointtable ggowner.checkpointtable Successfully created checkpoint table ggowner.checkpointtable. |
Generate the table definitions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
GGSCI (srv1.in.com.au) 6> edit params defgen defsfile /ora/app/gg/product/12.1.2/oggcore_1/dirdef/bob.def USERID ggowner, PASSWORD ggowner table bob.*; GGSCI (srv1.in.com.au) 7> exit [gguser@srv1 oggcore_1]$ ./defgen paramfile ./dirprm/defgen.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316 Linux, x64, 64bit (optimized), Oracle 11g on Sep 24 2013 18:22:24 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. Starting at 2014-06-18 09:54:49 *********************************************************************** Operating System Version: Linux Version #1 SMP Mon Dec 20 10:52:42 EST 2010, Release 2.6.18-194.32.1.el5 Node: srv1.in.com.au Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 26346 *********************************************************************** ** Running with the following parameters ** *********************************************************************** defsfile /ora/app/gg/product/12.1.2/oggcore_1/dirdef/bob.def USERID ggowner, PASSWORD ******* table bob.*; Expanding wildcard table specification bob.*: Retrieving definition for BOB.T1. Definitions generated for 1 table in /ora/app/gg/product/12.1.2/oggcore_1/dirdef/bob.def. |
Configure the extract
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
GGSCI (srv1.in.com.au) 2> edit params bobex1 EXTRACT BOBEX1 USERID ggowner, PASSWORD ggowner EXTTRAIL ./dirdat/sa TABLE BOB.*; GGSCI (srv1.in.com.au) 3> add extract bobex1,tranlog, begin now EXTRACT added. GGSCI (srv1.in.com.au) 4> add exttrail ./dirdat/sa, extract bobex1 EXTTRAIL added. GGSCI (srv1.in.com.au) 5> edit params bobpump1 EXTRACT bobpump1 PASSTHRU RMTHOST srv1, MGRPORT 7809 RMTTRAIL ./dirdat/ta TABLE BOB.*; GGSCI (srv1.in.com.au) 5> edit params ffex1 EXTRACT ffex1 SOURCEDEFS ./dirdef/bob.def CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU, INCLUDEUPDATEBEFORES, PARAMS '/home/gguser/gg/ffwriter.properties' TABLE BOB.*; GGSCI (srv1.in.com.au) 14> add extract ffex1, exttrailsource ./dirdat/sa EXTRACT added. GGSCI (srv1.in.com.au) 3> start extract bobex1 Sending START request to MANAGER ... EXTRACT BOBEX1 starting GGSCI (srv1.in.com.au) 15> start extract ffex1 Sending START request to MANAGER ... EXTRACT FFEX1 starting |
Once all started lets check the status.
|
1 2 3 4 5 6 7 |
GGSCI (srv1.in.com.au) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING BOBEX1 00:00:00 00:00:08 EXTRACT RUNNING FFEX1 00:00:00 00:00:01 |
Now we can update some data and lets see some flat files created.
|
1 2 3 4 5 6 7 |
SQL> update t1 set id = id + 100000 where id < 10; 9 rows updated. SQL> commit; Commit complete. |
Now lets check to see if we have a flat file in our out directory as configured in ffwriter.properties.
|
1 2 |
ls -lart /home/gguser/gg/out/pump_BOB* -rw-r----- 1 gguser oinstall 675 Jun 18 10:17 /home/gguser/gg/out/pump_BOB_T1_2014-06-18_10-16-57_00001_data.dsv |
Success.
Thanks Stojan Veselinovski, I just read this article. I am looking for setting up GG replication from oracle source server (oracle 12.1) to target server (IBM PureData).
I see you explained well how to create a flat file on source. How do we update the Netezza server. You created pump but I did not see how you sent the flat file to Netezza server. Pl. update Thanks
Hi Chou,
This was a proof of concept for a particular project at the time. If memory serves me right the flat files would be written to NFS and be loaded by Netezza commands like copy and nzload.
Regards,
Stojan.
Chou, did you figure out how to load the files to Netezza once they were produced by GG?