• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

GoldenGate Not Keeping Up? Split the Process Using GoldenGate Range Function

August 11, 2015 by Natik Ameen   /  Goldengate, GoldenGate 12c, Oracle-Blog

In most environments one set of GoldenGate process (1 Extract & 1 Replicat process) is sufficient for change data synchronization. But if your source database generates a huge volume of data then a single process may not be sufficient to handle the data volume.

In such a scenario there may be need to split the workload, across multiple Extract and/or Replicat processes.

You can configure multiple GoldenGate Extract and Replicat process to split workload using one of the two options below.

• Break up current Extract/Replicat processes for one table, into multiple processes using the GoldenGate Range function.
• Break up current Extract/Replicat processes, into multiple processes containing different set of tables in different processes.

For a given table if there is high data volume generated, then you can make use of the first option using the GoldenGate @Range function to split the workload for heavily accessed tables across multiple Extract/Replicat processes.

How does GoldenGate Range Function @Range work?

The @Range function divides a workload into multiple, randomly distributed groups of data. It determines the group that the range falls in, by computing a hash against the primary key or user-defined columns. It guarantees that the same row is always processed by the same process group.

The @Range function can be used on either the TABLE or the MAP parameter of Extract and Replicat parameter respectively. The @Range function is used along with the FILTER clause.

Limitations of @Range

You can’t use @Range function, if primary key updates are performed on the database.

Demonstration

In this example we will configure multiple Replicats (3) on the target system to split workload for SCOTT.EMP and SCOTT.DEPT tables. Each Replicat Process will read the same trail file generated by the Pump Extract process but operate on different set of data for the same table.

Environment Details
Parameters         Source/Primary          Target/Secondary
DB Name                        orcl                                     orcl
DB Version                11.2.0.3                            11.2.0.3
Schema                        SCOTT                               SCOTT
Table Name            EMP, DEPT                      EMP, DEPT
OGG Version          11.2.1.0.20                     11.2.1.0.20

Enable Supplemental logging at Table level
GGSCI > dblogin userid ggs, password oracle
Successfully logged into database.
GGSCI> add trandata scott.emp
GGSCI> add trandata scott.dept

Extract and Pump on Source System

Primary Extract

$ cd $GG_HOME
$ ./ggsci
GGSCI> EDIT PARAMS Erange1
Extract Erange1
USERID GGS, PASSWORD oracle
EXTTRAIL ./dirdat/rt
TABLE Scott.emp;
TABLE Scott.dept;
GGSCI> ADD Extract Erange1, TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/rt, Extract Erange1
GGSCI> Start Erange1
GGSCI> Info Erange1

Pump Extract

GGSCI> EDIT PARAMS Prange1
Extract Prange1
USERID GGS, PASSWORD oracle
RMTHOST 192.168.1.30, MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE Scott.EMP;
TABLE Scott.DEPT;
GGSCI> ADD Extract Prange1, EXTTRAILSOURCE ./dirdat/rt
ADD RMTTRAIL ./dirdat/rt, Extract Prange1
GGSCI> Start prange1
GGSCI> Info prange1

Replicat processes on Target System

Here we will create 3 Replicat processes, with each Replicat group processing one-third of the data in the GoldenGate trail based on the primary key.

Replicat - 1

$ cd $GG_HOME
$ ./ggsci
GGSCI> EDIT PARAMS Rrange1
Replicat Rrange1
USERID GGS, Password oracle
DISCARDFILE ./dirrpt/Rrange1.dsc, Append
ASSUMETARGETDEFS
MAP Scott.EMP, TARGET Scott.EMP, FILTER (@RANGE (1,3));
MAP Scott.DEPT, TARGET Scott.DEPT, FILTER (@RANGE (1,3));
GGSCI> ADD Replicat Rrange1, EXTTRAIL ./dirdat/rt
GGSCI> Start Rrange1
GGSCI> Info Rrange1

Replicat - 2

GGSCI> EDIT PARAMS Rrange2
Replicat Rrange2
USERID GGS, Password oracle
DISCARDFILE ./dirrpt/Rrange2.dsc, Append
ASSUMETARGETDEFS
MAP Scott.EMP, TARGET Scott.EMP, FILTER (@RANGE (2,3));
MAP Scott.DEPT, TARGET Scott.DEPT, FILTER (@RANGE (2,3));
GGSCI> ADD Replicat Rrange2, EXTTRAIL ./dirdat/rt
GGSCI> Start Rrange2
GGSCI> Info Rrange2

Replicat – 3

GGSCI> EDIT PARAMS Rrange3
Replicat Rrange3
USERID GGS, Password oracle
DISCARDFILE ./dirrpt/Rrange3.dsc, Append
ASSUMETARGETDEFS
MAP Scott.EMP, TARGET Scott.EMP, FILTER (@RANGE (3,3));
MAP Scott.DEPT, TARGET Scott.DEPT, FILTER (@RANGE (3,3));
GGSCI> ADD Replicat Range3, EXTTRAIL ./dirdat/rt
GGSCI> Start Rrange3
GGSCI> Info Rrange3

Now when the data is loaded into the source database, each of the 3 Replicat processes will apply the trail data to target database to different sets groups of rows at the same time (in parallel).

Read more on VirtualBox For GoldenGate

• Provision VirtualBox using Pre-Built Developer VMs
• Adding Disk Storage to Oracle VirtualBox
• Connecting Applications from Desktop to your VirtualBox Database

Back to GoldenGate Homepage

Filed Under: Goldengate, GoldenGate 12c, Oracle-Blog Tagged With: GoldenGate tuning

Comments

  1. kboussif@gmail.com'kalid says

    February 13, 2015 at 10:54 pm

    Some issue with

    ERROR DDL committed by one Replicat before waiting for all preceding DMLs
    ERROR Delete occurs before the primary key is updated by another Replicat

    Now you have other options Integrated and Coordinated Replicat
    1) Coordinated Delivery Approach

    REP.PRM MAP sales.acct, TARGET sales.acct, THREADRANGE(1-3, ID));

    Creating Coordinated Replicat Groups
    GGSCI> add replicat rep1, coordinated, exttrail ¡K, maxthreads 10

    Creates 12 Replicats¨ in total
    rep1 (Coordinator), rep1000 (Thread 0 that is created by default)
    rep1001, rep1002, rep1003¡K. rep1010 (Threads 1, 2 ¡K etc.)
    Each Replicat has its own checkpoint file, report file and is a fully functional replicat.
    Processes will only be created for threads mentioned in the Parameter file MAXTHREADS defaults to 25 threads.

    2) Integrated Replicat
    • Integrated Replicat for Oracle target databases only 12.1.0.1 and 11.2.0.4
    • Leverages database parallel apply servers for automatic dependency aware parallel apply
    Single replicat parameter file for all tables
     No split transaction semantics

    Reply
  2. prasanpd@gmail.com'PRASANNA says

    August 11, 2015 at 12:55 pm

    can this be done at the schema level ?

    Reply
    • Natik Ameen says

      August 11, 2015 at 6:55 pm

      Hi Prasanna,

      What version of the database and GoldenGate are you using? In GoldenGate 12c there is better support for paralleling these large tables without manual setup of the @range functions. Hopefully this helps.

      Reply

Leave a Reply Cancel reply

Your email address will not be published.

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials