Oracle GoldenGate - Integrated Capture

This page discusses the configuration of integrated capture in Oracle GoldenGate.

The page is based on Oracle GoldenGate version 11.2.1.0.1. For a basic configuration I used two Linux VMs (OEL5U6) running single instance Oracle 11.2.0.3 databases. I created both databases using DBCA.

This page assumes that the source and target databases have already been configured as described in Oracle GoldenGate - Basic Configuration.

This configuration uses following hosts and databases.

Source Target
Hostname vm4 vm5
Database Name NORTH SOUTH

The configuration includes the following on both nodes:

The GoldenGate process names are:

Source Target
Extract ex2 -
Data Pump dp2 -
Replicat - rep2

Prerequisites

Integrated capture is supported in Oracle 11.2.0.3 and above. In addition to the patch set, a bundle patch is required. The patch number is 15987144.

The patch should be installed in both source and target databases, although I am not convinced it is necessary for the target database.

The patch should be downloaded and installed in the Oracle home directory. In a RAC cluster the patch should be installed on each node.

For Linux x86-64, the zip file is called p15987144_112030_Linux-x86-64.zip. I downloaded this file and copied it to the /home/oracle/patches directory

Install Patch

First shut down the database

Unzip the archive:

[oracle@vm4]$ cd /home/oracle/patches
[oracle@vm4]$ unzip p15987144_112030_Linux-x86-64.zip

Install the patch:

[oracle@vm4]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@vm4]$ cd /home/oracle/patches/15987144
[oracle@vm4]$ opatch apply

Verify that the patch has been installed correctly using

[oracle@vm4]$ opatch lsinventory

Finally restart the database

Run postinstall.sql script

The patch includes a post-install script which should be installed by a user with SYSDBA privileges.

[oracle@vm4]$ cd /home/oracle/patches/15987144
[oracle@vm4]$ sqlplus / as sysdba
SQL> @postinstall.sql

This script creates or updates a number of functions, procedures and packages required for integrated capture.

The postinstall.sql script updates the following PL/SQL binary files:

The contents of these files are summarized below:

$ORACLE_HOME/rdbms/admin/prvtlmcb.plb

Object Type Owner Object Name
FUNCTION SYS logmnr_get_gt_protocol
PROCEDURE SYS logmnr_gtlo3
PACKAGE SYS logmnr_dict_cache
PROCEDURE SYS logmnr_ddl_trigger_proc
TRIGGER SYS logmnrggc_trigger

$ORACLE_HOME/rdbms/admin/prvthxstr.plb

Object Type Owner Object Name
PACKAGE SYS dbms_xstream_adm
PACKAGE SYS dbms_xstream_adm_utl
PACKAGE SYS dbms_xstream_adm_internal
PACKAGE SYS dbms_xstream_auth
PACKAGE SYS dbms_xstream_utl_ivk
PACKAGE SYS dbms_xstream_gg
PACKAGE SYS dbms_xstream_gg_adm
PACKAGE SYS dbms_goldengate_auth

$ORACLE_HOME/rdbms/admin/prvtbcap.plb

Object Type Owner Object Name
PACKAGE SYS dbms_capture_adm
PACKAGE SYS dbms_capture_adm_internal
PACKAGE SYS dbms_capture_process

$ORACLE_HOME/rdbms/admin/prvtbapp.plb

Object Type Owner Object Name
PACKAGE SYS dbms_apply_adm
PACKAGE SYS dbms_apply_adm_internal
PACKAGE SYS dbms_apply_process
PACKAGE SYS dbms_apply_error
PACKAGE SYS dbms_apply_position

Database Compatibility

If the COMPATIBLE parameter is not set to 11.2.0.3.0 or above then the following warning will appear when the extract is registered with the database:

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.

To avoid receiving this warning, on both the source and target databases set the COMPATIBLE parameter to 11.2.0.3. The COMPATIBLE parameter is not dynamic, so an instance restart will be required following the change.

SQL> ALTER SYSTEM SET compatible = '11.2.0.3.0' SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE

Memory Configuration

Integrated capture is based on Streams technology. The capture process uses part of the Streams pool in the SGA.

The amount of shared memory allocated to integrated capture is specified within GoldenGate by the ambiguously named SGA_MAX_SIZE parameter. This should not be confused with the SGA_MAX_SIZE database parameter.

If insufficient memory is available in the shared pool the integrated capture extract process will fail with an error such as:

OGG-02050  Not enough database memory to honor requested MAX_SGA_SIZE of 100.

For testing purposes I needed to adjust several memory parameters to ensure that the integrated capture process was able to start. I had a couple of constraints

To accommodate the GoldenGate SGA memory requirement I identified that I needed to increase the Streams pool to a minimum of 200MB. Consequently I decided to increase the MEMORY_TARGET parameter from 800MB to 1GB.

I first attempted to increase the MEMORY_TARGET parameter to 1GB

ALTER SYSTEM SET memory_target = 1G SCOPE=SPFILE;

Restarting the instance failed with the following error:

ORA-00845: MEMORY_TARGET not supported on this system

This is not a particularly accurate error message. However additional information was written to the alert log:

WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to
be mounted for at least 1073741824 bytes. /dev/shm is either not mounted or is mounted with available space
less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is
1050001408 and used is 0 bytes. Ensure that the mount point is /dev/shm for this directory.

By default in the Linux virtual machine around 1GB is allocated to /dev/shm.

This can be verified using:

[oracle@vm4]$ df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                  1025392    490852    534540  48% /dev/shm

Note that the amount used/available is dependent on the amount of time the instance has been started and also the usage patterns.

This is configured in /etc/fstab as follows:

tmpfs                   /dev/shm                tmpfs   defaults       0 0

I increased the amount of shared memory allocated by modifying /etc/fstab as follows:

tmpfs                   /dev/shm                tmpfs   size=1024m     0 0

After a reboot the change can be verified using:

[oracle@vm4]$ df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                  1048576    625852    422724  60% /dev/shm

Following the reboot it was possible to start the Oracle instance without any errors.

Finally I increased the value of the STREAMS_POOL_SIZE parameter to 200MB

ALTER SYSTEM SET streams_pool_size=200M;

When Automatic Memory Management is enabled setting a value for the STREAMS_POOL_SIZE parameter specifies a minimum size for this pool which is allocated at instance restart.

Administrative Privileges

On the source server, grant administrative privileges for capture operations to the GoldenGate user using the following:

BEGIN
  dbms_goldengate_auth.grant_admin_privilege
  (
    grantee => 'GG01',
    privilege_type => 'CAPTURE',
    grant_select_privileges => TRUE
  );
END;
/

Configuration

Register the Extract Group

On the source server, the primary extract group must be registered with the database using the REGISTER EXTRACT command.

[oracle@vm4]$ ggsci

GGSCI (vm4) 1> DBLOGIN USERID gg01 PASSWORD gg01
Successfully logged into database.

GGSCI (vm4) 2> REGISTER EXTRACT ex2 DATABASE

2013-04-19 14:04:01  INFO    OGG-02003  Extract EX2 successfully registered with database at SCN 2109330.

Add Extract Process

On the source server, add a new Extract process called ex2:

GGSCI (vm4) 3> ADD EXTRACT ex2 INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.

Add Extract Trail

On the source server, add a new GoldenGate trail for the extract process:

GGSCI (vm4) 4> ADD EXTTRAIL /home/oracle/goldengate/dirdat/il, EXTRACT ex2
EXTTRAIL added.

Add Data Pump Process

On the source server, add a new Extract process for the data pump called dp2:

GGSCI (vm4) 5> ADD EXTRACT dp2 EXTTRAILSOURCE /home/oracle/goldengate/dirdat/il
EXTRACT added.

Add Data Pump Trail

On the source server, add a new GoldenGate trail for data pump process:

GGSCI (vm4) 6> ADD RMTTRAIL /home/oracle/goldengate/dirdat/ir, EXTRACT dp2
RMTTRAIL added.

Set Extract Parameters

On the source server, modify the parameter file for the ex2 extract process:

GGSCI (vm4) 1> edit params ex2

Add the following:

EXTRACT ex2
USERID gg01, PASSWORD gg01
TRANLOGOPTIONS INTEGRATED PARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /home/oracle/goldengate/dirdat/il
TABLE us01.t101;

The TRANLOGOPTIONS parameter specifies that the maximum amount of space allocated to the integrated capture within the Streams pool is 100MB.

Set Data Pump Parameters

On the source server, modify the parameter file for the dp2 extract process:

GGSCI (vm4) 1> EDIT PARAMS dp2

Add the following:

EXTRACT dp2
USERID gg01, PASSWORD gg01
RMTHOST vm5, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/ir
TABLE US01.*;

Add Replicat Process

On the target server, add the replicat process called rep2:

[oracle@vm5]$ ggsci
GGSCI (vm5) 1> ADD REPLICAT rep2, EXTTRAIL /home/oracle/goldengate/dirdat/ir
REPLICAT added.

Set Replicat Parameters

On the target server, modify the parameter file for the rep1 replicat process:

REPLICAT rep2
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
MAP US01.* TARGET US01.*;

Note that the configuration of the target server is similar for both classic and integrated capture.

Start Extract Process

On the source server, start the ex2 extract process:

[oracle@vm4]$ ggsci

GGSCI (vm4) 1> START EXTRACT ex2

Sending START request to MANAGER ...
EXTRACT EX2 starting

Start Data Pump Process

On the source server, start the dp2 extract process:
[oracle@vm4]$ ggsci

GGSCI (vm4) 1> START EXTRACT dp2

Sending START request to MANAGER ...
EXTRACT DP2 starting

Start Replicat Process

On the target server, start the rep2 replicat process:
[oracle@vm4]$ ggsci

Sending START request to MANAGER ...
REPLICAT REP1 starting

References

Some useful references include: