Dear Readers,
In this article we will seeData Selection and Filtering in Oracle GoldenGate 12c.
We care for your speed. Below is an easy way to perform Data Selection & Filtering in Oracle GoldenGate 12c.
We can perform Data selection or Filtering in GoldenGate in two ways.
They are :
- Where Clause
- Filter Clause
Where Clause :
Where clause is the simplest form of selection
WHERE clause appears on either the MAP or TABLE parameter and must be surrounded by parenthesis.
Oracle GoldenGate data selection is conceptually similar to the where clause found on SQL. The purpose is to limit data replication to a specific criteria. The selection can be developed as part of the primary extract, data pump extract or replicate process.
Example :
On Source :
Check table which to be replicat
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 MARKETING USA 6 rows selected. |
Login into GGSCI prompt
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@gg ogg_src]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO Linux, x64, 64bit (optimized), Oracle 12c on May 9 2019 06:21:59 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (gg.orcl.com) 1> dblogin useridalias ggadmin_src Successfully logged into database. |
Check GG processes
|
1 2 3 4 5 |
GGSCI (gg.orcl.com as ggadmin@orcl) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:05 EXTRACT RUNNING EXTINT 00:00:05 00:00:02 |
Edit extarct param file and add WHERE clause with condition
|
1 2 3 4 5 6 7 8 9 10 11 |
GGSCI (gg.orcl.com as ggadmin@orcl) 3> edit param extint EXTRACT EXTINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100) EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt --DDL include all --TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN); TABLE AJAY.DEPT,where (loc='US'); TABLE AJAY.EMP; |
Here i have added WHERE clause in the TABLE .
Now stop EXTINT and START it again
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
GGSCI (gg.orcl.com as ggadmin@orcl) 4> stop extint Sending STOP request to EXTRACT EXTINT ... Request processed. GGSCI (gg.orcl.com as ggadmin@orcl) 5> start extint Sending START request to MANAGER ... EXTRACT EXTINT starting GGSCI (gg.orcl.com as ggadmin@orcl) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:09 EXTRACT RUNNING EXTINT 00:00:23 00:00:03 |
Check stats for extint
|
1 2 3 |
GGSCI (gg.orcl.com as ggadmin@orcl) 8> stats extint Sending STATS request to EXTRACT EXTINT ... No active extraction maps. |
Insert records into dept table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 MARKETING USA SQL> insert into dept values(60,'NETWORK','US'); 1 row created. SQL> insert into dept values(70,'UI','USA'); 1 row created. SQL> insert into dept values(80,'UI','US'); 1 row created. SQL> commit; Commit complete. |
Note : I have inserted three records but Extract will allow only loc=’US’ values only
Check stats for EXTINT
|
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 |
GGSCI (gg.orcl.com as ggadmin@orcl) 9> stats extint Sending STATS request to EXTRACT EXTINT ... Start of Statistics at 2019-08-21 19:34:17. Output to /u01/app/oracle/product/ogg_src/dirdat/lt: Extracting from AJAY.DEPT to AJAY.DEPT: *** Total statistics since 2019-08-21 19:34:03 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2019-08-21 19:34:03 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2019-08-21 19:34:03 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2019-08-21 19:34:03 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 End of Statistics. |
Check stats for DPINT
|
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 |
GGSCI (gg.orcl.com as ggadmin@orcl) 10> stats dpint Sending STATS request to EXTRACT DPINT ... Start of Statistics at 2019-08-21 19:34:46. Output to /u01/app/oracle/product/ogg_trg/dirdat/rt: Extracting from AJAY.DEPT to AJAY.DEPT: *** Total statistics since 2019-08-21 19:34:04 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2019-08-21 19:34:04 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2019-08-21 19:34:04 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2019-08-21 19:34:04 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 End of Statistics. |
TARGET :
On GGSCI check replicat stats
Check records available in target table DEPT
|
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 46 47 48 49 50 51 52 53 54 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 8> stats repint Sending STATS request to REPLICAT REPINT ... Start of Statistics at 2019-08-21 19:35:59. Integrated Replicat Statistics: Total transactions 2.00 Redirected 0.00 Replicated procedures 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Operation type functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from AJAY.DEPT to AJAY.DEPT: *** Total statistics since 2019-08-21 19:35:55 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Daily statistics since 2019-08-21 19:35:55 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Hourly statistics since 2019-08-21 19:35:55 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 *** Latest statistics since 2019-08-21 19:35:55 *** Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 2.00 End of Statistics. |
Check records in Target table DEPT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select * from dept order by deptno; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 MARKETING USA 60 NETWORK US 80 UI US 7 rows selected. |
Deptno 70 row has been ignored by extract process because of loc is not equal to US
We can check using report file of EXTINT
What we cant do with selection-where clause
- Perform arithmetic operations
- Refer to trail header and user token values
- You can use the filter clause for more complex selections with built-in functions
Check Filtering for below link
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
