5 Configuring Oracle GoldenGate for Real-time Data Warehousing
Topics:
5.1 Overview of the Data Warehousing Configuration
A data warehousing configuration is a many-to-one configuration. Multiple source databases send data to one target warehouse database. 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).
5.2 Considerations for a Data Warehousing Configuration
This section describes considerations for a data warehousing configuration.
5.2.1 Isolation of Data Records
This configuration assumes that each source database contributes different records to the target system. If the same record exists in the same table on two or more source systems and can be changed on any of those systems, conflict resolution routines are needed to resolve conflicts when changes to that record are made on both sources at the same time and replicated to the target table. See Configuring Oracle GoldenGate for Active-Active Configuration for more information about resolving conflicts.
Parent topic: Considerations for a Data Warehousing Configuration
5.2.2 Data Storage
You can divide the data storage between the source systems and the target system to reduce the need for massive amounts of disk space on the target system. This is accomplished by using a data pump on each source, rather than sending data directly from each Extract across the network to the target.
-
A primary Extract writes to a local trail on each source.
-
A data-pump Extract on each source reads the local trail and sends it across TCP/IP to a dedicated Replicat group.
Parent topic: Considerations for a Data Warehousing Configuration
5.2.3 Filtering and Conversion
If not all of the data from a source system will be sent to the data warehouse, you can use the data pump to perform the filtering. This 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.
Parent topic: Considerations for a Data Warehousing Configuration
5.2.4 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: Considerations for a Data Warehousing Configuration
5.3 Creating a Data Warehousing Configuration
Refer to Figure 5-1 for a visual representation of the objects you will be creating.
Figure 5-1 Configuration for Data Warehousing
Description of "Figure 5-1 Configuration for Data Warehousing"
5.3.1 Source Systems
Configure the Manager process and primary Extract groups for the source systems.
To Configure the Manager Process
-
On each source, configure the Manager process according to the instructions in Configuring Manager and Network Communications.
-
In each Manager parameter file, use the
PURGEOLDEXTRACTS
parameter to control the purging of files from the trail on the local system.
To Configure the primary Extract Groups
-
On each source, use the
ADD EXTRACT
command to create a primary Extract group. For documentation purposes, these groups are called ext_1 and ext_2.Command on source_1:
ADD EXTRACT
ext_1
, {TRANLOG | INTEGRATED TRANLOG}, BEGINtime
[option
[, ...]]Command on source_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 each source, use the
ADD EXTTRAIL
command to create a local trail.Command on source_1:
ADD EXTTRAIL
local_trail_1
, EXTRACText_1
Command on source_2:
ADD EXTTRAIL
local_trail_2
, EXTRACText_2
Use the
EXTRACT
argument to link each Extract group to the local trail on the same system. The primary Extract writes to this trail, and the data-pump reads it. -
On each source, use the
EDIT PARAMS
command to create a parameter file for the primary Extract. 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.Parameter file for ext_1:
-- Identify the Extract group: EXTRACT
ext_1
-- Specify database login information as needed for the database: [SOURCEDBdsn_1
][, USERIDALIASalias
] -- Log all scheduling columns if using integrated Replicat LOGALLSUPCOLS -- Specify the local trail that this Extract writes to -- and the encryption algorithm: ENCRYPTTRAILalgorithm
EXTTRAILlocal_trail_1
-- Specify tables and sequences to be captured: SEQUENCE [container
.|catalog
.]owner
.sequence; TABLE [container
.|catalog
.]owner
.table
;Parameter file for ext_2:
-- 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 or CDR LOGALLSUPCOLS -- Specify the local trail that this Extract writes to -- and the encryption algorithm: ENCRYPTTRAILalgorithm
EXTTRAILlocal_trail_2
-- Specify tables and sequences to be captured: SEQUENCE [container
.|catalog
.]owner
.sequence; TABLE [container
.|catalog
.]owner
.table
;
To Configure the Data Pumps
Parent topic: Creating a Data Warehousing Configuration
5.3.2 Target System
Configure the Manager process and primary Replicat groups for the target system.
To Configure the Manager Process
-
Configure the Manager process. See Configuring Manager and Network Communications for instructions.
-
In the Manager parameter file, use the
PURGEOLDEXTRACTS
parameter to control the purging of files from the trail.
To Configure the Replicat Groups
Parent topic: Creating a Data Warehousing Configuration