Proper online backup strategy

[g***@bilumina.com]

Hi!

Using DB2 Express-C 10.5 with fixpack 5 on Ubuntu server 12.04.5 64-bit.

Every night I do online backup like this:
db2 backup db MYDB online to /home/db2inst1/backup compress include logs
db2 connect to MYDB
FIRST_ACTIVE_LOG=$(db2 get db cfg for MYDB | grep "First active log file" | awk '{print $6}')
db2 prune logfile prior to $FIRST_ACTIVE_LOG
db2 disconnect current

Is this OK or will I get some data loss between backup and prune? By data loss I mean missing transactional logs, so when I do a restore (taking last backup and all remaining transactional logs) I will not get data up to 5 minutes ago (for example)?

Best regards,
Kovi

[Jeremy Rickard]

Hi,

Even assuming the commands are scripted to run immediately after each preceding command, you could still get data loss.

Remember that the last log file included in the online backup is usually not the final version of that file. So in some circumstances your sequence of commands, even if scripted, could end up pruning a later version of that last log file, resulting in a broken chain of logs.

For example, think about what might happen if a long running transaction commits at about the same time as the end of the online backup, and also just before the last log file in the backup is filled. The first active log number will then (potentially) leap forward to the log file number that is the same as that of the last log file in the backup image. Now, if another transaction then causes that last log file to fill and get archived just after the backup completes and just as your script evaluates the FIRST_ACTIVE_LOG value, then you may get a log file number higher than the last log in the backup image. You would then prune the later version of the last log file, leaving only the earlier, incomplete version in the backup image. Consequently you could recover to end of backup but not to end of logs.

Maybe that would be rare, but certainly not impossible. Why risk it? Personally I would also like to see at least one earlier backup and all the logs since then.

Remember too that there are database configuration parameters that let DB2 remove old archive logs for you, automatically, so you don't necessarily have to script all this.

Regards, Jeremy

[g***@bilumina.com]

Post by Jeremy Rickard
Hi,
Even assuming the commands are scripted to run immediately after each preceding command, you could still get data loss.
Remember that the last log file included in the online backup is usually not the final version of that file. So in some circumstances your sequence of commands, even if scripted, could end up pruning a later version of that last log file, resulting in a broken chain of logs.
For example, think about what might happen if a long running transaction commits at about the same time as the end of the online backup, and also just before the last log file in the backup is filled. The first active log number will then (potentially) leap forward to the log file number that is the same as that of the last log file in the backup image. Now, if another transaction then causes that last log file to fill and get archived just after the backup completes and just as your script evaluates the FIRST_ACTIVE_LOG value, then you may get a log file number higher than the last log in the backup image. You would then prune the later version of the last log file, leaving only the earlier, incomplete version in the backup image. Consequently you could recover to end of backup but not to end of logs.
Maybe that would be rare, but certainly not impossible. Why risk it? Personally I would also like to see at least one earlier backup and all the logs since then.
Remember too that there are database configuration parameters that let DB2 remove old archive logs for you, automatically, so you don't necessarily have to script all this.
Regards, Jeremy

Hi!

Exactly what I was afraid of. If I understand you correctly:
- do online backup, don't do pruning
- backup transaction logs to a safe place (this is done every X minutes)
- set DB2 parameters so that DB2 itself removes transaction logs that are not needed anymore. Can you tell me what this parameters are, since I'm not aware of them?

Best regards,
Kovi

[Larry]

Post by g***@bilumina.com

Post by Jeremy Rickard
Hi,
Even assuming the commands are scripted to run immediately after each preceding command, you could still get data loss.
Remember that the last log file included in the online backup is usually not the final version of that file. So in some circumstances your sequence of commands, even if scripted, could end up pruning a later version of that last log file, resulting in a broken chain of logs.
For example, think about what might happen if a long running transaction commits at about the same time as the end of the online backup, and also just before the last log file in the backup is filled. The first active log number will then (potentially) leap forward to the log file number that is the same as that of the last log file in the backup image. Now, if another transaction then causes that last log file to fill and get archived just after the backup completes and just as your script evaluates the FIRST_ACTIVE_LOG value, then you may get a log file number higher than the last log in the backup image. You would then prune the later version of the last log file, leaving only the earlier, incomplete version in the backup image. Consequently you could recover to end of backup but not to end of logs.
Maybe that would be rare, but certainly not impossible. Why risk it? Personally I would also like to see at least one earlier backup and all the logs since then.
Remember too that there are database configuration parameters that let DB2 remove old archive logs for you, automatically, so you don't necessarily have to script all this.
Regards, Jeremy

Hi!
- do online backup, don't do pruning
- backup transaction logs to a safe place (this is done every X minutes)
- set DB2 parameters so that DB2 itself removes transaction logs that are not needed anymore. Can you tell me what this parameters are, since I'm not aware of them?
Best regards,
Kovi

I believe this is what you are likely looking for:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0051348.html?lang=en

[g***@bilumina.com]

Post by Larry

Post by g***@bilumina.com

Post by Jeremy Rickard
Hi,
Even assuming the commands are scripted to run immediately after each preceding command, you could still get data loss.
Remember that the last log file included in the online backup is usually not the final version of that file. So in some circumstances your sequence of commands, even if scripted, could end up pruning a later version of that last log file, resulting in a broken chain of logs.
For example, think about what might happen if a long running transaction commits at about the same time as the end of the online backup, and also just before the last log file in the backup is filled. The first active log number will then (potentially) leap forward to the log file number that is the same as that of the last log file in the backup image. Now, if another transaction then causes that last log file to fill and get archived just after the backup completes and just as your script evaluates the FIRST_ACTIVE_LOG value, then you may get a log file number higher than the last log in the backup image. You would then prune the later version of the last log file, leaving only the earlier, incomplete version in the backup image. Consequently you could recover to end of backup but not to end of logs.
Maybe that would be rare, but certainly not impossible. Why risk it? Personally I would also like to see at least one earlier backup and all the logs since then.
Remember too that there are database configuration parameters that let DB2 remove old archive logs for you, automatically, so you don't necessarily have to script all this.
Regards, Jeremy

Hi!
- do online backup, don't do pruning
- backup transaction logs to a safe place (this is done every X minutes)
- set DB2 parameters so that DB2 itself removes transaction logs that are not needed anymore. Can you tell me what this parameters are, since I'm not aware of them?
Best regards,
Kovi

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0051348.html?lang=en
--
Larry E.

Hi!

I've read a great response on Stackoverflow about this at http://dba.stackexchange.com/a/91841/31847
This gives me an idea how DB2 makes everything automatic. But let's say I don't want this and I still want to do manual PRUNE LOGFILE. Then I could use this SQL: SELECT FIRSTLOG AS FIRST_BACKUP_LOG FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='B' ORDER BY EID DESC FETCH FIRST ROW ONLY
and only do this: prune logfile prior to FIRST_BACKUP_LOG

Do you agree?

Best regards,
Kovi

[Jeremy Rickard]

Well, you're along the right lines now. But, playing devil's advocate, what if someone runs a backup that includes only a few of the tablespaces? Then you have a problem.

Actually this is quite a difficult thing to script because of edge-case scenarios. I would suggest adding a minimum age, certain to include at least one complete backup.

Regards, Jeremy

Post by g***@bilumina.com
I've read a great response on Stackoverflow about this at http://dba.stackexchange.com/a/91841/31847
This gives me an idea how DB2 makes everything automatic. But let's say I don't want this and I still want to do manual PRUNE LOGFILE. Then I could use this SQL: SELECT FIRSTLOG AS FIRST_BACKUP_LOG FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='B' ORDER BY EID DESC FETCH FIRST ROW ONLY
and only do this: prune logfile prior to FIRST_BACKUP_LOG
Do you agree?
Best regards,
Kovi

[g***@bilumina.com]

Post by Jeremy Rickard
Well, you're along the right lines now. But, playing devil's advocate, what if someone runs a backup that includes only a few of the tablespaces? Then you have a problem.
Actually this is quite a difficult thing to script because of edge-case scenarios. I would suggest adding a minimum age, certain to include at least one complete backup.
Regards, Jeremy

Post by g***@bilumina.com
I've read a great response on Stackoverflow about this at http://dba.stackexchange.com/a/91841/31847
This gives me an idea how DB2 makes everything automatic. But let's say I don't want this and I still want to do manual PRUNE LOGFILE. Then I could use this SQL: SELECT FIRSTLOG AS FIRST_BACKUP_LOG FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='B' ORDER BY EID DESC FETCH FIRST ROW ONLY
and only do this: prune logfile prior to FIRST_BACKUP_LOG
Do you agree?
Best regards,
Kovi

Nobody, but me does backups. So, I'm covered. :)