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?

Can you post all of the commands you are using to do the restore? Also DB2 version and OS.

Andy

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?

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

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.

Yes, the log files should be inside. Put them where ever you want them, whatever makes sense.

Andy

It seems there is no logs. I've got SQL2581N error with reason 1 (The backup image does not include logs.)

Any other ideas?

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".

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

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: Mrs.

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"

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.....

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!

Are you sure the backup image with timestamp 20110913113341 is an online image?