2 Using Oracle GoldenGate for Live Reporting
Topics:
2.1 Overview of the Reporting Configuration
The most basic Oracle GoldenGate configuration is a one-to-one configuration that replicates in one direction: from a source database to a target database that is used only for data retrieval purposes such as reporting and analysis. Oracle GoldenGate supports like-to-like or heterogeneous transfer of data, with capabilities for filtering and conversion on either system in the configuration (support varies by database platform).
Oracle GoldenGate is ideal for creating a reporting environment because the target can be optimized for reporting, while allowing the source to be optimized for OLTP workloads. This includes adding additional indexes or materialized views on the target database to allow faster execution of queries. Oracle GoldenGate can also pull metadata from the source database to help track how the records changed, when the record changed, who changed it, and even track the history of how column values changed.
Oracle GoldenGate supports different reporting topologies that enable you to custom-configure the processes based on your requirements for scalability, availability, and performance. This section contains things to take into consideration when choosing a reporting configuration.
Parent topic: Using Oracle GoldenGate for Live Reporting
2.1.1 Filtering and Conversion
Data filtering and data conversion both add overhead, and these activities are sometimes prone to configuration errors. If Oracle GoldenGate must perform a large amount of filtering and conversion, consider using one or more data pumps to handle this work. You can use Replicat for this purpose, but you would be sending more data across the network that way, as it will be unfiltered. You can split filtering and conversion between the two systems by dividing it between the data pump and Replicat.
To filter data, you can use:
-
A
FILTER
orWHERE
clause in aTABLE
statement (Extract) or in aMAP
statement (Replicat) -
A SQLEXEC can perform a query or execute a stored procedure on the database. The values returned can then be used to evaluate a
FILTER
clause. -
User exits
To transform data, you can use:
-
The Oracle GoldenGate conversion functions. See Column Conversion Functions in the Reference for Oracle GoldenGate.
-
Metadata from the source database or trail. See GETENV function.
-
A user exit from the Extract or Replicat process that applies rules from an external transformation solution, then returns the manipulated data to Oracle GoldenGate.
-
Replicat to deliver data directly to an ETL solution or other transformation engine.
For more information about Oracle GoldenGate's filtering and conversion support, see:
Parent topic: Overview of the Reporting Configuration
2.1.2 Read-only vs. High Availability
The Oracle GoldenGate live reporting configuration supports a read-only target. See Configuring Bi-Directional Replication if the target in this configuration will also be used for transactional activity in support of high availability.
Parent topic: Overview of the Reporting Configuration
2.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.
Parent topic: Overview of the Reporting Configuration
2.2 Creating a Standard Reporting Configuration
In the standard Oracle GoldenGate configuration, one Extract group sends captured data over TCP/IP to a trail on the target system, where it is stored until processed by one Replicat group.
Refer to the following figure for a representation of the objects you will be creating in Classic Architecture.
Figure 2-1 Configuration Elements for Creating a Standard Reporting Configuration
![Description of Figure 2-1 follows Description of Figure 2-1 follows](img/reporting_basic.jpg)
Description of "Figure 2-1 Configuration Elements for Creating a Standard Reporting Configuration"
Refer to the following figure for a representation of the objects you will be
creating in Microservices Architecture.
2.2.1 Source System
Configure the Manager process and Extract group on the source system.
To Configure the Manager Process
On the source, configure the Manager process.
To Configure the Extract Group
-
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[, ...]]
-
On the source, use the
ADD RMTTRAIL
command to specify a remote trail to be created on the target system.ADD EXTTRAIL local_trail, EXTRACT ext
Use the
EXTRACT
argument to link this trail to the Extract group. -
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 ENCRYPTTRAIL algorithm EXTTRAIL local_trail -- Specify tables and sequences to be captured: SEQUENCE [container.|catalog.]owner.sequence; TABLE [container.|catalog.]owner.table;
To send the trail files to the target
- For Classic Architecture, use the following command option:
ADD EXTRACT ext_pmp, EXTTRAILSOURCE remote_trail, BEGIN time [option[,...]]
For Microservices Architecture, use the following command:ADD DISTPATH path-name SOURCE source-uri TARGET target-uri| [TARGETTYPE ( MANAGER | COLLECTOR | RECVSRVR ) ]|
-
Run the following command to add the remote trail:
ADD RMTTRAIL remote_trail, EXTRACT ext_pmp
-
Use the
EDIT PARAMS
command to create a parameter file for the target.
Parent topic: Creating a Standard Reporting Configuration
2.2.2 Target System
Configure the Manager process and Replicat group on the target system.
To Configure the Manager Process
-
On the target, configure the Manager process.
-
In the Manager parameter file, use the
PURGEOLDEXTRACTS
parameter to control the purging of files from the local trail.
To Configure the Replicat Group
Parent topic: Creating a Standard Reporting Configuration
2.3 Creating a Reporting Configuration with a Data Pump on the Source System
You can add a data pump on the source system to isolate the primary Extract from TCP/IP functions, to add storage flexibility, and to offload the overhead of filtering and conversion processing from the primary Extract.
In this configuration, the primary Extract writes to a local trail on the source system. A local data pump reads that trail and moves the data to a remote trail on the target system, which is read by Replicat.
You can, but are not required to, use a data pump to improve the performance and fault tolerance of Oracle GoldenGate.
Here's a representation of the objects, you will be creating.
Figure 2-2 Configuration Elements for Replicating to One Target with a Data Pump
![Description of Figure 2-2 follows Description of Figure 2-2 follows](img/data_pump_single.jpg)
Description of "Figure 2-2 Configuration Elements for Replicating to One Target with a Data Pump"
Topics:
2.3.1 Source System
Configure the Manager process and Extract group on the source system.
To Configure the Manager Process
-
On the source, configure the Manager process.
-
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
-
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. -
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. -
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
2.3.2 Target System
Configure the Manager process and Replicat group on the target system.
To Configure the Manager Process
-
On the target, configure the Manager process.
-
In the Manager parameter file, use the
PURGEOLDEXTRACTS
parameter to control the purging of files from the local trail.
To Configure the Replicat Group
2.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 2-3 Configuration Elements for Replication through an Intermediary System
![Description of Figure 2-3 follows Description of Figure 2-3 follows](img/data_pump_on_intermediary.jpg)
Description of "Figure 2-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.
-
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.
Topics:
Parent topic: Using Oracle GoldenGate for Live Reporting
2.4.1 Source System
Here are the objects you will be creating.
To Configure the Manager Process
-
On the source, configure the Manager process.
-
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
-
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. -
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. -
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
2.4.2 Intermediary System
Configure the Manager process and data pump on the intermediary system.
To Configure the Manager Process on the Intermediary System
-
On the intermediary system, configure the Manager process.
-
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
2.4.3 Target System
Configure the Manager process and Replicat group on the target system.
To Configure the Manager Process on the Target
-
On the target system, configure the Manager process.
-
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
2.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 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.
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.
-
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
andGETREPLICATES
.
Parent topic: Using Oracle GoldenGate for Live Reporting
2.5.1 Source System
Refer to Figure 2-4 for a visual representation of the objects you will be creating.
To Configure the Manager Process on the Source
-
On the source, configure the Manager process.
-
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
-
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. -
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. -
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
Parent topic: Creating a Cascading Reporting Configuration
2.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
-
On the second system, configure the Manager process.
-
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
-
Create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Creating a Checkpoint Table for instructions.
-
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 [MAXTHREADSnumber
]] , EXTTRAILremote_trail_1
, , BEGINtime
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.
-
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: [TARGETDBdsn_2
][, USERIDALIASalias
] -- 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 theSTRJRNPF
command to assign the tables to the appropriate journal. If the target tables are not required to be replicated by Oracle GoldenGate, theIMAGES(*AFTER)
option can be used withSTRJRNPF
. 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
-
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}, BEGINtime
[option
[, ...]]See Reference for Oracle GoldenGate for detailed information about these and other
ADD EXTRACT
options that may be required for your installation. -
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
, EXTRACText_2
Use the
EXTRACT
argument to link this local trail to the ext_2 Extract group. -
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: [SOURCEDBdsn_2
][, USERIDALIASalias
] -- Log all scheduling columns if using integrated Replicat LOGALLSUPCOLS -- Specify the local trail that this Extract writes to -- and encryption algorithm: ENCRYPTTRAILalgorithm
EXTTRAILlocal_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
Parent topic: Creating a Cascading Reporting Configuration
2.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
-
On the third system, configure the Manager process.
-
In the Manager parameter file, use the
PURGEOLDEXTRACTS
parameter to control the purging of files from the trail.
To Configure the Replicat Group
Parent topic: Creating a Cascading Reporting Configuration