Db2 Recovery History File in HADR Clusters

According to the Db2 Knowledge Centre:

“A recovery history file is created with each database and is automatically updated during various operations.”

Amongst many others, the following operations (of particular interest for this article) cause the recovery history file to be updated:

• A table space is created
• A database or table spaces are backed up

So, let’s see how exactly this plays out in HADR clusters!

The Situation

Our client is running their production database in a (simplest possible) HADR cluster consisting of two nodes – the primary and the (main) standby node.

The backup strategy for this PROD database, due to its large size, is to take a full DB backup on Sundays and incremental (delta) backups on all other days of the week (all this on the primary node, of course):

• Sunday: FULL DB backup
• Monday: INCREMENTAL backup
• Tuesday: INCREMENTAL backup
• Wednesday: INCREMENTAL backup
• Thursday: INCREMENTAL backup
• Friday: INCREMENTAL backup
• Saturday: INCREMENTAL backup

Unrelated to the database backup strategy, and as a part of the regular database activities, new tablespaces are being added to the primary database (and replicated via HADR to the standby) once every month.

Immediately after the tablespaces are created – even if completely empty at the time – they are also backed up via a targeted TBSP backup. As previously mentioned, this updates the Recovery History file and also creates a baseline (for these new objects) so that the subsequent execution of incremental backups on the primary can carry on without interruptions.

The Problem

When the new tablespaces are backed up (on the primary node), this information is obviously written to the local Recovery History file on the primary node.

However, changes to the Recovery History file on the HADR primary database are not automatically replicated to the HADR standby database (details here).

Therefore, the standby will not have this same information that the new tablespaces have already been backed up (and therefore prepared for incremental backups).

In such a situation, if a TAKEOVER on the standby is executed (whereby it becomes the new primary), any subsequent incremental backups on this new primary will fail with the error messages (written to the db2diag.log) similar to those shown below:

...
MESSAGE : ADM8005W  Incremental backup was not enabled for table space
          "IS_BIG_202603" (ID "366").  A non-incremental backup of this table space 
is required.
...
MESSAGE : SQL2426N  The database has not been configured to allow the
          incremental backup operation. Reason code = "".
...
DATA #1 : String, 50 bytes
Backup terminated.
...

The Solution

To resolve this situation, either of the following can be done:

1. Following a TAKEOVER, run a full DB backup on the new primary node first and only then the incremental backups.

or:

2. Before a TAKEOVER is executed, refresh the recovery history file on the standby server (as explained below).

Once these actions are carried out, the incremental backups will be able to run and complete without any issues.

Refreshing the recovery history file on a HADR standby node

Thankfully, for the 2^nd option listed above, the refresh procedure is very simple:

1. Backup just the Recovery History file on the primary node, online:

db2 backup db <dbname> no tablespace online exclude logs

2. On the standby node, restore the recovery history file (this can be done online as well):

db2 restore db <dbname> history file online taken at <TIMESTAMP>

The Conclusion

Even if this problem can be a bit annoying (alerts on failed backups… unhappy clients…), in reality it is relatively easily fixed (especially so if your affected client can afford to run a full DB backup on those “other” days of the week, i.e. immediately) and will have no lasting consequences.

In any case, it would really be helpful if the IBM Db2 documentation (i.e. the Knowledge Centre) mentioned the fact that the Recovery History File may need to be updated manually before a HADR TAKEOVER is executed.
And preferably this should be mentioned in a relevant place, for example where the HADR TAKEOVER command is covered in full detail (so, here)!