Chapter 5. Configuration Options

This chapter focuses on the additional configuration options available in Oracle GoldenGate 12c. The powerful options discussed here allow your configuration to extend in functionality and increase in performance. We will start with a performance enhancing option, enabling SQL statements to be grouped and applied as a batch against a target database. Later, we will explore the security features, including data compression and encryption, take a look at heterogeneous environments, and finally, discuss DDL support and the tools available to monitor the DDL replication.

This chapter explains the following configuration options:

• Batching SQL by operation type

• Data compression techniques

• Data and password encryption methods

• Using the Credential Store

• Triggering an action from an event

• Replicating DML statements to reduce lag during batch processing

• The loop and conflict detection

• The DDL replication

• Using utilities to migrate Oracle Streams environment to GoldenGate

Using BATCHSQL

In default mode, the Replicat process will apply SQL to the target database, one statement at a time. This often causes a performance bottleneck, where the Replicat process cannot apply the changes quickly enough compared with the rate at which the Extract process delivers the data. Even with GoldenGate spawning additional Replicat parallel processes, performance may still be a problem.

GoldenGate has addressed this issue through the use of the BATCHSQL Replicat configuration parameter. As the name implies, BATCHSQL segregates similar SQL statements into batches and applies them all at once. The batches are assembled in arrays in a memory queue on the target database server that are subsequently applied.

Similar SQL statements are those that perform a specific operation type (insert, update, or delete) against the same target table with the same column list. For example, multiple inserts in table A will be batched together. Similarly, multiple inserts in table B will form a...

Data compression

Oracle GoldenGate has supported data compression at the network layer for many years, enhancing data transfer rates. Compression techniques often improve performance, given the reduction in data that needs to be transferred from source to target. When configured in the Extract or data pump process parameter file, the server collector process on the target machine automatically decompresses the data before writing to the remote trail files.

Compressing the data stream

Depending on your data, the maximum compression ratio can exceed 5:1, which will help transfer speeds on low bandwidth networks. However, additional CPU utilization is required when compared to no data compression, which is the default.

If compression is enabled, the following statistics are available in the Extract process report, which is obtained via the GGSCI SEND command with the GETTCPSTATS argument:

• The compression CPU time: This specifies the on-CPU time that the process consumed.

• The compress time: This...

Security features

First available in the Oracle GoldenGate 11g Release 2, encryption is governed by one of the following four supported algorithms that use 64-, 128-, 192-, and 256-bit cypher keys:

• AES128 uses the AES-128 cipher with a key size of 128 bits

• AES192 uses the AES-192 cipher with a key size of 192 bits

• AES256 uses the AES-256 cipher with a key size of 256 bits

• Blowfish uses a symmetric 64-bit block size and a variable length key size from 32 bits to 128 bits

Advanced Encryption Security (AES) has been adopted as an industry standard and offers stronger encryption over the deprecated Blowfish algorithm that should no longer be used. However, it is still supported to maintain backward compatibility.

The AES cypher keys can be configured by two possible methods:

• The wallet method

• The ENCKEYS method

The wallet method

Oracle GoldenGate 12c supports the Oracle wallet method that can be used to centrally and securely store the database user logon credentials and encryption keys.

Note

The user...

Event-based processing

It is important in any data replication environment to capture and manage events, such as trail records containing specific data or operations or maybe the occurrence of a certain error. These are known as Event Markers.

GoldenGate provides a mechanism to perform an action on a given event or condition. These are known as Event Actions and are triggered by Event Records. If you are familiar with Oracle Streams, Event Actions are like rules.

The Event Marker System

GoldenGate's Event Marker System, also known as event marker interface (EMI), allows custom DML-driven processing on an event. This comprises of an Event Record to trigger a given action. An Event Record can be either a trail record that satisfies a condition evaluated by a WHERE or FILTER clause or a record written to an event table that enables an action to occur. Typical actions are writing status information, reporting errors, ignoring certain records in a trail, invoking a shell script, or performing an...

Bidirectional configuration options

When implementing a bidirectional configuration, you must consider the following areas to avoid data integrity issues. These are as follows:

• Loop detection

• Conflict detection

• Conflict resolution

• Replicating Oracle sequences

• Oracle triggers

Let's take a look at the first potential problem (data looping) and how to detect it.

The loop detection

GoldenGate has a built-in loop detection, which is configured through the IGNOREREPLICATES and GETAPPLOPS parameters to prevent local transactions from being replicated and causing endless loops. Another solution would be the TRANLOGOPTIONS EXCLUDEUSER parameter in the Extract process configuration, which effectively blocks the GGADMIN user on the target system (the user associated with the Replicat process). However, loop detection is only half the battle in a bidirectional environment. We must also consider conflict detection and resolution.

Tip

Truncate table operations cannot be detected by the loop detection scheme. To...

The DDL support

GoldenGate supports the replication of DDL commands operating at the schema level. By default, the DDL replication is enabled on the target (Replicat) for data integrity and disabled on the source (Extract). If you wish to perform DDL and DML replication from your source database, the DDL part must be explicitly configured through a single DDL parameter statement.

The support for DDL has increased since the integrated capture feature was introduced in Oracle 11g Release 2. If you wish to perform extensive DDL replication, particularly in the active-active configuration, refer to the following online Oracle documentation to assess the data type and database object support:

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/system_requirements.htm#GIORA121

The DDL replication for database tables is supported in Teradata database environments for unidirectional and bidirectional configurations between two systems. The source and target database must be identical.

The DDL replication...

Heterogeneous environments

Although not heavily discussed in this book, one of the main features of GoldenGate is its support for heterogeneous databases. GoldenGate's decoupled architecture and its trail file universal data format enables heterogeneity. This is the main selling feature for Oracle because GoldenGate does not require additional gateways or any conversion software in order to extract and replicate data across environments.

The decoupled architecture allows a database-specific flavor of the same version of GoldenGate software to be installed on each source and target server, enabling trail files to be written to in a common file format. Such files are subsequently consumed by the target server's apply process.

This is illustrated in the following diagram:

GoldenGate's decoupled architecture for heterogeneous databases

Unfortunately, the decoupled nature of heterogeneous data replication provides some limited functionality—for example, DDL replication is not supported. Oracle GoldenGate...

Oracle Streams to GoldenGate Migration

Oracle GoldenGate 12c and Oracle database version 11.2.0.4 and higher support the Streams2OGG utility that converts Streams capture, propagate, and apply processes to GoldenGate equivalents. The utility is available for download on the Oracle Support Portal (https://support.oracle.com). You will need an MOS account to sign in.

At the time of writing, the downloaded zip file: streams2oggv3_0.zip includes the following contents:

• s2gg_config_check.sql: This is a health check script to assess the Streams environment prior to migration

• streams2ogg.sql: This is a PL/SQL package that must be compiled in the Streams admin user schema on both source and target databases

The installation script prompts for the GoldenGate admin user and a separate staging directory, as shown in the following code:

SQL> connect strmadmin/strmadmin@strmdb
--Load the package in database
SQL> @streams2ogg.sql
Enter value for gg_username: ggadmin
Enter value for staging_dir: /u01...

Summary

Oracle GoldenGate 12c is a highly configurable product. Obtaining the optimal configuration for your application may be a challenge, but you will be rewarded with a robust, scalable, secure, and high performance data replication and synchronization solution. What's more, the additional options and utilities help you to build on the existing configuration when the system requirements change with time.

In this chapter, we discovered various powerful configuration options provided by the Event Marker Interface that enable event-based processing and huge performance gains in data replication.

In the next chapter, Chapter 6, Configuring GoldenGate for HA, we will discuss high availability options and how Oracle GoldenGate can be RAC aware, providing the ability to failover to a surviving node in a clustered environment.