DYNAMIC_LOGS and its effect on logging

The dynamic log file allocation feature prevents hanging problems that are caused by rollbacks of a long transaction because the database server does not run out of log space. The DYNAMIC_LOGS configuration parameter specifies whether the dynamic log file allocation feature is off, on, or causes the server to pause to allow the manual addition of a logical log file.

Dynamic log allocation allows you to do the following actions:
  • Add a logical log file while the system is active, even during fast recover.
  • Insert a logical log file immediately after the current log file, instead of appending it to the end.
  • Immediately access the logical log file even if the root dbspace is not backed up.
The default value for the DYNAMIC_LOGS configuration parameter is 2, which means that the database server automatically allocates a new logical log file after the current log file when it detects that the next log file contains an open transaction. The database server automatically checks if the log after the current log still contains an open transaction at the following times:
  • Immediately after it switches to a new log file while writing log records (not while reading and applying log records)
  • At the beginning of the transaction cleanup phase which occurs as the last phase of logical recovery

    Logical recovery happens at the end of fast recovery and at the end of a cold restore or roll forward.

  • During transaction cleanup (rollback of open transactions), a switch to a new log file log might occur

    The database server also checks after this switch because it is writing log records for the rollback.

When you use the default value of 2 for DYNAMIC_LOGS, the database server determines the location and size of the new logical log for you:
  • The database server uses the following criteria to determine on which disk to allocate the new log file:
    • Favor mirrored dbspaces
    • Avoid root dbspace until no other critical dbspace is available
    • Least favored space is unmirrored and noncritical dbspaces
  • The database server uses the average size of the largest log file and the smallest log file for the size of the new logical log file. If not enough contiguous disk space is available for this average size, the database server searches for space for the next smallest average size. The database server allocates a minimum of 200 kilobytes for the new log file.
If you want to control the location and size of the additional log file, set DYNAMIC_LOGS to 1. When the database server switches log files, it still checks if the next active log contains an open transaction. If it does find an open transaction in the next log to be active, it does the following actions:
  • Issues alarm event 27 (log required)
  • Writes a warning message to the online log
  • Pauses to wait for the administrator to manually add a log with the onparams -a -i command-line option

You can write a script that will execute when alarm event 27 occurs to execute onparams -a -i with the location you want to use for the new log. Your script can also execute the onstat -d command to check for adequate space and execute the onparams -a -i command with the location that has enough space. You must use the -i option to add the new log right after the current log file.

If you set DYNAMIC_LOGS to 0, the database server still checks whether the next active log contains an open transaction when it switches log files. If it does find an open transaction in the next log to be active, it issues the following warning:
WARNING: The oldest logical log file (%d) contains records
from an open transaction (0x%p), but the Dynamic Log 
Files feature is turned off.

Copyright© 2019 HCL Technologies Limited