Displaying Checkpoint History and Status

Select from the SYS.V$CKPT_HISTORY system view 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.V$CKPT_HISTORY, 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.