Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Unanswered: Not enough logs for online backup?

    Hello,

    I've setup a daily cronjob that performs an online backup of my DB every night. Now I noticed that the backup fails sometimes with missing logfiles.

    The DB is configured for 10 primary and 5 secondary logfiles. I took an listing of all logfiles exactly before the backup (which fails) and after the backup.

    Here is the listing before the backup:

    Code:
    Thu Aug 29 12:44:32 CEST 2013
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 12:40 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027804.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 12:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027805.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 10:00 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027806.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 10:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027807.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:27 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027808.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:37 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027809.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:37 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027810.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:38 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027811.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:42 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027812.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:47 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027813.LOG
    Now the backup aborts with missing logfile S0027804.LOG which was there before the backup starts. Now the listing after the failed backup:
    Code:
    Thu Aug 29 12:44:41 CEST 2013
    -rw------- 1 db2inst1 db2iadm1 2523136 Aug 29 12:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027805.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 12:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027806.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 10:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027807.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:27 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027808.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:37 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027809.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:37 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027810.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:38 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027811.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:42 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027812.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 11:47 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027813.LOG
    -rw------- 1 db2inst1 db2iadm1 33562624 Aug 29 12:44 /sync/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/S0027814.LOG
    It seems that DB2 cycles the backups exactly the moment the backup should
    take place. Any advice? Should I increase the number of primary logs? Thx in advance.

    DB2 WSE 9.7 FP6 on Linux

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the problem is not the nbr of logs
    when online backup is taken - default is include logs
    when backup terminates, it will include in backup image the log files needed
    now the log file has been archived while doing backup and now cannot get this file back (because of some reason ..)
    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
    Aug 2013
    Posts
    6
    We use USEREXIT to archive logs but why do these techniques interfere? It seems that USEREXIT archives the logs just before the moment the backup takes place and needs these logfiles.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 version - platform ?
    what is userexit doing : tsm - copy disk ?
    with any method db2 should be able to retrieve archived logfiles. they might be needed for rollback. there can be logging files for archive and retrieve describing the error
    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
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Have you implemented RETRIEVE logic inside the USEREXIT routine?
    Probably db2 calls the routine with this request when it is not able to find an appropriate log?
    Regards,
    Mark.

  6. #6
    Join Date
    Aug 2013
    Posts
    6
    Hi,

    DB2 9.7 FP6 WSE on Linux 64bit (see 1st post). USEREXIT moves the files to another partition.

    Here's the code from the userexit source code:

    Code:
    #define ARCHIVE_PATH      "/db2_backup/"           /* path must end with a slash      */
    #define RETRIEVE_PATH     "/sync/db2inst1/db2inst1/"           /* path must end with a slash      */
    #define AUDIT_ACTIVE          1           /* enable audit trail logging      */
    #define ERROR_ACTIVE          1           /* enable error trail logging      */
    #define AUDIT_ERROR_PATH  "/db2_backup/"           /* path must end with a slash      */
    #define AUDIT_ERROR_ATTR    "a"           /* append to text file             */
    #define BUFFER_SIZE          32           /* # of 4K pages for output buffer */
    The code wasn't written by me. Maybe ARCHIVE_PATH and RETRIEVE_PATH have to be the same?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by RobVanWinkle View Post
    We use USEREXIT to archive logs but why do these techniques interfere? It seems that USEREXIT archives the logs just before the moment the backup takes place and needs these logfiles.
    You should switch to LOGARCHMETH1 and 2 instead of using a user exit program.
    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
    Apr 2012
    Posts
    156
    Completely agree with Marcus_A, since you are running on DB2 V9.7 you should switch to LOGARCHMETH1 and 2 instead. This will also make restores and crash recoveries much smoother. My understanding is the user exit method is/has been depricated as of DB2 V9.5. Do you have anything set for logarchmeth1?

  9. #9
    Join Date
    Aug 2013
    Posts
    6
    OK, thx for the advice. I'll switch to LOGARCHMETH1:disk and see how it works.

  10. #10
    Join Date
    Aug 2013
    Posts
    6
    LOGARCHMETH disk: seems to do the trick. Thank you.

Tags for this Thread

Posting Permissions

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