Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2007
    Posts
    62

    Unanswered: "Backup .. include logs" requires archived logs ?

    Hello,

    Yesterday I tried to make online backup using "include logs" option and got this:
    Code:
    SQL2428N  The BACKUP did not complete because one or more of the requested log files could not be retrieved.
    Our systems archiving:
    1. Userexit archives logs from log_dir to log_archive.
    2. Cron job moves logs from log_archive elswhere.
    (so log retrieval using userexit is not possible)

    I always thought that "include logs" option adds active log files(at the moment backup is started) to backup image. Here is some log entries from db2diag.log (backup start time 20:31):
    Code:
    2009-05-19-20.00.51.651769+180 I6482618A360       LEVEL: Warning
    PID     : 282694               TID  : 1           PROC : db2logmgr (DBP) 0
    INSTANCE: db2dbp               NODE : 000
    FUNCTION: DB2 UDB, data protection, sqlpgArchiveLogFile, probe:3170
    MESSAGE : Completed archive for log file S0063621.LOG to USEREXIT from
              /db2/DBP/log_dir/NODE0000/.
    ----------------------------------------------------------------------------------------------------
    2009-05-19-20.55.37.675820+180 E6493117A491       LEVEL: Error
    PID     : 266446               TID  : 1           PROC : db2bm.598144.4 0
    INSTANCE: db2dbp               NODE : 000         DB   : DBP
    INSTANCE: db2dbp               NODE : 000
    FUNCTION: DB2 UDB, data protection, sqlpgRetrieveLogFile, probe:4165
    MESSAGE : ADM1847W  Failed retrieve for log file "S0063621.LOG" on chain "0"
              from "/db2/DBP/log_dir/NODE0000/".
    ----------------------------------------------------------------------------------------------------
    2009-05-19-20.55.37.675820+180 E6493117A491       LEVEL: Error
    PID     : 266446               TID  : 1           PROC : db2bm.598144.4 0
    INSTANCE: db2dbp               NODE : 000         DB   : DBP
    APPHDL  : 0-808                APPID: *LOCAL.db2dbp.090519173110
    FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1917
    MESSAGE : ADM8010E  Backup was unable to copy requested log file "S0063621.LOG"
              for inclusion in the backup image. The backup has been aborted.
    So S0063621.LOG was archived 20:00, then 20:31 backup was started, then 20:55 db2 tried to retrieve S0063621.LOG(which do not exist - see above ) and failed.

    Do db2 backup with "include logs" option realy needs logs which are archived even before backup is started ?
    If yes why ?

    thanks
    Vilius

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    when the backup is started, the log file name is retained
    while running the backup log files can get closed and archived
    at end of backup logs need to be included in image and log file has been archived; at that time we retrieve a log from archive location. if log file has been renamed in mean time by other procedure, the log file is not available anymore and backup will fail
    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

  3. #3
    Join Date
    Nov 2007
    Posts
    62
    Hi,
    Code:
    while running the backup log files can get closed and archived
    Yes I understand that - but my example shows that db2 tried to retrieve log file which was archived half an hour before backup started ?

    V

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by vilius
    Our systems archiving:
    1. Userexit archives logs from log_dir to log_archive.
    2. Cron job moves logs from log_archive elswhere.
    (so log retrieval using userexit is not possible)
    No delay in the cron procedure? You got guts!
    My experions:
    on v9.5 the "include logs" work fine, the database can be re-created on another server using the log-files which were included.
    Recently we needed te re-create an old database (13 months ago) and that was still v8.2 We did not succeed to restore/rollforeward based upon the included logs. Extra restore of old logs was required.
    So it seems that IBM did some work on that area and now (v9.5) it works as designed.
    Hope this helps....

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes because some luw has started long time ago and is still present
    to find the complete luw we need the older log files and the current files, to have it all
    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

  6. #6
    Join Date
    Nov 2007
    Posts
    62
    I've done one mistake I thought that log is archived when it is inactive, but:
    Userexit archives logs as soon as they are full, even if they are still active. So S0063621.LOG probably was active at the time backup started. The only explanation is that it became inactive during backup time.
    yes because some luw has started long time ago and is still present
    I don't agree with that - if log contains transaction entries which is not commited, even log is full - it should stay in log_dir.

    V
    Last edited by vilius; 05-20-09 at 09:10.

  7. #7
    Join Date
    Nov 2009
    Location
    Harrisburg, PA
    Posts
    5
    I'm using db2 9 on AIX and have the same problem. Is there a way to find the LSN the database is expecting to see what the oldest log file the backup will need to include? Or some other way to find all the log files a backup neededs to include before it starts?

  8. #8
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Run the command Archive logs before the backup.
    Logs will be truncated and then you may not need archived logs

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by JamesTJr View Post
    I'm using db2 9 on AIX and have the same problem. Is there a way to find the LSN the database is expecting to see what the oldest log file the backup will need to include? Or some other way to find all the log files a backup neededs to include before it starts?

    db2 will include all logs that were active (some of them may be already archived/reused and some not) from the time backup started and until it ended. You can get the "First active log file" from db cfg.
    Last edited by db2girl; 11-03-09 at 12:27.

  10. #10
    Join Date
    Nov 2009
    Location
    Harrisburg, PA
    Posts
    5
    I have tried 'archive log' command before backup and still get the error. Also, the log file identified in the diaglog needed to be retrieved is the one before the 'first active log'.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Do you move the logs from the archived location somewhere else?

  12. #12
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Looks like you are running into a bug. May want to open a PMR with IBM
    Backup command should have the ability to grab any logs that is required when you use "InCLUDE LOGS" option

  13. #13
    Join Date
    Nov 2009
    Location
    Harrisburg, PA
    Posts
    5
    Db backup runs daily at 9.30pm for several hours. Daily at 5am sysadms backup archive log path and clean it out. Log backup is never running while db backup is. There are once in a blue moon times where the db has no update activity and the backup is looking for an archived log that was cleaned-out 16 1/2 hours earlier.

  14. #14
    Join Date
    Feb 2009
    Posts
    114
    Logs to be included in the backup include those active at the start of the oldest in-flight uncommitted transaction running at the start of backup

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by JamesTJr View Post
    the log file identified in the diaglog needed to be retrieved is the one before the 'first active log'.
    This doesn't sound the way DB2 should work - it should not look for any logs prior to the 'first active log'.


    Try to disable the manual cleanup of archived logs for a few days and see if the problem reoccurs. If it does, then follow koganti's advice and open a pmr.

Posting Permissions

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