Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    23

    Unanswered: db2 rollforward to specific time for a Restore?

    I'm using DB2 9.7 LUW with AIX 6.1. I would like to restore a database to a specific time. So I"m using a backup from last night at around 1 AM and want to rollforward to 1:30 PM....it doesn't matter the RF time.

    But I'm getting these errors and the Logs look fine to me.


    dbtest /db2_backup/p1proddb/db: db2 rollforward db copd900 to 2013-03-12-13.37.00.000000 using local time
    SQL4970N Roll-forward recovery on database "COPD900" cannot reach the
    specified stop point (end-of-log or point-in-time) on database partition(s)
    "0". Roll-forward recovery processing has halted on log file "S0006638.LOG".

    Has anyone RF to specific time successfully many times and/or had problems with this? Please let me know what you did, I"m looking for some help here.

    Thanks

    CC

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    issue
    Code:
    rollforward db copd900 query status
    you should see the information on the next log file to process.. Is that log file available ?
    If it is, then check db2diag.log for messages. You may want to change diaglevel to 4 (update dbm cfg using diaglevel 4).

    ---
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Do you archive logs to disk or TSM? Can you see log S0006638.LOG in the archived location?

  4. #4
    Join Date
    Apr 2009
    Posts
    23
    We didn't archive the Logs off. The logs are sitting out on /db2_data/copd900/logs/NODE0000/. Here are the two logs currently on our Test environment:
    -rw------- 1 db2inst1 db2grp1 3801088 Mar 13 12:06 S0006637.LOG
    -rw------- 1 db2inst1 db2grp1 4202496 Mar 13 12:08 S0006638.LOG
    -rw------- 1 db2inst1 db2grp1 512 Jan 9 13:48 SQLLPATH.TAG

    Here is what the db2diag.log error says:
    2013-03-13-12.08.02.427758-300 I30208A518 LEVEL: Error
    PID : 7864328 TID : 29391 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : COPD900
    APPHDL : 0-44510 APPID: *LOCAL.db2inst1.130313170801
    AUTHID : DB2INST1
    EDUID : 29391 EDUNAME: db2agent (COPD900) 0
    FUNCTION: DB2 UDB, recovery manager, sqlpForwardRecovery, probe:920
    DATA #1 : <preformatted>
    Rollforward is not able to reach the specified point in time. nextLso 27722147713


    Here is RF query status:

    db2 rollforward db copd900 query status

    Rollforward Status

    Input database alias = copd900
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = DB working
    Next log file to be read = S0006638.LOG
    Log files processed = S0006637.LOG - S0006637.LOG
    Last committed transaction = 2013-03-12-06.12.52.000000 UTC


    I'm not sure by the db2diag.log what is really wrong here? Can you tell or is there anything else that I can look at?

    Thank you so much!

    CC

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Could that be because the log file S0006638.LOG has not been archived yet? Just thinking out loud.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Please provide:
    - all db2diag.log messages related to rollforward (there should be another message with the RC)
    - db cfg for this db
    - db2 rollforward db copd900 query status using local time

  7. #7
    Join Date
    Apr 2009
    Posts
    23
    I tried rolling forward yesterday again from the night before's COPD900 backup and got the same thing. Here is the RF query status:
    db2 rollforward db copd900 query status using local time

    Rollforward Status

    Input database alias = copd900
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = DB working
    Next log file to be read = S0006642.LOG
    Log files processed = S0006641.LOG - S0006641.LOG
    Last committed transaction = 2013-03-13-01.12.54.000000 Local


    Here is what is in /db2_data/copd900/logs/NODE0000/ right now after the backup restore from yesterday:

    -rw------- 1 db2inst1 db2grp1 4141056 Mar 13 15:08 S0006641.LOG
    -rw------- 1 db2inst1 db2grp1 4202496 Mar 13 15:27 S0006642.LOG
    -rw------- 1 db2inst1 db2grp1 512 Jan 9 13:48 SQLLPATH.TAG


    Then attached is the other documents you are asking for. I really appreciate this, I've only done this once successfully and need to figure this out. So our team knows that RF to a specific time really works!

    Thank you for your help!

    CC
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Why are you restoring the logs stored in the backup image? The logs stored in the backup image can be used to rollforward to the end of backup image, not past it.

    Are you restoring into a database different from the backup image (ie. copied the backup image from another server)?


    db2diag.log shows:

    2013-03-13-15.06.56.707737-300 E44689A489 LEVEL: Info
    PID : 7864328 TID : 27324 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : COPD900
    APPHDL : 0-44591 APPID: *LOCAL.db2inst1.130313200657
    AUTHID : DB2INST1
    EDUID : 27324 EDUNAME: db2agent (COPD900) 0
    FUNCTION: DB2 UDB, database utilities, sqludPrintStartingMsg, probe:1372
    DATA #1 : <preformatted>
    Starting a logs only restore.

    ....
    2013-03-13-15.08.51.896391-300 E46207A480 LEVEL: Warning
    PID : 7864328 TID : 27324 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : COPD900
    APPHDL : 0-44591 APPID: *LOCAL.db2inst1.130313200657
    AUTHID : DB2INST1
    EDUID : 27324 EDUNAME: db2agent (COPD900) 0
    FUNCTION: DB2 UDB, database utilities, sqludBMResponse, probe:684
    DATA #1 : <preformatted>
    Log file extent range restored: 6641 - 6641.


    So, log 6641 was in the backup image and you restored it into some dir (I suppose /db2_data/copd900/logs/NODE0000/). There was no log 6642.


    Next:

    2013-03-13-15.27.09.849149-300 I53119A371 LEVEL: Warning
    PID : 7864328 TID : 16514 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 16514 EDUNAME: db2lfr (COPD900) 0
    FUNCTION: DB2 UDB, recovery manager, sqlplfrFMOpenLog, probe:5120
    MESSAGE : Return code for LFR opening file S0006642.LOG was -2045837302


    -2045837302 means "File not found"


    and then:

    2013-03-13-15.27.10.298409-300 I53915A403 LEVEL: Info
    PID : 7864328 TID : 15488 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : COPD900
    EDUID : 15488 EDUNAME: db2loggr (COPD900) 0
    FUNCTION: DB2 UDB, recovery manager, sqlpgSwitchFromRedoToUndo, probe:1800
    DATA #1 : <preformatted>
    Create Last Log, FileCount is 1, TailIndex is 1


    So, it looks like db2 created log 6642 (the timestamp in the diag.log and the log matches)


    At the end, rollforward returned RC -2146434885, which means "rollforward missing log files"


    If you need to rollforward past the end of backup image, you need to manually copy the logs from the source db.

Posting Permissions

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