5 Using Oracle GoldenGate for Live Reporting

本章描述了如何使用Oracle GoldenGate进行实时报告。

Topics:

5.1 Overview of the Reporting Configuration

Oracle GoldenGate最基本的配置是一对一的配置，它按一个方向复制:从源数据库复制到仅用于数据检索目的(如报告和分析)的目标数据库。Oracle GoldenGate支持like-to-like或异构数据传输，支持在配置中的任意系统上进行过滤和转换(support varies by database platform).

Description of the illustration simple_config_reporting.jpg

Oracle GoldenGate支持不同的报告拓扑，允许您根据可伸缩性、可用性和性能需求自定义配置流程。本节包含在选择报表配置时要考虑的事项。

5.1.1 Filtering and Conversion

数据过滤和数据转换都会增加开销，而且这些活动有时容易出现配置错误。如果Oracle GoldenGate必须执行大量的过滤和转换，那么可以考虑使用一个或多个数据泵来处理这些工作。您可以使用Replicat实现这个目的，但是这样做会在网络上发送更多的数据，因为它是未经过滤的。通过在数据泵和副本之间划分过滤和转换，可以在两个系统之间进行分割。

To filter data, you can use:

• 表语句(提取)或MAP语句(副本)中的筛选器或WHERE子句
  一个SQL查询或过程
  用户出口

To transform data, you can use:

• 说明:Oracle GoldenGate转换函数。-
  从从外部转换解决方案应用规则的Extract或Replicat流程中退出的用户，然后将处理后的数据返回给Oracle GoldenGate
  将数据直接交付到ETL解决方案或其他转换引擎

For more information about Oracle GoldenGate's filtering and conversion support, see:

• Mapping and Manipulating Data

• Customizing Oracle GoldenGate Processing

5.1.2 Read-only vs. High Availability

Oracle GoldenGate实时报告配置支持只读目标。See Configuring Oracle GoldenGate for Active-Active High Availability if the target in this configuration will also be used for transactional activity in support of high availability.

5.1.3 Additional Information

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

• For additional system requirements, process configuration, and database setup requirements, see the Oracle GoldenGate installation and configuration document for your database type. These guides are listed in the Preface of this book.

• For detailed instructions on configuring Oracle GoldenGate change capture and delivery groups, see Configuring Online Change Synchronization.

• For additional tuning options for Oracle GoldenGate, see Tuning the Performance of Oracle GoldenGate.

• For complete syntax and descriptions of the Oracle GoldenGate commands and parameters, see Reference for Oracle GoldenGate for Windows and UNIX.

5.2 Creating a Standard Reporting Configuration

在标准的Oracle GoldenGate配置中，一个提取组通过TCP/IP将捕获的数据发送到目标系统上的一个跟踪，并将其存储到一个副本组处理。

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

Figure 5-1 Configuration Elements for Creating a Standard Reporting Configuration

Description of "Figure 5-1 Configuration Elements for Creating a Standard Reporting Configuration"

5.2.1 Source System

配置管理器进程并在源系统上提取组。
配置管理器进程
在源代码上，根据说明配置Manager流程in Configuring Manager and Network Communications.

To Configure the Extract Group

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 RMTTRAIL command to specify a remote trail to be created on the target system.

   ADD RMTTRAIL remote_trail, EXTRACT ext
   

   Use the EXTRACT argument to link this trail to the Extract group.

   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 the 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 of the supplementally logged columns if using integrated Replicat
   LOGALLSUPCOLS
   -- Valid for Oracle. Specify the name or IP address of the target system and
   -- optional encryption across TCP/IP:
   RMTHOSTOPTIONS target, MGRPORT port_number, ENCRYPT encryption_options
   -- Specify the remote trail and encryption algorithm on the target system:
   ENCRYPTTRAIL algorithm
   RMTTRAIL remote_trail
   -- Specify tables and sequences to be captured:
   SEQUENCE [container.|catalog.]owner.sequence;
   TABLE [container.|catalog.]owner.table;
   

5.2.2 Target System

Configure the Manager process and Replicat group on the target system.

To Configure the Manager Process

1. On the target, configure the Manager process according to the instructions in Configuring Manager and Network Communications.

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

To Configure the Replicat Group

1. On the target, create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Creating a Checkpoint Table for instructions. All Replicat groups can use the same checkpoint table.
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
   , BEGIN time
   

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

   See Reference for Oracle GoldenGate 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
   -- 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])]
   ;
   

   Note:

   For DB2 for i, you may need to use the ADD TRANDATA command on the target tables if they are not already journaled. Alternatively, you could use the STRJRNPF command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, the IMAGES(*AFTER) option can be used with STRJRNPF. Since Oracle GoldenGate operates using transactions, all tables must be journaled to support transactions and this is not the default with DB2 for i.

5.3 Creating a Reporting Configuration with a Data Pump on the Source System

：您可以在源系统上添加一个数据泵，将主提取与TCP/IP功能隔离开来，增加存储灵活性，并将过滤和转换处理的开销从主提取中解脱出来。

在此配置中，主提取写入源系统上的本地跟踪。本地数据泵读取该跟踪并将数据移动到目标系统上的远程跟踪，该跟踪由Replicat读取

您可以(但不是必须)使用数据泵来改进Oracle GoldenGate的性能和容错能力。

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

Figure 5-2 Configuration Elements for Replicating to One Target with a Data Pump

Description of "Figure 5-2 Configuration Elements for Replicating to One Target with a Data Pump"

5.3.1 Source System

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

To Configure the Manager Process

1. On the source, configure the Manager process according to the instructions in Configuring Manager and Network Communications.

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 Group

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. 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 Extract Group

1. On the source, use the ADD EXTRACT command to create a data pump group. For documentation purposes, this group is called pump.

   ADD EXTRACT pump, 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 target system.

   ADD RMTTRAIL remote_trail, EXTRACT pump
   

   Use the EXTRACT argument to link the remote trail to the 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 the data pump. Include the following parameters plus any others that apply to your database environment.

   -- Identify the data pump group:
   EXTRACT pump
   -- 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 target system
   -- and optional encryption of data over TCP/IP:
   RMTHOSTOPTIONS target, MGRPORT port_number, ENCRYPT encryption_options
   -- Specify the remote trail and encryption algorithm on the target system:
   ENCRYPTTRAIL alogrithm
   RMTTRAIL remote_trail
   -- Specify tables and sequences to be captured:
   SEQUENCE [container.|catalog.]owner.sequence;
   TABLE [container.|catalog.]owner.table;
   

5.3.2 Target System

Configure the Manager process and Replicat group on the target system.

To Configure the Manager Process

1. On the target, configure the Manager process according to the instructions in Configuring Manager and Network Communications.

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

To Configure the Replicat Group

1. On the target, create a Replicat checkpoint table (unless using Oracle integrated Replicat). See 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 
   , BEGIN time
   

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

   See Reference for Oracle GoldenGate 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
   -- 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])]
   ;
   

   Note:

   For DB2 for i, you may need to use the ADD TRANDATA command on the target tables if they are not already journaled. Alternatively, you could use the STRJRNPF command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, the IMAGES(*AFTER) option can be used with STRJRNPF. Since Oracle GoldenGate operates using transactions, all tables must be journaled to support transactions and this is not the default with DB2 for i.

5.4 Creating a Reporting Configuration with a Data Pump on an Intermediary System

您可以使用中间系统作为源系统和目标系统之间的转接点。在此配置中，源系统上的数据泵将捕获的数据发送到中介系统上的远程跟踪。中间系统上的数据泵读取轨迹并将数据发送到目标上的远程轨迹。目标上的副本读取远程跟踪并将数据应用于目标数据库。

Figure 5-3 Configuration Elements for Replication through an Intermediary System

Description of "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 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 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 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 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. For a local Extract, you must use EXTTRAIL not RMTTRAIL.

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 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:
   RMTHOSTOPTIONS target_1, MGRPORT port_number, ENCRYPT encryption_options
   -- Specify remote trail and encryption algorithm on intermediary system:
   ENCRYPTTRAIL algorithm
   RMTTRAIL remote_trail_1
   -- Specify tables and sequences to be captured:
   SEQUENCE [container.|catalog.]owner.sequence;
   TABLE [container.|catalog.]owner.table;
   

5.4.2 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 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 additional ADD RMTTRAIL options.

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.
   -- 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:
   RMTHOSTOPTIONS 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
   -- Specify tables and sequences to be captured:
   SEQUENCE [container.|catalog.]owner.sequence;
   TABLE [container.|catalog.]owner.table;
   

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 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 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 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
   -- 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])]
   ;
   

   Note:

   For DB2 for i, you may need to use the ADD TRANDATA command on the target tables if they are not already journaled. Alternatively, you could use the STRJRNPF command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, the IMAGES(*AFTER) option can be used with STRJRNPF. Since Oracle GoldenGate operates using transactions, all tables must be journaled to support transactions and this is not the default with DB2 for i.

5.5 Creating a Cascading Reporting Configuration

Oracle GoldenGate支持级联同步，其中Oracle GoldenGate将数据更改从源数据库传播到第二个数据库，然后再传播到第三个数据库。在这个配置:

• 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 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

Description of "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 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 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 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 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:
   RMTHOSTOPTIONS 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
   -- 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 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 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.

   See Reference for Oracle GoldenGate 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
   -- 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])]
   ;
   

   Note:

   For DB2 for i, you may need to use the ADD TRANDATA command on the target tables if they are not already journaled. Alternatively, you could use the STRJRNPF command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, the IMAGES(*AFTER) option can be used with STRJRNPF. Since Oracle GoldenGate operates using transactions, all tables must be journaled to support transactions and this is not the default with DB2 for i.

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

Note:

If replicating DDL operations, IGNOREAPPLOPS, GETREPLICATES functionality is controlled by the DDLOPTIONS parameter.

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 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
   [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:
   RMTHOSTOPTIONS 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
   -- 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.

To Configure the Manager Process

1. On the third system, configure the Manager process according to the instructions in 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 Creating a Checkpoint Tablefor 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 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
   -- 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])]
   ;
   

   Note:

   For DB2 for i, you may need to use the ADD TRANDATA command on the target tables if they are not already journaled. Alternatively, you could use the STRJRNPF command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, the IMAGES(*AFTER) option can be used with STRJRNPF. Since Oracle GoldenGate operates using transactions, all tables must be journaled to support transactions and this is not the default with DB2 for i.