5 Using Oracle GoldenGate for Live Reporting
5.4 Creating a Reporting Configuration with a Data Pump on an Intermediary System
5.4 Creating a Reporting Configuration with a Data Pump on an Intermediary System
You can use an intermediary system as a transfer point between the source and target systems. In this configuration, a data pump on the source system sends captured data to a remote trail on the intermediary system. A data pump on the intermediary system reads the trail and sends the data to a remote trail on the target. A Replicat on the target reads the remote trail and applies the data to the target database.
Figure 5–3 Configuration Elements for Replication through an Intermediary System
When considering this topology, take note of the following:
■ This configuration is practical if the source and target systems are in different networks and there is no direct connection between them. You can transfer the data through an intermediary system that can connect to both systems.
■ This configuration can be used to add storage flexibility to compensate for deficiences on the source or target.
■ This configuration can be used to perform data filtering and conversion if the character sets on all systems are identical. If character sets differ, the data pump cannot perform conversion between character sets, and you must configure Replicat to perform the conversion and transformation on the target.
■ To use the data pump on the intermediary system to perform data conversion and transformation, assuming character sets are identical, you must create a source definitions file and a target definitions file with the DEFGEN utility and then transfer both files to the intermediary system. See Chapter 13, "Associating Replicated Data with Metadata" for more information about definitions files and conversion.
■ This configuration is a form of cascaded replication. However, in this
configuration, data is not applied to a database on the intermediary system. See Section 5.5, "Creating a Cascading Reporting Configuration" to include a database on the intermediary system in the Oracle GoldenGate configuration.
5.4.1 Source System
Refer to Figure 10 for a visual representation of the objects you will be creating.
To configure the Manager process
1. On the source, configure the Manager process according to the instructions in Chapter 3, "Configuring Manager and Network Communications".
2. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.
To configure the primary Extract group on the source
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 Windows and UNIX 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. The primary Extract writes to this trail, and the data-pump Extract reads it.
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 group reads 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;
To configure the data pump on the source
1. On the source, use the ADD EXTRACT command to create a data pump group. For documentation purposes, this group is called pump_1.
ADD EXTRACT pump_1, EXTTRAILSOURCE local_trail, BEGIN time
Use EXTTRAILSOURCE as the data source option, and specify 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 the intermediary system.
ADD RMTTRAIL remote_trail_1, EXTRACT pump_1
Use the EXTRACT argument to link the remote trail to the pump_1 data pump group.
The linked data pump writes to this trail.
See Reference for Oracle GoldenGate for Windows and UNIX for additional ADD RMTTRAIL options.
3. On the source, use the EDIT PARAMS command to create a parameter file for the pump_1 data pump. Include the following parameters plus any others that apply to your database environment.
-- 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 intermediary system -- and optional encryption of data over TCP/IP:
RMTHOST target_1, MGRPORT port_number, ENCRYPT encryption_options -- Specify remote trail and encryption algorithm on intermediary system:
ENCRYPTTRAIL algorithm RMTTRAIL remote_trail_1
-- Allow mapping, filtering, conversion or pass data through as-is:
[PASSTHRU | NOPASSTHRU]
-- Specify tables and sequences to be captured:
SEQUENCE [container.|catalog.]owner.sequence;
TABLE [container.|catalog.]owner.table;
5.4.2 Intermediary System
Configure the Manager process and data pump on the intermediary system.
To configure the Manager process on the intermediary system
1. On the intermediary system, configure the Manager process according to the instructions in Chapter 3, "Configuring Manager and Network Communications".
2. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.
To configure the data pump on the intermediary system
1. On the intermediary system, use the ADD EXTRACT command to create a data-pump group. For documentation purposes, this group is called pump_2.
ADD EXTRACT pump_2, EXTTRAILSOURCE local_trail_1, BEGIN time
Use EXTTRAILSOURCE as the data source option, and specify the name of the trail that you created on this system
2. On the intermediary system, use the ADD RMTTRAIL command to specify a remote trail on the target system.
ADD RMTTRAIL remote_trail_2, EXTRACT pump_2
Use the EXTRACT argument to link the remote trail to the pump_2 data pump. The linked data pump writes to this trail.
See Reference for Oracle GoldenGate for Windows and UNIX for additional ADD RMTTRAIL options.
Note: To use PASSTHRU mode, the names of the source and target objects must be identical. No column mapping, filtering, SQLEXEC functions, transformation, or other functions that require data manipulation can be specified in the parameter file. You can combine normal processing with pass-through processing by pairing PASSTHRU and NOPASSTHRU with different TABLE statements.
3. On the intermediary system, use the EDIT PARAMS command to create a parameter file for the pump_2 data pump. Include the following parameters plus any others that apply to your database environment.
-- Identify the data pump group:
EXTRACT pump_2
-- Note that no database login parameters are required in this case.
-- State whether or not source and target definitions are identical:
SOURCEDEFS full_pathname | ASSUMETARGETDEFS
-- Specify the target definitions file if SOURCEDEFS was used:
TARGETDEFS full_pathname
-- Decrypt the data only if the data pump must process it.
-- DECRYPTTRAIL
-- Specify the name or IP address of the target system -- and optional encryption of data over TCP/IP:
RMTHOST target_2, MGRPORT port_number, ENCRYPT encryption_options
-- Specify the remote trail and encryption algorithm on the target system:
ENCRYPTTRAIL algorithm RMTTRAIL remote_trail_2
-- Allow mapping, filtering, conversion or pass data through as-is;
[PASSTHRU | NOPASSTHRU]
-- Specify tables and sequences to be captured:
SEQUENCE [container.|catalog.]owner.sequence;
TABLE [container.|catalog.]owner.table;
■ Use SOURCEDEFS and TARGETDEFS to specify the definitions files if the data pump will perform conversion and transformation.
■ Use NOPASSTHRU (the default) if the data pump will perform conversion and transformation. Otherwise, use PASSTHRU.
5.4.3 Target System
Configure the Manager process and Replicat group on the target system.
To configure the Manager process on the target
1. On the target system, configure the Manager process according to the instructions in Chapter 3, "Configuring Manager and Network Communications".
2. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.
To configure the Replicat group on the target
1. On the target, create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Section 14.3, "Creating a Checkpoint Table" for instructions.
2. On the target, use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep.
ADD REPLICAT rep
[, INTEGRATED | COORDINATED [MAXTHREADS number]]
, EXTTRAIL remote_trail_2, , BEGIN time
Use the EXTTRAIL argument to link the Replicat group to the trail on this system.
See Reference for Oracle GoldenGate for Windows and UNIX for detailed information about these and other options that may be required for your installation.
3. On the target, use the EDIT PARAMS command to create a parameter file for the Replicat 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 Replicat group:
REPLICAT rep
-- State whether or not source and target definitions are identical:
SOURCEDEFS full_pathname | ASSUMETARGETDEFS
-- 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])]
;