Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How to determine point-in-time.

    Hi guys,

    Here is a scenario.
    Online backup finishes 3 hours later in production. Logs are included.
    Restore restores database and logs are extracted to 'h:\daily_backup\logs'.

    I count and check that logs are 40 in count and 21 GB in size.

    I decide not to go for full rollforward, but minimum to bring database out of rollforward-pending state. Data accuracy is not crucial as its a mirror database. So I decide to just start rollforward but to nearest point-in-time and complete.

    I want to issue command like

    ROLLFORWARD DB POS TO 20090614024601 AND COMPLETE OVERFLOW LOG PATH ('h:\daily_backup\logs')

    But I donot know how to find nearest point-in-time so I always fail to provide correct one.
    Hence the errror
    SQL1276N Database "POS" cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to "2009-06-14-07.46.01.000000 UTC", because node "0" contains information later than the specified time.
    Can someone help me find correct point-in-time token (first one after backup starts) so that it takes minimum time for roll-forward.


    Thanks
    DBFinder

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Use a timestamp 10 years ago. DB2 will give you an error message which tells you the minimum rollforward time you can roll-forward to. I would also use the NORETRIEVE option on the rollforward.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2008
    Posts
    39
    Hi,


    SQL1276N Database "POS" cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to "2009-06-14-07.46.01.000000 UTC", because node "0" contains information later than the specified time.

    This message says that database should be rolled foward to "2009-06-14-07.46.01.000000 UTC" (which is min PIT to which you should rollfrward the database)

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Please keep in mind that the time you indicate in the rollforward command is in UTC, unless you also specify "using local time".
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks all,

    I got some idea.
    But I think we donot have any way to determine first, second , third PITs by examining logfiles OR any SYS table that can provide these PITs.

    The incident Sunday morning can tell upon my employment unless I have really vast knowledge of how and why rollforward can take that long.

    Even though I know that I did not even touch anything, It was restoring and rollingforward as a scheduled job, no one will be ready to absorb that a database was rolling forward for over 26 hours and still did not complete.

    And all I can tell them cannot be only that I read from books or certification.


    Thanks anyway, I will be studying more for this kind of situations.

    DBFinder

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DBFinder
    But I think we donot have any way to determine first, second , third PITs by examining logfiles OR any SYS table that can provide these PITs.
    Database history (LIST HISTORY) and the backup image itself (db2ckbkp) can give you the completion timestamp of the backup, after which any timestamp will be valid for rolling forward.

    You can monitor the rollforward progress with the LIST UTILITIES command or with "db2pd -utilities".
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Nick,

    Your prompt response encouraged me to study furthur how can db2pd will help me.
    I had been using list utilities but never used db2pd.
    Thanks again

    DBFinder

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Here is what I do when I want to rollforward to a minimum PIT (end of online backup):


    - if logs are included in the backup image, then extract the logs to some dir and rollforward using the overflow log path and noretrieve option.


    - if logs are not included, then two options:

    1) issue rollforward stop and without specifying end of logs or PIT. For example: "db2 rollforward db <db name> stop". This command will return sql1276n and give you the PIT in UTC which is the minimum PIT

    or

    2) issue "db2 list history backup all for db <db name>" and look for the timestamp of your backup image. Once you found it, check the "Earliest Log" and "Current Log". This is the logs range you need to provide in order to rollforward to a minimum PIT. Copy these logs to some dir and issue rollforward using the overflow log path and noretrieve option.

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi Bella,

    Alright, in our setup we have been doing exactly all this for years except minimum PIT.

    I used this for min PIT

    rollforward DB POS complete logs overflow path ('h:\daily_backup\logs')

    This generated same error as I listed in beg.

    Then I used with to <timestamp> but I got same error.

    Still wondering why, I should open a PMR.

    Thanks
    DBFinder

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    From your post:

    ROLLFORWARD DB POS TO 20090614024601 AND COMPLETE OVERFLOW LOG PATH ('h:\daily_backup\logs')

    Quote:
    SQL1276N Database "POS" cannot be brought out of rollforward pending state until roll-forward has passed a point in time greater than or equal to "2009-06-14-07.46.01.000000 UTC", because node "0" contains information later than the specified time.


    The minimum PIT is 2009-06-14-07.46.01.000000 UTC

    You specified 20090614024601. This is not a valid timestamp format, but db2 didn't complain for some reason... The valid format is 2009-06-14-07.... Anyway, the reason it didn't work is because you specified a local time on the rollforward command but forgot to use "using local time". The command you needed to use is:

    db2 "rollforward db pos to 2009-06-14-07.46.01.000000 and complete overflow..." - this is in UTC (by default)

    or

    execute db2 "values current timezone" to get the difference between UTC and local tme. For example:

    db2 "values current timezone"

    1
    --------
    -40000.


    In my example, I need to substrat 4 hrs

    db2 "rollforward db pos to 2009-06-14-03.46.01.000000 using local time and complete overflow..." - this is in local time

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    C:\Program Files\IBM\SQLLIB\BIN>db2 values timestamp('20090614024601')

    1
    --------------------------
    2009-06-14-02.46.01.000000

    1 record(s) selected.
    Actually I did mean UTC. DB2 takes this format too yyyymmdd.

    DBFinder

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    To rollforward to a minimum PIT in UTC - you need to specify 2009-06-14-07.46.01.000000 or 20090614074601 (if it takes this format)

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    That's fine,

    but now I got surprised

    C:\SGILL>db2 ROLLFORWARD DATABASE TRIUMPH to 2009-06-16-01.31.01.000000 using local time OVERFLOW LOG PATH ('h:\daily_backup\logs')
    SQL1266N Database "TRIUMPH" has been rolled forward to "2009-07-11-17.15.15.000000", which is past the specified point-in-time.
    This happend just now while I was trying the command as you suggested.

    DBFinder

    ps: this is different DB
    Last edited by DBFinder; 06-16-09 at 14:12.

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    2009-07-11-17.15.15.000000 is in future. Was your OS time set to the future sometime in the past? If it was, the log records will store the future time.

Posting Permissions

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