GoldenGate Downstream deployment – Basics

GoldenGate Downstream deployment allows you to offload the source database extract load to intermediate or target server. The source database ships its redo logs to a downstream database, and Extract uses the logmining server at the downstream database to mine the redo logs. A downstream mining database can accept both archived logs and online redo logs from a source database

Oracle GoldenGate Change Data Capture (CDC) Extract is commonly installed on the Source database server, but an Integrated Extract can optionally be installed on a secondary database server, known as a Downstream Mining database, where the Source database’s redo and archive logs are shipped and finally data will flow from Downstream database to target database.

When deploying Integrated Extract in a downstream mining database you can choose between two options:

Option 1 is real-time mining:  This option can be used to capture changes only from a single source database. You need to create standby redo logs on the target side so that the source database can use Oracle Data Guard redo transport to send redo to the downstream mining database as it is written to the online redo log at the source. In a real-time downstream capture environment, the primary database is configured so that the transport services use the log writer process (LGWR) at the source database to send redo data to the downstream database either synchronously or asynchronously.

Option 2 is archive log mining:   In this option you can use the downstream mining database to capture changes for single or multiple source databases. You don’t need standby redo logs in the downstream database for this configuration. Note that using this option will incur additional latency. In this option downstream extract is not configured for real time capture, it waits for next archive log, which will be available after a log switch on source database. The latency of Downstream Extract can be controlled by setting the source database init.ora parameter ARCHIVE_LAG_TARGET. This parameter is set to a  number of seconds. Since this parameter forces a log switch on all nodes, it is not recommended to set this very low. The recommended setting should be around 15 minutes or 900 seconds.

BENEFITS OF GOLDENGATE DOWNSTREAM DEPLOYMENT:

• Since the logmining activity imposes additional overhead on the source database where it is running because it adds additional processes as well as consumes memory from the SGA from the source database, it is beneficial to offload this processing from the source database to the downstream database. The only impact would be the cost to ship redo via ASYNC transport which is extremely lightweight. This is major benefit.

LIMITATIONS  OF GOLDENGATE DOWNSTREAM DEPLOYMENT:

• This configuration requires the deployment and maintenance of another database.
• Downstream Integrated Extract can be configured to process either in real-time mode or archive log only mode.  Real-time mining can be only be configured for one source database at a time. Downstream mining database can accept online redo logs from only one of those source databases. The rest of the source databases must ship archived logs.
• The source database and mining database must be of the same platform. For example, if the source database is running on Linux 64-bit, the downstream database must also be on the Linux 64-bit platform.
• This feature is specific to Oracle databases only.
• Every time any PSU patches need to be applied to Source side,  then first associated mining database need to be patched. Downstream capture can mine logs from lower release databases, but not from higher release databases.

GoldenGate Initial Troubleshooting Tips for Begineers

When we face issues in GoldenGate extract or replicat setup, we follow some of the tried and tested initial troubleshooting steps mentioned below that helps us in pointing out the exact case of the issue.

To start with, GGSCI commands which are useful for Gathering evidences of issue:

• SEND <group>, STATUS 
shows current processing status.

• STATS <group> 
shows information about operations processed.

• INFO ALL 
shows status and lag for all Manager, Extract, and Replicat processes on the system.

• INFO <group>, DETAIL 
shows process status, data source, checkpoints, lag, working directory, files containing processing information.

• INFO EXTRACT <group>, SHOWCH 
Checkpoint details on the Extract side

• INFO REPLICAT <group>, SHOWCH 
Checkpoint details on the Replicat side

• VIEW REPORT <group> 
Shows the report for specific group (extract or replicat)

Another most important place where you should looks is the Oracle GoldenGate Event Log (aka GGSCI error log file)

In this log file you will be able to see :

• History of GGSCI commands
• Processes that got started and/or stopped
• Errors that occurred during extract/replication
• Informational messages which shows what events lead to an error.

You can view this event log file by:

■ Using standard text editor or shell command like VI editor.
■ GGSCI command > VIEW GGSEVT
■ Oracle GoldenGate Director

Oracle GoldenGate Discard file is another important file that you should focus on:

NOTE: In 11g, this file is created only if the DISCARD FILE <filename> parameter is used in the Extract/Replicat parameter file. In 12C , this file is created by default when defining any extract/replicat process.

Key points about discard file:
• Created when Extract or Replicat has a problem with the record it is processing
• Contains column-level details for operations that the GoldenGateprocess could not handle
• Usually used for Replicat to log operations that could not be reconstructed or applied
• Can help in pointing out the data mapping issues

Oracle GoldenGate 12c New Features Overview

GoldenGate 12c version  has many great features as compared to older versions. Below are some of the key new features of Oracle GoldenGate 12c that are worth mentioning

1) Integrated Delivery for the Oracle Database

Prior to 12c, to handle high pressure replication work you had to define many replicat processes. In 12c, A new lightweight Streaming API has been built by oracle exclusively for Oracle GoldenGate. This utility moves GoldenGate Delivery closer to the RDBMS engine.

This is a Unique feature which is considered next generation and high-speed data apply process. Internally this mechanism leverages the database parallel apply servers to create auto dependency aware parallel apply.

Note that Integrated Delivery is applicable for Oracle target databases only 12.1.0.1 and 11.2.0.4

Key Benefits of GoldenGate 12c Integrated Delivery:

•    Minimal changes to GoldenGate configuration
•    Single parameter file for all tables
•    Single process with minimal configuration to distribute load

In 12c, Integrated Delivery can be broadly broken down into two parts

A) Delivery

•  Reads the trail file
•  Constructs logical change records (LCRs)
•  Transmits LCRs to Oracle Database via the Lightweight Streaming API

B) Inbound Server (Database Apply Process)

•  Receiver: Reads LCRs
•  Preparer: Computes the dependencies between the transactions (primary key, unique indexes, foreign key) , group the transactions and sorting in dependency order.
• Coordinator: Coordinates transactions, maintains the order between applier processes.
• Appliers: Performs changes for assigned transactions, including conflict detection and error handing

2) Optimized for Oracle Database 12c

• Support now the 12c data types example Large Varchar of 32k.
• DDL capture is optimized further. It can now directly read from database redo logs
• Supports three part naming convention (catalog.schema.table)
• Reduces overhead by extracting changed data at the container level rather than at the individual databases. This means that memory/processes requirement is at the container level only
• Ease of administration with DBMS package for GoldenGate
• Additionally, GoldenGate 12c supports both cloud-based and on-premises hosted database systems. Supports both Unrestricted secured network and Restricted network access from on-premises and cloud through HTTP proxy / tunnel

3) Improved Ease of Use

A) DISCARDFILE: GoldenGate automatically creates discard file rather than having to manually create it during each installation as DBAs used to do prior to 12c

B) SHOWSYNTAX: New command that helps now in enhanced Debugging

• It has ability to view the SQL that Oracle GoldenGate is generating
• Aids developers in qualifying mappings and index usage for DML operatins.

C) WILDCARD: Ease of Configuration with Schema Wildcarding

•  Applications support wildcard schema and catalog
• Wildcard schema support is available for all databases

4) Coordinated Delivery for Non-Oracle Databases

For the Non-Oracle database, GoldenGate provides the High-Speed Apply Processes & Simplifies Setup and Management. You can have one replicate with multi-threaded process which is relatively  easy to handle. It simplifies a bit the deployments for Non-Oracle Databases.

Full Coordination Across Events Requiring Ordering;

• DDL
• Primary Key Updates
• EMI
• SQLEXEC.

Simplified Parameter File Management  and simpler error recovery. This further increases the ease of Use

5) Expanded Heterogeneity

GoldenGate 12c enhances further the support for New Databases and existing Supported Platforms. Various enhancement have been done for : MySQL, MS SQL Server, Sybase, Teradata, IBM DB2.  Also, various new versions for these databases are now supported.

6) Enhanced High Availability

This is one of the cool new feature.  Fast start FailOver(FSFO) is now possible for primary/standby system for Goldengate also. So as soon as your primary database failover to standby database an automated & transparent failover will occur for GoldenGate Components too. This means that your replication can continue without any manual intervention.

In the instance of a failover/switchover, Oracle GoldenGate processes are started on the standby during the role transition and insures that no data is lost during the failover/switchover.

7) Tighter Security

Previous releases of Oracle Golden Gate stored user details as either plain text or with light encrypted.  Golden Gate 12c includes integration with the Oracle Credential Store and Oracle Wallet provides great encryption facility . It also provides the ability to store and retrieve the key username and passwords using aliases.

A) Credential Store

•   Credential store is created during the set-up of Oracle GoldenGate
•    Usemames and passwords are stored as encrypted. Everything is access via an alias
•   A key is automatically generated for each trail file

B) Oracle Wallet

•    Provides the services to Oracle GoldenGate to manage and edit security credentials.
•    Use Wallet instead of ENCKEYS file
•    Common set of commands across all Oracle products

8) Downstream Capture

This is not exactly a new feature but this feature has been further enhanced in 12c.

Downstream capture mode can utilize Data Guard redo log transport to process transactions off-source.

If you select Real-Time Mining capabilties of GoldenGate to  Capture the transactions on Downstream / Remote Machines, major benefits are:

• Provides means to off-Ioad GoldenGate processing to downstream / remote machines.
• No chance of data loss by using the Data Guard Redo Iog transport mechanism to ship redo logs to a downstream / remote machine where Golden gate extract processes are installed.

9) Expanded Oracle Application and Technology Support

GoldenGate provides the methods to migrate to new Platforms and Database versions with extremely low downtime.

NOTE: It does not include Application Upgrades.

GoldenGate 12c had expanded Oracle Application and Technology Support

10) OUI Based Installation process

Finally GoldenGate, just like other Oracel software, can be installed using Oracle Universal Installer (OUI). Now in this release the OUI based installation takes care of some of the manual install process (like creating sub-directories) that DBA used to perform in earlier GoldenGate releases.

GoldenGate – An introduction

In the last few years, GoldenGate has become the preferred choice for DBAs to handle the replication requirement of their data centers. Besides being extremely easy to configure, GoldenGate offers immense flexibility in the configuration strategies available with it. This series of articles will discuss GoldenGate technology, covering concepts, configuration options, troubleshooting and so forth.

The Scenario

Imagine you are working for a multi-national bank, though its headquarters are in London, UK you are based in a Mumbai, India branch. This bank uses a specific account for its financial application used globally at all the branches. You have been asked by your manager that transactions that have happened for that account in the database at Mumbai branch be kept in synch with the centralized database situated at the UK daily. The volume of transactions is massive, and even the slightest delay can greatly impact the business. Since there isn’t just one database in any bank, neither is there just one branch for the bank, so the same kind of setup probably is required at multiple destinations. This setup would also require to be monitored continuously, preferably through some sort of GUI based tool for the ease of management. Additionally, there are several other, non-critical applications used at all the branches. These applications are based on non-Oracle databases such MYSQL etc, but the transactions done over these non-Oracle databases also need to be loaded into an Oracle database located at the headquarters. The replication technology used must be support both Oracle and non-Oracle databases so they can talk to each other.

Given this situation, the most important question becomes, which software can achieve all these requirements?

If you guessed GoldenGate, you were absolutely spot-on and in this series of articles we shall be elaborating this technology in more details with every subsequent instalment.

GoldenGate – What and Why

GoldenGate is a lightweight, log-based software. Interestingly, it’s popularly known as Oracle GoldenGate but it wasn’t initially an Oracle product. GoldenGate Software Inc. came into the existence in 1995 and was founded by Eric Fish and Todd Davidson. Yes, the name is definitely inspired from the London’s Golden Gate Bridge. In 2009, GoldenGate was acquired by Oracle Corp. and the current release of it, 12.1.2, matches with the latest release of the Oracle database – 12c. The genesis of GoldenGate was inspired by its founders to make data replication happen easily, seamlessly and not between just between Oracle databases, but also between Oracle and non-Oracle databases. That’s why GoldenGate brings a very unique heterogeneous environment support. For a database, maintaining transaction-level integrity is of utmost importance. GoldenGate ensures such data integrity and provides support for zero-data-loss for fault-tolerance.

Let’s have a look at few of the most significant benefits that GoldenGate offers:

1. With GoldenGate, data sending is in “near real time” which reduces the possibility of latency
2. To maintain the performance and consistency, only committed data is sent. Uncommitted data is captured by GoldenGate, but is discarded after receiving a Rollback.
3. GoldenGate offers support for different versions of Oracle database and also for many non-Oracle databases, along with availability over a wide range of Operating Systems and hardware platforms.
4. Though the performance of the replication is really good, the impact on the underlying databases is minimal when GoldenGate is in action.
5. The architecture and configuration of GoldenGate are very simplistic, making it a very effective and yet also an easy technology to learn and implement.
6. GoldenGate has in-built mechanisms for recovery of data for gap resolution for different kinds of failures i.e. site or network failure.
7. GoldenGate uses its own proprietary format files for keeping the committed data coming from the source database making it independently functional irrespective of the database in use.
8. GoldenGate uses the standard network between a source and the target database and doesn’t rely on Oracle’s Network services. Thus any data transfer done by GoldenGate processes doesn’t impact the network performance of the source and target databases.
9. For fault-tolerance, GoldenGate has its own mechanism to keep a track of how much work (transactions) is completed and how much is pending. This mechanism is independent of any database and ensures that no data loss occurs with the help of automatic gap-resolution.

A long list of features isn’t it!

So what benefits does GoldenGate offer with its technology stack for a business? With its data replication technology, the following are a few of the benefits.

1. GoldenGate provides high availability. Using it, one can have a standby database that’s been constantly made in sync with the primary environment. In case of a crash, an immediate fail over would make the impact on the business minimal.
2. GoldenGate offers Bi-Directional replication using which a complete Active-Active configuration can be created making the impact on the business from minimal to almost negligible.
3. Golden Gate is a great tool to have a zero downtime for upgrades and migrations.
4. Using GoldenGate’s replication technique, a separate system (where replication is going on) can be used for reporting thus relieving the burden from the source database.
5. GoldenGate also offers adapters which can extend the functionality of it. For example, GoldenGate application adapter for Java, the capture data can be send to a non-RDBMS target i.e. Java Messaging Service (JMS).

In the forthcoming parts, we shall have a more detailed look at these benefits and shall also see the examples to implement them.

Configuration options with GoldenGate

One of the biggest benefits of the GoldenGate technology is that it offers a lot of flexibility and can be used in varied ways to cater to different requirements. It’s important to mention that all the available configurations hold merit in their own perspective. Thus usage of each configuration is subjective to the business requirement and may vary from one customer to another. Following are the available system configurations that GoldenGate supports,

1. One-to-One (Unidirectional)
2. One-to-Many (Broadcast)
3. Many-to-One (Consolidation)
4. Bi-Directional (Active-Active)
5. Multimaster (Peer-to-Peer)
6. Cascading Data Marts

Following pictorial representation covers all these topologies.

Image Source: Oracle documentation

Since we have had a glance on the GoldenGate’s features and benefits, let’s get a closer look at the underlying technology.

GoldenGate-Technology Overview

GoldenGate is based on transaction log shipping and log-apply mechanism. Its architecture is made up of components that do the formerly mentioned tasks. It’s important to mention that deliberately, I haven’t mentioned Redo Log but just the word “log”. The reason for this is that GoldenGate is not meant to work only with Oracle database where transaction logs (the term used by other RDBMSs) are called Redo Logs. So if you are going to use GoldenGate with Oracle database, the terminology would be Redo Logs but for some other databases the terminology would be different.

Following key terms are essential for anyone to know who wants to use GoldenGate. In this part, I am going to give a very brief overview to introduce the essential components. In the subsequent articles, I shall go deeper into the processes etc.

Extract (Capture) Process

Extract process runs on the source side. This process, as the name suggests, captures the committed transactions from the source database and writes them into a GoldenGate’s proprietary format trail files. Extract process reads database’s transaction logs as the source for the committed transactions

Pump Extract

Pump Extract or commonly known as Pump process is an optional process but it’s highly recommended that it should be configured. It’s important to note that there is no relationship between Pump extract and the Data Pump utility available with Oracle database software. To avoid the confusion, it’s better to use the term Pump extract instead of Data Pump extract.

Pump extract resides on the source side just like the Capture process. The best usage of the Pump extract is to safeguard the replication from any sort of network or site crashes. Data manipulation, if required, can be done on the source side using the Pump extract. Also, Pump extract allows sending trail data to more than one targets.

Replicat (Apply) Process

Replicat, also known as Apply process, is configured on the target side. Replicat receives the data being sent by the Capture process in the form of the trail files. Trail files data is applied by Replicat on the target side. Integrity control for the data and to ensure that no data loss occurs, Replicat maintains a checkpointing mechanism.

Manager Process

This process is available on both the source and the target sides. Manager process controls the overall environment on both sides i.e. starting or restarting the extract and Replicats, performing the space management for the trail files, producing reports etc. There is one process on source and the target side.

Collector Process

These processes are available on the target side. A collector receives the committed data send by either the source Extract or Pump Extract and accumulates it in the target side trail (also known as remote trail). The resultant trail file is consumed by the Replicat.

Trail Files

If you know what Redo Logs are to the Oracle database, then you can assume the same for trail files for GoldenGate (for illustration purposes). Trail files collect the data that’s captured by the Extract process from the source database and similar sort of trail files are created on the target database side which would be consumed by the Replicat to apply the captured changes. Trail files are organized in the Canonical format in the commit order of the captured transactions in order to keep the transaction integrity. Furthermore, GoldenGate deploys its own checkpointing mechanism to keep a track of data being written to a trail file.

Parameter Files

GoldenGate configuration is all about the parameters that are configured for Extracts and Replicats. These parameters are configured in process parameter files which are stored in a dedicated folder within GoldenGate home. These files are plain text files and can be edited by any text editor i.e. Notepad or GEdit.

Like it’s said a picture is worth thousand words, let’s put it all in a pictorial diagram as shown below,

Image source: Oracle Documentation

We haven’t discussed what an “Initial Load” is yet. It’s something that we shall dive into after understanding the configuration options of the processes i.e. Extracts, Replicats in a subsequent part of the series.

GoldenGate Product Family

GoldenGate in itself is for data replication between two databases. But it’s complemented by a further couple of products that make the usage of GoldenGate much easier and efficient. The following are all part of GoldenGate family.

GoldenGate Director

Director is a GUI-based product for the complete administration of the GoldenGate environment configurement. It offers nice GUI interface for administration of the core processes i.e. Extract, Replicat etc. and also provides monitoring of the entire environment. It’s a part of GoldenGate Management pack.

GoldenGate Monitor

Monitor is another product in the GoldenGate family and part of Management Pack. It runs on a web browser and displays the entire configuration of the GoldenGate environment including details of the core processes, their statistics and lot more. Unlike Director, it doesn’t have capability to make changes in the configuration but it can be used to create notifications via alerts.

GoldenGate Veridata

Veridata offers data verification services for the data that’s being sent from the source database and is applied over the target database. Any data discrepancies are checked by Veridata and are reported by it without putting any additional impact on either the source or the target databases.

Besides the above mentioned products, GoldenGate configurations can take use of Cloud Control 12c and also the Oracle GoldenGate Adapters.

Wrapping-up

In this first part of GoldenGate series, we have got the introduction of the GoldenGate as a replication software, features & benefits offered by it. We had a glance at the technology underneath along with the complimenting products for it. In the next part, we shall understand the installation of GoldenGate 12c over Linux operating system. Stay tuned!