4 Using Oracle GoldenGate for Real-time Data Distribution

This chapter describes the usage of Oracle GoldenGate for real-time data distribution.

Topics:

4.1 Overview of the Data-distribution Configuration

A data distribution configuration is a one-to-many configuration. Oracle GoldenGate supports synchronization of a source database to any number of target systems. Oracle GoldenGate supports like-to-like or heterogeneous transfer of data, with capabilities for filtering and conversion on any system in the configuration (support varies by database platform).

4.2 Considerations for a Data-distribution Configuration

These sections describe considerations for a data-distribution configuration.

4.2.1 Fault Tolerance

For a data distribution configuration, the use of data pumps on the source system ensures that if network connectivity to any of the targets fails, the captured data still can be sent to the other targets. Use a primary Extract group and one data-pump Extract group for each target.

4.2.2 Filtering and Conversion

You can use any process to perform filtering and conversion. However, using the data pumps to perform filtering operations removes that processing overhead from the primary Extract group, and it reduces the amount of data that is sent across the network. See Mapping and Manipulating Data for filtering and conversion options.

4.2.3 Read-only vs. High Availability

The data distribution configuration supports read-only targets. See Configuring Oracle GoldenGate for Active-Active Configuration if any target in this configuration will also be used for transactional activity in support of high availability.

4.2.4 Additional Information

The following documentation provides additional information of relevance to configuring Oracle GoldenGate.

4.3 Creating a Data Distribution Configuration

Refer to Figure 4-1 for a visual representation of the objects you will be creating.

Figure 4-1 Oracle GoldenGate Configuration Elements for Data Distribution

Description of Figure 4-1 follows
Description of "Figure 4-1 Oracle GoldenGate Configuration Elements for Data Distribution"

4.3.1 Source System

Configure the Manager process and primary Extract on the source system.

To Configure the Manager Process

  1. On the source, configure the Manager process. See Configuring Manager and Network Communications for instructions.

  2. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the local trail.

To Configure the Primary Extract

  1. On the source, use the ADD EXTRACT command to create an Extract group. For documentation purposes, this group is called ext.

    ADD EXTRACT ext, {TRANLOG | INTEGRATED TRANLOG}, BEGIN time [option[, ...]]
    

    See Reference for Oracle GoldenGate for detailed information about these and other ADD EXTRACT options that may be required for your installation.

  2. On the source, use the ADD EXTTRAIL command to create a local trail.

    ADD EXTTRAIL local_trail, EXTRACT ext
    

    Use the EXTRACT argument to link this trail to the primary Extract group. The primary Extract group writes to this trail, and the data pump groups read it

  3. On the source, use the EDIT PARAMS command to create a parameter file for the primary Extract group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

    -- Identify the Extract group:
    EXTRACT ext
    -- Specify database login information as needed for the database:
    [SOURCEDB dsn_1][, USERIDALIAS alias]
    -- Log all scheduling columns if using integrated Replicat
    LOGALLSUPCOLS
    -- Specify the local trail that this Extract writes to
    -- and encryption algorithm:
    ENCRYPTTRAIL algorithm
    EXTTRAIL local_trail
    -- Specify tables and sequences to be captured:
    SEQUENCE [container.|catalog.]owner.sequence;
    TABLE [container.|catalog.]owner.table;
    

Use EXTTRAIL to specify the local trail.

To Configure the Data Pump Extract Groups

  1. On the source, use the ADD EXTRACT command to create a data pump for each target system. For documentation purposes, these groups are called pump_1 and pump_2.
    ADD EXTRACT pump_1, EXTTRAILSOURCE local_trail, BEGIN time
    ADD EXTRACT pump_2, EXTTRAILSOURCE local_trail, BEGIN time
    

    Use EXTTRAILSOURCE as the data source option, and supply the name of the local trail.

  2. On the source, use the ADD RMTTRAIL command to specify a remote trail that will be created on each of the target systems.
    ADD RMTTRAIL remote_trail_1, EXTRACT pump_1
    ADD RMTTRAIL remote_trail_2, EXTRACT pump_2
    

    Use the EXTRACT argument to link each remote trail to a different data pump group. The linked data pump writes to this trail.

    See Reference for Oracle GoldenGate for additional ADD RMTTRAIL options.

  3. On the source, use the EDIT PARAMS command to create a parameter file for each of the data pumps. Include the following parameters plus any others that apply to your database environment.

    Parameter file for pump_1:

    -- Identify the data pump group:
    EXTRACT pump_1
    -- Specify database login information:
    [SOURCEDB dsn_1][, USERIDALIAS alias]
    -- Decrypt the data only if the data pump must process it.
    -- DECRYPTTRAIL
    -- Specify the name or IP address of the first target system
    -- and optional encryption of data over TCP/IP:
    RMTHOSTOPTIONS target_1, MGRPORT port_number, ENCRYPT encryption_options
    -- Specify remote trail and encryption algorithm on first target system:
    ENCRYPTTRAIL algorithm
    RMTTRAIL remote_trail_1
    -- Specify tables and sequences to be captured:
    SEQUENCE [container.|catalog.]owner.sequence;
    TABLE [container.|catalog.]owner.table;
    

    Parameter file for pump_2:

    -- Identify the data pump group:
    EXTRACT pump_2
    -- Specify database login information as needed for the database:
    [SOURCEDB dsn_1][, USERIDALIAS alias]
    -- Decrypt the data only if the data pump must process it.
    -- DECRYPTTRAIL
    -- Specify the name or IP address of the second target system
    -- and optional encryption of data over TCP/IP:
    RMTHOSTOPTIONS target_2, MGRPORT port_number, ENCRYPT encryption_options
    -- Specify remote trail and encryption algorithm on second target system:
    ENCRYPTTRAIL algorithm
    RMTTRAIL remote_trail_2
    -- Specify tables and sequences to be captured:
    SEQUENCE [container.|catalog.]owner.sequence;
    TABLE [container.|catalog.]owner.table;

4.3.2 Target Systems

Configure the Manager process and Replicat groups on the target systems.

To Configure the Manager Process

  1. On each target, configure the Manager process. See Configuring Manager and Network Communications for instructions.

  2. In each Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.

To Configure the Replicat Groups

  1. On each target, create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Creating a Checkpoint Table for instructions.
  2. On each target, use the ADD REPLICAT command to create a Replicat group for the remote trail on that system. For documentation purposes, these groups are called rep_1 and rep_2.

    Command on target_1:

    ADD REPLICAT rep_1 
    [, INTEGRATED | COORDINATED [MAXTHREADS number]]
    , EXTTRAIL remote_trail_1, BEGIN time
    

    Command on target_2:

    ADD REPLICAT rep_2
    [, INTEGRATED | COORDINATED [MAXTHREADS number]]
    , EXTTRAIL remote_trail_2, BEGIN time
    

    Use the EXTTRAIL argument to link the Replicat group to the correct trail.

    See Reference for Oracle GoldenGate for detailed information about these and other options that may be required for your installation.

  3. On each target, use the EDIT PARAMS command to create a parameter file for the Replicat group. Use the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

    Parameter file for rep_1:

    -- Identify the Replicat group:
    REPLICAT rep_1
    -- Specify database login information as needed for the database:
    [TARGETDB dsn_2][, USERIDALIAS alias]
    -- Specify error handling rules:
    REPERROR (error, response)
    -- Specify tables for delivery and threads if using coordinated Replicat:
    MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
    [, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
    ;
    

    Parameter file for rep_2:

    -- Identify the Replicat group:
    REPLICAT rep_2
    -- Specify database login information as needed for the database:
    [TARGETDB dsn_3][, USERIDALIAS alias]
    -- Specify error handling rules:
    REPERROR (error, response)
    -- Specify tables for delivery and threads if using coordinated Replicat:
    MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
    [, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
    ;
    

    You can use any number of MAP statements for any given Replicat group. All MAP statements for a given Replicat group must specify the same objects that are contained in the trail that is linked to the group.