DB2 Database Restore and Rollforward: Details of Log Files

I do at least one restore a week. Usually between environments or for setting/resetting HADR, but sometimes real-world recoveries too. Since I’m almost always restoring from online full backups, I’ve developed my restore method and stick to it. It works for the relatively homogenous and standard enviroments that are the bulk of what I support. I’m pretty sure I can do(and possibly have done) a basic restore with extracting logs in my sleep at this point.

But when you’re first doing a restore with rollforward, or if you haven’t done one for a while, you don’t have the intuitive feel for where your transaction log files are and where they need to be.

When Transaction Log File Location Is An Issue

If you’re lucky enough to be able to take offline backups and restore from them without rolling forward through any log files, then you won’t need the content in this particular post. I live in the real world of e-commerce databases – even dev/qa/staging environments are expected to be up 24/7. So it’s a rare day when I get to actually take an offline backup. If you are so lucky, please just include the keywords “WITHOUT ROLLING FORWARD” on your restore command, and you won’t have to rollforward or deal with transaction log files. Obviously that only works if you have no specific time requirement for your restore.

If you’re restoring a database into itself and rolling forward and you have all of your transaction log files needed on disk or archived to TSM, then DB2 should be able to find the needed log files. But that’s my rarest type of restore. Much more often, I’m using restore to restore a database from one server to another, and that’s when you need to be most aware of where your log files are.

I also remember witnessing a small nightmare when I had been a DBA for less than 3 months. A colleague did a restore without rollforward into an existing database. They were trying restoring to different points in time because they didn’t know exactly when an error occurred. But when she did the restore without rollforward, it deleted all the “future” transaction logs. So every time I do a restore into the same database the backup was taken from, I now copy off all transaction logs that I might need in case I end up needing to do that rollforward to a later time than initially specified.

Extracting Transaction Log Files From a Database Backup Image

If you took an online backup (and included the “INCLUDE LOGS” keywords if before 9.5, where they became the default), then you have log files included with the backup image. BUT these log files only give you enough to rollforward your database to the end of the backup image. That may seem an obvious thing – that the backup image can’t include log files after it finished the backup – but I’ve had clients who were confused by that.

In order to extract the transaction log files you need from the backup image, you must include the LOGTARGET keyword (I believe you can also get the logs without restoring the database by using the LOGS keyword on the restore command). When specifying LOGTARGET, you must specify a directory that exists and does not have log files by the same name in it. If you don’t, you will get error SQL2581N at the end of the restore. The whole restore failing after being almost complete because the logs could not be extracted is frustrating.

My Normal Procedure

So my normal restore procedure looks like this:

1. rm /db_bkup/logs/*.LOG
2. db2 restore db sample from /db_bkup taken at into sample logtarget /db_bkup/logs replace existing without prompting
3. db2 get db cfg for sample |grep -i LOG – note the path to the active log files
4. cp /db_bkup/logs/*.LOG /PATH/TO/LOG/FILES
5. db2 rollforward db sample to end of backup and stop - love the “to end of backup” syntax introduced in 9.5

Where Other Transaction Log Files Might Come From

If you’re not just rolling forward to the end of a backup image, but to a specific timestamp after the backup, you have to make sure you have the right log files. Many of my clients archive logs only off to a different filesystem – if you’re retaining logs for recovery, you should be archiving them somewhere even if it’s just a different directory on the same filesystem to help prevent compressing or deleting an active log file (yes, I’ve seen someone do that to a production database – it is NOT fun to recover from). If you have TSM or another tape library, you may be archiving them directly to TSM, though watch your TSM setup to make sure you have the appropriate redundancy there.

If you archive logs to disk and then back that disk up, and have to restore log files to complete a rollforward, then you may run into space issues. I’ve got more and more clients on virtual or cloud servers, and in these cases, you can actually have a filesystem created to hold the log files while you’re rolling forward, and then have that same filesystem blown away when you’re done to reduce costs. On the actual rollforward command, you can use the OVERFLOW LOG PATH keywords to give DB2 an additional place to look for log files.

Similar to the above, if you’re compressing log files at the OS level (using gzip or similar), and suddenly need them all uncompressed to support a rollforward, you can have a filesystem created just for holding the uncompressed log files, specify it using he OVERFLOW LOG PATH keywords on the rollforward command, and then blow it away when you’re done with the restore.

You can also specify the OVERFLOW LOG PATH either at the db cfg level(OVERFLOWLOGPATH) or in the rollforward command. If you’re using TSM, this will give DB2 more space to extract log files to and make sure you’re not waiting on TSM calls and disk deletes for every step of the rollforward. Consider using the NORETRIEVE rollforward keyword as a part of this strategy.

Where DB2 Looks For Transaction Log Files During Rollforward

There really is diversity in how you’ve got transaction logs set up, so there are actually a lot of locations where DB2 will look for a log file before giving up and throwing an error. DB2 looks in the following places, in order:

1. Path to log files in the db cfg

   > db2 get db cfg for tempload|grep "Path to log files"
    Path to log files                                       = /db_logs/TEMPLOAD/NODE0000/
   

2. MIRRORLOGPATH in the db cfg

   > db2 get db cfg for tempload|grep MIRRORLOGPATH
    Mirror log path                         (MIRRORLOGPATH) =
   

3. OVERFLOWLOGPATH in the db cfg OR OVERFLOW LOG PATH on the rollforward command

   > db2 get db cfg for tempload|grep OVERFLOWLOGPATH
    Overflow log path                     (OVERFLOWLOGPATH) =
   

4. LOGARCHMETH1 in the db cfg

   > db2 get db cfg |grep LOGARCHMETH1
    First log archive method                 (LOGARCHMETH1) = DISK:/db_arch_logs/SAMPLE/
   

   No matter what type of location this is – DISK, TSM, Vendor Routine, or even if you’re using the old USEREXIT, DB2 retrieves log files from this path into the active log path OR overflow log path, rolls forward through the data in them, then deletes them from the active log path – a whole lot of I/O, especially when you’re rolling forward through a lot of log files.

5. LOGARCHMETH2 in the db cfg

   > db2 get db cfg |grep LOGARCHMETH2
    Second log archive method                (LOGARCHMETH2) = OFF
   

6. FAILARCHPATH in the db cfg

   > db2 get db cfg |grep FAILARCHPATH
    Failover log archive path                (FAILARCHPATH) =
   

While We’re Talking About Log Locations

One frustration that you may not have run into, but you may someday is restoring a database with an active log path that does not exist on the server you’re restoring to. In this case, if you try to set NEWLOGPATH between the restore and the rollforward, it won’t take. The reason is that NEWLOGPATH takes effect on database activation, and if you’re in a rollforward pending state, you cannot activate the database.

Because of this scenario, there’s an option to specify the new log path on the restore command using the NEWLOGPATH keyword. If you wait until the restore completes without using this keyword, it’s too late – you have to re-do the restore with the keyword to change the active log path.

If You’re Archiving To TSM or Other Tape Library Directly

Using TSM for the archiving of your log files directly is a nifty way of managing them (assuming your tsm maintains appropriate multiple copies). If so, you might actually dual-path during your restore. While the database restore itself is running, assuming your architecture supports it, you could be extracting log files to a path you will use as your OVERFLOW LOG PATH on rollforward (or extract in parallel with the rollforward, or in whatever point in the process you like), and then use the NORETRIEVE option on the rollforward to tell DB2 not to look for log files on TSM. You can also use NORETRIEVE on the the rollforward to limit how many log files are applied while using rollforward “TO END OF LOGS”.

I also wonder – when I’m restoring to the same server the backup was taken on, (and assuming I happen to archive log files to disk), if I specified the overflow log path to be the same as the archive log path, would it speed up my rollforward by avoiding the retreive-and-delete process that archive log files go through?

References

DB2 Info Center Entry on ROLLFORWARD: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0001978.html
The brain of Melanie Stopfer – Thanks, Melanie for answering some technical questions on this for me, and inspiring me to write a blog post on it.
DB2 Info Center Entry on RESTORE: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0001976.html