Let’s have look at the Defgen Utility In Oracle Goldengate. If the source and target table structures are different from source has then we need to use definition file in replicate file.
Example:
Here we will generate definition file of a table DBACLASS.EMPLOYEE from the source database and use it in target db.
1. Create defgen parameter file
ggsci>edit params defgen
defsfile /u01/ggate/dirsql/dbaclass_def.sql
userid ggatebss, password ggatebss#123
TABLE DBACLASS.EMPLOYEE;
This will create the parameter file in $GG_HOME/dirprm
ls -ltr /u01/ggate/dirprm/defgen.prm
2. Now execute the param file( to generate the defgen file)
./defgen paramfile /u01/ggate/dirprm/defgen.prm
./defgen paramfile /u01/ggate/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 16:58:29
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-12-20 14:52:05
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Sep 16 01:56:35 EDT 2014, Release 2.6.32-504.el6.x86_64
Node: b23e2bay16
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: 29614
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /u01/ggate/dirsql//dbaclass_def.sql
userid ggatebss, password ************
TABLE DBACLASS.EMPLOYEE;
Retrieving definition for DBACLASS.EMPLOYEE
Definitions generated for 1 tables in /u01/ggate/dirsql/dbaclass_def.sql
Now copy the file dbaclass_def.sql to the target server(where replicate is running.
4. Stop the replication and Update the extract file with sourcedef parameter as below
SOURCEDEFS /ggatebss/app/ggate/product/11.2.1.0.3/ggate_1/myobjects3.sql
REPLICAT BLTLR
USERID ggatebss, PASSWORD ggatebss
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
SOURCEDEFS /ggatebss/app/ggate/product/11.2.1.0.3/ggate_1/myobjects3.sql
DISCARDFILE /ggatebss/install/datacapture_bsst/discardbltlr,append,megabytes 1000
MAP DBACLASS.EMPLOYEE, TARGET DBACLASS.EMPLOYEE
Now you can start your replicate.
NOTE – If your source and target database versions are different. i.e source db is of 12c and target db is of 11g, then use format command in the defgen file. i.e the file will look as below. format release < target db release version>
ggsci>edit params defgen
defsfile /u01/ggate/dirsql/dbaclass_def.sql ,format release 11.2
userid ggatebss, password ggatebss#123
TABLE DBACLASS.EMPLOYEE;
