5 Using Oracle GoldenGate for Live Reporting
5.5 Creating a Cascading Reporting Configuration
5.5 Creating a Cascading Reporting Configuration
Oracle GoldenGate supports cascading synchronization, where Oracle GoldenGate propagates data changes from the source database to a second database, and then on to a third database. In this configuration:
■ A primary Extract on the source writes captured data to a local trail, and a data pump sends the data to a remote trail on the second system in the cascade.
■ On the second system, Replicat applies the data to the local database.
■ Another primary Extract on that same system captures the data from the local database and writes it to a local trail.
■ A data pump sends the data to a remote trail on the third system in the cascade, where it is applied to the local database by another Replicat.
Note: See Section 5.4, "Creating a Reporting Configuration with a Data Pump on an Intermediary System" if you do not need to apply the replicated changes to a database on the secondary system.
Figure 5–4 Cascading Configuration
Use this configuration if:
■ One or more of the target systems does not have a direct connection to the source, but the second system can connect in both directions.
■ You want to limit network activity from the source system.
■ You are sending data to two or more servers that are very far apart
geographically, such as from Chicago to Los Angeles and then from Los Angeles to servers throughout China.
When considering this topology, take note of the following:
■ This configuration can be used to perform data filtering and conversion if the character sets on all systems are identical. If character sets differ, a 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 second system to perform data conversion and transformation, assuming character sets are identical, you must create a source definitions file on the first system with the DEFGEN utility and then transfer it to the second system. Additionally, you must create a source definitions file on the second system and transfer it to the third system. See Chapter 13, "Associating Replicated Data with Metadata" for more information about definitions files and conversion.
■ On the second system, you must configure the Extract group to capture Replicat activity and to ignore local business application activity. The Extract parameters that control this behavior are IGNOREAPPLOPS and GETREPLICATES.
5.5.1 Source System
Refer to Figure 5–4 for a visual representation of the objects you will be creating.
To configure the Manager process on the source
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_1.
ADD EXTRACT ext_1, {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.
ADD EXTTRAIL local_trail_1, EXTRACT ext_1
Use the EXTRACT argument to link this trail to the ext_1 Extract group.
3. On the source, use the EDIT PARAMS command to create a parameter file for the ext_1 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_1
-- 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_1, 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 second system in the cascade.
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 if using NOPASSTHROUGH:
[SOURCEDB dsn_1][, USERIDALIAS alias]
-- Decrypt the data only if the data pump must process it.
-- DECRYPTTRAIL
-- Specify the name or IP address of second system in cascade -- and optional encryption of data over TCP/IP:
RMTHOST target_1, MGRPORT port_number, ENCRYPT encryption_options
-- Specify the remote trail and encryption algorithm on the second 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.5.2 Second System in the Cascade
Configure the Manager process, Replicat group, and data pump on the second system in the cascade.
To configure the Manager process on the second system
1. On the second 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 second system
1. Create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Section 14.3, "Creating a Checkpoint Table" for instructions.
2. On the second system, use the ADD REPLICAT command to create a Replicat group.
For documentation purposes, this group is called rep_1.
ADD REPLICAT rep_1
[, INTEGRATED | COORDINATED [MAXTHREADS number]]
, EXTTRAIL remote_trail_1, , BEGIN time
Use the EXTTRAIL option to link the rep_1 group to the remote trail remote_trail_1 that is on the local system.
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.
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 second system, 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_1
-- 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])]
;
To configure an Extract group on the second system
1. On the source, use the ADD EXTRACT command to create an Extract group. For documentation purposes, this group is called ext_2.
ADD EXTRACT ext_2, {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 second system, use the ADD EXTTRAIL command to specify a local trail that will be created on the third system.
ADD EXTTRAIL local_trail_2, EXTRACT ext_2
Use the EXTRACT argument to link this local trail to the ext_2 Extract group.
3. On the second system, use the EDIT PARAMS command to create a parameter file for the ext_2 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_2
-- Specify database login information as needed for the database:
[SOURCEDB dsn_2][, 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_2
-- Ignore local DML, capture Replicat DML:
IGNOREAPPLOPS, GETREPLICATES
-- Specify tables and sequences to be captured:
SEQUENCE [container.|catalog.]owner.sequence;
TABLE [container.|catalog.]owner.table;
To configure the data pump on the second system
1. On the second 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_2, BEGIN time
Use EXTTRAILSOURCE as the data source option, and specify the name of the local trail.
2. On the second system, use the ADD RMTTRAIL command to specify a remote trail that will be created on the third system in the cascade.
ADD RMTTRAIL remote_trail_2, EXTRACT pump_2
Use the EXTRACT argument to link the remote trail to the pump_2 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 second 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
-- Specify database login information if using NOPASSTHRU:
[SOURCEDB dsn_2][, USERIDALIAS alias]
-- Decrypt the data only if the data pump must process it.
-- DECRYPTTRAIL
-- Specify the name or IP address of third system in cascade -- 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 third 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;
5.5.3 Third System in the Cascade
Configure the Manager process and Replicat group on the third system in the cascade.
Note: If replicating DDL operations, IGNOREAPPLOPS, GETREPLICATES functionality is controlled by the DDLOPTIONS parameter.
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.
To configure the Manager process
1. On the third 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
1. On the third system, create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Section 14.3, "Creating a Checkpoint Table" for instructions.
2. On the third system, use the ADD REPLICAT command to create a Replicat group.
For documentation purposes, this group is called rep_2.
ADD REPLICAT rep_2
[, INTEGRATED | COORDINATED [MAXTHREADS number]]
, EXTTRAIL remote_trail_2, , BEGIN time
Use the EXTTRAIL option to link the rep_2 group to the remote_trail_2 trail.
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 third system, 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_2
-- 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_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])]
;
6
6