GoldenGate configure Initial Load Extract tips

Configure and verify a GoldenGate initial-data load extract

We start by preparing the parameter files for the initial-load extract. The parameter files instructs the initial- load extract to connect to the database and read from the source tables directly. Using the target system hostname and the manager process port number, the remote files created as configured, single file or multiple remote files depending on the overall size of the source database. Oracle GoldenGate restrict a maximum remote file size to 2GB.

Configure GoldenGate initial-load extract

Because the initial load extract is of type special run, it's not associated with a checkpoint file, a failure of the initial load extract process requires resubmitting the initial load task. Table 3-3 lists the initial load extract parameter file (eload01.prm).

 

Parameter	Description
SOURCEISTABLE	Instructs the initial load extract to read from the source database tables
USERID, PASSWORD	The source database login credential
RMTHOST, MGRPORT	The target system identified by the hostname and manager port number
RMTFILE, PURGE	The remote file location and name with the purge option
TABLE	The list of tables read by the initial load extract

Table 3-3: Initial load extract parameters

 

The RMTFILE parameter uses the options purge or append. When purge is specified, the initial load extract removes the initial data load file, if exists, before instantiating the data file. When append is specified, the initial data load extract instantiate the file by appending to the file, if exists.

 

From within GGSCI, use the edit params command to create the initial data load extract parameter file. The RMTFILE parameter is used to create one initial data load only. However; If the initial data load file is expected to exceed 2GB, then use the RMTFILE options maxfiles and megabytes which instruct the initial load extract to create multiple initial data load files.

 

 

 

Let's create the initial-load extract. This extract will be used during the deployment phase.

 

 

EXTRACT added.

 

GGSCI (ggs-source) 17>

 

Next let's look at GoldenGate configuration options

GoldenGate Configuration options

An optimized-advanced initial extract parameter file considers security, compression and multiple initial load remote files settings. Applying such parameters should be used selectively, and they are mostly used for databases that are subject to compliance regulation, such as credit cards and social security numbers.

GoldenGate Security settings

The GoldenGate security settings are suitable for highly sensitive environment by encrypting passwords and remote trails. An encrypted Oracle GoldenGate generated password stored in a flat file protected by file-system read-write permission.

 

Encrypted data sent across TCP/IP network applies encryption to remote trail files. Oracle GoldenGate data encryption requires the generation of encryption key. When applying trail file encryption, be aware of the performance overhead introduced for encryption on source and decryption on target systems. See chapter 1 - Oracle GoldenGate fundamentals for details encrypting password techniques and generating encryption keys. Performance testing showed encryption slowdown initial-load process by approximately 23%.

GoldenGate Compression settings

Adding the compression parameter COMPRESSION enables default compression algorithm. Additionally, the parameter COMPRESSIONTHRUSHHOLD <size> further optimizes the compression based on the threshold specified, eliminating unnecessary compression. The compression ratio depends on columns data types, an average ratio of 1:4 is highly achievable. Our particular initial load the compression ratio achieved is 1:5. Compression considerably help enhances the performance across low-bandwidth wide-area network.

GoldenGate Multiple remote files settings

This option supports very large database (VLDB) initial-load. Using maxfiles and maxsize options of RMTFILE parameter creates multiple remote files on the target system. This feature avoids having one initial-load remote file reaching 2GB, which is an enforced upper limit by Oracle GoldenGate. During the initial-load delivery, temporary disabling tables constraints improves the performance and avoids referential integrity violations. We deploy parallel delivery groups for high-performance initial-load by mapping one delivery group per one remote file as illustrated in figure 3-7 where multiple delivery groups (replicate processes) are read in parallel remote files.

GoldenGate Range processing

Use the sqlpredicate clause of TABLE to partition the rows of large tables among the different Extract processes. This is treated as parallel initial-load extract when running concurrently.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

     Figure 3-7: File to replicate initial-load using 4-degree parallel delivery groups

 

The parameter file eload02.prm configures an initial-load extract (ELOAD02) to support the following Oracle GoldenGate features.

 

§  Password encryption

§  Remote trail compression

§  Remote trail encryption

§  Multiple remote files

§  Rows splitting for large tables

 

Password encryption and encryption key must be generated, otherwise the extract process terminates with an error.

 

 

 

 

Where pf stands for PARAMFILE and rf stands for REPORTFILE.  Note that these can be used interchangeably.

 

The generated remote files using the RMTFILE options are composed of two parts: the specified file name sub-string appended with numbers staring at 000000. From the target server, list the remote files created from the above initial-load parameters file eload01.prm and extract session.

 

Oracle GoldenGate 12c The above is an excerpt from the upcoming 12c book Oracle GoldenGate 12c: A Hands-on Guide to Data Replication & Integration using Oracle & SQL Server.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.