By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Tablespace Offline / Corrupt Quandry

P: n/a
OK, I'm stumped now. I need help restoring a tablespace that has been
marked offline. Here's a little background on the problem:

1) There was a hardware failure that caused the tablespace to become marked
offline: State = 0x4000; Detailed explanation: Offline
2) I tried switching it back online and got this error: DB21034E The
command was processed as an SQL statement because it was not a valid Command
Line Processor command. During SQL processing it returned: SQL0293N Error
accessing a table space container. SQLSTATE=57048
3) I double-checked the raw device assinged to the container and it *does*
have appropriate permissions: bash-2.05$ ls -lt /dev/rdsk/c3t5d2s6
lrwxrwxrwx 1 root root 48 Jul 31 2003 /dev/rdsk/c3t5d2s6 ->
.../../devices/pseudo/rdnexus@3/rdriver@5,2:g,raw
4) Ran DB2DART to determine the extent of the damage: ERROR: One or more
object tables or DMS tablespaces are corrupt. Some tablespaces may not be
usable.

So basically, I need to restore the tablespace. Our system is not set up
for regular backups, but rather exists as a mirrored setup. The application
that populates the database populates a primary and a standby, so I have a
live database to extract the lost data from to bring over and re-create.

We've repaired the hardware problem, but the problem I'm having now is I
can't drop the tablespace because it is not empty, but I can't drop the
tables in the tablespace because access is not allowed:
bash-2.05$ db2 drop tablespace arc_event_data
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0282N Table space "ARC_EVENT_DATA" cannot be dropped because at least
one
of the tables in it, "ARCSIGHT.ARC_EVENT_MAX", has one or more of its parts
in
another table space. SQLSTATE=55024
bash-2.05$ db2 drop table ARC_EVENT_MAX
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039

I'm in a pickle here. Any suggestions short of dropping the database and
re-creating?

Amy
One perplexed DBA

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Amy,

I would restart the database with drop pending tablespaces
- db2 "restart db <dbname> drop pending tablespaces(tbs1,tbs2,....)"

This will allow you to drop the tablespaces, once the database has been
restarted, as it flags the tablespaces as drop pending.

Cheers,
"Amy DBA" <te********@gmail.NOSPAM.com> wrote in message
news:tf****************@newssvr22.news.prodigy.com ...
OK, I'm stumped now. I need help restoring a tablespace that has been
marked offline. Here's a little background on the problem:

1) There was a hardware failure that caused the tablespace to become marked offline: State = 0x4000; Detailed explanation: Offline
2) I tried switching it back online and got this error: DB21034E The
command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0293N Error accessing a table space container. SQLSTATE=57048
3) I double-checked the raw device assinged to the container and it *does*
have appropriate permissions: bash-2.05$ ls -lt /dev/rdsk/c3t5d2s6
lrwxrwxrwx 1 root root 48 Jul 31 2003 /dev/rdsk/c3t5d2s6 -> ../../devices/pseudo/rdnexus@3/rdriver@5,2:g,raw
4) Ran DB2DART to determine the extent of the damage: ERROR: One or more object tables or DMS tablespaces are corrupt. Some tablespaces may not be
usable.

So basically, I need to restore the tablespace. Our system is not set up
for regular backups, but rather exists as a mirrored setup. The application that populates the database populates a primary and a standby, so I have a
live database to extract the lost data from to bring over and re-create.

We've repaired the hardware problem, but the problem I'm having now is I
can't drop the tablespace because it is not empty, but I can't drop the
tables in the tablespace because access is not allowed:
bash-2.05$ db2 drop tablespace arc_event_data
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0282N Table space "ARC_EVENT_DATA" cannot be dropped because at least
one
of the tables in it, "ARCSIGHT.ARC_EVENT_MAX", has one or more of its parts in
another table space. SQLSTATE=55024
bash-2.05$ db2 drop table ARC_EVENT_MAX
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039

I'm in a pickle here. Any suggestions short of dropping the database and
re-creating?

Amy
One perplexed DBA


Nov 12 '05 #2

P: n/a
Great suggestion, Fred, but no bueno. The restart command finished
successfully, but the tablespaces came back up with the same label. I'm
mess around with the bad container for a bit. Last resort, I could just
create a new tablespace and put the tables in there.

Amy
"Lack of planning on your part will constitute an emergency on my part.
That's why I'm the DBA."

"Fred Nurk" <fr****@nospam.yahoo.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
Amy,

I would restart the database with drop pending tablespaces
- db2 "restart db <dbname> drop pending tablespaces(tbs1,tbs2,....)"

This will allow you to drop the tablespaces, once the database has been
restarted, as it flags the tablespaces as drop pending.

Nov 12 '05 #3

P: n/a
Amy,

Did you just do it just for the bad tablespace? As it is DMS, the are be
dependancies in other DMS tablesapces. ie data in one tablespace and
indexes/blobs in another, this is shown by the SQL0282N you recieved while
trying to drop the tablespace. You need to alter the problem tablespace and
the dependant tablespace(s) as well.

You can check this simply by quering the syscat.tables view.

select tabschema, tabname, tbspace, index_tbspace, long_tbspace
from syscat.tables
where tbspace = "ARC_EVENT_DATA"

Cheers,
"Amy DBA" <te********@gmail.NOSPAM.com> wrote in message
news:Vw******************@newssvr30.news.prodigy.c om...
Great suggestion, Fred, but no bueno. The restart command finished
successfully, but the tablespaces came back up with the same label. I'm
mess around with the bad container for a bit. Last resort, I could just
create a new tablespace and put the tables in there.

Amy
"Lack of planning on your part will constitute an emergency on my part.
That's why I'm the DBA."

"Fred Nurk" <fr****@nospam.yahoo.com.au> wrote in message
news:41**********************@news.optusnet.com.au ...
Amy,

I would restart the database with drop pending tablespaces
- db2 "restart db <dbname> drop pending tablespaces(tbs1,tbs2,....)"

This will allow you to drop the tablespaces, once the database has been
restarted, as it flags the tablespaces as drop pending.


Nov 12 '05 #4

P: n/a
Yes, I tried issuing the command to drop both the DMS and the index
tablespace, with no luck. Maybe it won't drop the tablespace becaue they
aren't pending, but rather offline?

db2 "restart database arcsight drop offline tablespaces
(ARC_EVENT_INDEX,ARC_EVENT_DATA)"
"Fred Nurk" <fr****@nospam.yahoo.com.au> wrote in message
news:41***********************@news.optusnet.com.a u...
Amy,

Did you just do it just for the bad tablespace? As it is DMS, the are be

--
**Amy**
"Lack of planning on your part will constitute an emergency on my part.
That's why I'm the DBA."
Nov 12 '05 #5

P: n/a
Ian
Amy DBA wrote:
Yes, I tried issuing the command to drop both the DMS and the index
tablespace, with no luck. Maybe it won't drop the tablespace becaue they
aren't pending, but rather offline?

db2 "restart database arcsight drop offline tablespaces
(ARC_EVENT_INDEX,ARC_EVENT_DATA)"
"Fred Nurk" <fr****@nospam.yahoo.com.au> wrote in message
news:41***********************@news.optusnet.com.a u...
Amy,

Did you just do it just for the bad tablespace? As it is DMS, the are be


Amy,

Did you check the permissions on the device? Your earlier post showed
the permissions for the link in /dev/rdsk, but not the actual device
in /devices ?

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #6

P: n/a
Excellent question Ian. What type of permissions do I need? Here's what I
have:

crw-rw---- 1 root root 197, 38 Aug 4 06:00 rdriver@5,2:g,raw
crw-rw---- 1 arcsight db2grp1 197, 54 Aug 2 00:23 rdriver@5,3:g,raw

"Ian" <ia*****@mobileaudio.com> wrote in message
news:41********@corp.newsgroups.com...

Did you check the permissions on the device? Your earlier post showed
the permissions for the link in /dev/rdsk, but not the actual device
in /devices ?

--
**Amy**
"Lack of planning on your part will constitute an emergency on my part.
That's why I'm the DBA."
Nov 12 '05 #7

P: n/a
Ian
Amy DBA wrote:
Excellent question Ian. What type of permissions do I need? Here's what I
have:

crw-rw---- 1 root root 197, 38 Aug 4 06:00 rdriver@5,2:g,raw
This looks like the problem to me. /dev/rdsk/c3t5d2s6 points to this
device. I assume that your instance owner is 'arcsight' with group
'db2grp1', so with these permissions DB2 won't be able to open the
raw device!

You should set the permissions/ownership per the other device you
listed:
crw-rw---- 1 arcsight db2grp1 197, 54 Aug 2 00:23 rdriver@5,3:g,raw


Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #8

P: n/a
Well I've corrected the permissions, but db2 still won't take the tablespace
offline. Looks like I'm going to have to call db2 support. :(

"Ian" <ia*****@mobileaudio.com> wrote in message
news:41********@corp.newsgroups.com...
Amy DBA wrote:
Excellent question Ian. What type of permissions do I need? Here's what I have:

crw-rw---- 1 root root 197, 38 Aug 4 06:00 rdriver@5,2:g,raw


This looks like the problem to me. /dev/rdsk/c3t5d2s6 points to this
device. I assume that your instance owner is 'arcsight' with group
'db2grp1', so with these permissions DB2 won't be able to open the
raw device!

You should set the permissions/ownership per the other device you
listed:
crw-rw---- 1 arcsight db2grp1 197, 54 Aug 2 00:23 rdriver@5,3:g,raw


Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.