Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2013
    Posts
    31

    Unanswered: Unable to restore online backup to another server

    Hi everyone,

    I'm a seasoned DBA working with MS SQL for almost 15 years, but in the last month I've become the maintainer of a DB2 LUW 9.5 system which is my first exposure to DB2.

    Anyway I'm trying to restore an online backup created on one server to another, both DB2 LUW 9.5.3a, and I'm not able to do it.

    The backup script on Server1 where the database resides is this:
    backup database TestDB1
    online to g:\db2backup\TestDB1
    compress
    INCLUDE LOGS WITHOUT PROMPTING ;
    And within this folder after the backup ran last night the backup image is this:
    TestDB1.0.DB2TEST.NODE0000.CATN0000.20130409060036 .001

    I then copied this image file from Server1 to C:\DB2_BU\ on Server2 then copied the logs for TestDB1 from Server1 to C:\DB2_Logs\ on Server2 and ran the following command:

    restore db TestDB1
    from "C:\DB2_BU\"
    taken at 20130409060036
    TO "E:\TestDB1_Data"
    into TestDB1
    logtarget "E:\TestDB1_BULogs"
    NEWLOGPATH "E:\TestDB1_Log"
    REDIRECT;
    And here are the results:

    restore db TestDB1 from "C:\DB2_BU\" taken at 20130409060036 TO "E:\TestDB1_Data" into TestDB1logtarget "E:\TestDB1_BULogs" NEWLOGPATH "E:\TestDB1_Log" REDIRECT
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    A JDBC connection to the target has failed. Features such as Query Results, Access Plan and SQL Assist will be unavailable.
    At this point I can't connect to the database. I assumed the logs saved within the image would be in E:\TestDB1_BULogs but this directory is empty. I also tried running these, but I get the error below:
    rollforward db TestDB1to end of logs
    rollforward db TestDB1to complete

    SQL1119N A connection to or activation of database "TestDB1" cannot be made
    because a previous restore is incomplete or still in progress. SQLSTATE=57019

    SQL1119N A connection to or activation of database "TestDB1 " cannot be made because a previous restore is incomplete or still in progress.

    Explanation:

    The database is in an inconsistent state because a system error occurred
    during a restore function or a restore is still in progress.

    No connection was made.
    And db2diag has this showing-up every few minutes:

    2013-04-09-14.35.33.234000-300 I1249507H452 LEVEL: Warning
    PID : 2804 TID : 4028 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TestDB1
    APPHDL : 0-3399 APPID: *LOCAL.DB2.130409193533
    AUTHID : DB2ADMIN
    EDUID : 4028 EDUNAME: db2agent (TestDB1) 0
    FUNCTION: DB2 UDB, base sys utilities, sqleCheckForRestore, probe:10
    RETCODE : ZRC=0xFFFFFBA1=-1119

    2013-04-09-14.35.33.234000-300 I1249961H460 LEVEL: Warning
    PID : 2804 TID : 4028 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TestDB1
    APPHDL : 0-3399 APPID: *LOCAL.DB2.130409193533
    AUTHID : DB2ADMIN
    EDUID : 4028 EDUNAME: db2agent (TestDB1) 0
    FUNCTION: DB2 UDB, base sys utilities, sqlePrepareForSerialization, probe:20
    RETCODE : ZRC=0xFFFFFBA1=-1119

    2013-04-09-14.35.33.234000-300 I1250423H465 LEVEL: Severe
    PID : 2804 TID : 4028 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TestDB1
    APPHDL : 0-3399 APPID: *LOCAL.DB2.130409193533
    AUTHID : DB2ADMIN
    EDUID : 4028 EDUNAME: db2agent (TestDB1) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeDBMgr:tartUsingLocalDatabase, probe:
    10
    RETCODE : ZRC=0xFFFFFBA1=-1119
    Is the restore still running in the background? The database is large, but I'm not sure if it's still working or if I need to do something to import the other logs... or if it failed. Also the database is showing-up in Control Center, but I'm getting the error above there when I attempt to connect there as well. IBM's site just says this means either the DB is restoring or an error occured, but DB2Diag isn't showing much.

    I also read that this can be ran to show the status of the restore if it's still going:

    db2 list utilities show detail
    But it's just returning this:

    SQL1611W No data was returned by Database System Monitor. SQLSTATE=00000
    Thanks for any advice.
    Last edited by samalex01; 04-09-13 at 16:45.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are doing a REDIRECTed restore. You did not complete the entire restore process. Once you start a redirected restore, you have to state where you want the containers for the tablespaces.

    Have the RESTORE generate a REDIRECT script. Edit that and then run it.


    You will also have to ROLLFORWARD the logs when the overall restore completes.


    Andy

  3. #3
    Join Date
    Feb 2013
    Posts
    31
    Quote Originally Posted by ARWinner View Post
    You are doing a REDIRECTed restore. You did not complete the entire restore process. Once you start a redirected restore, you have to state where you want the containers for the tablespaces.

    Have the RESTORE generate a REDIRECT script. Edit that and then run it.


    You will also have to ROLLFORWARD the logs when the overall restore completes.


    Andy
    Andy, thanks for the reply, I figured I was missing something. I ran this with the following results:

    ------------------------------ Commands Entered ------------------------------
    restore db TestDB1
    from "C:\DB2_BU\"
    taken at 20130409060036
    TO "E:\TestDB1_Data"
    into TestDB1
    logtarget "E:\TestDB1_BULogs"
    NEWLOGPATH "E:\TestDB1_Log"
    REDIRECT
    generate script RestoreScript.txt;
    ------------------------------------------------------------------------------
    restore db TestDB1 from "C:\DB2_BU\" taken at 20130409060036 TO "E:\TestDB1_Data" into TestDB1 logtarget "E:\TestDB1_BULogs" NEWLOGPATH "E:\TestDB1_Log" REDIRECT generate script RestoreScript.txt
    DB20000I The RESTORE DATABASE command completed successfully.
    but silly question, where did it put RestoreScript.txt? I've searched both C: and E:, but it's not there. I also tried giving it a path instead of just a file name, but it didn't do anything there either.

    What am I missing? Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First, (unrelated) make the logtarget and newlogpath the same. This is where the transactions logs will be placed and then used during the ROLLFORWARD process.

    Second, explicitly define the script path, and make sure you can write to that path.

    Andy

  5. #5
    Join Date
    Feb 2013
    Posts
    31
    Quote Originally Posted by ARWinner View Post
    First, (unrelated) make the logtarget and newlogpath the same. This is where the transactions logs will be placed and then used during the ROLLFORWARD process.

    Second, explicitly define the script path, and make sure you can write to that path.

    Andy
    That did it, and wow this thing is huge! We have about 400 table spaces, so with comments it's rather lengthy. I can do find/replace though to alter the paths since the drives are setup differently on the second server.

    Thanks for your help, and I'll report back probably tomorrow on any issues or successes given the database may take a few hours to restore.

  6. #6
    Join Date
    Feb 2013
    Posts
    31
    Also I did make logtarget and newlogpath the same, but should I copy the log files I copied from the original server here before I run the script? Or given I Backed it up with 'Include Logs' would this be sufficient?

    Thanks again.

  7. #7
    Join Date
    Feb 2013
    Posts
    31
    Actually it didn't take as long as I expected, and after it was completed I copied the Log files and Archived Log files from the original server to E:\TestDB1_Log on the new server then ran this to finish everything:

    rollforward db TestDB1to end of backup and complete;

    And now my TestDB1 database is setup on the new box The Federated objects aren't working, but I'll tackle that tomorrow...

    Thanks for your help.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you used "INCLUDE LOGS" in the backup and then used LOGTARGET and NEWLOGPATH on the restore, and then rollforward to END OF BACKUP, you do not need to copy logs from the source server. What you need is in the backup image already (INCLUDE LOGS) and you placed them in the correct location (LOGTARGET, NEWLOGPATH). You can just issue the ROLLFORWARD TO END OF BACKUP and it should work.

    Andy

  9. #9
    Join Date
    Feb 2013
    Posts
    31
    Quote Originally Posted by ARWinner View Post
    If you used "INCLUDE LOGS" in the backup and then used LOGTARGET and NEWLOGPATH on the restore, and then rollforward to END OF BACKUP, you do not need to copy logs from the source server. What you need is in the backup image already (INCLUDE LOGS) and you placed them in the correct location (LOGTARGET, NEWLOGPATH). You can just issue the ROLLFORWARD TO END OF BACKUP and it should work.

    Andy
    When I issued this command - rollforward db TestDB1to end of backup and complete - it said there was a log file missing, and going back to Server1 I found it in the Archived Log folder. I copied it along with all the other logs from Server1 for this DB and the rollforward command ran fine and my database was functional. I assumed this was as it should be, but is it not? Or was my adding 'and complete' to the end what prompted it to dig deeper and require the archived log?

    Thanks.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I have not had that occur before. The backup image (with logs included)is all I have ever needed to produce a working database.

    Andy

Posting Permissions

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