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 |
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
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
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:
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 |
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 |
Object Type | Owner | Object Name |
---|---|---|
PACKAGE | SYS | dbms_capture_adm |
PACKAGE | SYS | dbms_capture_adm_internal |
PACKAGE | SYS | dbms_capture_process |
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 |
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
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.
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; /
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.
On the source server, add a new Extract process called ex2:
GGSCI (vm4) 3> ADD EXTRACT ex2 INTEGRATED TRANLOG, BEGIN NOW EXTRACT added.
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.
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.
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.
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.
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.*;
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.
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.
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
[oracle@vm4]$ ggsci GGSCI (vm4) 1> START EXTRACT dp2 Sending START request to MANAGER ... EXTRACT DP2 starting
[oracle@vm4]$ ggsci Sending START request to MANAGER ... REPLICAT REP1 starting
Some useful references include: