Saturday, November 26, 2011

DB2 Backup and Recovery (BAR) - Part 04 - DB2 Backup with example


Part 02 - Introduction to DB2 Log Manger

Introduction

  • Backup is an important part of the overall backup and recovery strategy. 
  • Backup along with archive log file will help to recovery the database to a Point in time or to a point before the failure.
  • Depending on the backup type, the DB2 backup image contains all the pages in the database including the used and free pages.
  • DB2 backup also stores following information
    1. Tablespace details
    2. Container details
    3. Log control file
    4. Recovery history files
    5. Database Configuration parameters


Backup file naming convention

  • Click here for more details on DB2 backup file naming convention.
  • File name of the backup image created on disk is of following formation
DB_alias.Type.Inst_name.NODEnnnn.CATNnnnn.timestamp.Seq_num 
  • For example:
    -- Database backup
SAMPLE.0.DB2INST1.NODE0000.CATN0000.20110518165214.001
    -- Tablespace backup
SAMPLE.3.DB2INST1.NODE0000.CATN0000.20110518165214.001

Types of Backup

1) Offline Backup

  • Database is inaccessible to the users.
  • It is a complete copy of the database. i.e each and every pages in the database are stored in the backup image.
  • For example 
      
    Sample DB2 Command
    BACKUP DATABASE  
       <db_name> 
    TO 
       <backup location> 
    COMPRESS

  • Only full database backup option is available.

2) Online Backup

  • Database is accessible to the users.
  • Database must be enabled for archival logging.
  • For incremental or delta backup operation, DB CFG parameter TRACKMOD must be enabled follwed by complete full online or offline database backup.
  • Following backup options are available in Online backup configuration


a) Full Online database backup

  • Backup image stores all the used and free pages in the databases
  • All changed and unchanged pages are stored as part of full online database backup.


Sample DB2 Command
 
BACKUP DATABASE 
   <db_name> 
ONLINE 
TO 
   <backup location> 
COMPRESS 

b) Incremental database backup


  • In order to track only the changed pages in the database, TRACKMOD must be enabled
  • Backup image stores only the pages that are changed from the last full database backup
  • Unchanged pages are not backed up    



Sample DB2 Command

-- Set the TRACKMOD parameter 
UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON; 
-- Need a full backup of the database to use the 
-- incremental or delata backup after setting the 
-- TRACKMOD parameter. This enables TRACKMOD on all tablespaces. 
BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS; 
-- Example 
BACKUP DATABASE 
   <DB_NAME>
ONLINE INCREMENTAL 
TO
   <backup location>
COMPRESS
-- Click hear to know when the log files are include in backup
INCLUDE LOGS       
 

c) Delta database backup


  • In order to track only the changed pages in the database, TRACKMOD must be enabled
  • Backup image stores only the pages that are changed from the last full or incremental or delta backup
  • Unchanged pages are not backed up  


Sample DB2 Command

-- Set the TRACKMOD parameter 
UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON; 
-- Need a full backup of the database to use the 
-- incremental or delata backup after setting the 
-- TRACKMOD parameter.This enables TRACKMOD on all tablespaces. 
BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS; 
-- Example 
BACKUP DATABASE 
   <db_name>
ONLINE INCREMENTAL DELTA 
TO
   <backup location> 
COMPRESS 
-- Click hear to know when the log files are include in backup
INCLUDE LOGS        
 

d) Full online Tablespace backup




Sample DB2 Command

-- Set the TRACKMOD parameter
UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;
 
--Need a full backup of the database to use the incremental or delata backup after setting the TRACKMOD parameter. This enables TRACKMOD on all tablespaces.
BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;
 
-- Example
BACKUP DATABASE   <DB_NAME>
TABLESPACE(<tbsp_name>) ONLINE
TO
    <backup location>
COMPRESS
-- Click hear to know when the log files are include in backup
INCLUDE LOGS        



e) Incremental Tablespace backup





Sample DB2 Command

-- Set the TRACKMOD parameter
UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;

--Need a full backup of the database to use the 
-- incremental or delata backup after setting 
-- the TRACKMOD parameter. This enables TRACKMOD on all tablespaces.
BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;

-- Example
BACKUP DATABASE
   <db_name>
TABLESPACE(<tbsp_name>) ONLINE INCREMENTAL
TO
    <backup location>
COMPRESS
-- Click hear to know when the log files are include in backup
INCLUDE LOGS


f) Delta Tablespace backup





Sample DB2 Command

-- Set the TRACKMOD parameter
UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;

-- Need a full backup of the database to use the 
-- incremental or delata backup after setting the 
-- TRACKMOD parameter. This enables TRACKMOD on all tablespaces.
BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;

-- Example
BACKUP DATABASE
   <db_name>
TABLESPACE(<tbsp_name>) ONLINE INCREMENTAL DELTA
TO
    <backup location>
COMPRESS
-- Click hear to know when the log files are include in backup 
 INCLUDE LOGS












Posted by Gilroy N. Gonsalves at 6:28 PM 0 comments

Sunday, August 21, 2011

DB2 Backup and Recovery (BAR) - Part 03 - Introduction to DB2 Logging Mechanism


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
  1. Only full offline backup of the database is possible
  2. Crash and version recovery is possible in circular logging
  3. 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
  1. Crash Recovery
  2. Version Recovery
  3. 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
    1. Enable the archival logging on the database using “logarchmeth1” parameter
    2. 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
      1. Enable archive logging using “logarchmeth1” parameter and set its values to “logretain”
    update db cfg for sample using logarchmeth1 logretain immediate


Additional References



Posted by Gilroy N. Gonsalves at 10:00 PM 1 comments

DB2 Backup and Recovery (BAR) - Part 02 - Introduction to DB2 Log Manger



Introduction

DB2 Log manager is the central component in the DB2 Log file management. It is used when the database is enable for archival logging and allows moving DB2 log files from Primary location (Active Log Location) to Archive location.

In this artical I will discuss the DB2 log manger.

Details

  • DB2 log manager is integrated with DB2 kernel.
  • It is responsible for archiving log files and retrieving log files during rollforward recovery
  • It is started when the database is enable for archival logging.
  • The DB2 log manager process/thread is called “db2logmgr"
  • It supports following archiving media
    • DISK - Log file are archived to DISK location
    • TSM - DB2 is closely integrated with IBM TSM and allow archiving log to TSM storage
    • USEREXIT - Avaiable for backward compatibility.
    • VENDOR - Support for third party storage to store the archive log files    
  • It can be configured using DB CFG parameters. Click hear for more details on DB2 Logging Parameters.
  • Following command can be used to find the DB2 log manager process
    db2pd -edus | grep -i db2log
     
  • When DB2 Log Manager retrieves the log files from archive storage, it puts the archive log to DB2 Active Log Path needed for recovery process. The “Active Log Path” is identified by “logpath” DB CFG parameter.
  • If OVERFLOWLOGPATH parameter is set then the Archive Log files are moved to OVERFLOWLOGPATH during the Rollforward recovery instead of Active Log path
  • LOGARCHMETH1 DB CFG parameter tell DB2 log manager where to move the offline archive log
  • When the Primary log archive is unavailable, then FAILARCHPATH DB CFG can be defined which tell the temporary local directory where the archive log file will be stored.
  • FAILARCHPATH help to avoid the log full condition when the Active Log path is full
  • When the connectivity to Primary log archive destination is established, DB2 log manager moves the archive log files from FAILARCHPATH to LOGARCHMETH1 destination.
     
Posted by Gilroy N. Gonsalves at 8:28 PM 0 comments
Subscribe to: Comments (Atom)