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?
ARWinner, thank you for response!
OS: Solaris 10
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.
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".
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) .
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.
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.
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
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