PART 3 : GOLDEN GATE FILTERING DATA / Definitions changes using Defgen utility
On Source: & On Target create a test table
create table ggs_owner.filter tablespace users as select * from dba_objects;
create table ggs_owner.filter tablespace users as select * from dba_objects where rownum < 1;
(only definition)
On Source create extract for Initial Load
GGSCI (testdb1) 1> add extract myload1, sourceistable
EXTRACT added.
edit params myload1
================
EXTRACT myload1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 192.168.1.108, MGRPORT 7809
RMTTASK replicat, GROUP myload1
TABLE ggs_owner.filter, FILTER (@STRFIND (OBJECT_TYPE,'TABLE') > 0);
On Target , run special run to load the dataadd replicat myload1, specialrun
edit params myload1
REPLICAT myload1
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP ggs_owner.filter, TARGET ggs_owner.filter;
On Source: start extract myload1
On Target: start replicat myload1
On Source create extract for Online Synchornisation
add extract myload2, tranlog, begin now
add rmttrail /u01/ggt_target/dirdat/bb, extract myload2
edit params myload2
EXTRACT myload2
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 192.168.1.108, MGRPORT 7809
RMTTRAIL /u01/app/oracle/goldengate/dirdat/bb
TABLE ggs_owner.filter, FILTER (@STRFIND (OBJECT_TYPE, "INDEX") > 0);
On Target create Replicate for Online Synchornisationadd replicat myload2, exttrail /u01/app/oracle/goldengate/dirdat/bb
edit params myload2
REPLICAT myload2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.filter, TARGET ggs_owner.filter;
On Source: Start extract myload2
On Source: info extract myload2, detail
On Target: start replicat myload2
On Source: info replicat myload2, detail
On Source
GGSCI (testdb1) 6> info extract myload2, detail
EXTRACT MYLOAD2 Last Started 2016-06-23 20:35 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:07:51 ago)
Log Read Checkpoint Oracle Redo Logs
2016-06-23 20:27:37 Seqno 14, RBA 11447312
SCN 0.0 (0)
*** No TARGETEXTTRAILS specified yet ***
Extract Source Begin End
/u01/app/oracle/oradata/testdb1/redo02.log 2016-06-23 20:27 2016-06-23 20:27
Not Available * Initialized * 2016-06-23 20:27
Not Available * Initialized * 2016-06-23 20:27
Not Available * Initialized * 2016-06-23 20:27
Current directory /u01/app/oracle/goldengate
Report file /u01/app/oracle/goldengate/dirrpt/MYLOAD2.rpt
Parameter file /u01/app/oracle/goldengate/dirprm/myload2.prm
Checkpoint file /u01/app/oracle/goldengate/dirchk/MYLOAD2.cpe
Process file /u01/app/oracle/goldengate/dirpcs/MYLOAD2.pce
Error log /u01/app/oracle/goldengate/ggserr.log
On Target
GGSCI (testdb2) 6> info replicat myload2, detail
REPLICAT MYLOAD2 Last Started 2016-06-23 20:35 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 16823
Log Read Checkpoint File /u01/app/oracle/goldengate/dirdat/bb000000000
First Record RBA 0
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
Extract Source Begin End
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
/u01/app/oracle/goldengate/dirdat/bb000000000 * Initialized * First Record
Current directory /u01/app/oracle/goldengate
Report file /u01/app/oracle/goldengate/dirrpt/MYLOAD2.rpt
Parameter file /u01/app/oracle/goldengate/dirprm/myload2.prm
Checkpoint file /u01/app/oracle/goldengate/dirchk/MYLOAD2.cpr
Checkpoint table ggs_owner.CHKPTAB
Process file /u01/app/oracle/goldengate/dirpcs/MYLOAD2.pcr
Error log /u01/app/oracle/goldengate/ggserr.log
Now Insert into ggs_owner.filter with two rows "one with index" type and another "with table
index"
SQL> insert into ggs_owner.filter (object_name,object_type) values ('AAAA','INDEX');
1 row created.
SQL> insert into ggs_owner.filter (object_name,object_type) values ('BBBB','TABLE');
1 row created.
SQL> commit;
Commit complete.
Check the dataselect * from ggs_owner.filter where object_name='AAAA';
select * from ggs_owner.filter where object_name='BBBB'; --> Shoudl not be in Target as filter is applied to exclude the data
Enf of topic
Definitions changes using Defgen utility : (Column definition got changed on Source but not in Target to solve this issue we need to create the definition file using deffgen utility )
On Target
SQL> alter table ggs_owner.filter rename column object_name to objname;
Table altered.
When replicate try to replicate on target it throws the error where log saying objname invalid identifier
First create defgen script using defgen utility
On Source : create def param & generate sql file
EDIT PARAMS defgen
===================
DEFSFILE /u01/app/oracle/goldengate/dirsql/def.sql
USERID ggs_owner, PASSWORD ggs_owner
TABLE ggs_owner.filter;
./defgen paramfile /u01/app/oracle/goldengate/dirprm/defgen.prm
[oracle@testdb1 goldengate]$ ./defgen paramfile /u01/app/oracle/goldengate/dirprm/defgen.prm
Output:
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), Oracle 11g on Dec 11 2015 21:37:21
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-06-23 23:21:35
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu May 12 11:03:55 UTC 2016, Release 3.10.0-327.18.2.el7.x86_64
Node: testdb1.sample.com
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: 23591
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/app/oracle/goldengate/dirsql/def.sql
USERID ggs_owner, PASSWORD ***
TABLE ggs_owner.filter;
Retrieving definition for GGS_OWNER.FILTER.
2016-06-23 23:21:42 WARNING OGG-06439 No unique key is defined for table FILTER. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Definitions generated for the filter table from the source in /u01/app/oracle/goldengate/dirsql/def.sql
need to copy file from Source to Target.
scp /u01/app/oracle/goldengate/dirsql/def.sql 192.168.1.108:/u01/app/oracle/goldengate/dirsql/def.sql
Note: Physical path of defgen /u01/app/oracle/goldengate/dirprm/defgen.prm
On Target:
========
EDIT params myload2
REPLICAT myload2
SOURCEDEFS/u01/app/oracle/goldengate/dirsql/def.sql
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.filter, TARGETggs_owner.filter, COLMAP (usedefaults,object_name=objname);
start replicatmyload2
