Skip to main content

Oracle GoldenGate

GG Trail Reader can be used to write Striim applications for auditing and troubleshooting Oracle GoldenGate. If your Striim cluster is licensed for Oracle GoldenGate, GG Trail Reader will be available in the Flow Designer. Striim supports GoldenGate 11g (all versions), 12c (all versions), 18.1, and 19.1.

Running GG Trail Reader on the GoldenGate host

If the GoldenGate trail files are not directly readable over the network by the Striim host, the GG Trail Reader source must be run on the remote GoldenGate host using the Striim Forwarding Agent. See Striim Forwarding Agent installation and configuration.

GG Trail Reader properties

To rewrite an application using FileReader + GG Trail Parser to use GG Trail Reader, copy the values for the deprecated old properties to the corresponding new properties:

  • FileReader Compression Type > GG Trail Reader Trail Compression type

  • FileReader Directory > GG Trail Reader Trail Directory

  • FileReader Wildcard > GG Trail Reader Trail FIle Pattern

  • GG Trail Parser Metadata > GG Trail Reader Definition File

property

type

default value

notes

CDDL Action

enum

Process

12.2 or later for Oracle Database only: see Handling schema evolution.

CDDL Capture

Boolean

False

12.2 or later for Oracle Database only: see Handling schema evolution. When set to True, you must specify a Definition File.

Charset Map

String

Optionally, override the GoldenGate source character set mapping (see SOURCECHARSET using the syntax <character set 1>,<Java charset 1>;<character set 2>,<Java charset 2>;....

Compression

Boolean

False

If set to True, update operations for tables that have primary keys include only the primary key and modified columns, and delete operations include only the primary key. With the default value of False, all columns are included.

DB Charset ID

String

When the database does not use the ASCII character set, specify the character set here, for example, Cp037. When this property is specified, the Support Column Charset setting is ignored.

Definition File

String

With GoldenGate version 12.2 or later, leave this property blank to read the metadata from the trail file.

Otherwise, specify the path (from root or relative to the .../Striim directory) and name of a GoldenGate source definition file (generated by the GoldenGate defgen utility) containing the metadata description of all the tables for which trail data was captured. When using CDDL, this file is required, and it must contain metadata for all tables specified in Tables and not in Excluded Tables.

Do not specify the same file for the Definition File and Trail File Pattern. They should have different prefixes.

Exclude Tables

String

Data for any tables specified here will not be returned. For example, if Tables uses a wildcard, data from any tables specified here will be omitted. Multiple table names (separated by semicolons) and wildcards may be used exactly as for Tables.

Filter Transaction Boundaries

Boolean

True

With the default value of True, begin and commit transactions are filtered out. Set to False to include begin and commit transactions.

Start Position

String

Optionally, specify an offset (FileName:<filename>; offset:<offset>) or RBA value (FileName:<filename>; RBA:<RBA value>) from which to start reading the trail file(s). The value must be either 0 or a valid record position.

Offset and RBA two names for the same thing. When you see RBA 1863 in a GoldenGate trail file, that will show up in the corresponding WAEvent metadata as "FileOffset":1863.

If you are using schema evolution (see Handling schema evolution, set a Start Position only if you are sure that there have been no DDL changes after that point.Handling schema evolution

Support Column Charset

Boolean

False

Use the default value of False when all columns use the ASCII character set.

Set to True if the data contains a mix of ASCII and non-ASCII columns. The DEFGEN must include the database locale and character set and the character set for each column. When a character set is specified using the DB Charset ID property, this setting is ignored.

Tables

String

%

The table(s) to be read. With the default value, all tables will be read. Alternatively, specify one or more table names, separated by semicolons, or a string ending with the % wildcard, such as HR.%. The % wildcard is allowed only at the end of the string. For example, mydb.prefix% is valid, but mydb.%suffix is not.

With GoldenGate 12.2 or later, when a DDL operation (CREATE, ALTER, DROP, or REPLACE) is performed on one of the specified tables, GG Trail Reader will terminate. With earlier versions, GG Trail Reader may terminate unpredictably at a later time.

Trail Byte Order

String

BigEndian

Set to LittleEndian if that is the TRAILBYTEORDER of the trail file.

Trail Compression Type

String

Set to gzip when Trail File Pattern specifies a file or files in gzip format. Otherwise, leave blank.

Trail Directory

String

Specify the path to the directory containing the trail files.

Trail File Pattern

String

Specify the name of the file, or a wildcard pattern to match multiple files. When reading multiple files, Striim will read them in the default order for the operating system. Once Striim has read a file, it will ignore any further updates to it.

Do not specify the same file for the Definition File and Trail File Pattern. They should have different prefixes.

Sample:

CREATE SOURCE GGTrailSource USING GGTrailReader (
  TrailDirectory:'Samples/GG/data',
  TrailFilePattern:'rt*',
  DefinitionFile:'Samples/GG/PosAuthorizationsDef.def'
)
OUTPUT TO GGTrailStream;

GG Trail Reader WAEvent fields

The output data type for GG Trail Reader is WAEvent. The elements are:

metadata: a map including:

  • CSN: the Commit Sequence Number for the transaction

  • FileName: name of the trail file from which the operation was read

  • Offset: the position of the operation record in the trail file

  • OperationName: INSERT, UPDATE, or DELETE

    When schema evolution is enabled, OperationName for DDL events will be Alter, AlterColumns, Create, or Drop. This metadata is reserved for internal use by Striim and subject to change, so should not be used in CQs, open processors, or custom Java functions.

  • Oracle ROWID: the Oracle ID for the inserted, updated, or delete row

  • TxnID: transaction ID

  • TimeStamp: timestamp from the CDC log

  • TableName: fully qualified name of the table

To retrieve the values for these elements, use the META function. See Parsing the fields of WAEvent for CDC readers.

data: an array of fields, numbered from 0, containing:

  • for an INSERT or DELETE operation, the values that were inserted or deleted

  • for an UPDATE, the values after the operation was completed

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

before (for UPDATE operations only): the same format as data, but containing the values as they were prior to the UPDATE operation

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

GG Trail Reader sample code and output

Sample code:

CREATE SOURCE GgTrailReadSrc USING GGTrailReader (
  TrailDirectory:’./Samples/AppData/gg’,
  TrailFilePattern:’b1*’,
  DefinitionFile:’./Samples/AppData/gg/GGReadMultipleTables.def’,
  startPosition:’Filename:b1000000;offset:1039’,
  ExcludeTables :’QATEST.MULTTBL2’
)
OUTPUT TO GGTrailReadStream;

Sample output, insert:

data: ["1","CharInsert1         ","VCahrInsert1","1","1.1",[2000,4,22,0,0,0,0],
[2015,10,29,12,57,37,153],"NCharInsert1        ","NVCharInsert1","1"]
  metadata: {"TableID":0,"TableName":"QATEST1.MULTTBL3","TxnID":"2.17.1883",
"OperationName":"INSERT","FileName":"b1000000","FileOffset":1701,
"TimeStamp":1446103675037,"Oracle ROWID":"AAAXOvAAEAAAAyEAAA","CSN":"2222016",
"RecordStatus":"VALID_RECORD"}
  userdata: null
  before: null
  dataPresenceBitMap: "fwc="
  beforePresenceBitMap: "AAA="
  typeUUID: {"uuidstring":"01eaaa3d-0dc1-4b81-b8b4-acde48001122"}
};

Sample output, update:

  data: ["1","Update1             ","Update1","2","2.2",[1970,12,12,0,0,0,0],
[2015,10,29,12,58,39,327],"Update1             ","Update1",null]
  metadata: {"TableID":0,"TableName":"QATEST1.MULTTBL3","TxnID":"2.25.1884",
"OperationName":"UPDATE","FileName":"b1000000","FileOffset":3268,
"TimeStamp":1446103733953,"Oracle ROWID":"AAAXOvAAEAAAAyEAAA","CSN":"2222056",
"RecordStatus":"VALID_RECORD"}
  userdata: null
  before: ["1",null,null,null,null,null,null,null,null,null]
  dataPresenceBitMap: "fwM="
  beforePresenceBitMap: "AQA="
  typeUUID: {"uuidstring":"01eaaa3d-0dc1-4b81-b8b4-acde48001122"}
};

Sample output, delete:

  data: ["1",null,null,null,null,null,null,null,null,null]
  metadata: {"TableID":0,"TableName":"QATEST1.MULTTBL3","TxnID":"6.30.2195",
"OperationName":"DELETE","FileName":"b1000000","FileOffset":4379,
"TimeStamp":1446103861964,"Oracle ROWID":"AAAXOvAAEAAAAyEAAA","CSN":"2222189",
"RecordStatus":"VALID_RECORD"}
  userdata: null
  before: null
  dataPresenceBitMap: "AQA="
  beforePresenceBitMap: "AAA="
  typeUUID: {"uuidstring":"01eaaa3d-0dc1-4b81-b8b4-acde48001122"}
};

GG Trail Reader data type support and correspondence

Golden Gate data type

scale

Striim type

0

n/a

String

1

n/a

String

2

n/a

Double

64

n/a

String

65

n/a

String

66

n/a

Double

130

> 0

Double

130

<=0

Short

131

> 0

Double

131

<=0

Integer

132

> 0

Double

132

<=0

Integer

133

> 0

Double

133

<=0

Long

134

> 0

Double

134

<=0

Long

135

> 0

Double

135

<=0

Long

140

n/a

Double

141

n/a

Double

142

n/a

Double

143

n/a

Double

150

n/a

Double

151

n/a

Double

152

n/a

Double

153

n/a

Double

154

n/a

Double

155

n/a

Double

191

n/a

org.joda.time.DateTime

192

n/a

org.joda.time.DateTime

195

n/a

Integer

196

n/a

Integer

197

n/a

Integer

198

n/a

Integer

199

n/a

Integer

200

n/a

Integer

201

n/a

Integer

202

n/a

Integer

203

n/a

Integer

204

n/a

Integer

205

n/a

Integer

206

n/a

Integer

207

n/a

Integer

208

n/a

Integer

209

n/a

Integer

210

n/a

Integer

211

n/a

Integer

212

n/a

Integer