ORACLE GOLDENGATE CHECKPOINTS

Each and every processes in Oracle GoldenGate has it’s own checkpoints to maintain data integrity.

This article explains the different types of checkpoints and the uses of it.

As we know there are multiple processes involved in Oracle GoldenGate replication, the below are the processes which maintains the checkpoint information for each and every operations it does.,

1. Extract or Capture Process
2. Secondary Extract or DataPump Process
3. Replicat Process

EXTRACT PROCESS

This process captures the committed changes from the Redo Log or Archive Log files and writes them to the Trail files called Local Trails. The Extract Process
not only captures the committed changes but also keeps track of all the Open transactions so that it ensures that it does not misses any data or no data loss.

The Extract Process maintains the below checkpoints,

1. Startup Checkpoint
2. Current Read Checkpoint
3. Recovery Checkpoint
4. Current Write Checkpoint

STARTUP CHECKPOINT

Whenever an Extract process is started, it makes it’s first checkpoint in the data source. The first checkpoint made in the data source when the extract process starts is the STARTUP CHECKPOINT.

Read Checkpoint #1

 Oracle Redo Log

 Startup Checkpoint (starting position in the data source):
 Thread #: 1
 Sequence #: 258 
 RBA: 11913232
 Timestamp: 2015-12-25 20:38:13.000000
 SCN: 0.3302225 (3302225)
 Redo File: /vol3/GGDB1/redo03.log

CURRENT READ CHECKPOINT

The Extract process reads from the Redo Logs or Archive Log files. This Checkpoint shows the current read position of the Extract process. It shows the information of the extract process where currently it is reading like, which is the current redo log or archive log file.

Current Checkpoint (position of last record read in the data source):
 Thread #: 1
 Sequence #: 260
 RBA: 25050112
 Timestamp: 2015-12-26 12:02:17.000000
 SCN: 0.3333968 (3333968)
 Redo File: /vol2/GGDB1/redo02.log

RECOVERY CHECKPOINT

As I mentioned earlier, the extract process not only captures the committed changes from the redo or archive log files but also keeps track of all the open transaction
so that it ensures that it does not misses any data or no data loss.

This is the checkpoint which shows the Oldest Open transaction in the database.

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
 Thread #: 1 -------------------> This is the Single Instance, so the number of thread is 1
 Sequence #: 260 ---------------> Redo Log sequence number
 RBA: 25021456
 Timestamp: 2015-12-26 12:01:21.000000
 SCN: 0.3333913 (3333913)
 Redo File: /vol2/GGDB1/redo02.log

CURRENT WRITE CHECKPOINT

Extract process captures the changes from the redo log or archive log files and writes these changes to the Trail files called Local Trails. The CURRENT WRITE CHECKPOINT show the information
of the extract process where it is currently writing. It means the information like, to which trail file sequence and RBA it is currently writing to.

Write Checkpoint #1

 GGS Log Trail

 Current Checkpoint (current write position):
 Sequence #: 11  --------------> The Trail file sequence number to which the Extract Process is writing to.
 RBA: 1468       --------------> Relative Byte Address
 Timestamp: 2015-12-26 12:02:20.784341
 Extract Trail: /vol3/ogg/dirdat/et
 Trail Type: EXTTRAIL

The below command is executed to know all the checkpoint information of the Extract Process.

INFO EXTRACT <EXTRACT_NAME>, SHOWCH

Below is the example of the above commands output. Here my extract name is EXT1,

GGSCI (OGG1.localdomain) 8> INFO EXTRACT EXT1, SHOWCH

EXTRACT EXT1 Last Started 2015-12-26 11:32 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 4790
Log Read Checkpoint Oracle Redo Logs
 2015-12-26 12:02:17 Seqno 260, RBA 25050112
 SCN 0.3333968 (3333968)


Current Checkpoint Detail:

Read Checkpoint #1

 Oracle Redo Log

 Startup Checkpoint (starting position in the data source):
 Thread #: 1
 Sequence #: 258
 RBA: 11913232
 Timestamp: 2015-12-25 20:38:13.000000
 SCN: 0.3302225 (3302225)
 Redo File: /vol3/GGDB1/redo03.log

 Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
 Thread #: 1
 Sequence #: 260
 RBA: 25021456
 Timestamp: 2015-12-26 12:01:21.000000
 SCN: 0.3333913 (3333913)
 Redo File: /vol2/GGDB1/redo02.log

 Current Checkpoint (position of last record read in the data source):
 Thread #: 1
 Sequence #: 260
 RBA: 25050112
 Timestamp: 2015-12-26 12:02:17.000000
 SCN: 0.3333968 (3333968)
 Redo File: /vol2/GGDB1/redo02.log

Write Checkpoint #1

 GGS Log Trail

 Current Checkpoint (current write position):
 Sequence #: 11
 RBA: 1468
 Timestamp: 2015-12-26 12:02:20.784341
 Extract Trail: /vol3/ogg/dirdat/et
 Trail Type: EXTTRAIL

Header:
 Version = 2
 Record Source = A
 Type = 10
 # Input Checkpoints = 1
 # 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 = 2015-12-26 11:32:46
 Last Update Time = 2015-12-26 12:02:20
 Stop Status = A
 Last Result = 400

DATAPUMP PROCESS

The DataPump process reads the changes from the Local Trail Files, pumps the changes to the target side and writes it to the Target Trail files called Remote Trails.
This process also has three main checkpoints.

1. Startup Checkpoint
2. Current Read Checkpoint
3. Current Write Checkpoint

Here for the DataPump the read checkpoint will be in Local Trails and write checkpoint will be in the Remote Trails. Let us see in depth about it.

STARTUP CHECKPOINT

The first checkpoint made in the Local Trail when the Pump process starts is the STARTUP CHECKPOINT.

GGS Log Trail

 Startup Checkpoint (starting position in the data source):
 Sequence #: 10 
 RBA: 792627
 Timestamp: 2015-12-25 18:57:54.000000
 Extract Trail: /vol3/ogg/dirdat/et

CURRENT READ CHECKPOINT

DataPump process reads the changes from the Local Trail files which are written by the Extract Process. So the Pump’s read checkpoint shows the information of the
process, like which record it is currently reading and from which Local Trail file it is reading.

Current Checkpoint (position of last record read in the data source):
 Sequence #: 11 --------------> Local Trail Sequence Number
 RBA: 1468 -------------------> RBA information in the Local Trail
 Timestamp: 2015-12-26 11:32:46.875773
 Extract Trail: /vol3/ogg/dirdat/et

CURRENT WRITE CHECKPOINT

As explained earlier, the Pump process reads the changes from the Local Trails and writes it to the Remote Trails. So this shows, at which Remote Trail file sequence and RBA the Pump process
is writing to.

Write Checkpoint #1

 GGS Log Trail

 Current Checkpoint (current write position):
 Sequence #: 17 -----------------> Remote Trail Sequence Number 
 RBA: 1513 ----------------------> RBA Information in the Remote Trail
 Timestamp: 2015-12-26 12:06:05.789294
 Extract Trail: /vol3/ogg/dirdat/et
 Trail Type: RMTTRAIL

The same command as we used in Extract process is used here to know the checkpoint information. Please check the below example,

INFO EXTRACT <DATAPUMP_NAME>, SHOWCH

GGSCI (OGG1.localdomain) 9> INFO EXTRACT DMP1, SHOWCH

EXTRACT DMP1 Last Started 2015-12-26 11:32 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 4789
Log Read Checkpoint File /vol3/ogg/dirdat/et000011
 2015-12-26 11:32:46.875773 RBA 1468


Current Checkpoint Detail:

Read Checkpoint #1

 GGS Log Trail

 Startup Checkpoint (starting position in the data source):
 Sequence #: 10
 RBA: 792627
 Timestamp: 2015-12-25 18:57:54.000000
 Extract Trail: /vol3/ogg/dirdat/et

 Current Checkpoint (position of last record read in the data source):
 Sequence #: 11
 RBA: 1468
 Timestamp: 2015-12-26 11:32:46.875773
 Extract Trail: /vol3/ogg/dirdat/et

Write Checkpoint #1

 GGS Log Trail

 Current Checkpoint (current write position):
 Sequence #: 17
 RBA: 1513
 Timestamp: 2015-12-26 12:06:05.789294
 Extract Trail: /vol3/ogg/dirdat/et
 Trail Type: RMTTRAIL

Header:
 Version = 2
 Record Source = A
 Type = 1
 # Input Checkpoints = 1
 # Output Checkpoints = 1

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 = 2015-12-26 11:32:44
 Last Update Time = 2015-12-26 12:06:05
 Stop Status = A
 Last Result = 400

REPLICAT PROCESS

The role of the Replicat process is to read the changes from the Remote Trail file and apply the changes to the target database. As every process has, this process also have the
below checkpoints,

1. Startup Checkpoint
2. Current Read Checkpoint

This process does not have any Write Checkpoints since it is applying the changes to the database.

STARTUP CHECKPOINT

The first checkpoint made in the Remote Trail when the Replicat Process starts is the Startup Checkpoint.

GGS Log Trail

 Startup Checkpoint (starting position in the data source):
 Sequence #: 15
 RBA: 956263
 Timestamp: 2015-12-25 18:57:40.453113
 Extract Trail: /vol3/ogg/dirdat/et

CURRENT READ CHECKPOINT

This Checkpoint shows the last transaction read by the Replicat Process from the Remote Trail file. The sequence number of the Remote Trail and the RBA information.

Current Checkpoint (position of last record read in the data source):
 Sequence #: 17 ----------------> Remote Trail Sequence Number
 RBA: 1513 ---------------------> RBA information in the Remote Trail
 Timestamp: 2015-12-26 11:32:44.580331
 Extract Trail: /vol3/ogg/dirdat/et

The below command is executed to know all the checkpoint information of the Replicat Process.

INFO REPLICAT <REPLICAT_NAME>, SHOWCH

Check the below example.

GGSCI (OGG2.localdomain) 3> INFO REPLICAT REP1, SHOWCH

REPLICAT REP1 Last Started 2015-12-26 11:32 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 4880
Log Read Checkpoint File /vol3/ogg/dirdat/et000017
 2015-12-26 11:32:44.580331 RBA 1513


Current Checkpoint Detail:

Read Checkpoint #1

 GGS Log Trail

 Startup Checkpoint (starting position in the data source):
 Sequence #: 15
 RBA: 956263
 Timestamp: 2015-12-25 18:57:40.453113
 Extract Trail: /vol3/ogg/dirdat/et

 Current Checkpoint (position of last record read in the data source):
 Sequence #: 17
 RBA: 1513
 Timestamp: 2015-12-26 11:32:44.580331
 Extract Trail: /vol3/ogg/dirdat/et

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

Database Checkpoint:
 Checkpoint table = ggadmin.ggschkpt
 Key = 4074703616 (0xf2df0b00)
 Create Time = 2015-12-25 15:41:35

Status:
 Start Time = 2015-12-26 11:32:55
 Last Update Time = 2015-12-26 12:06:18
 Stop Status = A
 Last Result = 400

Hope you understood clearly about the Oracle GoldenGate Checkpoints of each and every processes involved in the replication. There is also something called Oracle GoldenGate CHECKPOINT TABLE. This table is maintained in the target side by the REPLICAT PROCESS.

Please check the next post to know about the Oracle GoldenGate Checkpoint Table.

Thanks for your support. Enjoy 🙂