A case of missing logs !

At work, we had a situation when we had to restore a database from a previous back up and we were missing logs.  Have you been in such situations ?  I hope not !  Its not a pretty situation…not a pretty situation at all.   On Oct 2010 I wrote an article about dealing with db2 backup and archive logs ,well today I reemphasis the importance of a good backup strategy.
Here is quick glance on what I normally do on a production DB2 V9.5 + server :
1. Turn on archive logging
2. Setup cronscript to backup the database to a filesystem
3. Config the DB to automatically prune the archive log, history and backup after certain number of days (depending on requirement)
4. Ensure TSM backups the archive log, active log and backup and home directories frequently depending on SLA.

But couple of  days ago I encountered a system which was not setup properly, for some reason  one of the DBAs decided to keep only 1 day worth of archive log and backup on the system and to top it forgot to configure the TSM to backup the directory. The result, we only have previous night’s online backup and 1 day of history on the history file . Not an ideal situation. Lucky we never had a filesystem corruption.

Couple of nights ago, something went wrong during a batch data update.  A restore from the only available online backup from previous night was quickly initiated, only to find out the first log in the chain was missing. Oops !  This means you can’t rollforward and without completing the rollforward you can’t connect to the database since the database will in ROLLFORWARD PENDING mode.  I was woken up to help with the effort.  I tried a rollforward to the time of backup and that of course didn’t work. For the interest of time we contacted IBM tech support to let them know of the shameful situation, the tech support quickly responded and after the usual db2support.zip upload suggested resetting the control files so that the database will not be in rollforward pending state anymore (that was a stupid suggestion , you will find why later on) and I will be able to connect to the database. Sound good ? Well, the problem is I would lose a days worth of data, not really acceptable.

While the  “expert” tech support was creating the control file, I quickly glanced at the backup script and saw it didn’t exclude the logs ! Great news !    DB2 by default will backup the log chain for the backup file.  This means  the backup file will include the first and the last log required to rollforward to the first consistent point.  So when you execute the following,  the corresponding transaction logs are included on the backup file :

db2 backup database rajudb online to /db2_backups   or
db2 backup database rajudb online to /db2_backups include logs

The backup file will be a little larger and the backups will take a bit longer, but sure will save your back when you get into situation like this.  Include logs is the default. If you don’t want the logs to the included you need to mention EXCLUDE LOGS . Example :

db2 backup database rajudb online to /db2_backups exclude logs

So how do you extract the logs from this file ? Well you restore :

db2 restore db rajudb from /db2_backups logtarget /db2_templogs

This will restore the database and will extract the corresponding logs to the /db2_templogs directory.

Once the restore was complete, I ensured all the logs where there to rollfoward to a point just before the bad update happened and executed the rollfoward

db2 rollforward db rajudb to 2011-01-22-21.14.00 using local time and complete overflow log path (/db2_templogs);

DB2 picked up the logs from the /db2_templogs and the regular log directories found on the database configurations parameters

Even though the logs included on the backup will not be sufficient to get you to a no data loss point, it will be something to fall back on if you miss or have a bad log file in the chain.  Now had I just listened to the IBM tech support guy, I would have lost a days worth of data.  I could not believe the tech support guy didn’t ask me what kind of online backup I had and whether it included the logs.  I expect a pretty smart guy when I initiate a Sev 1 PMR.  So thumbs down to IBM Support on this.

I decided to post this because I could not find anything on the web that said what to do or to try during such situations. So, if you get into a situation like this, try the restore with log target  and rollforward to the last available consistency point or call IBM tech support to get the database control file reset. Now if you get the control file reset, there is a whole lot of catches that comes with it.  IBM suggests that you run a db2look , extract all the data , recreate the database and then reload the data.  So beware of  it !

Moral :  Ensure that you have your database, logs backup’d up and desirably multiple versions.