Oracle GoldenGate: HP NonStop Audited Enscribe to IBM DB2

Purpose

Upon completion of this Oracle-By-Example (OBE) tutorial, you will be able to keep two heterogeneous databases synchronized; in this case an HP NonStop audited Enscribe database (source) to an IBM DB2 database (target) using Oracle GoldenGate.

During this lesson, you will learn how to:

• Prepare your environment to configure the GoldenGate processes.
• Configure and execute the initial data load process.
• Configure and start the change capture of database operations.
• Configure and start the change delivery of database operations.

Time to Complete

Approximately 2 hours

Introduction

Using Oracle GoldenGate for HP NonStop Audited Enscribe to IBM DB2 Database Synchronization

Oracle GoldenGate provides very fast replication of heterogeneous databases by reading transaction logs and writing the changes to one or more target databases. There are five processes involved in a typical environment:

Manager:

There is a Manager on the source host and a separate Manager on the target host. The Manager starts and stops the other processes on the source and target instances. The Manager is not required for passing traffic once the Extract or Replicat is running.

Initial Load:

Optional. Used to populate the target tables one time. It can read either from the source tables directly or from ASCII files.

Extract:

Runs on the source to capture transaction data to trail files. There are two versions of Extract: Classic (this OBE) and Integrated.

Data Pump:

Optional, but highly recommended. The Data Pump sends trail files from the source instance to the target instance over an IP network. Technically the Data Pump is a secondary Extract. This OBE does not have a Data Pump.

Replicat:

Delivers data to the target database. Normally the Replicat runs on the target.

NonStop Audited Enscribe to DB2 configuration

The following diagram illustrates GoldenGate a configuration with NonStop audited source data being replicated to a DB2 target database.

In summary, the environment is:

Prerequisites

The source is an audited Enscribe database on HP NonStop and the target is an IBM DB2 database.

Bear in mind that there are two hosts: host01 and host02; and that each host has three environment prompts: OS, GGSCI, and SQL. That makes six different places in which you could be typing! Try to be extra careful about which command you enter in which location. The wrong command in the wrong context is the most common error.

Overview of Tasks

Prepare the Environment

In order to execute this lesson, the GoldenGate application must be installed on both the source and target systems. The installation includes a sample database and scripts to generate initial data as well as subsequent update operations. The source and target tables are created and loaded with initial data. The GoldenGate Manager processes are also started so that other processes may be configured and started.

Configure Initial Data Load

Initial database synchronization is almost always required. To initially load data across heterogeneous databases, you have limited choices. GoldenGate provides the ability to perform initial data synchronization while your application remains active. The capture process also provides flexible output options, including the capability to format the data to be loaded with bulk load utilities, such as DB2 Load.

This lesson demonstrates using the direct load method to capture data directly from the source tables and send the data directly to the delivery process on the target system.

Configure Change Capture (Extract)

For audited tables, the Extract process is configured to capture change data directly from the TMF audit trails and store the changes in a data queue known as GoldenGate remote trails.

Configure Change Delivery (Replicat)

Once the tables have been initially loaded with data, the delivery process is configured to synchronize the captured change data into the target database.

Typographic Conventions

Text color and font in the directions and in the screens should be interpreted as follows:

Some_Command

You type this as a command or a value. Example:
Enter ./ggsci to start the command line interpreter.

Some_Prompt

The system responds with this as a prompt or reply. Example:
After the welcome splash banner, you can enter commands at the GGSCI (host01) 1> prompt.

Some_Button

Click this on-screen button. Example:
After selecting the version you want, click Continue to start the download.

Some_Variable

A variable that you substitute with a real value. Example:
Enter your userid/password at the prompt.

Some_Filename

A filename, path, or folder/directory. Example:
Edit the hosts file in the /etc directory.

Some_Code

A keyword or code element. Example:
Change the parameter HandleCollisions to NoHandleCollisions after the initial load.

File and Process Naming Conventions

File and process naming conventions can be whatever works for you or your company. Here are the sample conventions used in this OBE:
  pxxxxhh.ext
where:

p=process=

e(xtract), p(ump), r(eplicat), i(nitial), d(efgen), s(tartup).

xxxx=project=

All files related to a common project xxxx, for example hr, sales, engr, test.

hh=host-to-host=

aa, ab, ba, bb, as indicated by source and target host names where a=host01, and b=host02. Later on you will see that you cannot use numbers as part of some file names, so better not to go there at all.

ext=extension=

prm=parameter (stored in dirprm/), dsc=discard, rpt=report (stored in dirrpt/), def=definition (stored in dirdef/), oby=obey (stored in installation directory), sql=SQL (stored in dirsql/).

Objective

The goals of this exercise are to:

• Configure and start the Manager processes.
• Prepare the source and target sample databases.

Objective

The goals of this exercise are to:

• Configure the initial load capture task.
• Configure the delivery of the data to the target system.
• Execute and verify the initial load of data.

Objective

The goals of this exercise are to:

• Configure and add the Extract process that will capture changes.
• Add the trail that will store these changes.
• Start the capture (Extract) process.

Objective

The goals of this exercise are to:

• Set up the checkpoint table on the target system.
• Create a named group that includes the Replicat process and the checkpoint tables.
• Configure the Replicat group by adding parameters.
• Start the Replicat group.

Objective

The goals of this exercise are to:

• Execute miscellaneous update, insert, and delete operations on the source system.
• Verify the delivery of the changes to the target.
• Turn off the error handling used for the initial load.

This completes the generation of activity.

Oracle GoldenGate can do far more than was demonstrated in this simple exercise.

In this tutorial, you have learned how to:

• Configure and start Extract and Replicat processes for Initial Load of tables
• Generate sample data and test the validity of the replication
• Manage the reports produced by the processes

Resources

Help topics relevant to the topic of this tutorial:

• Oracle GoldenGate Product Documentation version 11.2.1 (E35209-01) and other older versions
• Courses from Oracle University
• External Web sites for related information
• To learn more about Oracle GoldenGate, refer to additional OBEs in the Oracle Learning Library
• The original PDF of this OBE locally. The HTML-based version supercedes the PDF-based version. You can see the original listed by going here, you can view the original online here.

Credits

• Lead Curriculum Developer: Steve Friedberg.
• Other Contributors: Doug Reid, Chris Lawless, Hadi Koesnodihardjo.