Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Exclamation Unanswered: DB: not able to restore from backup

    I am using command

    db2 restore db S18 from /users/intadm/s18backup/ taken at 20110913113341 on /users/db2inst1/ dbpath on /users/db2inst1/ redirect without rolling forward
    to restore database from backup file located in /users/intadm/s18backup/ .

    Command execution gives such output:

    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.


    When I'm trying to connect to restored DB (by executing 'db2 connect to S18'), I'm getting this message:

    SQL0752N Connecting to a database is not permitted within a logical unit of
    work when the CONNECT type 1 setting is in use. SQLSTATE=0A001


    When I'm trying to connect to db with db viewer like SQuireL, the error is like:

    DB2 SQL Error: SQLCODE=-1119, SQLSTATE=57019, SQLERRMC=S18, DRIVER=3.57.82
    which means that 'error occurred during a restore function or a restore is still in progress' (from IBM DB2 manuals)


    I've executed db2ckbkp on backup file and it did not identified any issues with backup file itself.

    How can I resolve this and connect to restored database?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post all of the commands you are using to do the restore? Also DB2 version and OS.

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    ARWinner, thank you for response!
    OS: Solaris 10
    DB2: 9.7.4

    I'm trying a little bit different scenario now:

    db2 restore db S18 from /users/intadm/s18backup/ taken at 20110913113341 on /users/db2inst1/ dbpath on /users/db2inst1/ -> seems successful, but with warning that some table spaces are not moved

    ->

    Trying to connect to database - > db2 says I'm not able to connect, because ROLL-FORWARD PENDING (SQL1117N)

    ->

    OK, executing
    db2 ROLLFORWARD DATABASE S18 TO 2011-09-13-13.33.41.000000 USING LOCAL TIME AND COMPLETE OVERFLOW LOG PATH "("/users/db2inst1/")"
    ->

    gives this error:

    SQL1266N Database "S18" has been rolled forward to
    "2011-09-13-13.33.42.000000 Local", which is past the specified point-in-time.
    bash-3.00$


    if I set timestamp to 2011-09-13-13.33.42.000000 or more the error is
    SQL4970N Roll-forward recovery on database "S18" 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 "S0000004.LOG".
    bash-3.00$


    After those operations I'm still not able to connect to S18 (error is the same (SQL1117N))

    I'm not DBA, but just developer which needs to setup dev DB environment. I wasted few days while installed and configured few DB2 instances on Solaris (I'm doing this for a first time) .

    Any suggestions how to get this backup work?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am going to assume that a DBA created the backup image and that the logs are included. You need to add the LOGTARGET clause to the restore command. Also use the NEWLOGPATH clause and set it to the same value as LOGTARGET. Since it is not doing all the tablespaces, you also probably need to do a redirected restore. Once the restore completes, then you should be able to do the ROLLFORWARD.

    Andy

  5. #5
    Join Date
    Nov 2011
    Posts
    5
    I have one large backup image. Should log files be somewhere inside? How to determine LOGTARGET path?

    Thanks for help!

    UPD: But what happens if I add 'redirect without rollining forward' to the end of restore command (as in my first post)? In this case there is no tablespace related warning, but still not able to connect.
    Last edited by oginskis; 11-01-11 at 16:34.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, the log files should be inside. Put them where ever you want them, whatever makes sense.

    Andy

  7. #7
    Join Date
    Nov 2011
    Posts
    5
    It seems there is no logs. I've got SQL2581N error with reason 1 (The backup image does not include logs.)

    Any other ideas?

  8. #8
    Join Date
    Sep 2011
    Posts
    26
    Backup image will include logs only if you took backup with logs option.


    Once restored just give "rollforward db <dbname> to end of logs and complete".

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you have an online backup (which it appears you have), you have to have the logs to get a viable database. Without them, you are out of luck.

    Andy

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Confirm if the image is online or offline (I suspect it's offline).
    If online, look for the logs in order to rollforward to a minimum point in time (end of backup)
    If offline, specify without rolling forward (or you can just issue rollforward stop after the restore)

    BUT you need to fix the restore first. It looks like you need to redefine tablespace containers using ON and/or REDIRECT + SET TABLESPACE CONTAINERS + CONTINUE. We just went through this last week, see: http://www.dbforums.com/db2/1670975-mrs.html

  11. #11
    Join Date
    Nov 2011
    Posts
    8
    you should use db2ckbkp to check the "Backup Mode" and "Include Logs".for example(from db2 information center):
    db2ckbkp -h SAMPLE2.0.krodger.NODE0000.CATN0000.19990818122909 .001

    =====================
    MEDIA HEADER REACHED:
    =====================
    Server Database Name -- SAMPLE2
    Server Database Alias -- SAMPLE2
    Client Database Alias -- SAMPLE2
    Timestamp -- 19990818122909
    Database Partition Number -- 0
    Instance -- krodger
    Sequence Number -- 1
    Release ID -- 900
    Database Seed -- 65E0B395
    DB Comment's Codepage (Volume) -- 0
    DB Comment (Volume) --
    DB Comment's Codepage (System) -- 0
    DB Comment (System) --
    Authentication Value -- 255
    Backup Mode -- 0
    Include Logs -- 0
    Compression -- 0
    Backup Type -- 0
    Backup Gran. -- 0
    Status Flags -- 11
    System Cats inc -- 1
    Catalog Database Partition No. -- 0
    DB Codeset -- ISO8859-1
    DB Territory --
    LogID -- 1074717952
    LogPath -- /home/krodger/krodger/NODE0000/
    SQL00001/SQLOGDIR
    Backup Buffer Size -- 4194304
    Number of Sessions -- 1
    Platform -- 0

    Backup Mode:0 offline ;1 online
    Include Logs: 0 not include logs; 1 include logs

    if the backup operation was offline,you can use "WITHOUT ROLLING FORWARD" when issue restore command;else,you should rollforward database to "end of backup" or "end of logs"

  12. #12
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    hi

    try to give db2 commit and db2 rollback....

    this will bring the db to consistant state.. and you should be able to login..

    it worked for me many times.....

  13. #13
    Join Date
    Nov 2011
    Posts
    5

    Thumbs up Solution

    Thank you all for responses!
    So, what I did and what has worked:

    1. Executed:
    db2 restore db S18 from /users/intadm/s18backup/ taken at 20110913113341 on /<path with sufficient disk space> dbpath on /<path with sufficient disk space>
    Warning that some table spaces are not moved disappeared now.

    2. As it was online backup, according to jumshoos's suggestion, issued:
    rollforward db S18 to end of logs and complete

    Thats it, I am able to connect now!

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by oginskis View Post
    As it was online backup
    Are you sure the backup image with timestamp 20110913113341 is an online image?

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
  •