Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Unanswered: Db2 Archive Logs

    I have been reviewing a number of posts on this subject and nothing I have seen has directly answered my question.

    Nightly an on-line backup (include logs) is performed on our production 9.1 UDB database running on a Windows 2007 server.

    Logs are archived to a seperate drive on the server. This drive is currently at 95% of capacity and there are over 3,000 log files in the archive directory. My question is -- which of these files can safely be deleted without compromising any potential restore or recovery.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I would keep the logs that are about a week old, and dump the rest. You have nightly backups, so all of the last week's backups would have to be corrupted to have a problem trying to restore without the logs.

    Andy

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    [QUOTE=ARWinner]and dump the rest/QUOTE]
    Let DB2 do the cleaning for you; DB2 created the file so actually it is a db2-object. Use "db2 prune" for this.
    DB2 Universal Database

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all depends on how far in time you would like to restore your db
    if you plan to restore a db from a backup of 2 weeks old, keep all log files after this backup
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    I've been wondering the same thing.....

    I'm running nightly online backups starting with a FULL on Sunday and INCREMENTAL DELTAs Monday-Saturday.

    How many log files would you recommend holding on to? A weeks worth? And we're talking about the logs inside the ~/db2i1/NODE0000/SQL00001/SQLOGDIR directory, correct?

    I have a backup script I wrote for my HADR cluster, so I think I would incorporate the deletion inside there, or utilize the PRUNE command.

    What say the almighty DB2 forum?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Like Guy said, the number of logs you keep depends on how far back you may be required to go.

    If your company requires you to keep backup images for 3 months, then you also need to keep the corresponding logs. The "include logs" option will only save the logs required to rollforward to a minimum point in time (end of online backup), but you need to have more logs than what's stored in the image if you need to rollforward to a different point in time.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I usually is not necessary to keep logs older than the last few backups. If one is keeping backups for three months, it is unlikely that you would need ot roll forward the logs to a point in between the backups. But you must make sure that all backups have the "include logs" option for onliine backups, becasue an online backup with the logs active during the backup cannot be restored.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    If you're 100% sure that you won't be required to rollforward to a point in between the backups, then no reason to keep all the logs. Our strategy has always been to keep the logs for as long as we required to keep the images...You never know what you may be asked to do.

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    AS Marcus pointed out. As long as you include logs in your backup, you should be ok to smoke them.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    This is all really good stuff.

    So here's a scenario, and I want to make sure I'm getting this right:

    Sunday: Full Online backup include logs
    Active Log # 20
    Action: This will backup up the whole database and include Active Log #20

    Monday: Incremental Delta backup include logs
    Active Log #25
    Action: This will backup the newly modified pages since Sunday's successful backup AND include Active Log #25

    Tuesday: Incremental Delta backup include logs
    Active Log #34
    Action: This will backup the newly modified pages since Monday's successful backup AND include Active Log #34

    Observation:
    If business rules dictate that I only need to keep the logs responsible for restoring at any time for the previous day to present then I would keep Monday's backup, logfiles #26-#33, and Tuesday's backup

    If the rules say that I don't need to backup to a certain point in time, just back it up, I would only keep Tuesday's backup and no logfiles

    Is this correct?

    What's the easiest way to determine which was the active log when the backup took place?

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I'll answer your last question first:

    You can use "db2 list history backup all for db <db name>" and look at the "Earliest Log" and "Current Log" value. This identifies the log range you need in order to rollforward to a minimum point in time (end of backup). This log range is part of the backup image when logs are included.


    I'm not too clear about your first question.. Let's say, today is Wednesday and you have to restore your db back to Tuesday. Let's say, tuesday's backup completed at 2:30am. What is the requirement? To restore your db back to Tuesday 2:30am or some later time?

    If the requirement is to restore it back to 2:30am (end of backup), then you only need to have the logs that were active while the backup was executing (those logs are part of the image). If you have to restore it back to 10am (for example), then you have to have additional logs.

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If your Tuesday backup is good. And you crash on Wed before the back up. Technically speaking all you would need are the logs starting after your Tuesday backup. #35+. as #34- and before will be extracted from the back up image.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    ok great, I think I'm getting it...

    So needless to say, if I'm backing up every night, I only need to keep that day's logs around priot to the next backup....


    I know this is really easy to grasp, I don't know why I'm having such a hard time with it...

    OH! And is it safe to say that the logs you get rid of on the PRIMARY server, you can get rid of the same logs on the Standby server, in a clustered environment?

  14. #14
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    bulump, As long as you only want the ability to restore to some point in time on the previous day, all you need is:

    1) Last Full Backup
    2) All Delta Incremental backups since the Full backup and before the restore time.
    3) All Logs since the last Delta Incremental backup before the restore time and up to and including the restore time.

    For example, you might have the following timeline:

    Sun 2:30 AM Full Backup
    Logs
    Mon 2:30 AM Delta Incremental Backup (1)
    Logs
    Tue 2:30 AM Delta Incremental Backup (2)
    Logs
    Wed 2:30 Am Delta Incremental Backup (3)
    Logs

    On Wed, you determine the need to restore to Tue 3:00 PM. You will need Full backup from Sun, Delta Incremental backup (1) from Mon, Delta Incremental Backup (2) from Tue, and Tue Logs.

    If you never want to restore any earlier than the previous day, you could delete the Sun Logs on Tues and the Mon Logs on Wed, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •