This chapter describes how to work with Oracle GoldenGate in order to capture changes on source transactional systems and replicate them in a staging server for consumption by Oracle Data Integrator mappings.
This chapter includes the following sections:
Oracle GoldenGate (OGG) product offers solutions that provide key business applications with continuous availability and real-time information. It provides guaranteed capture, routing, transformation and delivery across heterogeneous databases and environments in real-time.
Using the Oracle GoldenGate knowledge modules requires that you know and understand Oracle GoldenGate concepts and architecture. See the Oracle GoldenGate Documentation on OTN for more information:
http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html
Oracle Data Integrator can capture changes in a source database using Oracle GoldenGate to process them in the ODI CDC framework. Oracle Data Integrator uses Oracle GoldenGate to replicate data from a source database to a staging database. This staging database contains a copy of the source tables and the ODI Changed Data Capture (CDC) infrastructure, both loaded using Oracle GoldenGate.
The staging database can be stored in an Oracle or Teradata schema. The source database can be Oracle, Microsoft SQL Server, DB2 UDB, or Sybase ASE. In this chapter, <database> refers to any of these source database technologies.
Setting up CDC with GoldenGate is done using the following process:
A replica of the source tables is created in the staging database, using, for example, the Oracle Data Integrator Common Format Designer feature.
Oracle Data Integrator Changed Data Capture (CDC) is activated on the source tables using either the JKM <database> to Oracle Consistent (OGG Online) or the JKM <database> to Teradata Consistent (OGG Online).
The journals are started in either online mode or offline mode.
Online mode: Starting the journals in online mode configures and starts the GoldenGate Capture (Extract) process to capture the changes in the source database and corresponding Delivery (Replicat) processes to replicate the changes in the staging database. Changes are replicated into both the replicated source table and the CDC infrastructure.
The GoldenGate Capture and Delivery processes are deployed and started using the GoldenGate JAgent interface. The GoldenGate JAgent facilitates communication between Oracle Data Integrator and Oracle GoldenGate.
Offline mode: Starting the journals in offline mode creates the Oracle GoldenGate configuration files and sets up a CDC infrastructure in the staging database. Note that no active process is started for capturing source data at this stage.
Using the generated configuration files, an Oracle GoldenGate Capture process is configured and started to capture changes from the source database, and corresponding Delivery processes are configured and started to replicate these changes into the staging database. Changes are replicated into both the replicated source table and the CDC infrastructure.
GoldenGate can optionally be configured to perform the initial load of the source data into the staging tables.
ODI mappings can source from the replicated tables and use captured changes seamlessly within any ODI scenario.
Oracle Data Integrator provides the Knowledge Modules listed in Table 20-1 for replicating online data from a source to a staging database. Like any other CDC JKMs, the Oracle GoldenGate JKMs journalize data in the source server.
The JKM <database> to Oracle Consistent (OGG Online) and the JKM <database> to Teradata Consistent (OGG Online) perform the same tasks:
Create and manage the ODI CDC framework infrastructure on the replicated tables.
If the journals are started in online mode, configure and start the Oracle Capture and Delivery processes on the GoldenGate servers using the GoldenGate JAgent.
If the journals are started in offline mode, generate the parameter files to set up the Oracle GoldenGate Capture and Delivery processes and the Readme.txt explaining how to complete the setup.
Provide extra steps to check the configuration of the source database and proposes tips to correct the configuration.
Table 20-1 Oracle GoldenGate Knowledge Modules
| Knowledge Module | Description |
|---|---|
|
JKM Oracle to Oracle Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on an Oracle staging server and generates the Oracle GoldenGate configuration for replicating data from an Oracle source to this staging server. |
|
JKM DB2 UDB to Oracle Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on an Oracle staging server and generates the Oracle GoldenGate configuration for replicating data from an IBM DB2 UDB source to this staging server. |
|
JKM Sybase ASE to Oracle Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on an Oracle staging server and generates the Oracle GoldenGate configuration for replicating data from a Sybase ASE source to this staging server. |
|
JKM MSSQL to Oracle Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on an Oracle staging server and generates the Oracle GoldenGate configuration for replicating data from a Microsoft SQL Server source to this staging server. |
|
JKM Oracle to Teradata Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on a Teradata staging server and generates the Oracle GoldenGate configuration for replicating data from an Oracle source to this staging server. |
|
JKM DB2 UDB to Teradata Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on a Teradata staging server and generates the Oracle GoldenGate configuration for replicating data from an IBM DB2 UDB source to this staging server. |
|
JKM Sybase ASE to Teradata Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on a Teradata staging server and generates the Oracle GoldenGate configuration for replicating data from a Sybase ASE source to this staging server. |
|
JKM MSSQL to Teradata Consistent (OGG Online) |
Creates the infrastructure for consistent set journalizing on a Teradata staging server and generates the Oracle GoldenGate configuration for replicating data from a Microsoft SQL Server source to this staging server. |
Make sure you have read the information in this section before you start using the Oracle GoldenGate Knowledge Modules:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html.
See also the Oracle GoldenGate documentation on OTN for source and staging database version platform support.
In order to run the Capture and Delivery processes, Oracle GoldenGate must be installed on both the source and staging servers. Installing Oracle GoldenGate installs all of the components required to run and manage GoldenGate processes.
Oracle GoldenGate Manager Process must be running on each system before Capture or Delivery can be started, and must remain running during their execution for resource management.
In order to perform online journalizing, the Oracle GoldenGate JAgent process must be configured and running on the Oracle GoldenGate instances.
Oracle GoldenGate has specific requirement and installation instructions that must be performed before starting the Capture and Delivery processes configured with the Oracle GoldenGate JKMs. See the Oracle GoldenGate Documentation on OTN for more information.
If the source database is Oracle, there are no connectivity requirements for using Oracle GoldenGate data in Oracle Data Integrator.
If the source database is IBM DB2 UDB, Microsoft SQL Server, or Sybase ASE, Oracle GoldenGate uses the ODBC driver to connect to the source database. You need to install the ODBC driver and to declare the data source in your system. You also need to set the data source name (DSN) in the KM option SRC_DSN.
To use the JKM <database> to Oracle Consistent (OGG Online) or the JKM <database> to Teradata Consistent (OGG Online) in your Oracle Data Integrator integration projects, you need to perform the following steps:
This step consists in declaring in Oracle Data Integrator the staging data server, the source data server, as well as the physical and logical schemas attached to these servers.
To define the topology in this configuration, perform the following tasks:
You have to define a source data server from which Oracle GoldenGate will capture changes.
Create a data server for your source technology using the standard procedure. For more information, see the chapter corresponding to your source technology in this guide:
This data server represents the source database instance.
Create a physical schema under the data server that you have created in Section 20.3.1.1, "Define the Source Data Server". Use the standard procedure, as described in "Creating a Physical Schema" of the Developer's Guide for Oracle Data Integrator.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Developer's Guide for Oracle Data Integratorand associate it in a given context.
Create a data server for the Oracle or Teradata technology. For more information, see:
Create an Oracle or Teradata physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Developer's Guide for Oracle Data Integrator.
Note:
The physical schema defined in the staging server will contain in the data schema the changed records captured and replicated by the Oracle GoldenGate processes. The work schema will be used to store the ODI CDC infrastructure.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the the Developer's Guide for Oracle Data Integrator and associate it in a given context.
An Oracle GoldenGate data server corresponds to the Oracle GoldenGate JAgent process in Oracle Data Integrator (ODI). The Oracle GoldenGate JAgent process facilitates communication between ODI and the Oracle GoldenGate servers. You must create a JAgent process for both the source and the target Oracle GoldenGate servers.
Create a data server for the Oracle GoldenGate technology using the standard procedure, as described in "Creating a Data Server" of the Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining an Oracle GoldenGate data server:
In the Definition tab:
Name: Name of the data server that will appear in the Oracle Data Integrator.
Host: Hostname or the IP address of the server where the JAgent process is running.
JMX Port: Port number of the JAgent process.
Manager Port: Port number of the Oracle GoldenGate manager instance.
JMX User: User name to connect to the JAgent.
Password: Password of the user credentials.
Installation Path: Location path for the Oracle GoldenGate installation. You must use this path when you create the capture process definitions from a model.
The Oracle GoldenGate physical schemas in ODI correspond to the GoldenGate Capture and Delivery processes that perform CDC in Oracle GoldenGate. You must define the Oracle GoldenGate physical schemas to configure the Capture process on the source GoldenGate server and Delivery process on the target GoldenGate server.
Create a physical schema under the Oracle GoldenGate data server that you have created in Section 20.3.1.5, " Define the Oracle GoldenGate Data Servers". Use the standard procedure, as described in "Creating a Physical Schema" of the Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific to create the physical schemas to configure the Oracle GoldenGate Capture and Replicate processes.
Note:
Alternatively, you can create the Oracle GoldenGate physical schemas from the model. See Section 20.3.4.1, "Create Oracle GoldenGate Physical Schemas from the model" for information about how to create physical schemas from the model.
GoldenGate Capture Process Fields
Note that the GoldenGate Capture process must be configured on the source GoldenGate server.
In the Process Definition tab:
Process Type: Type of the process that you want to configure. Select Capture as the process type.
Name: Name of the process (physical schema) in Oracle Data Integrator. Process name cannot exceed 8 characters and only upper case is allowed.
Trail File Path: Location of the Oracle GoldenGate trail file. Only two characters for the file name part are allowed.
Remote Trail File Path: Location of the remote trail file. Only two characters for the file name part are allowed.
Trail File Size: Size of the Oracle GoldenGate trail file in Megabytes.
Report Fetch: Enables report information to include the fetching statistics.
Report Count Frequency: Reports the total operations count at specific intervals. If the interval is not specified the entry is not added to the parameter file.
Select a parameter: List of available Oracle GoldenGate parameters. Only the parameters for the supported database are listed. Select a parameter and click Add. A template of the selected parameter is added to the text box.
See the Oracle GoldenGate Reference Guide on OTN for information about the GoldenGate parameters.
Delivery Process Fields
Note that the GoldenGate Delivery process must be configured on the target GoldenGate server.
In the Process Definition tab:
Process Type: Type of the process that you want to configure. Select Delivery as the process type.
Name: Name of the process (physical schema) in Oracle Data Integrator. Process name cannot exceed 8 characters and only uppercase is allowed.
Trail File Path: Location of the trail file. Only two characters for the filename part are allowed.
Discard File Path: Location of the discard file.
Definition File Path: Location of the definition file.
Report Detail: Enables report information to include any collision counts.
Report Count Frequency: Report the total operations count at specific intervals. If the interval is not specified the entry is not added to the parameter file.
Select a parameter: List of available Oracle GoldenGate parameters. Only the parameters for the supported database are listed. Select a parameter and click Add.
See the Oracle GoldenGate Reference Guide on OTN for information about the GoldenGate parameters.
Create logical schemas for the GoldenGate physical schemas (GoldenGate Capture and Delivery processes) that you created in section Section 20.3.1.6, "Create the Oracle GoldenGate Physical Schemas". You must create a logical schema for both the Capture process and the Delivery process.
To create logical schemas:
In the Topology Navigator expand the Technologies node in the Logical Architecture accordion.
Right-click Oracle GoldenGate and select New Logical Schema.
Fill in the Logical Schema Name.
Select the appropriate process type, either Capture or Delivery, to which you want to attach your logical schema.
For each Context in the left column, select an existing Physical Schema in the right column. This Physical Schema is automatically associated to the logical schema in this context. Repeat this operation for all necessary contexts.
From File menu, click Save.
Oracle GoldenGate will replicate in the staging server the records changed in the source. In order to perform this replication, the source table structures must be replicated in the staging server.
To replicate these source tables:
Create a new Data Model using the Oracle or Teradata technology. This model must use the logical schema created using the instructions in Section 20.3.1.4, "Create the Staging Physical Schema".
See "Creating a Model" in the Developer's Guide for Oracle Data Integratorfor more information on model creation.
Note that you do not need to reverse-engineer this data model.
Create a new diagram for this model and add to this diagram the source tables that you want to replicate.
Generate the DDL Scripts and run these scripts for creating the tables in the staging data server.
An initial load of the source data can be made to replicate this data into the staging tables. You can perform this initial load with ODI using the Generate Interface IN feature of Common Format Designer. Alternately, you can use Oracle GoldenGate to perform this initial load, by specifying a capture or delivery process to perform the initial load or by setting the USE_OGG_FOR_INIT JKM option to Yes to create a process to perform the initial load when you Configure CDC for the Source Datastores.
Note:
See "Creating Data Models with Common Format Designer" in the Developer's Guide for Oracle Data Integrator for more information on diagrams, generating DDL, and generating Interface IN features.
Setting up a project using Oracle GoldenGate features follows the standard procedure. See "Creating an Integration Project" of the Developer's Guide for Oracle Data Integrator.
Depending on the technology of your source data server and staging server, import one of the following KMs into your project:
JKM Oracle to Oracle Consistent (OGG Online)
JKM DB2 UDB to Oracle Consistent (OGG Online)
JKM Sybase ASE to Oracle Consistent (OGG Online)
JKM MSSQL to Oracle Consistent (OGG Online)
JKM Oracle to Teradata Consistent (OGG Online)
JKM DB2 UDB to Teradata Consistent (OGG Online)
JKM Sybase ASE to Teradata Consistent (OGG Online)
JKM MSSQL to Teradata Consistent (OGG Online)
Changed Data Capture must be configured for the source datastores. This configuration is similar to setting up consistent set journalizing and is performed using the following steps.
Edit the data model that contains the source datastore. In the Journalizing tab of the data model, set the Journalizing Mode to Consistent Set and select the appropriate JKM <database> to Oracle Consistent (OGG Online) or JKM <database> to Teradata Consistent (OGG Online).
Select the following GoldenGate processes (physical schemas) using the process selection drop-down list:
Capture Process
Delivery Process
Initial Load Capture Process
Initial Load Delivery Process
If you do not want to use an existing GoldenGate process, you can create new processes from here using the Create button next to the <Process Name> field. See Section 20.3.4.1, "Create Oracle GoldenGate Physical Schemas from the model" for information about how to create GoldenGate processes from the model.
Set the KM options as follows:
ONLINE: If you set this option to true, the JKM configures the CDC infrastructure and configures and starts the GoldenGate Capture and Delivery processes. If you set this option to false, the JKM generates the CDC infrastructure and the configuration files that are required to set up the GoldenGate Capture and Delivery processes. It also generates the Readme.txt that contains the instructions to configure and start the GoldenGate processes.
For more information about online and offline mode, see Section 20.1.1, "Overview of the GoldeGate CDC Process".
For information about how to configure and start GoldenGate processes using the configuration files, see Section 20.3.5, "Configure and Start Oracle GoldenGate Processes (Offline mode only)".
LOCAL_TEMP_DIR: Full path to a temporary folder into which the Oracle GoldenGate configuration files will be generated
SRC_DSN: Name of the data source. This KM option is required when the ODBC driver is used. Note that this option does not exist in the JKM Oracle to Oracle Consistent (OGG Online).
Note:
For Sybase users only: When defining the data source name, you have to add the database server name to the datasource name as follows:
DSN_name@SYBASE_DBSERVER
USE_OGG_FOR_INIT: Applicable for offline mode only. Generate the Oracle GoldenGate processes to perform the initial load of the replicated tables. If you have performed this initial load using Oracle Data Integrator while Creating the Replicated Tables, you can leave this option to NO.
Select the datastores that you want to replicate or the model if want to replicate all datastores, right-click then select Changed Data Capture > Add to CDC.
Select the model, right-click then select Changed Data Capture > Subscriber > Subscribe. Add subscribers for this model.
Select the model, right-click then select Changed Data Capture > Start Journal. If journals are started in online mode (ONLINE option for the JKM is set to true), the JKM creates the CDC infrastructure and configures and starts the Oracle GoldenGate processes. If journals are started in offline mode (ONLINE option for the JKM is set to false), the JKM creates the CDC infrastructure and generates the configuration files that are required to configure the Oracle GoldenGate processes. It also generates Readme.txt that contains the instructions to configure and start the GoldenGate processes.
For information about how to configure and start GoldenGate processes, see Section 20.3.5, "Configure and Start Oracle GoldenGate Processes (Offline mode only)".
You can review the result of the journal startup action:
If journals are started in online mode, the Oracle GoldenGate processes are configured and started. The changed data in the source datastores is captured and replicated in the staging tables.
If the journals are started in offline mode, the Oracle GoldenGate configuration files, as well as a Readme.txt file are generated in the directory that is specified in the LOCAL_TEMP_DIR KM option. You can use these files to Configure and Start Oracle GoldenGate Processes (Offline mode only).
The CDC infrastructure is set up correctly. The journalized datastores appear in the Models accordion with a Journalizing Active flag. You can right-click the model and select Changed Data Capture > Journal Data… to access the journalized data for these datastores.
See "Using Journalizing" in the Developer's Guide for Oracle Data Integrator for more conceptual information and detailed instructions on CDC.
Note:
Although this CDC configuration supports consistent set journalizing, it is not required to order datastores in the Journalized Tables tab of the model after adding them to CDC.
You can create the Oracle GoldenGate physical schemas for the following GoldenGate processes from the Journalizing tab of the Model Editor.
Capture Process
Delivery Process
Initial Capture Process (Capture process to be used for initial load)
Initial Delivery Process (Delivery process to be used for initial load)
When you create the Oracle GoldenGate physical schemas from the models, the default values are derived from the JAgent and the Model details.
To create the Oracle GoldenGate physical schemas from the model:
In the Designer Navigator expand the Models panel.
Expand the Models folder that contains the model from which you want to create the physical schemas.
Right-click the Model and select Open.
Click the Journalizing tab of the Model Editor.
Click Create button next to the Capture Process field.
Select the appropriate JAgent and Context.
Fill in the Process Name and Logical Process Name.
Click OK to create and select the Capture process.
WARNING:
The physical schema generated for the Capture process needs to be changed manually. The Remote Trail File Path property of the physical schema uses the path for the Capture instance and needs to be changed to use the path for the Delivery instance.
Click Create button next to the Delivery Process field.
Select the appropriate JAgent and Context.
Fill in the Process Name and Logical Process Name.
Select the Target Database Logical Schema for the Delivery process.
Click OK.
Similarly, click Create buttons next to the Initial Load Capture Process and Initial Load Delivery Process fields to create physical schemas for them.
Note:
This section is applicable only if the journals are started in offline mode. That means only if the ONLINE option for the JKM is set to false.
The JKM generates in the LOCAL_TEMP_DIR a folder named after the source and target object groups. This folder contains the following:
The Readme.txt file that contains detailed instructions for configuring and starting the Oracle GoldenGate processes.
The src folder that contains configuration files to upload on the source server, in the Oracle GoldenGate installation directory.
The stg folder that contains configuration files to upload on the staging server, in the Oracle GoldenGate installation directory.
The detailed instructions, customized for your configuration, are provided in the readme file.
These instructions include:
Uploading or copying files from the src folder to the source server.
Uploading or copying files from the stg folder to the staging server.
Running on the source server the OBEY file generated by the JKM for starting the Capture process, using the ggsci command line.
Generating on the source server definition file using the defgen command line.
Copying this definition file to the staging server.
If the initial load option is used:
Running on the staging server the OBEY file generated by the JKM for the initial load, using the ggsci command line.
Running on the source server the OBEY file generated by the JKM for the initial load, using the ggsci command line.
Finally Running on the staging server the OBEY file generated by the JKM for the starting the Delivery processes, using the ggsci command line.
See the Oracle GoldenGate documentation on OTN for more information on OBEY files, the ggsci and defgen utilities.
You can use the data in the replicated data as a source in your mappings. This process is similar to using a source datastore journalized in consistent set mode. See "Using Changed Data: Consistent Set Journalizing" in the Developer's Guide for Oracle Data Integrator for more information.
This section includes the following advanced configuration topics:
The staging tables contain a replica of the structure and data from the source tables. The Oracle GoldenGate processes capture changes on the source tables and apply them to the target. Yet the staging tables must be initially loaded with the original content of the source tables. You can use the following methods to perform the initial load:
Using Oracle GoldenGate: A specific GoldenGate process loads the whole content of the source tables into the staging tables.
Using Oracle Data Integrator: The Generate Interfaces IN option of Oracle Data Integrator's Common Format Designer. This method uses ODI mappings to transfer the data.
Using database backup/restore tools to copy data and structures.
The following KM options can be used to improve replication performances:
COMPATIBLE: This Oracle-specific option affects the use of the PURGE key word and the way statistics (using DBMS_STATS or ANALYZE) are collected. Set this value to the database version of your staging server.
NB_APPLY_PROCESS: Number of Oracle GoldenGate Delivery processes created on the staging server.
TRAIL_FILE_SIZE: Size of the Oracle GoldenGate trail file in Megabytes.
For the NB_APPLY_PROCESS and TRAIL_FILE_SIZE parameters, see the Oracle GoldenGate Documentation on OTN for more information on performance tuning.
Note that one source multiple staging configuration can be done only in the offline journalizing mode.
It is possible to set up a configuration where changes are captured on a single source and replicated to several staging servers. The example below illustrates how to set this up in a typical configuration.
Replication should source from source server SRC and replicate in both STG1 and STG2 staging servers.
Edit the source model and ensure that the logical schema for STG1 is selected.
Start the journals in offline mode and follow the instructions in the readme to set up the Oracle GoldenGate processes in SRC and STG1.
Edit the source model again, and select the logical schema for STG2.
Start the journals in offline mode and follow the instructions in the readme to set up the Oracle GoldenGate process in SRC and STG2.
Note:
Playing the configuration on SRC again will not recreate a capture process, trail files, or definition files. It will simply create a new Oracle GoldenGate Datapump process to push data to STG2.