Oracle GoldenGate NonStop SQL/MP to Oracle Database -- Initial Data Load

Purpose

Upon completion of this Oracle-By-Example (OBE) tutorial, you will be able to keep two heterogeneous databases synchronized; in this case HP NonStop SQL/MP to Oracle 11g.

During this lesson, you will learn how to:

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

Time to Complete

Approximately 1 hour

Introduction

Using Oracle GoldenGate for Audited NonStop SQL/MP to Oracle Database Synchronization -- SQLLoader Initial Data Load and Data Pump for CDC

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. This OBE focuses on the Initial Load reading from source tables directly.

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.

Audited NonStop to Oracle data pump configuration

The diagram illustrates a configuration using a primary Extract process creating a local trail. This trail is read by a data pump Extract, which then creates a remote trail on the target. The Replicat process applies the changes from the remote trail to the target database.

In summary, the environment is:

Prerequisites

The source is a SQL/MP database on the HP NonStop and the target is an Oracle 11g database on a Windows or UNIX platform.

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.

Initial Data Load

To initially load data between heterogeneous databases, use the GoldenGate application to perform your initial data load while the application remains active. The direct method uses Extract to pull data from the source files and send the data directly to Replicat on the target system.

This lesson uses Extract to create ASCII files that are sent to the target to be loaded by the SQL*Loader utility.

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 a GoldenGate local trail. A data pump Extract reads the local trail sending the data to a remote GoldenGate trail on the target.

Configure Change Delivery (Replicat)

Once the tables have been initially loaded with data and the Extract process is capturing all operations, the Replicat process is configured to deliver the captured operations to 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.
• Generate a data definition and copy it to the target system.

Objective

The goals of this exercise are to:

• Configure and execute the initial Extract to create an ASCII delimited input file.
• Configure and execute the initial Replicat to generate SQL*Loader run and control files.
• Execute SQL*Loader to populate the target tables.

Objective

The goals of this exercise are to:

• Configure and add the Extract process that will capture changes.
• Add the local trail that will store these changes.
• Configure and add a data pump Extract to read the local trail and create a remote trail on the target.
• Start the two Extract processes.

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.