통합모드 ogg

[송지환]

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.

SourceTarget

Hostname	vm4	vm5
Database Name	NORTH	SOUTH

The configuration includes the following on both nodes:

• Creation of a GoldenGate schema owner called GG01.
• Specification of GGSCHEMA as GG01 in GoldenGate parameters
• Creation of GOLDENGATE tablespace which is default tablespace for GG01

The GoldenGate process names are:

SourceTarget

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:

• $ORACLE_HOME/rdbms/admin/prvtlmcb.plb
• $ORACLE_HOME/rdbms/admin/prvthxstr.plb
• $ORACLE_HOME/rdbms/admin/prvtbxstr.plb
• $ORACLE_HOME/rdbms/admin/prvtbcap.plb
• $ORACLE_HOME/rdbms/admin/prvtbapp.plb

The contents of these files are summarized below:

$ORACLE_HOME/rdbms/admin/prvtlmcb.plb

Object TypeOwnerObject 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 TypeOwnerObject 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 TypeOwnerObject Name

PACKAGE	SYS	dbms_capture_adm
PACKAGE	SYS	dbms_capture_adm_internal
PACKAGE	SYS	dbms_capture_process

$ORACLE_HOME/rdbms/admin/prvtbapp.plb

Object TypeOwnerObject 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

• I did not want to increase the amount of physical memory allocated to each VM on my 8GB laptop.
• Within VirtualBox 2GB of physical memory is allocated to each VM.
• I wanted to use Automatic Memory Management (AMM)

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; / ConfigurationRegister 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

     startingStart Data Pump ProcessOn the source server, start the dp2 extract process:[oracle@vm4]

 $ ggsci GGSCI (vm4) 1> START EXTRACT dp2 Sending START request to MANAGER ... EXTRACT DP2 startingStart       Replicat ProcessOn the target server, start the rep2 replicat process:[oracle@vm4]$ ggsci Sending START request to MANAGER ... REPLICAT REP1 starting