DB2 9.5 Backup and Recovery simplified

Backup

OFFLINE full backup

  • Database remains disconnected during backup
  • Needs to stop db or quiesce db before you start taking backup
CONNECT TO <db> USER <dbadmin> using <pswd>;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;

BACKUP DB <db>
 TO "/home/db2inst1/backup/offline_full"
 COMPRESS
 UTIL_IMPACT_PRIORITY 50
 WITHOUT PROMPTING;

CONNECT TO <db> USER <dbadmin> using <pswd>;
UNQUIESCE DATABASE;
CONNECT RESET;
  • Generates the backup-image and stores it to location specified
  • COMPRESS – will compress the backup-image using the default DB2 compression library, this library will be added to the backup-image
  • UTIL_IMPACT_PRIORITY 50 – allows you to throttle the backup command so that it does not impact your production workload as much, better to keep it below 50
  • WITHOUT PROMPTING – any action which normally require user intervention will return an error message

ONLINE full backup

  • Database remains online during backup
  • “ONLINE” backup requires log in archive mode, DB2 databases are created by default with Circular logging
UPDATE DATABASE CONFIGURATION
USING LOGARCHMETH1 LOGRETAIN IMMEDIATE;
  • It is must to take an offline full backup once you set LOGARCHMETH1 = LOGRETAIN
  • This backs up the data pages as soon as they become available, a backed up page can be altered before backup process finishes
  • when all data pages are backed up, it adds the logs committed during backup-process to the backup-image – (requires to add INCLUDE LOGS)
BACKUP DB <db>
 ONLINE
 TO "/home/db2inst1/backup/online_full"
 COMPRESS
 UTIL_IMPACT_PRIORITY 20
 INCLUDE LOGS
 WITHOUT PROMPTING;

ONLINE incremental backup

  • This backs-up data pages that have changed since last successful full backup, includes standard metadata set (db configuration, table space definitions, db history), includes full copy of changed LOB and LF data types from previous full, incremental or delta backup
  • It needs to enable database for incremental backup with the database configuration parameter TRACKMOD=ON (track modified pages), before allowing applications to change data, you must take a full backup after turning on TRACKMOD
UPDATE DATABASE CONFIGURATION USING TRACKMOD Yes DEFERRED;
  • Before you take first incremental backup since the database is created, it is must to take a full backup
BACKUP DATABASE <db>
 ONLINE INCREMENTAL
 TO "/home/db2inst1/backup/backup_online_incremental"
 COMPRESS
 UTIL_IMPACT_PRIORITY 20
 INCLUDE LOGS
 WITHOUT PROMPTING;
  • This generates small backup image, downside is when you restore the backup, you must first restore the full backup and then apply the most recent incremental backup

ONLINE incremental (delta) backup

  • Backup data pages that have changed since last successful full/incremental backup, includes standard metadata set (db configuration, table space definitions, db history), includes full copy of changed LOB and LF data types from previous full, incremental or delta backup
  • It needs to enable database for incremental backup with the database configuration parameter TRACKMOD=ON (track modified pages), before allowing applications to change data, you must back up the database after turning on TRACKMOD
UPDATE DATABASE CONFIGURATION USING TRACKMOD Yes DEFERRED;
  • Before you take first incremental backup since the database is created, it is must to take a full backup
BACKUP DATABASE <db>
 ONLINE INCREMENTAL DELTA
 TO "/home/db2inst1/backup/backup_online_incremental_delta"
 COMPRESS
 UTIL_IMPACT_PRIORITY 20
 INCLUDE LOGS
 WITHOUT PROMPTING;
  • Generates very small backup images, downside is when you restore the backup, you must first restore the full backup and then apply the most recent incremental backup and then apply any delta backups that you have since that incremental. It can still take longer than just restoring a full backup. You need to figure out what is best for your environment to optimize both the backup times but also to optimize the restore time

Restore

  • Rebuilds a damaged or corrupted database that has been backed up using the DB2 backup utility
  • Restored database is in the same state that it was in when the backup copy was made
  • It can also overwrite a database with a different image or restore the backup copy to a new database

OFFLINE FULL restore

  • You can drop the exiting database which is probably either damaged or corrupted
RESTORE DB <db>
 FROM "/home/db2inst1/backup/offline_full"
 TAKEN AT <timestamp>
 INTO <db2>;
  • <timestamp:yyyymmddhhmmss> – generated when backup was taken, its part of backup-image file name. it can be a weekday e.g. (sat)
  • <db> – the db name/alias which was backed up
  • <db2> : target database – the new db name, it can be <db> when not specified. If the target database does not exist, it is created
  • FROM is optional when backup-image is at current location “.”
  • TAKEN AT is optional when there exists single backup-image at FROM or current location

ONLINE FULL restore

RESTORE DB <db>
 FROM "/home/db2inst1/backup/online_full"
 TAKEN AT <timestamp>
 INTO <db2>
 LOGTARGET /home/db2inst1/backup/online/logs;
  • LOGTARGET – logs that are extracted from backup-image during RESTORE are put here, if not specified then logs are not extracted
  • Complete database restore by rollforwarding the extracted log(s) as follows
ROLLFORWARD DB <db>
 TO END OF LOGS
 AND COMPLETE
 OVERFLOW LOG PATH ("/home/db2inst1/backup/online/logs");
  • TO END OF LOGS – all committed transactions from all online archive log files listed in the database configuration parameter logpath are to be applied
  • AND COMPLETE – completes the rollforward recovery process by rolling back any incomplete transactions and turning off the rollforward pending state of the database
  • OVERFLOW LOG PATH – Specifies an alternate log path to be searched for archived logs during recovery, this should be same as LOGTARGET used in above RESTORE command

incremental(/delta) restore

  • You must have taken full backup and one or more incremental backup when it comes to restore incremental backup
  • Incremental restore requires
    • One full backup restore
    • If taken one (last taken) incremental backup – cumulative
    • One or more (taken after last incremental/last full) incremental delta backup(s)
    • It depends upon the backup strategy

                      Weekly full backup and nightly incremental(cumulative) backup
 
 
 
 
                  Weekly full backup and nightly incremental delta backup
 
 
 
 

                 Weekly full backup and nightly delta backup, incremental on the mid of week
 
 
 
 

Incremental restore examples

[db2inst1@ubuntu backup] ll offline_full/
total 23632
-rw------- 1 db2inst1 db2iadm1 12079104 Mar 27 16:30 BKP.0.db2inst1.NODE0000.CATN0000.20090327163013.001 - ts1
-rw------- 1 db2inst1 db2iadm1 12079104 Mar 27 16:35 BKP.0.db2inst1.NODE0000.CATN0000.20090327163534.001 - ts2
[db2inst1@ubuntu backup] ll online_full/total 32376
-rw------- 1 db2inst1 db2iadm1 11030528 Mar 27 16:36 BKP.0.db2inst1.NODE0000.CATN0000.20090327163640.001 - ts3
-rw------- 1 db2inst1 db2iadm1 11030528 Mar 27 16:37 BKP.0.db2inst1.NODE0000.CATN0000.20090327163707.001 - ts4
-rw------- 1 db2inst1 db2iadm1 11030528 Mar 27 16:39 BKP.0.db2inst1.NODE0000.CATN0000.20090327163939.001 - ts5
[db2inst1@ubuntu backup] ll online_incremental
total 18576
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:40 BKP.0.db2inst1.NODE0000.CATN0000.20090327164058.001 - ts6
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:41 BKP.0.db2inst1.NODE0000.CATN0000.20090327164113.001 - ts7
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:41 BKP.0.db2inst1.NODE0000.CATN0000.20090327164140.001 - ts8
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:43 BKP.0.db2inst1.NODE0000.CATN0000.20090327164326.001 - ts10
[db2inst1@ubuntu backup] ll online_incremental_delta/
total 13932
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:42 BKP.0.db2inst1.NODE0000.CATN0000.20090327164206.001 - ts9
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:43 BKP.0.db2inst1.NODE0000.CATN0000.20090327164352.001 - ts11
-rw------- 1 db2inst1 db2iadm1 4739072 Mar 27 16:44 BKP.0.db2inst1.NODE0000.CATN0000.20090327164425.001 - ts12
  • Above we have some backup images taken, two offline full backup ts1, ts2, then some full online backup, then incremental including some delta.
  • ts(n-1) is taken before ts(n) – it is timestamp, you can check the timestamp as the part of backup-image files
  • Now using db2ckrst you can see the restore chain, e.g. you have to restore the db at 20090327164425(ts12), it will require the following
    • One full backup (ts5)
    • One incremental(cumulative) backup – ts10
    • One or more incremental backup(s) – ts11, ts12
[db2inst1@ubuntu backup] db2ckrst -r database -d bkp -t 20090327164425
Suggested restore order of images using timestamp 20090327164425 (ts12) for database bkp
==================================================================
restore db bkp incremental taken at 20090327164425 - ts12
restore db bkp incremental taken at 20090327163939 - ts5
restore db bkp incremental taken at 20090327164326 - ts10
restore db bkp incremental taken at 20090327164352 - ts11
restore db bkp incremental taken at 20090327164425 - ts12
==================================================================
  • You have to follow above sequence if you have to restore db till time ts12
  • You do not need to specify LOGTARGET option for intermediate online restore, specify LOGTARGET to only last image i.e. ts12
  • So why does it show that first you restore the ts12? and in the end again do it, answer is – it does not restore the (final)backup-image at first time, it just reads the headers and history. History of database backups taken, and some relevant information. This helps it to construct the restore chain require to restore the db
  • If you have your original db dropped/corrupted/damaged then first you restore the target image, once you do that, you can use the db2ckrst utility

Lets see some more examples

[db2inst1@ubuntu backup] db2ckrst -r database -d bkp -t 20090327164206
Suggested restore order of images using timestamp 20090327164206 (ts9) for database bkp

==================================================================
 restore db bkp incremental taken at 20090327164206 - ts9
 restore db bkp incremental taken at 20090327163939 - ts5
 restore db bkp incremental taken at 20090327164140 - ts8
 restore db bkp incremental taken at 20090327164206 - ts9
==================================================================

[db2inst1@ubuntu backup] db2ckrst -r database -d bkp -t 20090327164113
Suggested restore order of images using timestamp 20090327164113 (ts7) for database bkp

==================================================================
restore db bkp incremental taken at 20090327164113 - ts7
restore db bkp incremental taken at 20090327163939 - ts5
restore db bkp incremental taken at 20090327164113 - ts7
==================================================================
  • One good news is, you do not need to follow this sequence and do a manual restore, yes.. you really do not need to follow this sequence to restore db to say ts12 or ts7, all you need to do is
    • Make sure the required backup-images are stored at the same location where the backups were taken
    • Issue a single command using a word ‘auto’ and you are done
[db2inst1@ubuntu backup] db2 restore db bkp incremental auto from /home/db2inst1/backup/online_incremental_delta/
 taken at 20090327164425 into bkp2 logtarget /home/db2inst1/backup/online_incremental_delta/

DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ubuntu backup]# db2 'rollforward db bkp2 to end of logs and complete OVERFLOW LOG
 PATH ("/home/db2inst1/backup/online_incremental_delta/")'

                      Rollforward Status

Input database alias                 = bkp2
Number of nodes have returned status = 1
Node number                          = 0
Rollforward status                   = not pending
Next log file to be read             =
Log files processed                  = S0000019.LOG - S0000019.LOGLast committed transaction           = 2009-03-27-11.14.26.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

Appendix

DB2 Logs

  • Circular logging(default) – reuse of log files in round robin, content not preserved, LOGRETAIN=OFF, USEREXIT=OFF
  • Archive logging – upon commit, log files are closed and become offline archived logs
  • Online log file – log file with active content in defined database log directory
  • Offline log file – filled log file, moved from log file path to archive storage
  • logbufsz and mincommit can be configured to gain the performance by delaying the operation of writing logs to the disk

LOGARCHMETH1

  • Log archive method configuration parameter
  • Default value : off, it means logs are not retained and made circular, so database is said to be not in rollforward recovery mode, for rollforward recovery, it needs archived logs
  • Specifies the media type of the primary destination for archived logs, e.g.
db2 UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 DISK:/home/db2inst1/backup/archived_logs IMMEDIATE ;

USEREXIT

  • You can develop a user exit program to automate log file archiving and retrieval. Before invoking a user exit program for log file archiving or retrieval, ensure that the logarchmeth1 database configuration parameter has been set to USEREXIT

References

Advertisement

5 thoughts on “DB2 9.5 Backup and Recovery simplified”

  1. Thanks for such important tips. Though it is not handy right now, but in future it may come very useful for me. Thanks once again.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s