Example 21: Integrated Goldengate Capture

In 11gR2 and 12c Releases of goldengate software, there is new capture mode called integrated capture mode.

Until before this releases, goldengate capture process capture the dml changes out side of the database , that means independent of database.

However, Integrated capture can be created to run inside the database as a database process, Sound similar? like streams , replication, yes. This will also mines the redo records using log miner as like streams, but what is the difference?

The difference is trail files again. the traill files are generated as goldengate format although they mine from log miner sessions. Where in streams / replication does not generate any of their own trail files instead they send the transactions (LCR) directly to the target database using tns over tcp/ip.

And then what the benefit,  The benefit is that integrated capture mode supports

• Full Support of Basic, OLTP and EHCC compressed data.
• No need to fetch LOB’s from tables.
• Full Secure File support for Secure file lobs.
• Full XML support.
• Automatically handles addition of nodes and threads in RAC environment.
• Senses node up down in RAC and handles it in its processes transparently.

Further, this is supports the database after 11.2.0.3 only.

In addition to above, Integrated capture supports Upstream (source itself capture) and Downstream (remote host does the capture) capture modes. Sounds similar again Upstream/Downstream capture , yes same way.

How to setup this Integrated capture

1. New Setup is just seem less, need to add the tranlog option in extract

2. Existing setup will do some work, to stop / start / upgrade extract etc.

Let's check the Option 1: New Setup

## In Database, grant the necessary privileges to GG Admin user

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

##Create a extract with following params

ggsci> edit params ext3

EXTRACT ext3

USERID ggs_owner, PASSWORD ggs_owner

EXTTRAIL ./dirdat/ex

TRANLOGOPTIONS EXCLUDEUSER ggs_owner

TABLE test.*;

## Register the extract to database

ggsci> DBLOGIN USER dbuser PASSWORD dbpasswd

ggsci> REGISTER EXTRACT ext3 DATABASE

## Add the extract to process

GGSCI (source) 2> ADD EXTRACT ext3, INTEGRATED TRANLOG, BEGIN NOW

EXTRACT added.

## Start the extract

GGSCI (source) 3> start extract ext3

## Verify the extract started in integrated mode

In database alert log you see reader,capture process started for goldengate

## Monitoring the integrated capture details from the source database

col state for a30;

SELECT sid, serial#, capture#, CAPTURE_NAME, STARTUP_TIME, CAPTURE_TIME,

state, SGA_USED, BYTES_OF_REDO_MINED,

to_char(STATE_CHANGED_TIME, 'mm-dd-yy hh24:mi') STATE_CHANGED_TIME

FROM V$GOLDENGATE_CAPTURE;

col capture_message_create_time for a30;

col enqueue_message_create_time for a27;

col available_message_create_time for a30;

SELECT capture_name,

to_char(capture_time, 'mm-dd-yy hh24:mi') capture_time,

capture_message_number,

to_char(capture_message_create_time ,'mm-dd-yy hh24:mi') capture_message_create_time,

to_char(enqueue_time,'mm-dd-yy hh24:mi') enqueue_time,

enqueue_message_number,

to_char(enqueue_message_create_time, 'mm-dd-yy hh24:mi') enqueue_message_create_time,

available_message_number,

to_char(available_message_create_time,'mm-dd-yy hh24:mi') available_message_create_time

FROM GV$GOLDENGATE_CAPTURE; SELECT component_name capture_name, count(*) open_transactions,

sum(cumulative_message_count) LCRs

FROM GV$GOLDENGATE_TRANSACTION

WHERE component_type='CAPTURE'

group by component_name;