Checkpoint Operations

A checkpoint operation synchronizes the current state of the TimesTen in-memory database with the state in the latest on disk checkpoint file.

A checkpoint operation writes any regions of the in-memory database that have been changed since the last checkpoint operation. Checkpoint operations alternate between two checkpoint files so that there is always the latest and latest – 1 checkpoint images available for recovery. Once the checkpoint completes, TimesTen purges the related transaction log files (which are now a part of the checkpoint) as they are no longer required.

By default, TimesTen automatically performs background checkpoints at regular intervals. Checkpointing may generate a large amount of I/O activity and have a long processing time depending on the size of the database and the number of database changes since the most recent checkpoint.

Note:

Applications can programmatically initiate checkpoint operations. See Setting and Managing Checkpoints.

The following sections describe checkpoint operations and how you can manage them:

• Purpose of Checkpoints

• Usage of Checkpoint Files

• Types of Checkpoints

• Setting and Managing Checkpoints

Purpose of Checkpoints

A checkpoint operation has two primary purposes.

• Decreases the amount of time required for database recovery, because it provides a more up-to-date database image on which recovery can begin.

• Makes a portion of the transaction log unneeded for any future database recovery operation, typically allowing one or more transaction log files to be deleted.

Both of these functions are very important to TimesTen applications. The reduction in recovery time is important, as the amount of a transaction log needed to recover a database has a direct impact on the amount of downtime seen by an application after a system failure. The removal of unneeded transaction log files is important because it frees file system space that can be used for new transaction log files. If these files were never purged, they would eventually consume all available space in the transaction log files directory, causing database operations to fail due to log space exhaustion.

Usage of Checkpoint Files

TimesTen creates two checkpoint files for each database, named dsname.ds0 and dsname.ds1, where dsname is the database path name and file name prefix specified in the database DSN.

During a checkpoint operation, TimesTen determines which checkpoint file contains the most recent consistent image and then writes the next in-memory image of the database to the other file. Thus, the two files contain the two most recent database images.

• In TimesTen Classic, the database maintains one set of checkpoint and transaction log files.

• In TimesTen Scaleout, each element maintains its own independent set of checkpoint and transaction log files. See Understanding Distributed Transactions in TimesTen Scaleout in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

TimesTen uses the most recent checkpoint file and transaction log files to recover the database to its most recent transaction-consistent state after a database shutdown or system failure. (The most recent transaction log files are those written since the checkpoint was done.) If any errors occur during this process, or if the more recent checkpoint image is incomplete, then recovery restarts using the other checkpoint file. See Transaction Logging.

Types of Checkpoints

TimesTen uses the most recent checkpoint file to recover the database to transaction-consistent state at the time of the last successful checkpoint operation completed.

It uses the transaction log files to recover the database to its most recent transaction-consistent state after a database shutdown or system failure.

TimesTen supports two types of database checkpoint operations:

• Fuzzy or Non-Blocking Checkpoints

• Blocking Checkpoints

Fuzzy or Non-Blocking Checkpoints

Fuzzy checkpoints, or non-blocking checkpoints, enable transactions to run against the database while the checkpoint is in progress.

Fuzzy checkpoints do not obtain locks, and therefore have a minimal impact on other database activity. Because transactions may modify the database while a checkpoint operation is in progress, the resulting checkpoint file may contain both committed and uncommitted transactions. Furthermore, different portions of the checkpoint image may reflect different points in time. For example, one portion may have been written before a given transaction committed, while another portion was written afterward. The term "fuzzy checkpoint" derives its name from this fuzzy state of the database image.

To recover the database when the checkpoint files were generated from fuzzy checkpoint operations, TimesTen requires the most recent checkpoint file and the transaction log to bring the database into its most recent transaction-consistent state.

Blocking Checkpoints

Blocking checkpoints obtain an exclusive lock on the database for a portion of the checkpoint operation, which blocks all access to the database during that time.

The resulting checkpoint image contains all committed transactions prior to the time the checkpoint operations acquired the exclusive lock on the database. Because no transactions can be active while the database lock is held, no modifications made by in-progress transactions are included in the checkpoint image.

In TimesTen Classic, an application uses the ttCkptBlocking built-in procedure to request a blocking checkpoint. The actual checkpoint is delayed until the requesting transaction commits or rolls back. If a blocking checkpoint is requested for a database for which both checkpoint files are already up to date then the checkpoint request is ignored.

Setting and Managing Checkpoints

There is a default behavior for TimesTen checkpoints.

• TimesTen performs periodic fuzzy checkpoints in the background. You can modify this behavior. See Configuring or Turning Off Background Checkpointing.

• TimesTen performs a blocking checkpoint operation of a database just before the database is unloaded from memory. See Blocking Checkpoints.

The following sections describe how to manage checkpointing:

• Programmatically Performing a Checkpoint in TimesTen Classic

• Configuring or Turning Off Background Checkpointing

• Displaying Checkpoint History and Status

• Setting the Checkpoint Rate

• Setting the Number of Checkpoint File Read Threads

Programmatically Performing a Checkpoint in TimesTen Classic

By default, TimesTen performs periodic fuzzy checkpoints in the background. Therefore, applications rarely need to issue manual checkpoints.

However, if an application wishes to issue a manual checkpoint against a TimesTen Classic database, it can call the ttCkpt built-in procedure to request a fuzzy checkpoint or the ttCkptBlocking built-in procedure to request a blocking checkpoint. This is not recommended. See ttCkpt and ttCkptBlocking in the Oracle TimesTen In-Memory Database Reference.

Configuring or Turning Off Background Checkpointing

You can configure TimesTen to checkpoint either at a specific timed frequency or when the transaction log files contain a certain amount of data.

To configure checkpointing in TimesTen, do the following:

Configure the CkptFrequency and CkptLogVolume connection attributes as follows:

• The CkptFrequency connection attribute controls how often, in seconds, that TimesTen performs a background checkpoint. The default is 600 seconds. Set the CkptFrequency connection attribute to 0 if you want to control background checkpointing with the CkptLogVolume connection attribute.

• The CkptLogVolume connection attribute controls how much data, in megabytes, that collects in the transaction log file between background checkpoints. By increasing this amount, you can delay the frequency of the checkpoint. The default is 0. Set the CkptFrequency connection attribute to 0 if you want to control background checkpointing with the CkptLogVolume connection attribute.

In most cases, it is recommended to use CkptLogVolume over CkptFrequency, since CkptFrequency does not take into account the rate of database transactions. If both CkptFrequency and CkptLogVolume attributes are set with a value greater than 0, then a checkpoint is performed when either of the two conditions becomes true.

Alternatively, you can configure background checkpointing or turn it off by calling the ttCkptConfig built-in procedure. The values set by ttCkptConfig take precedence over those set with the connection attributes.

Note:

For information on default values and usage, see CkptFrequency, CkptLogVolume, and ttCkptConfig in the Oracle TimesTen In-Memory Database Reference.

Displaying Checkpoint History and Status

Select from the SYS.GV$CKPT_HISTORY or SYS.V$CKPT_HISTORY system views to display information on the most recent eight checkpoints and checkpoint attempts.

Note:

You can also use the ttCkptHistory built-in procedure to display this information.

For example, some of the information that you can confirm:

• The amount of time a checkpoint takes (subtract the completion time of the checkpoint from the start time).

• The type of the checkpoint: fuzzy, blocking, static or none. See Types of Checkpoints.

• The status of the checkpoint: in-progress, completed, or failed. When examining checkpoint history, verify whether any recent checkpoints failed (indicated with status of FAILED). If a checkpoint has failed, the reason is displayed in the error or additional details columns.

• The initiator of the checkpoint. From a user-level application (including TimesTen utilities), from a background checkpoint request, or the managing subdaemon of the database.

• The reason why the checkpoint occurred. The most popular reasons are after database creation, after recovery, final checkpoint after shutdown, after the user runs a built-in procedure, or after a flush operation.

• The reason for a failure if a checkpoint fails.

• The amount of data (total number of bytes) written by a typical checkpoint.

• The checkpoint rate. See Setting the Checkpoint Rate for details on how to calculate the checkpoint rate from the data provided.

• The percentage of the checkpoint that has been completed. If there is an in-progress checkpoint, indicates the percentage of the checkpoint that has been completed.

• The number of actual transaction log files purged by this checkpoint. If this column displays a zero, this does not mean that no log records were purged within the transaction log file. Instead, log records can be purged continually within a transaction log file. This column displays the actual number of transaction log files purged to show when file system space is freed.

  There are times when TimesTen may not be able to purge some of the transaction files if there is a hold set on the transaction log. For example, this value may show when the checkpoint cannot purge transaction log files due to a long-running transaction or a replication bookmark located far back in the transaction log files.

• The bookmark name (the reason for the transaction log hold) that is preventing the removal of one or more transaction logs. This name can help identify why transaction log files are remaining on the file system longer than expected. See Log Holds by TimesTen Components or Operations for details on the different bookmark (transaction log hold) names.

  If this information does not provide enough context on why transaction logs are not purged, then you can also run the ttXactAdmin built-in procedure for more details on the transaction logs.

See SYS.GV$CKPT_HISTORY, SYS.V$CKPT_HISTORY, SYS.GV$LOG_HOLDS or SYS.V$LOG_HOLDS in the Oracle TimesTen In-Memory Database System Tables and Views Reference. See ttCkptHistory, ttLogHolds or ttXactAdmin in the Oracle TimesTen In-Memory Database Reference.

This example shows a checkpoint in progress:

% SELECT * FROM SYS.V$CKPT_HISTORY;

< 2019-02-05 16:56:34.169520, <NULL>, 
Fuzzy           , In Progress     , User            , 
BuiltIn         , <NULL>, 
0, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, 13, 6, 0, <NULL>, <NULL> >
 
< 2019-02-05 16:55:47.703199, 2019-02-05 16:55:48.188764, 
Fuzzy           , Completed       , Checkpointer    , 
Background      , <NULL>, 
1, 0, 8964304, 294, 33554432, 291, 5677288, 27, 1019512, 
1065408, <NULL>, 5, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:47.106110, 2019-02-05 16:54:47.723379, 
Static          , Completed       , Subdaemon       , 
FinalCkpt       , <NULL>, 
0, 0, 8960328, 294, 33554432, 291, 5677288, 256, 33157172, 
5321548, <NULL>, 4, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:41.633792, 2019-02-05 16:54:42.568469, 
Blocking        , Completed       , User            , 
BuiltIn         , <NULL>, 
1, 0, 8958160, 294, 33554432, 291, 5677288, 31, 1162112, 
6604976, <NULL>, 3, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:37.438827, 2019-02-05 16:54:37.977301, 
Static          , Completed       , User            ,
DbCreate        , <NULL>, 
0, 0, 1611984, 93, 33554432, 92, 1853848, 93, 33554432, 
1854052, <NULL>, 2, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:36.861728, 2019-02-05 16:54:37.438376, 
Static          , Completed       , User            , 
DbCreate        , <NULL>, 
1, 0, 1609936, 93, 33554432, 92, 1853848, 93, 33554432, 
1854052, <NULL>, 1, 0, Checkpoint, <NULL> >

This example shows that an error occurred during the most recent checkpoint attempt, which was a user-initiated checkpoint:

% SELECT * FROM SYS.V$CKPT_HISTORY;

< 2019-02-05 16:57:14.476860, 2019-02-05 16:57:14.477957, 
Fuzzy           , Failed , User            , 
BuiltIn         , 847, 
1, <NULL>, <NULL>, 0, 0, 0, 0, 0, 0, 0, <NULL>, 7, 0, <NULL>, 
Errors   1: TT0847: 16:57:14 (2019-02-05) >
 
< 2019-02-05 16:56:34.169520, 2019-02-05 16:56:59.715451, 
Fuzzy           , Completed       , User            , 
BuiltIn         , <NULL>, 
0, 0, 8966472, 294, 33554432, 291, 5677288, 5, 522000, 
532928, <NULL>, 6, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:55:47.703199, 2019-02-05 16:55:48.188764, 
Fuzzy           , Completed       , Checkpointer    , 
Background      , <NULL>, 
1, 0, 8964304, 294, 33554432, 291, 5677288, 27, 1019512, 
1065408, <NULL>, 5, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:47.106110, 2019-02-05 16:54:47.723379, 
Static          , Completed       , Subdaemon       , 
FinalCkpt       , <NULL>, 
0, 0, 8960328, 294, 33554432, 291, 5677288, 256, 33157172, 
5321548, <NULL>, 4, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:41.633792, 2019-02-05 16:54:42.568469, 
Blocking        , Completed       , User            , 
BuiltIn         , <NULL>, 
1, 0, 8958160, 294, 33554432, 291, 5677288, 31, 1162112, 
6604976, <NULL>, 3, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:37.438827, 2019-02-05 16:54:37.977301, 
Static          , Completed       , User            ,
DbCreate        , <NULL>, 
0, 0, 1611984, 93, 33554432, 92, 1853848, 93, 33554432, 
1854052, <NULL>, 2, 0, Checkpoint, <NULL> >
 
< 2019-02-05 16:54:36.861728, 2019-02-05 16:54:37.438376, 
Static          , Completed       , User            , 
DbCreate        , <NULL>, 
1, 0, 1609936, 93, 33554432, 92, 1853848, 93, 33554432, 
1854052, <NULL>, 1, 0, Checkpoint, <NULL> >

This example demonstrates the checkpoint history output. You use the ttCkptHistory built-in procedure to select specific columns from the checkpoint history:

% SELECT type, reason, bookmarkname, logsPurged FROM ttCkptHistory;;

< Fuzzy           , BuiltIn         , Oldest Transaction Undo, 0 >
< Static          , FinalCkpt       , Checkpoint, 6 >
< Blocking        , BuiltIn         , Checkpoint, 0 >
< Blocking        , BuiltIn         , Checkpoint, 0 >
< Blocking        , BuiltIn         , Checkpoint, 0 >
< Blocking        , BuiltIn         , Backup, 5 >
< Blocking        , BuiltIn         , Backup, 0 >
< Blocking        , BuiltIn         , Backup, 0 >

The output from this example shows that the oldest checkpoints (the last rows displayed) did not purge any transaction log files (indicated by a 0 in the logsPurged column) because there was a log hold set by an incremental backup. The backup caused a transaction log file accumulation. However, eventually, the log hold was removed and five transaction log files could be purged.

A checkpoint operation is started on the fourth row from the bottom. The checkpoint places a log hold that prevents transaction log files from being purged. Six transaction log files were purged by the final checkpoint (FinalCkpt) operation.

The most recent checkpoint shows that it is a fuzzy checkpoint with the log hold of Oldest Transaction Undo. This hold marks a point for the transaction that the checkpoint log purge operation cannot pass. If you see this message over several consecutive rows, then this may indicate a long running transaction that could be causing a transaction log file accumulation.

Setting the Checkpoint Rate

By default, there is no limit to the rate at which checkpoint data is written to the file system. You can use the CkptRate connection attribute or the ttCkptConfig built-in procedure to set the maximum rate at which background checkpoint data is written to the file system.

Checkpoints taken during recovery and final checkpoints do not honor this rate. In those situations, the rate is unlimited.

Note:

See CkptRate and ttCkptConfig in the Oracle TimesTen In-Memory Database Reference.

Setting a rate too low can cause checkpoints to take an excessive amount of time and cause the following problems:

• Delay the purging of unneeded transaction log files.

• Delay the start of backup operations.

Setting a rate too high can cause checkpoints to consume too much of the file system buffer cache bandwidth that could result in the following:

• Reduce overall database transaction throughput, as transaction logs are prevented from writing to the file system as quickly as they usually would.

• Cause contention with other file system I/O operations.

When choosing a rate, you should take into consideration the amount of data written by a typical checkpoint and the amount of time checkpoints usually take. Both of these pieces of information are available through the SYS.GV$CKPT_HISTORY or SYS.V$CKPT_HISTORY system views or the ttCkptHistory built-in procedure.

If a running checkpoint appears to be progressing too slowly, you evaluate the progress of this checkpoint with the Percent_Complete column of the SYS.GV$CKPT_HISTORY or SYS.V$CKPT_HISTORY system views. The rate can be increased by calling the ttCkptConfig built-in procedure. If a call to ttCkptConfig changes the rate, the new rate takes effect immediately, affecting even the running checkpoint.

Calculate the checkpoint rate (by viewing the SYS.GV$CKPT_HISTORY or SYS.V$CKPT_HISTORY system views or calling the ttCkptHistory built-in procedure):

1. For any given checkpoint, subtract the starttime from the endtime.

2. Divide the number of bytes written by this elapsed time in seconds to get the number of bytes per second.

3. Divide this number by 1024*1024 to get the number of megabytes per second.

When setting the checkpoint rate, you should consider the following:

• The specified checkpoint rate is only approximate. The actual rate of the checkpoint may be below the specified rate, depending on the hardware, system load and other factors.

• The above method may underestimate the actual checkpoint rate, because the starttime and endtime interval includes other checkpoint activities in addition to the writing of dirty blocks to the checkpoint file.

• The Percent_Complete field may show 100 percent before the checkpoint is actually complete. The Percent_Complete field shows only the progress of the writing of dirty blocks and does not include additional bookkeeping at the end of the checkpoint.

• When adjusting the checkpoint rate, you may also need to adjust the checkpoint frequency, as a slower rate makes checkpoints take longer, which effectively increases the minimum time between checkpoint beginnings.

Setting the Number of Checkpoint File Read Threads

By default, TimesTen reads checkpoint files serially with a single thread. Use the CkptReadThreads connection attribute to set the number of threads that TimesTen uses to read the checkpoint files when loading the database into memory.

When using n number of threads, TimesTen divides the checkpoint file into n portions of equal size. Each thread concurrently reads a portion of the file into memory. Once all threads are done reading their portion of the checkpoint file successfully, TimesTen checks the database for consistency.

Note:

See Set CkptReadThreads in this book, and CkptReadThreads in the Oracle TimesTen In-Memory Database Reference.