Tuning Capture Checkpointslink
Defining the Capture Checkpoint Tuning option enables storing of uncommitted long running transactions into checkpoint files so that if capture is interrupted, the uncommitted changes will be read from the checkpoint file when replication resumes. This prevents rereading the DBMS transaction logs from the beginning of the uncommitted transaction.
Fivetran HVR's capture job will save its open transactions periodically, so it can recover quickly on restart. You can also define the frequency at which the checkpoints are created using Perform Checkpointing (Capture_Checkpoint_Frequency), and the storage location of the capture checkpoint files can be configured using Disk Storage (Capture_Checkpoint_Storage). Also, you can define a retention period of the checkpoint files using Retain Checkpoints (Capture_Checkpoint_Retention).
Capture check-pointing is supported only for certain location types. For the list of supported location types, see Log-based capture checkpointing in Capabilities.
Perform Checkpointinglink
Checkpointing frequency in seconds for long running transactions, so the capture job can recover quickly when it restarts. Value secs is the interval (in seconds) at which the capture job creates checkpoints.
Thedefault
checkpoint frequency is 300 seconds (5 minutes). If value 0 is set, checkpoints are not written.
Without checkpoints, capture jobs must rewind back to the start of the oldest open transaction, which can take a long time and may require access too many old DBMS log files (e.g. archive files).
The checkpoints are written into HVR_CONFIG/capckp/hub/chn directory. If a transaction continues to make changes for a long period then successive checkpoints will not rewrite its same changes each time; instead the checkpoint will only write new changes for that transaction; for older changes it will reuse files written by earlier checkpoints.
Checkpoints are written only for long-running transactions. For example, if the checkpoint frequency is each 5 minutes but users always do an SQL commit within 4 minutes then checkpoints will never be written. However, if users keep transactions open for 10 minutes, then those transactions will be saved but shorter-lived ones in the same period will not.
The frequency with which capture checkpoints are written is relative to the capture jobs own clock, but it decides whether a transaction has been running long enough to be checkpointed by comparing the timestamps in its DBMS logging records. As a consequence, the maximum (worst-case) time that an interrupted capture job would need to recover (rewind back over all its open transactions) is its checkpoint frequency plus the amount of time it takes to reread the amount of changes that the DBMS can write in that period of time.
When a capture job is recovering it will only use checkpoints which were written before the 'capture cycle' was completed. This means that very frequent capture checkpointing (say every 10 seconds) is wasteful and will not speed up capture job recovery time.
Disk Storagelink
Storage location of capture checkpoint files for quick capture recovery.
Available options are:
- Agent machine
default
: Checkpoint files are saved in a directory on capture location. - Hub machine: Checkpoint files are saved in a directory on hub server.
Writing checkpoints on the hub is more expensive because extra data must be sent across the network. When capturing changes from an Oracle RAC, the checkpoint files should be stored on the hub server because the directory on the remote location where the capture job would otherwise write checkpoints may not be shared inside the RAC cluster, so it may not be available when the capture job restarts.
For both the storage locations, the checkpoint files are saved in HVR_CONFIG/capckp/ directory.
When the capture job is restarted and if it cannot find the most recent checkpoint files (perhaps the contents of that directory have been lost during a failover) then it will write a warning and rewind back to the start of the oldest open transaction.
In HVR UI, the option Disk Storage is displayed only when HVR Agent is used for connecting to the location. When an agent is not used, the checkpoint files are saved in a directory on the capture location.
Retain Checkpointslink
When this field/property is defined, it retains capture checkpoint files up to the specified period (in seconds).
The retained checkpoint files are saved in HVR_CONFIG/capckpretain/hub/channel/location directory.
By default, the checkpoint files are deleted as soon as the Capture does not require it anymore.