DB2 v9.1, DB2 v9.5 and DB2 v9.7 Learning: DB2 Backup and Recovery (BAR) - Part 03

Part 00 - Introduction to DB2 Backup and Recovery artical 

Part 01 - Introduction to DB2 Transaction Log files

Part 02 - Introduction to DB2 Log Manger

Introduction

In this artical i will discuss the the DB2 logging mechanism. Depending upon the type of DB2 Logging Mechanism, different type of recovery is possible for DB2 database.

Each new version of DB2 come with great backup and recovery functionality which ease DB2 DBA task.

Type of Logging Mechanism

There are two type of logging mechanism
1) Circular logging

Good Starting point in understanding circular logging mechanism is DB2 information center

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0051343.html

This is the default DB2 logging mechanism when a new database is created and no configuration is needed. It provide following feature

Only full offline backup of the database is possible
Crash and version recovery is possible in circular logging
No Point in time recovery possible

As the name implies this type of logging reuses the logs in a circular mode. For example, if you had 3 primary log files, then it would be used in following order LOG#1, LOG#2, LOG#3, LOG#1,LOG#2,LOG#3,LOG#1,LOG#2,LOG#3
A log file can be reused in circular logging only when it contain information about transaction that have being committed and its corresponding data pages are externalised to disk.
In case where a single transaction consume all the available PRIMARY log file (Primary) and then DB2 start adding dynamically the SECONDARY log files.

2) Archival logging (Roll forward recovery logging)

In Archive logging when enabled at DB2, it allows for more flexible recovery options. It allow us to recover the database to a particular point in time or recover the database to a point before failure.
ie. It allows

Crash Recovery
Version Recovery
Roll forward recovery

In a 24x7 environment when downtime is not an option, this logging mechanism allows for performing the online backup of the database.

DB2 information Center provide more info to this
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0051344.html

In this, the transaction log files are archived so that when recovery is needed, the archived log file are used along with backup image to restore the database to point before the failure.

Hear, DB2 archives the log file in following manner. For exmaple, if DB2 is configured to use five logs, then the order in which they are used is

LOG#1, LOG#2, LOG#3, LOG#4, LOG#5
When the LOG#5 becomes full and new log file need to be added, then DB2 will archive the LOG#1 to “Archive Location” only if LOG#1 has all transaction that are committed and data pages externalised to disk and then will rename the LOG#1 to LOG#5 and truncate its content. This is done to minimize the overhead of creating new files.
If LOG#1 is still having transaction that are not committed or data pages not externalised, then DB2 will allocated Secondary log file to record the transacion information.
Likewise, when LOG#5 is full, then LOG#2 is archived and renamed to LOG#6 and so on.

Enabling Archive Logging

To enable archive logging, one parameter needs to be set logarchmeth1 which tell where the archived log files are stored. This parameter decides following things
1) Archive media type (DISK, TAPE,etc)
2) Archive Location
3) Archive type (Archival Logging, Infinite Logging, Log retention Logging)
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu.admin.config.doc/doc/r0011448.html

Types of Archival Logging

Depending on the value set for DB CFG parameter logarchmeth1 following logging mechanism are used.

Archival Logging

logarchmeth1 is set to value which describe the “Archive Media type” and it Archive Locations.
For Example,

    logarchmeth1 =  DISK:/u21/db2inst1/sample/archived_logs
        Where Archive Media Type       = DISK 
Archive path          = u21/db2inst1/sample/archived_logs

In this, when the log files are filled up they are archived to the mentioned destination. This is done through DB2 log manager which copies the file from “Active Log Path” to “Archived log path”

Infinite Logging

Infinite logging can be called as a subset of the archive logging. The only difference is how the transaction log files are archived.

In Infinite logging mechanism, the log file are moved to the archived location as soon as they fills up and does not wait for all the transaction to commit and externalise to disk.
In case of archival logging the log file are move to the archived location only when the transaction log file does not include any transaction needed for crash recovery. This mean log file only contains COMMITED transaction and it associated data pages are externalised to disk.
Infinite logging is not a recommended approach because it may prolong the crash recovery because it may have to retrieved the active logs from the Archive location.
Enabling Infinite logging is two step process

Enable the archival logging on the database using “logarchmeth1” parameter
Setting the LOGSECOND database configuration parameter to -1.

Log Retention Logging

In this logging mechanism the log files are not archived to the “Archive Location” using the DB2 program. This needs manual interventions for archiving of the log files.

Hear, when the log file contains committed transaction and all it associated data pages externalised to disk, it still resides in the “Active Log path” and becomes “Online Archive Logs”.
It needs a manual movement if the LOG DISK full happens.
The log file needed for Roll forward recovery will be retrived from the “Active Log Path”
Enabling of Log Retention logging is done as follows