Checkpoint Tables Additional Details
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).
_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.
Column | Description |
---|---|
|
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.
|
|
The version of the checkpoint table format. Enables future enhancements to be identified as version numbers of the table. |
|
The timestamp of the commit of the source transaction. |
|
The sequence number of the input trail that Replicat was reading at the time of the checkpoint. |
|
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.
|
|
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. |
|
The date and time when the checkpoint table was last updated. |
|
The date and time when the checkpoint table was created. |
|
The current Oracle GoldenGate home directory or folder. |
|
Stores the transactions between the high and low watermarks that are already applied. |
|
Stores the low watermark, or the lower boundary, of the CSNs. Any transaction with a lower CSN than this value has already been processed. |
|
Stores the high watermark, or the upper boundary, of the CSNs. Any transaction with a CSN higher than this value has not been processed. |
|
Not used. Retained for backward compatibility. |
|
A unique identifier that, together with
, uniquely identifies a
checkpoint regardless of how many Replicat groups are writing to the
same table. This column is part of the primary key. |
Column | Description |
---|---|
|
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.
|
|
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. |
|
Stores the overflow of transactions between the high and low watermarks that are already applied. |
|
The foreign key that references the checkpoint table. This column is part of the primary key of the transaction table. |
|
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. |
Internal Checkpoint Information
The INFO
command with the SHOWCH
option not only
displays current checkpoint entries, but it also displays metadata information about
the record itself. This information is not documented and is for use by the Oracle
GoldenGate processes and by support personnel when resolving a support case.
The metadata is contained in the following entries in the SHOWCH
output.
Header:
Version = 2
Record Source = A
Type = 1
# Input Checkpoints = 1
# Output Checkpoints = 0
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 0
Transaction Integrity = -1
Task Type = 0
Status:
Start Time = 2011-01-12 13:10:13
Last Update Time = 2011-01-12 21:23:31
Stop Status = A
Last Result = 400
INFO EXTRACT SHOWCH Command: Checkpoint Information
INFO EXTRACT
command with the SHOWCH
option. In
this case, the data source is an Oracle RAC database cluster, so there is thread
information included in the output. You can view past checkpoints by specifying the
number of them that you want to view after the SHOWCH
argument.
EXTRACT JC108XT Last Started 2011-01-01 14:15 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint File /orarac/oradata/racq/redo01.log
2011-01-01 14:16:45 Thread 1, Seqno 47, RBA 68748800
Log Read Checkpoint File /orarac/oradata/racq/redo04.log
2011-01-01 14:16:19 Thread 2, Seqno 24, RBA 65657408
Current Checkpoint Detail:
Read Checkpoint #1
Oracle RAC Redo Log
Startup Checkpoint (starting position in data source):
Thread #: 1
Sequence #: 47
RBA: 68548112
Timestamp: 2011-01-01 13:37:51.000000
SCN: 0.8439720
Redo File: /orarac/oradata/racq/redo01.log
Recovery Checkpoint (position of oldest unprocessed transaction in data source):
Thread #: 1
Sequence #: 47
RBA: 68748304
Timestamp: 2011-01-01 14:16:45.000000
SCN: 0.8440969
Redo File: /orarac/oradata/racq/redo01.log
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 47
RBA: 68748800
Timestamp: 2011-01-01 14:16:45.000000
SCN: 0.8440969
Redo File: /orarac/oradata/racq/redo01.log
Read Checkpoint #2
Oracle RAC Redo Log
Startup Checkpoint(starting position in data source):
Sequence #: 24
RBA: 60607504
Timestamp: 2011-01-01 13:37:50.000000
SCN: 0.8439719
Redo File: /orarac/oradata/racq/redo04.log
Recovery Checkpoint (position of oldest unprocessed transaction in data source):
Thread #: 2
Sequence #: 24
RBA: 65657408
Timestamp: 2011-01-01 14:16:19.000000
SCN: 0.8440613
Redo File: /orarac/oradata/racq/redo04.log
Current Checkpoint (position of last record read in the data source):
Thread #: 2
Sequence #: 24
RBA: 65657408
Timestamp: 2011-01-01 14:16:19.000000
SCN: 0.8440613
Redo File: /orarac/oradata/racq/redo04.log
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 2
RBA: 2142224
Timestamp: 2011-01-01 14:16:50.567638
Extract Trail: ./dirdat/eh
Header:
Version = 2
Record Source = A
Type = 6
# Input Checkpoints = 2
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2011-01-01 14:15:14
Last Update Time = 2011-01-01 14:16:50
Stop Status = A
Last Result = 400
INFO REPLICAT, SHOWCH: Checkpoint Information
The basic command shows current checkpoints. To view a specific number of previous
checkpoints, type the value after the SHOWCH
argument.
REPLICAT JC108RP Last Started 2011-01-12 13:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 111:46:54 ago)
Log Read Checkpoint File ./dirdat/eh000000000
First Record RBA 3702915
Current Checkpoint Detail:
Read Checkpoint #1
GGS Log Trail
Startup Checkpoint(starting position in data source):
Sequence #: 0
RBA: 3702915
Timestamp: Not Available
Extract Trail: ./dirdat/eh
Current Checkpoint (position of last record read in the data source):
Sequence #: 0
RBA: 3702915
Timestamp: Not Available
Extract Trail: ./dirdat/eh
Header:
Version = 2
Record Source = A
Type = 1
# Input Checkpoints = 1
# Output Checkpoints =
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 0
Transaction Integrity = -1
Task Type = 0
Status:
Start Time = 2011-01-12 13:10:13
Last Update Time = 2011-01-12 21:23:31
Stop Status = A
Last Result = 400