About Oracle GoldenGate Trails
Oracle GoldenGate trail information is required for troubleshooting and technical support. Use the Logdump utility to view the Oracle GoldenGate trail records.
Topics:
Trail Recovery Mode
By default, Extract operates in append mode, where if there is a process failure, a recovery marker is written to the trail and Extract appends recovery data to the file so that a history of all prior data is retained for recovery purposes.
In append mode, the Extract initialization determines the identity of the last complete transaction that was written to the trail at startup time. With that information, Extract ends recovery when the commit record for that transaction is encountered in the data source; then it begins new data capture with the next committed transaction that qualifies for extraction and begins appending the new data to the trail. A Replicat starts reading again from that recovery point.
Overwrite mode is another version of Extract recovery that was used in versions of Oracle GoldenGate prior to version 10.0. In these versions, Extract overwrites the existing transaction data in the trail after the last write-checkpoint position, instead of appending the new data. The first transaction that is written is the first one that qualifies for extraction after the last read checkpoint position in the data source.
If the version of Oracle GoldenGate on the target is older than version 10, Extract
will automatically revert to overwrite mode to support backward compatibility. This
behavior can be controlled manually with the RECOVERYOPTIONS
parameter.
Trail Record Format
Each change record written by Oracle GoldenGate to a trail or Extract file includes a header area, a data area, and possibly a user token area. The record header contains information about the transaction environment, and the data area contains the actual data values that were extracted.
The token area contains information that is specified by Oracle GoldenGate users for use in column mapping and conversion.
Oracle GoldenGate trail files are unstructured. You can view Oracle GoldenGate records with the Logdump utility provided with the Oracle GoldenGate software. For more information, see Logdump Reference for Oracle GoldenGate.
Note:
As enhancements are made to the Oracle GoldenGate software, the trail record format is subject to changes that may not be reflected in this documentation. To view the current structure, use the Logdump utility.Topics:
Trail File Header Record
Each file of a trail contains a file header record that is stored at the beginning of the file. The file header contains information about the trail file itself. Previous versions of Oracle GoldenGate do not contain this header.
The file header is stored as a record at the beginning of a trail file preceding the data records. The information that is stored in the trail header provides enough information about the records to enable an Oracle GoldenGate process to determine whether the records are in a format that the current version of Oracle GoldenGate supports.
The trail header fields are stored as tokens, where the token format remains the same across all versions of Oracle GoldenGate. If a version of Oracle GoldenGate does not support any given token, that token is ignored. Depracated tokens are assigned a default value to preserve compatibility with previous versions of Oracle GoldenGate.
To ensure forward and backward compatibility of files among different Oracle
GoldenGate process versions, the file header fields are written in a standardized
token format. New tokens that are created by new versions of a process can be
ignored by older versions, so that backward compatibility is maintained. Likewise,
newer Oracle GoldenGate versions support older tokens. Additionally, if a token is
deprecated by a new process version, a default value is assigned to the token so
that older versions can still function properly. The token that specifies the file
version is COMPATIBILITY and can be viewed in the Logdump utility
and also by retrieving it with the GGFILEHEADER option of the
@GETENV function.
A trail or Extract file must have a version that is equal to, or lower than, that of the process that reads it. Otherwise the process will abend. Additionally, Oracle GoldenGate forces the output trail to be the same version as that of its input trail or file. Upon restart, Extract rolls a trail to a new file to ensure that each file is of only one version (unless the file is empty).
From Oracle GoldenGate 21c onward, for Oracle databases, you can specify a globally
unique name for the database using the DB_UNIQUE_NAME parameter. If
this database parameter is not set, then the DB_UNIQUE_NAME is the
same as DB_NAME. This feature allows unique identification of the
source of the trail data by viewing the trail file header.
See GETENV parameter
to know about the use of the DbUniqueName token.
The DbUniqueName token will be written to trail files with 19.1
compatibility level, however prior Oracle GoldenGate releases supporting that
compatibility level will ignore the new token. The token belongs to the Database
Information group. The field will be limited to 65536 bytes, to allow fitting all
possible values of DB_UNIQUE_NAME, limited to 30 characters.
Because the Oracle GoldenGate processes are decoupled and can be of different Oracle
GoldenGate versions, the file header of each trail file contains a version
indicator. By default, the version of a trail file is the current version of the
process that created the file. If you need to set the version of a trail, use the
FORMAT option of the EXTTRAIL,
EXTFILE, RMTTRAIL, or RMTFILE
parameter.
You can view the trail header with the FILEHEADER command in the
Logdump utility. For more information about the tokens in the file header, see Logdump Reference for Oracle
GoldenGate.
Topics:
Partition Name Record in Trail File Header
Each DML record in the trail file header can contain an index to a partition name record (PNR). Because the full partition name can be long, a PNR is created in each trail file for the first time the partition is written. Each PNR, contains the partition name and partition object ID.
For primary Extract, PNR is generated only for partition matching and included byPARTITION and PARTITIONEXCLUDE parameters. DML
records from these partitions have an index to the table definition record and another
index to the partition name record. DML records from all other tables such as
non-partitioned tables or partitioned tables not matching or excluded by the
PARTITION or PARTITIONEXCLUDE parameters, only
have an index to the table definition record as done today. For the Distribution
Service, the PNR is written if source trail record contains a PNR index.
Viewing the Partition Name and PNR Index in Logdump
Use the Logdump utility to display the partition name record and the DML containing the PNR index.
$ logdump > output.txt <<EOF ghdr on detail data open ./dirdat/tr000000000 n 200 EOF
The output displays the PNR and the DML with the PNR index values, as shown in the following example:
HDR-IND : E (X45) PARTITION : . (XFF80) UNDOFLAG : . (X00) BEFOREAFTER: A (X41) RECLENGTH : 0 (X0000) IO TIME : 2019/01/17 16:48:01.129.045 IOTYPE : 170 (XAA) ORIGNODE : 4 (X04) TRANSIND : . (X03) FORMATTYPE : R (X52) SYSKEYLEN : 0 (X00) INCOMPLETE : . (X00) TDR/PNR IDX: (001, 002) AUDITPOS : 13287580 CONTINUED : N (X00) RECCOUNT : 1 (X01) 2019/01/17 16:48:01.129.045 METADATA LEN 0 RBA 3425 PARTITION NAME: P1 PARTITION ID: 75,234 FLAGS: X00000001 ___________________________________________________________________ HDR-IND : E (X45) PARTITION : . (XFF8C) UNDOFLAG : . (X00) BEFOREAFTER: A (X41) RECLENGTH : 18 (X0012) IO TIME : 2019/01/17 16:47:58.000.000 IOTYPE : 5 (X05) ORIGNODE : 255 (XFF) TRANSIND : . (X00) FORMATTYPE : R (X52) SYSKEYLEN : 0 (X00) INCOMPLETE : . (X00) AUDITRBA : 15 AUDITPOS : 13287580 CONTINUED : N (X00) RECCOUNT : 1 (X01) 2019/01/17 16:47:58.000.000 INSERT LEN 18 RBA 3486 NAME: TKGGU1.T1 (PARTITION: P1, TDR/PNR INDEX: 1/2) AFTER IMAGE: PARTITION X8C G B 0000 0500 0000 0100 3101 0005 0000 0001 0031 | ........1........1 COLUMN 0 (X0000), LEN 5 (X0005) 0000 0100 31 | ....1 COLUMN 1 (X0001), LEN 5 (X0005) 0000 0100 31 | ....1
Example of an Oracle GoldenGate Record
Figure 15-1 Example of an Oracle GoldenGate Record
Record Header Area
The Oracle GoldenGate record header provides metadata of the data that is contained in the record and includes the following information.
-
The operation type, such as an insert, update, or delete
-
The before or after indicator for updates
-
Transaction information, such as the transaction group and commit timestamp
Description of Header Fields
Table: Oracle GoldenGate record header fields
| Field | Description |
|---|---|
|
Should always be a value of E, indicating that the record was created by the Extract process. Any other value indicates invalid data. |
|
The length, in bytes, of the record buffer. |
|
The type of operation represented by the record. See Table 15-* for a list of operation types. |
|
The place of the record within the current transaction. Values are: 0 — first record in transaction 1 — neither first nor last record in transaction 2 — last record in the transaction 3 — only record in the transaction |
|
Identifies the transaction log identifier, such as the Oracle redo log sequence number. |
|
(Windows and UNIX) Identifies whether or not the record is a
segment of a larger piece of data that is too large to fit
within one record. LOBs, CLOBS, and some VARCHARs are stored in
segments. Unified records that contain both before and after
images in a single record (due to the
Y — the record is a segment; indicates to Oracle GoldenGate that this data continues to another record. N — there is no continuation of data to another segment; could be the last in a series or a record that is not a segment of larger data. |
|
For Windows and UNIX records, this field will always be a value of 4 (FieldComp compressed record in internal format). For these platforms, the term Partition does not indicate that the data represents any particular logical or physical partition within the database structure. |
|
Identifies whether the record is a before (B) or after (A) image
of an update operation. Records that combine both before and after
images as the result of the UPDATERECORDFORMAT
parameter are marked as after images. Inserts are always after
images, deletes are always before images.
|
|
The time when the operation occurred, in local time of the source system, in GMT format. This time may be the same or different for every operation in a transaction depending on when the operation occurred. |
|
Identifies whether the data was read from the transaction log or fetched from the database. F — fetched from databaseR — readable in transaction log |
|
This field is obsolete. |
|
Identifies the position in the transaction log of the data. |
|
(Windows and UNIX) Used for LOB data when it must be split into chunks to be written to the Oracle GoldenGate file. RecCount is used to reassemble the chunks. |
Using Header Data
Some of the data available in the Oracle GoldenGate record header can be used for
mapping by using the GGHEADER option of the @GETENV function or by
using any of the following transaction elements as the source expression in a
COLMAP statement in the TABLE or
MAP parameter.
-
GGS_TRANS_TIMESTAMP -
GGS_TRANS_RBA -
GGS_OP_TYPE -
GGS_BEFORE_AFTER_IND
Using Header Data
The data area of the Oracle GoldenGate trail record contains the following:
- The time that the change was written to the Oracle GoldenGate file
- The type of database operation
- The length of the record
- The relative byte address within the trail file
- The table name
- The data changes in hex format
The following explains the differences in record image formats used by Oracle GoldenGate on Windows, UNIX, Linux, and NonStop systems.
Topics:
Full Record Image Format (NonStop Sources)
A full record image contains the values of all of the columns of a processed row. Full record image format is generated in the trail when the source system is HP NonStop, and only when the IOType specified in the record header is one of the following:
3 — Delete 5 — Insert 10 — Update
Each full record image has the same format as if retrieved from a program reading the original file or table directly. For SQL tables, datetime fields, nulls, and other data is written exactly as a program would select it into an application buffer. Although datetime fields are represented internally as an eight-byte timestamp, their external form can be up to 26 bytes expressed as a string. Enscribe records are retrieved as they exist in the original file.
When the operation type is Insert or Update, the
image contains the contents of the record after the operation (the after image).
When the operation type is Delete, the image contains the contents
of the record before the operation (the before image).
For records generated from an Enscribe database, full record images are output unless
the original file has the AUDITCOMPRESS attribute set to
ON. When AUDITCOMPRESS is ON,
compressed update records are generated whenever the original file receives an
update operation. (A full image can be retrieved by the Extract process by using the
FETCHCOMPS parameter.)
Compressed Record Image Format (Windows, UNIX, Linux Sources)
A compressed record image contains only the key (primary, unique, KEYCOLS) and the columns that changed in the processed row. By default, trail records written by processes on Windows and UNIX systems are always compressed.
The format of a compressed record is as follows:
column_index
column_length
column_data[...]Where:
-
is the ordinal index of the column within the source table (2 bytes).column_index
is the length of the data (2 bytes).colum_length
is the data, includingcolumn_data
orNULL
length indicators.VARCHAR
field_offset
field_length field_value[...]Where:
-
is the offset within the original record of the changed value (2 bytes).field_offset
is the length of the data (2 bytes).field_length
is the data, includingfield_value
orNULL
length indicators.VARCHAR
The first field in a compressed Enscribe record is the primary or system key.
Tokens Area
The trail record also can contain two areas for tokens. One is for internal use and is not documented here, and the other is the user tokens area. User tokens are environment values that are captured and stored in the trail record for replication to target columns or other purposes. If used, these tokens follow the data portion of the record and appear similar to the following when viewed with Logdump:
| Parameter | Value |
|---|---|
TKN-HOST TKN-GROUP TKN-BA_IND TKN-COMMIT_TS TKN-POS
TKN-RBA TKN-TABLE TKN-OPTYPE TKN-LENGTH
TKN-TRAN_IND |
: syshq : EXTORA : AFTER : 2011-01-24 17:08:59.000000 :
3604496 : 4058 : SOURCE.CUSTOMER : INSERT : 57 :
BEGIN |
Oracle GoldenGate Operation Types
The following are some of the Oracle GoldenGate operation types. Types
may be added as new functionality is added to Oracle GoldenGate. For a more updated
list, use the SHOW RECTYPE command in the Logdump utility:
| Type | Description | Platform |
|---|---|---|
| 1-Abort | A transaction aborted. | NSK TMF |
| 2-Commit | A transaction committed. | NSK TMF |
| 3-Delete | A record/row was deleted. A Delete record
usually contains a full record image. However, if the
COMPRESSDELETES parameter was used, then only
key columns will be present.
|
All |
| 4-EndRollback | A database rollback ended | NSK TMF |
| 5-Insert | A record/row was inserted. An Insert record
contains a full record image.
|
All |
| 6-Prepared | A networked transaction has been prepared to commit. | NSK TMF |
| 7-TMF-Shutdown | A TMF shutdown occurred. | NSK TMF |
| 8-TransBegin | No longer used. | NSK TMF |
| 9-TransRelease | No longer used. | NSK TMF |
| 10-Update | A record/row was updated. An Update record
contains a full record image. Note: If the partition indicator in
the record header is 4, then the record is in
FieldComp format (see below) and the update is
compressed.
|
All |
| 11-UpdateComp | A record/row in TMF AuditComp format was
updated. In this format, only the changed bytes are present. A
4-byte descriptor in the format of
2-byte_offset2-byte_length precedes each data
fragment. The byte offset is the ordinal index of the column within
the source table. The length is the length of the data.
|
NSK TMF |
| 12-FileAlter | An attribute of a database file was altered. | NSK |
| 13-FileCreate | A database file was created. | NSK |
| 14-FilePurge | A database file was deleted. | NSK |
| 15-FieldComp | A row in a SQL table was updated. In this format, only the
changed bytes are present. Before images of unchanged columns are
not logged by the database. A 4-byte descriptor in the format of
2-byte_offset2-byte_length precedes each data
fragment. The byte offset is the ordinal index of the column within
the source table. The length is the length of the data. A partition
indicator of 4 in the record header indicates
FieldComp format.
|
All |
| 16-FileRename | A file was renamed. | NSK |
| 17-AuxPointer | Contains information about which AUX trails have new data and the location at which to read. | NSK TMF |
| 18-NetworkCommit | A networked transaction committed. | NSK TMF |
| 19-NetworkAbort | A networked transaction was aborted. | NSK TMF |
| 90-(GGS)SQLCol | A column or columns in a SQL table were added, or an attribute changed. | NSK |
| 100-(GGS)Purgedata | All data was removed from the file
(PURGEDATA).
|
NSK |
| 101-(GGS)Purge(File) | A file was purged. | NSK non-TMF |
| 102-(GGS)Create(File) | A file was created. The Oracle GoldenGate record contains the file attributes. | NSK non-TMF |
| 103-(GGS)Alter(File) | A file was altered. The Oracle GoldenGate record contains the altered file attributes. | NSK non-TMF |
| 104-(GGS)Rename(File) | A file was renamed. The Oracle GoldenGate record contains the original and new names. | NSK non-TMF |
| 105-(GGS)Setmode | A SETMODE operation was performed. The Oracle
GoldenGate record contains the SETMODE
information.
|
NSK non-TMF |
| 106-GGSChangeLabel | A CHANGELABEL operation was performed. The
Oracle GoldenGate record contains the CHANGELABEL
information.
|
NSK non-TMF |
| 107-(GGS)Control | A CONTROL operation was performed. The Oracle
GoldenGate record contains the CONTROL
information.
|
NSK non-TMF |
|
115 and 117 (GGS)KeyFieldComp(32) |
A primary key was updated. The Oracle GoldenGate record contains
the before image of the key and the after image of the key and the
row. The data is in FieldComp format (compressed),
meaning that before images of unchanged columns are not logged by
the database.
|
Windows and UNIX |
|
116-LargeObject 116-LOB |
Identifies a RAW, BLOB,
CLOB, or LOB column. Data of
this type is stored across multiple records.
|
Windows and UNIX |
| 132-(GGS) SequenceOp | Identifies an operation on a sequence. | Windows and UNIX |
|
134-UNIFIED UPDATE 135-UNIFIED PKUPDATE |
Identifies a unified trail record that contains both before and
after values in the same record. The before image in a
UNIFIED UPDATE contains all of the columns that
are available in the transaction record for both the before and
after images. The before image in a UNIFIED UPDATE
contains all of the columns that are available in the transaction
record, but the after image is limited to the primary key columns
and the columns that were modified in the
UPDATE.
|
Windows and UNIX |
| 160 - DDL_Op | Identifies a DDL operation | Windows and UNIX |
|
161- RecordFragment |
Identifies part of a large row that must be stored across multiple records (more than just the base record). | Windows and UNIX |
|
200-GGSUnstructured Block 200-BulkIO |
A BULKIO operation was performed. The Oracle
GoldenGate record contains the RAW DP2
block.
|
NSK non-TMF |
| 201 through 204 |
These are different types of NonStop trace records. Trace records are used by Oracle GoldenGate support analysts. The following are descriptions.
|
NSK non-TMF |
| 205-GGSComment | Indicates a comment record created by the Logdump utility.
Comment records are created by Logdump at the beginning and end of
data that is saved to a file with Logdump's SAVE
command.
|
All |
| 249 through 254 |
These are different types of NonStop trace records. Trace records are used by Oracle GoldenGate support analysts. The following are descriptions.
|
NSK non-TM |