Monitoring Log Holds and Log File Accumulation

There are options for periodic monitoring of excessive transaction log accumulation.

  • For details of all log holds, select from the SYS.V$LOG_HOLDS system views or call the ttLogHolds built-in procedure. The information includes the following, as applicable:

    • Log file number, the offset of the hold position, and the type of hold, which can be checkpoint, replication, backup, XLA, long-running transaction, or long-running XA transaction

    • Name of the checkpoint file for a checkpoint hold

    • Name of the subscriber and the parallel track ID it uses for replication

    • Backup path for a backup hold

    • Name of the persistent subscription and process ID of the last process to open it for XLA

    • Transaction ID for a long-running transaction

    • XA XID for a long-running XA transaction

    See SYS.V$LOG_HOLDS in the Oracle TimesTen In-Memory Database SQL Reference or ttLogHolds in the Oracle TimesTen In-Memory Database Reference.

  • Select from the SYS.V$CKPT_HISTORY system views or call the ttCkptHistory built-in procedure to check the last several checkpoints to confirm none of the returned rows has a status of FAILED.

    See SYS.V$CKPT_HISTORY in the Oracle TimesTen In-Memory Database SQL Reference or ttCkptHistory in the Oracle TimesTen In-Memory Database Reference.

  • Check the SYS.SYSTEMSTATS table for operational metrics. Each transaction log file has a unique sequence number, which starts at 0 for the first log file and increments by 1 for each subsequent log file. The number of the current log file is available in SYS.SYSTEMSTATS.log.file.latest. The number of the oldest log file not yet purged is available in SYS.SYSTEMSTATS.log.file.earliest. You should raise an error or warning if the difference in the sequence numbers exceeds an inappropriate threshold.

    See SYS.SYSTEMSTATS in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

  • For XLA, check the SYS.TRANSACTION_LOG_API table that provides bookmark information, such as the process ID of the connected application, which could help diagnose the reason why a bookmark may be stuck or lagging.

    See SYS.TRANSACTION_LOG_API in the Oracle TimesTen In-Memory Database System Tables and Views Reference.