Strategic DB2 UDB Backup and Recovery

Backups   Recovery Scenarios   Related Tasks  

Automating Pruning and External File Management (dg_prune)  

Recovery Wizard  


Backups

The frequency with which DB2 databases are backed up depends on the use of the databases and their recovery requirements.

Data Warehouses

Very few data warehouse databases are built and then never updated. Quite often, large warehouses are built and then periodically updated.

Typically, updates (new data) may arrive weekly or monthly, and 90% or better of all access to the database is read-only. Such data warehouses are sometimes called read-mostly databases. Log enabled forward recovery is rarely appropriate for read-mostly databases because of the challenges of maintaining the log files. Instead, read-only or read-mostly databases can be recoverable provided that:

  1. an occassional OFFLINE backup is made
  2. load/input files from subsequent loads are appropriately saved
In this case, recovering the database involves restoring the backup and then re-loading any new data since the backup.

To summarize, Data Warehouse read-only or read-mostly databases should:

  • Use LOGRETAIN OFF
  • Use USEREXIT OFF
  • Be backed up in OFFLINE mode (default) at least monthly
    • This assumes a month's worth of load files can be saved to rebuild the database.

To verify the logging configuration options for a database, issue the command:

  • db2 "get db cfg for DBNAME" | grep -E "LOGRETAIN|USEREXIT"
Both of the values for LOGRETAIN and USEREXIT should be set to OFF.

To backup a database in OFFLINE mode, the keyword ONLINE must be omitted from the command syntax.

Backups to Disk

Most operating systems impose maximum sizes for files. Unix generally allows files to be up to 2GB in size. If the database is greater than the operating system maximum file size, then the backup must be written to multiple directories to avoid operating system restrictions.

For instance, continuing with the Unix example, a 150GB database would have to be backed up across 75 or more (150/2) different directories. The different directories must be spread over as many file systems as needed to obtain the required free disk space. Always add a few extra directories to the backup command to leave a margin of slack between the size of the backup files and the operating system limit.

Sample syntax of an OFFLINE database backup to Unix file systems (disk):

  • db2 "backup database DBNAME to /fs1/dir1, /fs2/dir1, /fs1/dir2, /fs2/dir2"
Assuming at least 4GB free space in each file system /fs1 and /fs2, the above sample command would back up a database up to 8GB (2,097,152 4K pages) in size.

Backups to Tape

Tape backups avoid the operating system maximum file size constraint, but tend to be much slower. Tape backups should use as many devices as possible since DB2 will write to them in parallel. Specifying the right value for the number of buffers is important or throughput to the tape devices will be diminished. The number of buffers should be equal to twice the number of devices.

Sample syntax of an OFFLINE database backup utilizing five tape devices:

  • db2 "backup database DBNAME to /dev/rmt0, /dev/rmt1, /dev/rmt2, /dev/rmt3, /dev/rmt4 with 10 buffers"
As each tape fills, DB2 will prompt to continue with the device (c), discontinue using the device that became full (d), or terminate the backup (t) altogether.

Before responding to the full device prompt, make certain that:

  1. the full tape is unloaded from the drive
  2. if the device will continue to be used, the new tape is loaded into the drive
Care should be taken to label the tapes accordingly. The first tape loaded into drive rmt0 should be labeled DBNAME.rmt0.1, the second tape should be labeled DBNAME.rmt0.2, the third tape DBNAME.rmt0.3, and so on and so forth. During a restore, the tapes will need to be loaded in correct sequence for each drive.

OLTP Databases

In contrast to data warehouse databases, OLTP databases contain data that is constantly accessed and modified. Generally, updates occur via transactions as opposed to periodic loads.

OLTP databases should have forward recovery enabled so that no updates are lost in the event of a crash, media failure, or other catastrophe.

Forward recovery is enabled for a database by turning on either the LOGRETAIN or USEREXIT configuration parameters. Once forward recovery is enabled, DB2 will allow ONLINE backups of databases to be performed. When a database is backed up ONLINE, it remains available to users/transactions during the backup so that continuous availability can be achieved.

LOGRETAIN

LOGRETAIN YES causes DB2 to save archive log files in the directory specified by the database configuration NEWLOGPATH/Path. As active logs fill, DB2 rolls filled logs into archive log files. These archive log files may later be used for ROLLFORWARD recovery. They must be saved until they become obsolete, which typically occurs after a number of full backups have been done.

USEREXIT

USEREXIT names an executable module in $DB2INSTANCEHOME/sqllib/adm which can be used to facilitate the archive and retrieval of archive log files. Sample userexits supplied by DB2 help manage the movement of log files from one medium to another. Shops with very high logging activity may need to offload archive log files to tape. Others may want to copy the archive files to other disks/file systems for automatic backup by a tape management system.

The syntax for performing an ONLINE database backup (either to tape or disk) is very similiar to the OFFLINE backup syntax. The word ONLINE is added as follows:

  • db2 "backup database DBNAME ONLINE to dir/dev [ {,dir/dev} ... ]"
Because ONLINE backups do not interrupt data availability, they can and should be taken much more frequently. If a recovery is necessary, the most recent backup will be used. The more current this backup, the quicker the recovery will run, as there will be less archive log activity to ROLLFORWARD.

Recovery Scenarios

The type of recovery to be performed will vary depending upon the type of failure, the state of system resources, and the type of logging that was being performed at the time of failure (circular or archive logging).

For read-only or read-mostly data warehouses that were backed up OFFLINE and that used circular logging (LOGRETAIN = OFF and USEREXIT = OFF), you must restore the database to the most recent offline backup and reload any updates via import or load.

For OLTP databases that were backed up OFFINE or ONLINE and for which archive logging was enabled (LOGRETAIN = YES or USEREXIT specified), you should restore the database to the most recent backup and then issue a ROLLFORWARD database command.

If a media device (disk or file system) is no longer available due to a hardware failure, then a special redirected restore must be performed which reassigns the devices/files supporting the database tablespaces.

Related Tasks

As you might expect, backups to disk and log archive files may eventually consume a substantial amount of disk space. The point in time at which backups and log files become obsolete is a function of how far back in time a database may need to be restored to. Generally, backup files and archive logs become obsolete after 30 days for an OLTP database.

Every time a database is backed up, restored, or loaded, DB2 records the event in a history file. This history file can also become very large and filled with obsolete entries. To maintain the history file, DB2 provides a "prune history" command. The prune history command requires a date/timestamp argument, for which 'yyyy' must be specified at a minimum. The command:

  • db2 "prune history 19980915"
would remove DB2 history file entries with time stamps equal to or less than September 15th, 1998, from the recovery history file. Unfortunately, however, prune history does not automatically discard external log and/or backup files. The DBA must delete external files manually.

Automating Pruning with dg_prune

Automating pruning and synchronization of DB2 history with external files

Database-Guys has created a shell script that can help automate the management of external files. As log files become obsolete through pruning of DB2 history, this script will automatically identify obsolete log files and delete them (the files are listed prior to deletion). If backups are made to disk files, and if the backups are stored in a separate directory, then obsolete backup files can be similarly listed and then deleted.

The script, dg_prune, has the following syntax and usage requirements:

  • dg_prune -db DBNAME -age DAYS [ -dirs d1 d2 d3 d4 ...]
  • Sample: dg_prune -db PRODDB -age 60 -dirs /home/db2inst1/proddb/archlogs /home/db2inst1/proddb/backups
    • The -db parameter identifies the database for which pruning is desired.
    • The -age parameter indicates the number of days of history which should be retained. The number of days specified by -age is subtracted from the current date to obtain the pruning date.
    • The -dirs parameter optionally names directories from which external files should be deleted. Any number of directories can be specified, but generally only two (one containing log files and one containing backup files to disk) are used.

      WARNING: All files older than the oldest required log file will be deleted from each directory listed with very little consideration given to the names of the files. If you accidentally list /usr/lpp as a directory argument, you may wipe out your system.

      EXTREME CARE MUST BE USED WHEN SPECIFYING DIRECTORIES

  • To successfully use dg_prune, you should ensure:
    1. Archive log files are stored in a separate directory from all other files.
    2. Backup files, if made to disk, should be stored in a separate directory from all other files. This directory may have multiple subdirectories.
    3. The dg_prune script should be run regularly. It can be scheduled with cron to run monthly, semi-monthly, or weekly.
  • Download dg_prune now! It's FREE!

Recovery Wizard

This recovery wizard guides you through the process of restoring a database. As you use the wizard, you should monitor the contents of the $DB2INSTANCEHOME/sqllib/db2dump/db2diag.log file closely.

Will DB2 start using the db2start command? Yes  No 

 

 

 

 

 

 

 

 

 

 

 

Check Shared Memory Message Queues

Is this your second attempt to start DB2 with the db2start command? Yes No

 

 

 

 

 

 

 

 

 

 

 

 

Check and clean up shared memory

  1. Use the ipcs command to determine if there are any stray semaphores or message queues in shared memory. Example:
    • ipcs | grep instanceowner
  2. If any queues, messages, etc. are returned for the instance owner, then delete them using the ipcrm command. Example:
    • ipcrm -q number
      • This deletes the message queue identified by the number.

  3. Click Here to continue.

 

 

 

 

 

 

 

 

Question 2: Media Failure

Was there a media failure, or do devices/containers/files need to be resized?   Yes  No

 

 

 

 

 

 

 

 

 

Question 3: Archive Logging

Is Archive Logging enabled?   Yes  No

 

 

 

 

 

 

 

 

 

 

 

Question 3: Archive Logging

Is Archive Logging enabled?   Yes  No

 

 

 

 

 

 

 

 

 

 

  Redirected Restore with ROLLFORWARD

  1. Restore database DBNAME from dir/dev [ {,dir/dev} ... ] REDIRECT
    • Example: db2 "restore database PRODDB from /dev/rmt0 REDIRECT"
    • Additional restore command options may be appropriate for your situtation or may improve performance of the restore (e.g. WITH num-buff BUFFERS).
    • After a reasonably short period of time, a command prompt should be returned to the user. The command window/terminal should have an EXCLUSIVE connection to the database. You can verify this with command:
      • db2 "get connection state"
  2. Use the "LIST TABLESPACES SHOW DETAIL"
    and "LIST TABLESPACE CONTAINERS FOR tablespace-id [SHOW DETAIL]"
    commands to determine the current tablespaces, their IDs, their sizes, and their devices or files.
    • It is a good idea to redirect the output from these commands to output files for subsequent viewing.
  3. Use the "SET TABLESPACE CONTAINERS FOR tablespace-id" command to redefine the storage characteristics for the tablespace. Think of this opportunity as your chance to move, resize, and otherwise tune and re-configure each tablespace. Remember when you thought, "Gee, I wish this tablespace was on disks hdisk4 and hdisk5 instead of hdisks hdisk2 and hdisk3"? Now is your chance to move them.
    • Examples:
      1. The tablespace identified by ID number 7 needs to be moved and/or resized
      2. SMS: db2 "set tablespace containers for 7 using (PATH 'home/tsid7dir')"
      3. DMS: db2 "set tablespace containers for 7 using (DEVICE 'dev/rtsid7c0' 262144, 'dev/rtsid7c1' 262144, 'dev/rtsid7c2' 262144)"
      4. DMS: db2 "set tablespace containers for 7 using (FILE 'home/tsid7.c0' 262144, 'home/tsid7.c1' 262144, 'home/tsid7.c2' 262144)"
    • You must set the containers for each tablespace (as changes are necessary) before you continue.
  4. Continue the database restore using command:
    • db2 "restore database DBNAME CONTINUE"
  5. Rollforward the database to apply log changes since the time of the backup:
    • db2 "rollforward database DBNAME to end of logs and complete"
    • Additional command arguments may be appropriate for your situation.
  6. Click Here to continue.

 

 

 

 

 

 

 

 

 

  Redirected Restore without ROLLFORWARD

  1. Restore database DBNAME from dir/dev [ {,dir/dev} ... ] REDIRECT
    • Example: db2 "restore database PRODDB from /dev/rmt0 REDIRECT"
    • Additional restore command options may be appropriate for your situation or may improve performance of the restore (e.g. WITH num-buff BUFFERS).
    • After a reasonably short period of time, a command prompt should be returned to the user. The command window/terminal should have an exclusive connection to the database. You can verify this with command:
      • db2 "get connection state"
  2. Use the "LIST TABLESPACES SHOW DETAIL"
    and "LIST TABLESPACE CONTAINERS FOR tablespace-id [SHOW DETAIL]"
    commands to determine the current tablespaces, their IDs, their sizes, and their devices or files.
    • It is a good idea to redirect the output from these commands to output files for subsequent viewing.
  3. Use the "SET TABLESPACE CONTAINERS FOR tablespace-id" command to redefine the storage characteristics for the tablespace. Think of this opportunity as your chance to move, resize, and otherwise tune and re-configure each tablespace. Remember when you thought, "Gee, I wish this tablespace was on disks hdisk4 and hdisk5 instead of hdisks hdisk2 and hdisk3"? Now is your chance to move them.
    • Examples:
      1. The tablespace identified by ID number 7 needs to be moved and/or resized
      2. SMS: db2 "set tablespace containers for 7 using (PATH 'home/tsid7dir')"
      3. DMS: db2 "set tablespace containers for 7 using (DEVICE 'dev/rtsid7c0' 262144, 'dev/rtsid7c1' 262144, 'dev/rtsid7c2' 262144)"
      4. DMS: db2 "set tablespace containers for 7 using (FILE 'home/tsid7.c0' 262144, 'home/tsid7.c1' 262144, 'home/tsid7.c2' 262144)"
    • You must set the containers for each tablespace (as changes are necessary) before you continue.
  4. Continue the database restore using command:
    • db2 "restore database DBNAME CONTINUE"
  5. Click Here to continue.

 

 

 

 

 

 

 

 

  Restore with ROLLFORWARD

  1. Restore database DBNAME from dir/dev [ {,dir/dev} ... ]
    • Example: db2 "restore database PRODDB from /dev/rmt0 "
    • Additional restore command options may be appropriate for your situtation or may improve performance of the restore (e.g. WITH num-buff BUFFERS).
    • After a reasonably short period of time, a command prompt should be returned to the user.
  2. Rollforward the database to apply log changes since the time of the backup:
    • db2 "rollforward database DBNAME to end of logs and complete"
    • Additional command arguments may be appropriate for your situation.
  3. Click Here to continue.

 

 

 

 

 

 

 

 

 

  Restore without ROLLFORWARD

  1. Restore database DBNAME from dir/dev [ {,dir/dev} ... ]
    • Example: db2 "restore database PRODDB from /dev/rmt0"
    • Additional restore command options may be appropriate for your situtation or may improve performance of the restore (e.g. WITH num-buff BUFFERS).
    • After a reasonably short period of time, a command prompt should be returned to the user.
  2. Click Here to continue.

 

 

 

 

 

 

 

 

 

 

  Restart and Activate Database

  1. Restart the database using command:
    • db2 "restart database DBNAME"
  2. Terminate your connection: db2 "terminate"
  3. Activate the database if it is a highly used/production database:
    • db2 "activate database DBNAME"
  4. It might be a good idea at this point to back up the database, but this is not required.
  5. Congratulations! You did it! At this point, the database should be recovered and operational!
  6. Return to Database-Guys home or top of page.

 

 

 

 

 

 

 

 

 

 

Contact IBM or other Support for assistance


Database-Guys Home   ScottHayes@database-guys.com   (585) 349-2450

Home | Contact Us | Support | Top 10 Tips | Customer Feedback | Careers | Downloads
Live! Monitor Our Database!   |   Live! Monitor Our Tables!
White Papers and Other Resources


Copyright © 2003 Database-Guys. All rights reserved. Privacy and Legal details.