Oracle GoldenGate Checkpoint Tables

When database checkpoints are being used, Oracle GoldenGate creates a checkpoint table with a user-defined name in the database upon execution of the ADD CHECKPOINTTABLE command, or a user can create the table by using the chkpt_db_create.sql script (where db is an abbreviation of the type of database that the script supports).

There are two tables: the main checkpoint table and an auxiliary checkpoint table that is created automatically. The auxiliary table, known as the transaction table, bears the name of the primary checkpoint table appended with _lox. Each Replicat, or each thread of a coordinated Replicat, uses one row in the checkpoint table to store its progress information.

At checkpoint time, there typically are some number of transactions (among the total n transactions) that were applied, and the rest are still in process. For example, if Replicat is processing a group of n transactions ranging from CSN1 to CSN3. CSN1 is the high watermark and CSN3 is the low watermark. Any transaction with a CSN higher than the high watermark has not been processed, and any transaction with a CSN lower than the low watermark has already been processed. Completed transactions are stored in the LOG_CMPLT_XID column of the checkpoint table. Any overflow of these transactions is stored in the transaction table (auxiliary checkpoint table) in the LOG_CMPLT_XID column of that table.

Currently, Replicat (or each Replicat thread of a coordinated Replicat) applies transactions serially (not in parallel); therefore, the high watermark (the LOG_CSN value in the table) is always the same as the low watermark (the LOG_CMPLT_CSN value in the table), and there typically is only one transaction ID in the LOG_CMPLT_XID column. The only exception is when there are multiple transactions sharing the same CSN.

Do not change the names or attributes of the columns in these tables. You can change table storage attributes as needed.

Table 13-4 Checkpoint table definition

Column	Description
`GROUP_NAME (primary key)`	The name of a Replicat group using this table for checkpoints. There can be multiple Replicat groups using the same table. This column is part of the primary key.
`GROUP_KEY (primary key)`	A unique identifier that, together with `GROUPNAME`, uniquely identifies a checkpoint regardless of how many Replicat groups are writing to the same table. This column is part of the primary key.
`SEQNO`	The sequence number of the input trail that Replicat was reading at the time of the checkpoint.
`RBA`	The relative byte address that Replicat reached in the trail identified by `SEQNO`. `RBA` + `SEQNO` provide an absolute position in the trail that identifies the progress of Replicat at the time of checkpoint.
`AUDIT_TS`	The timestamp of the commit of the source transaction.
`CREATE_TS`	The date and time when the checkpoint table was created.
`LAST_UPDATE_TS`	The date and time when the checkpoint table was last updated.
`CURRENT_DIR`	The current Oracle GoldenGate home directory or folder.
`LOG_BSN`	The `LOG_BSN` provides information needed to set Extract back in time to reprocess transactions. Some filtering by Replicat is necessary because Extract will likely re-generate a small amount of data that was already applied by Replicat.
`LOG_CSN`	Stores the high watermark, or the upper boundary, of the CSNs. Any transaction with a CSN higher than this value has not been processed.
`LOG_XID`	Not used. Retained for backward compatibility.
`LOG_CMPLT_CSN`	Stores the low watermark, or the lower boundary, of the CSNs. Any transaction with a lower CSN than this value has already been processed.
`LOG_CMPLT_XIDS`	Stores the transactions between the high and low watermarks that are already applied.
`VERSION`	The version of the checkpoint table format. Enables future enhancements to be identified as version numbers of the table.

Table 13-5 Transaction table definition

Column	Description
`GROUP_NAME`	The name of a Replicat group using this table for checkpoints. There can be multiple Replicat groups using the same table. This column is part of the primary key of the transaction table.
`GROUP_KEY`	A unique identifier that, together with `GROUPNAME`, uniquely identifies a checkpoint regardless of how many Replicat groups are writing to the same table. This column is part of the primary key of the transaction table.
`LOG_CMPLT_CSN`	The foreign key that references the checkpoint table. This column is part of the primary key of the transaction table.
`LOG_CMPLT_XIDS_SEQ`	Creates unique rows in the event there are so many overflow transactions that multiple rows are required to store them all. This column is part of the primary key of the transaction table.
`LOG_CMPLT_XIDS`	Stores the overflow of transactions between the high and low watermarks that are already applied.