100
Database logging modes
With every DB2 UDB database there are two different databases logging mode,
Circular database mode & Archival database mode.When the database is created by default it is in circular mode & as per the business Requirement you can change the mode from circular to archival logging mode.Also with the database creation there are 3 primary and 2 secondary log files gets created.
Circular logging mode
Circular logging is a default logging mode when it is used, records stored into the
Log buffer are written to primary log files in a circular fashion. Redo log entries are written to the current “active” log file and when that log file becomes full , it is marked as “unavailable”.DB2 marks next log file in the sequence as a active log file and continue writing log entries into it. And when that log file becomes full , then process
gets repeated. When the transactions are terminated the corresponding log records are released because they are no longer needed. When all the records stored in an individual log file are released, that log file is treated as being “reusable” and it becomes “active log file “ for the next transactions thus its contents are overwritten by new log entries.
DB2 always tries to write the log entries into the primary log files .when logging cycle
gets back to the primary log file which is marked as “unavailable” then DB2 database manager will allocate secondary log file. Now the log entries are being written into the secondary log file. As soon as this secondary log file becomes full,
the DB2 Database Manager will check the primary log file again and if its status
is still “unavailable”, another secondary log file is allocated and redo entries are being written into it. This process continues until all the secondary log files get full,
which is indicated by the parameter “logsecond” . Once all the secondary log files get full and there is no primary log file available for writing redo entries then following error message will generated :
SQL0964C The transaction log for the database is full.
Here you have to increase number of secondary log files .
Ø db2 update db cfg for db_name using logsecond ‘value’
In circular logging mode as the contents of log file are overwritten you can recover the database up to the last full database backup performed. and you cannot perform the point in time recovery with circular logging mode.
Archival logging mode
Like circular logging mode, when archival logging is used the redo log entries from log buffer are written into the primary log files. However unlike circular logging these log files are never reused. when all records stored in an individual log file are released, that file is marked as being “archived” rather than as being “reusable” and it can be used again if it is needed for the roll forward recovery. When the first primary log file becomes full , next primary log file is allocated so that the desired number of primary log files “logprimary” is always available for use. All the log entries related to a single transaction must fit within the active log space available.In case of log running transaction it requires more log space other than the primary log file size thus the secondary log file may be allocated and used.
In this mode once the log file gets full another primary log file is allocated and the transaction information gets logged into that. During that period of time the first primary log file is being archived to another destination configured by the parameter “Logarchmeth1” and can be reusable for the further log information storage. Same process is repeated until the disk space is available for the archived destination.
In case of archival logging mode you can recover your database up to a current state or up to a particular state by using the point in time recovery options which are described in later section of this article. Here the archived log files are be used for the point in time recovery.
b) How to change the database logging mode:
As described earlier wherever the DB2 UDB database is created by default it is in Circular logging mode , if you want to change the mode from circular to archival mode
Perform following steps:
Step 1: change the following parameters
i) logretain=recovery or userexit=on
Ø db2 terminate
Ø db2 force application all
Ø db2 update db cfg for db_name using Logretain recovery
or
Ø db2 update db cfg for db_name using Userexit=on
ii) blk_log_dsk_ful=yes
Ø db2 update db cfg for db_name using blk_log_dsk_ful yes
Setting blk_log_dsk_ful to yes causes applications to hang when DB2 encounters a log disk full error, thus allowing you to resolve the error and allowing the transaction to complete. You can resolve a disk full situation by moving old log files to another file system or by enlarging the file system, so that hanging applications can complete.
Note: Once you perform step 1 (i & ii) try to connect to your database
Ø db2 connect to db_name
You will be getting following warning
SQL1116N A connection to or activation of database "db_name" cannot be made because of backup pending: sqlstate=57019
This is because we have changed the database logging mode from circular to archival so we must perform full database backup here, as the backup of circular logging mode cannot be applicable to archival logging mode and vice versa.
Ø db2 backup database db_name to d:\db_name\backup
Ø db2 connect to db_name
Step 2: Set the archival destination
To set the archival destination create a folder “archive” on a disk where you have sufficient disk space so that the log files will get archived to this location once it is filled. Keep your archive destination other than log files destination.
e.g. e:\db_name\archive.
Ø db2 update db cfg for db_name using logarchmeth1 “Disk:e:\db_name\archive”
Ø > DB2 UPDATE DB CFG FOR DB2MIG USING LORARCHMETH1 "DISK:/home/db2inst2/archivedb2mig"
c) Best practices to keep a database’s data safe
Data protection against any failure is a huge challenge for the database administrator,
Databases in production or 24 X 7 environments are critical to handle where you cannot accept any data loss. So to always keep you database in a safe mode concentrate on the following points:
1) Keep your database in Archival logging mode so that in case of failure you can recover the database up to a current state or particular state by using point in time recovery options.
2) Perform database full/offline backup and Incremental backup as and when required.
3) Create a clone / standby database.
4) keep your redo log files and archived log files on different location with sufficient disk
space.
