1

I am interested in one thing, if I understood correctly. DB2 is in Archive mode (online backups are made and archive logs keep coming which is OK).

If I made offline full backup today and I know I will not need returning to previous days etc. can I be free to delete all archive logs? In case of doing the restore of today's backup I suppose I will not get some error "S000013.log is missing" or something like that?

By my opinion it should be OK but I want to check this with experts?

3

There are two ways to determine which logs to keep. One you write yourself. You would need to issue a db2 list history backup all for <db name>. This will show you the history file and locate the last backup and the last needed archive log. Anything prior to that can be deleted.

Now you can add in automation.

First step set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database you are connected to. This will now allow you to use the history file to prune unneeded backups and archive logs. You still will have to use fancy scripting and text parsing, but you can do it. (You can probably also locate a table in DB2 to get at the data. That may be easier to grab the data.) But then you can issue db2 prune history <timestamp> [with force option] and delete to remove unneeded backups. And db2 prune logfile prior to <log file name> to delete unneeded archived logs.

This is great, but why not let DB2 handle it for you all the way? Set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database in question. Then you can set db2 update db cfg using NUM_DB_BACKUPS <number>. This set the maximum number of backups you wish to retain for the period of REC_HIS_RETENTN days. Now set db2 update db cfg REC_HIS_RETENTN <number> to the number of days. If you set NUM_DB_BACKUPS to 3 and REC_HIS_RETENTN to 2, it will keep 3 backups for 2 days. In other words a backup needs to be at least two days old before it will be consider for removal. Once a backup is older than 2 days, it will check to see if there are more than 3 backups. If this is true, then it will remove the older backups.

Now for the logs.....As long as you set LOGARCHMETH1 to DISK rather than LOGRETAIN, DB2 will also clean up any archive logs that are no longer necessary. It will retain archive logs between each of the saved backups, but delete everything else. Example setting: db2 update db cfg using LOGARCHMETH1 'DISK:/archive/logs'. If you set to LOGRETAIN, then DB2 will not auto clean the logs and you are left to do it yourself.

| improve this answer | |
  • Thank you Chris,this sounds good. But I have important question: which location does DB2 "watch" when calculating the NUM_DB_BACKUPS value??For example list history gives me 15 backups, but many of those backups are deleted. Previously in Circular mode I had /backup folder which now contains 2 backup records before the online bkp (one of those 2 records is required backup when switching to archive) and I have new folder /archiveBackup where I put my Online backup files (it has currently 5 bkp records). So this is big dilemma - which location will DB2 look when examining the Backup Numbers? – Dejan Feb 12 '15 at 20:41
  • It watches all locations a backup was taken to. It knows where the backups are as per the history file. – Chris Aldrich Feb 12 '15 at 22:13
  • Hi Chris, question for AUTO_DEL_REC_OBJ how it is scheduled, when it is triggered? It checks all the time or it has some schedule when it will check the number and days of backups? – Dejan Feb 13 '15 at 14:12
  • It checks based on what is set for NUM_DB_BACKUPS and REC_HIS_RETENTN as explained above. – Chris Aldrich Feb 13 '15 at 14:42
  • Hi Chris, yes I understand that but when does it check? at what time, when this checking is triggered? Is it checking it all the time or when backup is made and then it checks NUM_DB_BACKUPS and REC_HIS_RETENTN? You just misunderstood my previous post – Dejan Feb 13 '15 at 16:01
1

When you restore an offline backup, you are not required to roll forward (although the database will be in ROLLFORWARD PENDING state at the end of the backup, which you can avoid by using the WITHOUT ROLLING FORWARD option for RESTORE DATABASE).

This means that you can remove any archived log files.

However, keep in mind that you may want to be careful about which archive log files you remove – if you aren't careful you could accidentally remove files newer than the backup that might be necessary.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.