Troubleshooting GoldenGate Bidirectional (Active-Active) Replication 2 - Configuration of Macros and Exceptions Handling Routines

  For Part 1 click here: Troubleshooting GoldenGate Bidirectional (Active-Active) Replication 1 - Conflict Resolution by Using Odd and Even Sequences

Table of Contents

Document Control    
Introduction
1. Overview of Default Exceptions Handling in GoldenGate
    1.1 Description of Current Setup
    1.2 Current GoldenGate Parameter Files
    1.3 Current Status of Goldengate Processes
2. Creating a Collision
    2.1 Breaking Current GoldenGate Setup - Inserting Unique Row on SOURCE and TARGET in Oracle
    2.2 Checking GoldenGate Processes State
3. Resolution - Building Macro and Exceptions
    3.1. Explanation of MACRO Parameters
    3.2 Creating EXCEPTIONS table in GoldenGate Admin Schema
    3.3 Starting Replication with New MACRO Parameters and Logging Exceptions
4. Testing the Macro and Exceptions Handling

Change Record

Reviewers

Distribution

This publication may not be reproduced, stored in a retrieval system, or transmitted in whole or in part, in any form, or by any means, electronic, mechanical, photocopying, recording or otherwise, without the prior written permission of Vladimir Grigorian.

Introduction

Purpose

The purpose of this document is document installation, configuration, testing and monitoring setup of Oracle GoldenGate

In this document details are provided on various parameters, their location and in some cases recommended settings. 

Audience

This document is intended for:

·         DBAs and Architects

Assumptions

Following assumptions have been verified prior to installing and configuring Oracle GoldenGate.

1.       All specified Hardware Requirements for customer’s platform have been met.

2.       All specified Operating System Requirements for source and destination have been satisfied.

3.       All Oracle recommended settings have been set on database level.

Conflicts take place in a bidirectional environment when the same row in a given table is updated or inserted around the same time on both source and target sites.  

GoldenGate (unlike Oracle Streams) doesn't have a default conflict resolution routine other than using HANDLECOLLISONS. This option, however, is only recommended to be used during initial load and is beside the point in this document.

If an error is encountered, replicat usually abends (rolls back a transaction and then quits while extract and datapump continue piling up the trail files). If this happens in a large production environment trail files will quickly fill the dirdat directory and most importantly, replication stalls until errors are resolved. In some environments, GoldenGate alert log is periodically truncated which leaves no residual information to troubleshoot with.

To enable detailed exceptions logging and allow continuing replication we will configure exceptions handling with Goldengate macros and GGATE.EXCEPTIONS table.

1.1 Description of Current Setup

Lets first test default replication error handling.

In previous document we configured bidirectional replication with the following processes and parameters. GoldenGate does not replicate Oracle database sequences values in an active-active environment. We used odd and even sequences to take care of an issue. The sequences must insert values on the target database from its own sequences. Therefore, to ensure that the source and destination sequences are unique, it is recommended to generate odd and even values to each system. We have done it in previous document. This time will will use the same sequence because we want to intentionally create a collision, but have Goldengate automatically handle it for us and keep records of them in a special EXCEPTIONS table.

We have two servers:

In bidirectional GoldenGate configuration changes made to either database replicate to the other database in real time. The process works like this:

esource1 (extract on source) -> psource1 (datapump on source) -> rsource1 (replicat on target)
etarget1 (extract on target) -> ptarget1 (datapump on target) -> rtarget1 (replicat on source)

1.2 Current GoldenGate Parameter Files

Lets document current replication parameter files (please keep in mind we will have to change some of the parameters later on).

1.3 Current Status of GoldenGate Processes

Lets check status of GoldenGate processes before we started break things:

SOURCE:

GGSCI (source) 36> info all

Program         Status     Group        Lag Time    Since Chkpt

MANAGER        RUNNING
EXTRACT        RUNNING     ESOURCE1     00:00:00     00:00:08
EXTRACT        RUNNING     PSOURCE1     00:00:00     00:00:03
REPLICAT       RUNNING     RTARGET1     00:00:00     00:00:02


GGSCI (source) 37>

TARGET:

GGSCI (target) 8> info all

Program         Status     Group         Lag Time     Since Chkpt

MANAGER        RUNNING
EXTRACT        RUNNING     ETARGET1      00:00:00     00:00:03
EXTRACT        RUNNING     PTARGET1      00:00:00     00:00:02
REPLICAT       RUNNING     RSOURCE1      00:00:00     00:00:02


GGSCI (target) 9>

2.1 Breaking Current GoldenGate Setup - Inserting Unique Row on SOURCE and TARGET in Oracle

Now lets insert identical record into source, target and then see what happens when target tries to insert this record back into source, which already has it.

Both statements complete on source on target, but replicat RTARGET1 running on SOURCE server abends, while all other processes on both servers continue running.

2.2 Checking GoldenGate Processes State

Lets check what GoldenGate error log tells us on SOURCE (since replicat runs on this machine)

Now lets check report for RTARGET1:

As you can see this bidirectional replicat abended because of "ORA-00001: unique constraint (SOURCE.CONST_CUSTOMERS_UNQ) violated." after we successfully inserted the same row on source and then target, which then tried to replicate the same row back to source. This error (along with

Report and GoldenGate error log provided some useful information, but how do we ensure that:

1) Replicat does not abend but continues processing transactions if this sort of error is encountered?

2) GoldenGate stores indefinitely and provides more details on the error  - before/after image, a more detailed error message, timestamp to the milliseconds precision, as opposed to just seconds.

3.1. Explanation of MACRO Parameters

In an effort to configure exceptions handling with GoldenGate parameters and  macros and logging this information in a custom table we will have to perform the following steps:

Alter extract and replicat parameters.

These are new parameter files (new parameter mark in red):

Lets consider what this new parameters are for.

GETUPDATEBEFORES | IGNOREUPDATEBEFORES
Valid for Extract and Replicat
Use the GETUPDATEBEFORES and IGNOREUPDATEBEFORES parameters to control whether or not the before images of updated columns are included in the records that are processed by Oracle GoldenGate. Before images contain column details that existed before a record was updated. Use the GETUPDATEBEFORES parameter in the Extract parameter file to extract before images or in the Replicat parameter file to replicate before images. You can compare before images with after images to identify the net results of a transaction or perform other delta calculations. For example, if a BALANCE field is $100 before an update and $120 afterward, a comparison would show the difference of $20. You can use the column-conversion functions of Oracle GoldenGate to perform the comparisons and calculations.
You also can use GETUPDATEBEFORES to maintain a transaction-history table. For more information about performing delta calculations and using transaction history, see the Oracle GoldenGate Windows and UNIX Administrator’s Guide.
The GETUPDATEBEFORES and IGNOREUPDATEBEFORES parameters are table-specific. One parameter remains in effect for all subsequent TABLE or MAP statements, until the other parameter is encountered.

The macro and REPERROR above tell GoldenGate what information should be logged in EXCEPTIONS table and how to handle it establishing a new default . For exmple:

Now lets put these new parameters to work by stopping extract and replicat, adding the parameters and starting them up but only after we had created exceptions table below:

3.2 Creating EXCEPTIONS table in GoldenGate Admin Schema

Table created

3.3 Starting Replication with New MACRO Parameters and Logging Exceptions

Now lets start extract and replicat with the option "ALTER <> BEGIN NOW" with new macro and exceptions handling and make sure replicat is running.

4. Testing the Macro and Exceptions Handling

Lets run another statement (also identical, but different from the previous one because it will fail, the row already exists):

Lets check if GGATE table has captured the error:

 1 row selected

The contents of the table can tell a DBA enough information about the error.

Also, unlike before replicat continues running despite the error.

At this point we have configured a replicat macro with exceptions handling.

 Back to Top

Copyright © 2012 Vladimir Grigorian. All Rights Reserved