Restore a DB2 LUW database without a DB2 backup copy from filesystem backup

19 January,2017 by Jack Vamvas

Question: A developer corrupted some data and requested a restore to a previous recovery point. A standard request. When checking the backup system , there were no backup copies available. The backups had been failing for the last couple of nights and the monitoring alerts were not working.

I checked around the backup history file , in case any ad-hoc backups had completed, and there none available. As a last resort I checked if there had been a flat file backup , and amazingly there was a flat file backup of all the files on the server. This is unusual as normally DB2 files are excluded on flatfile backups.

Is it possible to recover the database just with the flat files?

Answer: Yes it is possible, but careful planning is required. Before you proceed with this procedure , you must confirm the flat  files are recoverable and include all the files you need.These are the steps I would take

  1. Stop any application connections to the database
  2. Take a full OFFLINE backup of the db
  3. Take a copy of the get db cfg output , for reference
  4. Identify the folders required to be replaced. This is an important step.This will heavily depend on how the database was set up. I normally create a separate folder for the data files and log files.
  5. If you are unsure of step 4 – do some research or speak to another DBA , who can cross-check what folder you’re proposing to overwrite
  6. Stop the DB2 instance – making sure there are no auto starts or processes which will auto start the DB2 instance. It may also be necessary to notify any Operations teams to avoid alerts for this DB2 instance, just in case they attempt to restart
  7. Restore the files, it will be an overwrite of existing files.
  8. Before you restart the DB2 instance – you may be in luck and know the last active log file of the db you’re restoring. This will allow you to clean up any transaction log files which are from after this restore i.e related to the database you’re overwriting
  9. If you don’t have the last active log file number of the db you’re recovering, you could start the DB2 instance , quickly do a db2 get db cfg and obtain the information 
  10. You could check for the first active log and calculate the last active log
  11. Do a connection test
  12. FIX YOUR MONITORING ALERT SYSTEM
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Restore a DB2 LUW database without a DB2 backup copy from filesystem backup

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright