Unanswered: SQL0290N Table space access not allowed
I did a search at the forum and found some threads regarding this topic, but any of them match the condition I have, then sorry for repeting. Let me tell you the whole story.
We have a database running in a server that will have to be shut down for hardware maintenence. This DB is configured with tasks to do online backups daily and also registers LOGs:
'BACKUP DATABASE PRDTIBCO ONLINE TO "F:\Backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRESS COMPRLIB "C:\Program Files\IBM\SQLLIB\BIN\db2compr.dll" EXCLUDE INCLUDE LOGS WITHOUT PROMPTING'
The result of the task is:
Backup successful. The timestamp for this backup image is : 20110526051638
We can have complete access to all tablespaces and all tables are working, with all REORGS (weekly) and RUNSTATS (daily) updated.
I've prepared a substitute server which will run until we fix the old one. Then, I copied the online backup of the day to the new server and also the log files (including LOGARCHIVE).
So, I began testing. I did a restore from our daily online backup:
db2=> restore db <mydb> from f:\ taken at 20110526051638 replace history file comprlib "C:\Program Files\IBM\SQLLIB\BIN\db2compr.dll"
After completion, I received the folloowing message:
'SQL2563W The restore process has completed successfully, but one or more table spaces from the backup were not restored.'
An attempt to view the tables at the Control Center gives me:
'[IBM][CLI Driver] SQL1117N A connection to or activation of
database "PRDTIBCO" cannot be made because of ROLL-FORWARD
So I proceed to the rollforward:
db2=> rollforward db prdtibco to end of logs
And after that:
db2=> rollforward db prdtibco stop
And I get this from both commands:
'SQL1271W Database "MYDB" is recovered but one or more table spaces are off-line on node(s) "0".'
So I refresh the Control Center screen and finally get access to my database.
Now I can access tables at the Schema SYSIBM and SYSTOOLS. Though, when I try to access the tables on my working schema I get:
'SQL0290N Table space access is not allowed. SQLSTATE=55039'
Then I proceed:
db2=> connect to <MYDB>
db2=> list tablespaces show detail
I get the list of tablespaces where all are "normal" except the one where my working table resides:
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x2001100
Storage must be defined
Storage may be defined
That's where I stop and call for your help.
What should I do next so I regain access to my table at the substitute server?
thanks in advance,
P.S.: We tried HADR but gave up due to the enormous quantity of LOG files generated. It just ran us out of disk space.
The name of the instance installed in the old server was 'DB2TIBCO'. I did not take part in this process, because it was created before I was assigned to this job.
The new server, on the other hand, was configured by me, and I found it curious that the installation process would not let me choose a name for the DB instance. It is always already showed as 'DB2' and I could not change it.
This fact alone is responsible for this difference in paths and the difficulties I encountered doing backup and restore.
So my solution took me more time, but worked well. I just exported the DB for delimited files and imported into the new server. Takes more time, but the results were OK. And now I have the server configured my way, up and running.
I know this is the old thread. I have the same problem as you described. What did you mean when you say "I just exported the DB for delimited files and imported into the new server". Can you please explain a bit in more details?
I also had the same messages when there is not enough storage on the disks to completely restore the database. So, be sure to have a few GB free...
When I had this issue I also had around 2 GB free, but still it was not enough to restore the database in full, with all tablespaces.