Capturing Before Images in the Oracle GoldenGate Exception Table

I was recently asked by a customer of mine how to capture both the before image and the after image of a conflict in the Oracle GoldenGate exception table.  I didn’t know the answer but I was determined to find out.  It turns out that the answer was much easier than I thought it would be, but unfortunately a little more manual than I was hoping for.

In order to capture the before image of a column, simply use the @BEFORE function in your mapping statement for the exceptions.  Here is an example:

On the source side I have a table test1 with three columns; col1 int, col2 int, col3 int with col1 being the primary key.  This table is also created on the target side as well.  It is populated with the values:

SQL> select * from test1;

 

COL1       COL2       COL3

———- ———- ———-

1          1          1

2          2          2

The extract includes the table statement:

TABLE test.* GETBEFORECOLS( ON UPDATE ALL, ON DELETE ALL);

This will collect before images on both update and delete statements.

On the replicat I will create the following map statement for the table test1.

MAP TEST.TEST1, TARGET TEST.TEST1, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, DISCARD)),

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, DISCARD)),

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, DISCARD)),

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, DISCARD)),

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

This will generate an exception if a conflict exists (which I will create momentarily).

In addition to the test table, I will also create an exceptions table which will consist of the after image, the before image and a lot of stuff that I like to collect for conflicts.

create table test1_exceptions

( col1 int

, col2 int

, col3 int

, beforecol1 int

, beforecol2 int

, beforecol3 int

, beforeafter varchar(10)

, hostname varchar2(40)

, groupname varchar2(10)

, grouptype varchar2(10)

, database_name varchar2(61)

, table_name varchar2(61)

, errno int

, dberrmsg varchar2(4000)

, optype varchar2(20)

, errtype varchar2(20)

, committimestamp timestamp

, errortime timestamp

, ggtrailfile varchar2(40)

, filerba int

);

Now conditions are ready to map to my exceptions table.  I will use the following map statements in order to achieve the goal of collecting both the before and after image.

MAP TEST.TEST1, TARGET TEST.TEST1_EXCEPTIONS

, EXCEPTIONSONLY

, INSERTALLRECORDS

, COLMAP (

USEDEFAULTS

, beforecol1 = @BEFORE(col1)

, beforecol2 = @BEFORE(col2)

, beforecol3 = @BEFORE(col3)

, beforeafter = @GETENV (‘GGHEADER’, ‘BEFOREAFTERINDICATOR’)

, hostname = @GETENV (‘GGENVIRONMENT’, ‘HOSTNAME’)

, groupname = @GETENV (‘GGENVIRONMENT’, ‘GROUPNAME’)

, grouptype = @GETENV (‘GGENVIRONMENT’, ‘GROUPTYPE’)

, database_name = @GETENV (‘DBENVIRONMENT’, ‘DBNAME’)

, table_name = @GETENV (‘GGHEADER’, ‘TABLENAME’)

, errno = @GETENV (‘LASTERR’, ‘DBERRNUM’)

, dberrmsg = @GETENV (‘LASTERR’, ‘DBERRMSG’)

, optype = @GETENV (‘LASTERR’, ‘OPTYPE’)

, errtype = @GETENV (‘LASTERR’, ‘ERRTYPE’)

, committimestamp = @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’)

, errortime = @DATENOW()

, ggtrailfile = @GETENV (‘GGFILEHEADER’, ‘FILENAME’)

, filerba = @GETENV (‘RECORD’, ‘FILERBA’)

);

Notice how I used the @BEFORE function to map the before image of my test1 table.  So, now let’s see how it works in practice.

Step 1:

Check the values of test1 on the target.

SQL> select * from test1;

 

COL1       COL2       COL3

———- ———- ———-

1          1          1

2          2          2

Step 2:

Create the conflict condition on the target system

SQL> update test1 set col2=2 where col1=1;

 

1 row updated.

Step 3:

Cause the conflict.  On the source run the same query that was run on the target.

SQL> update test1 set col2=2 where col1=1;

 

1 row updated.

Step 4:

Check the exceptions table for both the before and after images. SQL> select col1, col2, col3, beforecol1, beforecol2, beforecol3 from test1_exceptions;

 

COL1       COL2       COL3 BEFORECOL1 BEFORECOL2 BEFORECOL3

———- ———- ———- ———- ———- ———-

1          2          1          1          1          1

This is as expected.  The before image on the source is being shown correctly here.  So, in order to access the before image in the exceptions table, use the @BEFORE function.