About Checkpoints

J.1 Extract Checkpoints

Extract checkpoint positions are composed of read checkpoints in the data source and write checkpoints in the trail. The following is a sampling of checkpoint information displayed with the 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.

Example J-1 INFO EXTRACT with SHOWCH

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

See Internal Checkpoint Informationfor information about the internal information that starts with the Header entry in the SHOWCH output.

Parent topic: About Checkpoints

J.1.1 About Extract read checkpoints

Extract places read checkpoints in the data source.

Parent topic: Extract Checkpoints

J.1.1.1 Startup Checkpoint

The startup checkpoint is the first checkpoint that is made in the data source when the process starts. This statistic is composed of the following:

Thread #: The number of the Extract thread that made the checkpoint, if Oracle GoldenGate is running in an Oracle RAC environment. Otherwise, this statistic is not displayed.
Sequence #: The sequence number of the transaction log where the checkpoint was made.
RBA: The relative byte address of the record at which the checkpoint was made.
Timestamp: The timestamp of the record at which the checkpoint was made.
SCN: The system change number of the record at which the checkpoint was made.
Redo File: The path name of the transaction log containing the record where the checkpoint was made.

Parent topic: About Extract read checkpoints

J.1.1.2 Recovery Checkpoint

The recovery checkpoint is the position in the data source of the record containing the oldest transaction not yet processed by Extract. The fields for this statistic are the same as those of the other read checkpoint types.

Parent topic: About Extract read checkpoints

J.1.1.3 Current Checkpoint

The current checkpoint is the position of the last record read by Extract in the data source. This should match the Log Read Checkpoint statistic shown in the summary and in the basic INFO EXTRACT command without options. The fields for this statistic are the same as those of the other read checkpoint types.

Parent topic: About Extract read checkpoints

J.1.2 About Extract Write Checkpoints

Extract places a write checkpoint, known as the current checkpoint, in the trail. The current checkpoint is the position in the trail where Extract is currently writing. This statistic is composed of the following:

Sequence #: The sequence number of the trail file where the checkpoint was written.
RBA: The relative byte address of the record in the trail file at which the checkpoint was made.
Timestamp: The timestamp of the record at which the checkpoint was made.
Extract trail: The relative path name of the trail.
Trail Type: Identifies the trail type. EXTTRAIL identifies the trail as a local trail, which means that it is directly accessible by Oracle GoldenGate processes through the host filesystem. RMTTRAIL identifies the trail as a remote trail, which means it is not directly accessible by Oracle GoldenGate processes through the host filesystem. A trail stored on a shared network device and accessible through NFS-like services are considered local because they are accessible transparently through the host filesystem.

Parent topic: Extract Checkpoints

J.2 Replicat Checkpoints

Replicat makes checkpoints in the trail file to mark its last read position. To view process checkpoints, use the INFO REPLICAT command with the SHOWCH option. The basic command shows current checkpoints. To view a specific number of previous checkpoints, type the value after the SHOWCH argument.

Example J-2 INFO REPLICAT, SHOWCH

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 = 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

See Internal Checkpoint Information for information about the internal information that starts with the Header entry in the SHOWCH output.

About Replicat Checkpoints

Parent topic: About Checkpoints

J.2.1 About Replicat Checkpoints

The following describes the detail of the Replicat checkpoints in the trail.

Parent topic: Replicat Checkpoints

J.2.1.1 Startup Checkpoint

The startup checkpoint is the first checkpoint made in the trail when the process starts. Comprising this statistic are:

Sequence #: The sequence number of the trail file where the checkpoint was written.
RBA: The relative byte address of the record at which the checkpoint was made.
Timestamp: The timestamp of the record at which the checkpoint was made.
Extract Trail: The relative path name of the trail.

Parent topic: About Replicat Checkpoints

J.2.1.2 Current Checkpoint

The current checkpoint is the position of the last record read by Replicat in the trail. This should match the Log Read Checkpoint statistic shown in the summary and in the basic INFO REPLICAT command without options. The fields for this statistic are the same as those of the Startup Checkpoint.

Parent topic: About Replicat Checkpoints

J.3 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

Parent topic: About Checkpoints

J.4 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 J-1 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 J-2 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.

Parent topic: About Checkpoints