News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

GoldenGate Integrated Capture Mode

  • Posted by Gavin Soorma
  • On January 30, 2013
  • 4 Comments
  • 11G, extract, GoldenGate, integrated capture

One of the new features in GoldenGate 11g is the Integrated Capture mode.

In the earlier classic capture mode, the Oracle GoldenGate Extract process captures data changes from the Oracle redo or archive log files on the source system.

In integrated capture mode, the Oracle GoldenGate Extract process interacts directly with the database log mining server which mines or reads the database redo log files and captures the changes in the form of Logical Change Records (LCR’s) which are from there written to the GoldenGate trail files.

The basic difference is that in the Integrated Capture mode, the extract process does not directly read the redo log files. That part of the job is done by the logmining server residing in the Oracle database.

Integrated capture supports more data types as well as compressed data and as it is fully integrated with the database there is no additional setup steps required when we are configuring GoldenGate with things like RAC, ASM and TDE (Transparent Data Encryption)

In the integrated capture mode there are two deployment options:

a) Local deployment
b) Downstream deployment

Basically it depends on where the log mining server is deployed.

In the Local deployment, the source database and the log mining server are the same database

In downstream deployment, the source and log mining databases are different databases. The source database uses redo transport to ship the archived redo log files to the ‘downstream’ database where the log mining server is residing. The log mining server extracts changes in the form of logical change records and these are then processed by GoldenGate and written to the trail files.

So in the downstream integrated capture mode, we offload any overhead associated with the capture or transformation from the source database to the downstream database which may be used only for GoldenGate processing and not for any production user connections.

In this example we will look at the setup of integrated capture local deployment and in the next post we will look at a downstream integrated capture model.

Database setup for Integrated Capture

We need to keep in mind the point that for full integrated capture support of all Oracle data and storage types, the compatibility setting of the source database must be at least 11.2.0.3.

Also, we need to apply the database patch 14551959 using opatch. Read the MOS note 1411356.1 for full details

After applying the patch 14551959 (the database and listener need to down to apply this patch) using opatch, we also need to do some post install steps as mentioned in the README.txt.

We need to start the database and run the postinstall.sql located in the patch directory.

This is to be followed by granting certain privileges to the GoldenGate database user account via the package
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE as shown below. In this case the database user is ‘ggate’.

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( -
grantee => 'ggate', -
privilege_type => 'capture', grant_select_privileges=> true, do_grants => TRUE);

If the patch is not applied or the privileges not granted, we can expect to see an error like the one shown below:

2013-01-24 17:30:24 ERROR OGG-02021 This database lacks the required libraries to support integrated capture.

What’s different to the classic capture setup?

When we add the extract we have to use the INTEGRATED CAPTURE clause in the ADD EXTRACT command as shown below

ADD EXTRACT intext INTEGRATED TRANLOG, BEGIN NOW

In the extract parameter file we have to use TRANLOGOPTIONS INTEGRATEDPARAMS parameter as show below

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 1)

The max_sga_size is denoted in MB and this memory is taken from the streans_pool_size part of the SGA memory. If the streams_pool_size is greater than 1 GB, max_sga_size defaults to 1 GB, otherwise it is 75% of the streans_pool_size

To test this I set the max_sga_size to 200 MB and the streans_pool_size was also 200 MB.

This error was noticed and the extract abended.

2013-01-24 17:59:42 ERROR OGG-02050 Not enough database memory to honor requested MAX_SGA_SIZE of 200.
2013-01-24 17:59:42 ERROR OGG-01668 PROCESS ABENDING.

We had to set the max_sga_size in this case to 150 and then the extract started.

The parallelism specifies the number of processes supporting the database log mining server. It defaults to 2

Register the extract

We use the REGISTER EXTRACT command to register the primary extract group with the Oracle database. The extract process does not directly read the redo log files as in the classic capture mode, but integrates with the datavase log mining server to receive changes in the form of Logical Change Records or LCR’s.

We do this before adding the extract and must connect to the database first via the DBLOGIN command

GGSCI> DBLOGIN USER dbuser PASSWORD dbpasswd
GGSCI> REGISTER EXTRACT ext1 DATABASE

Example


In this case we are creating the extract group intext and the extract datapump group intdp. We will be replicating the SH.customers table using the integrated capture mode.

GGSCI (pdemvrhl061) 1> DBLOGIN USERID ggate, PASSWORD ggate
Successfully logged into database.
GGSCI (pdemvrhl061) 2> REGISTER EXTRACT intext DATABASE
2013-01-24 17:58:28 WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.
2013-01-24 17:58:46 INFO OGG-02003 Extract INTEXT successfully registered with database at SCN 1164411.
GGSCI (pdemvrhl061) 1> ADD EXTRACT intext INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (pdemvrhl061) 3> ADD EXTTRAIL /u01/app/ggate/dirdat/lt, EXTRACT intext
EXTTRAIL added.
GGSCI (pdemvrhl061) 4> ADD EXTRACT intdp EXTTRAILSOURCE /u01/app/ggate/dirdat/lt
EXTRACT added.
GGSCI (pdemvrhl061) 5> ADD RMTTRAIL /u01/app/ggate/dirdat/rt, EXTRACT intdp
RMTTRAIL added.
GGSCI (pdemvrhl061) 6> EDIT PARAMS intext
EXTRACT intext
USERID ggate, PASSWORD ggate
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /u01/app/ggate/dirdat/lt
TABLE sh.customers;
GGSCI (pdemvrhl061) 7> EDIT PARAMS intdp
EXTRACT intdp
USERID ggate, PASSWORD ggate
RMTHOST 10.xx.206.xx, MGRPORT 7809
RMTTRAIL /u01/app/ggate/dirdat/rt
TABLE sh.customers ;
GGSCI (pdemvrhl061) 7> start extract intext
Sending START request to MANAGER ...
EXTRACT INTEXT starting
GGSCI (pdemvrhl061) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING INTDP 00:00:00 00:00:05
EXTRACT RUNNING INTEXT 01:17:18 00:00:04
On the target site, start the Replicat process.
GGSCI (pdemvrhl062) 4> START REPLICAT rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (pdemvrhl062) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06

In the background ….

When we register the extract, we will see that a capture process called OGG$CAP_INTEXT was created and a queue called OGG$Q_INTEXT was created in the GGATE schema.

A good source of information is also the database alert log and we can see messages like the ones shown below:

LOGMINER: session#=1 (OGG$CAP_INTEXT), reader MS00 pid=41 OS id=32201 sid=153 started
Thu Jan 24 18:04:15 2013
LOGMINER: session#=1 (OGG$CAP_INTEXT), builder MS01 pid=42 OS id=32203 sid=30 started
Thu Jan 24 18:04:15 2013
LOGMINER: session#=1 (OGG$CAP_INTEXT), preparer MS02 pid=43 OS id=32205 sid=155 started
Thu Jan 24 18:04:16 2013

LOGMINER: Begin mining logfile for session 1 thread 1 sequence 12, /u01/oradata/testdb1/redo03.log
LOGMINER: End mining logfile for session 1 thread 1 sequence 12, /u01/oradata/testdb1/redo03.log

Read further

GoldenGate Integrated Capture Healthcheck Script [Article ID 1448324.1]

Advisor Webcast : Extracting Data in Oracle GoldenGate Integrated Capture Mode (MOS note 740966.1)

 

4 Comments

Rajasekhar Amudala
  • Dec 5 2016
Hello Gavin - Thank you for sharing the knowledge. I just want to bring to your notice that you have mentioned as "One of the new features in GoldenGate 11g is the Integrated Capture mode.", but it should be Godengate 12c not Goldengate 11g. Please check Thank you, Rajasekhar Amudala
Gavin Soorma
  • Dec 7 2016
Hi - Integrated Extract is available in OGG 11g - Integrated Replicat is available in 12c - not sure how you are saying that IE is only available in GoldenGate 12c only ...?
Ali Osman Y?lmaz
  • Feb 27 2017
Hi Gavin, can we use more than one Integrated Extract in same source. if so what should be streams_pool_size.(ie. 150mb for ie1, 300mb for ie2 then should streams_pool_size be at least 150*4/3+300*4/3=600mb) also which do you prefer. 1 IE with parallesim 2 or 2 seperate IE with parallelism 1. thanks.
Gavin Soorma
  • Feb 27 2017
The recommendation is a minimum of 1 GB of Streams Pool memory allocation per Integrated Extract process - its mentioned in the official documentation as well . With Enterprise Edition the default parallelism is 2.

Leave Reply

Your email address will not be published. Required fields are marked *