Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    88

    Unanswered: db2 online restore

    Hi,
    I am working on db2 V9.1 on AIX server.
    setup:
    1. Userexit= ON
    2. logsecond= -1
    3. db2 backup database xxx online to <path>
    Problem:
    1. how do i determine the isotime at the time of performing rollforward operation
    2. Get SQL4970N error, how do i resolve this? I have copied all the active logs before restoration into archive log path, still facing the same problem.

    Please help me with the rollforward command...Don't want to include logs in the backup image itself.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2cap View Post
    1. how do i determine the isotime at the time of performing rollforward operation
    Looking at the watch usually works for me

    Quote Originally Posted by db2cap View Post
    2. Get SQL4970N error, how do i resolve this? I have copied all the active logs before restoration into archive log path, still facing the same problem.
    May be you need to copy some of the archived logs as well. Make sure you get time right: ROLLFORWARD assumes UTC time, unless the LOCAL TIME option is used.

    Quote Originally Posted by db2cap View Post
    Don't want to include logs in the backup image itself.
    Why?

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Try adding noretrieve to the rollforward command. db2 list history backup all will give you some additional info

  4. #4
    Join Date
    May 2010
    Posts
    88
    Hi,
    Getting the same SQL4970N error which says processing has halted at logfile <logfilename>. I have checked in the archive log path for the existence of that logfile which say it is there.

    I am experimenting this on a test machine now. Request you to take the trouble to check step-by-step and help me know what wrong am i doing.

    1. db2sampl
    2. DB cfg modifications:
    • db2 update db cfg for sample using userexit on
    • db2 update db cfg for salmple using logsecond -1
    • db2 update db cfg for sample using logfilsiz 200(parameter changed to fill the log files quickly during testing)

    3. db2 backup database sample to <backup_path> (full backup)
    4. connect to sample
    5. db2 "insert into staff(select * from staff)" (perform this operation 10 times to archive the log file automatically)
    6. db2 backup database sampe online to <backup_path>
    7. db2 drop table staff

    client asked to restore database from last backup avialable (here, its the online backup taken at step number6)
    8. copy all active logs into the archive log path
    9. db2 restore database sample taken at <online_backup_image_time>
    10. db2 rollforward database sample to <current time> using local time and stop
    SQL4970N:Roll-forward recovery on database "SAMPLE" cannot reach the
    specified stop point .....

    Its really on an urgent basis i need to start implementing userexit.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Why do you need step #8?

    I suppose you want to restore to a PIT prior to step #7 (drop table)?


    What you can do is:

    - execute "db2 list history backup all for db sample"
    - look for the timestamp you're going to restore in step #9 and check "Earliest Log" and "Current Log". This is the log range you need in order to rollforward to the end of online backup.
    - Create a new dir and copy this log range to this dir
    - execute rollforward using:
    db2 "rollforward db sample to end of logs and stop overflow log path <dir where you copied the logs in the previous step> noretrieve"


    But the rollforward should also work if you don't copy/move the logs. DB2 will invoke the userexit (don't specify noretrieve in this case) to retrieve the required logs. The only requirement is that you rollforward to at least the minimum PIT which is the end of online backup.

  6. #6
    Join Date
    May 2010
    Posts
    88
    thanks for the reply bela...
    I will just try it the way you said.

  7. #7
    Join Date
    May 2010
    Posts
    88
    Hi Bella,
    Thanks a ton...its working perfect.

    can you just help me point what was going wrong in my case. As you said, i dont need to copy the active logs, but even if i do, it shouln't be a problem. Doesn't db2 find on its own which logs it has to use to perform a rollforward operation from all the logs which were made available to it, i mean, why create a separate directory and put only the required logs in it?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    DB2 should find the required logs in the active/archive location (or some other location if "overflow log path" is specified). Try repeating your steps but don't move/copy any logs manually.

  9. #9
    Join Date
    May 2010
    Posts
    88
    thanks for your quick reply...
    I tried it that way...not working.
    What i understand is when userexit is enabled it automatically retrives required logs during rollforward from the archived path specified but its not working that way in my case.
    Anyways, yours was the perfect solution provided. Thanks again.
    Stay happy always!!!

Posting Permissions

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