Part 02 - Introduction to DB2 Log Manger
Part 03 - Introduction to DB2 Logging Mechanism
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
- Tablespace details
- Container details
- Log control file
- Recovery history files
- 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:
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 |
