Tablespace Offline / Corrupt Quandry

[Amy DBA]

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,r aw
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_E VENT_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

[Fred Nurk]

Amy,

I would restart the database with drop pending tablespaces
- db2 "restart db <dbname> drop pending tablespaces(tbs 1,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********@gma il.NOSPAM.com> wrote in message
news:tf******** ********@newssv r22.news.prodig y.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,r aw
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_E VENT_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

[Amy DBA]

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.c om.au...

Amy,

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

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

[Fred Nurk]

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********@gma il.NOSPAM.com> wrote in message
news:Vw******** **********@news svr30.news.prod igy.com...

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.c om.au...

Amy,

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

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

[Amy DBA]

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_INDE X,ARC_EVENT_DAT A)"
"Fred Nurk" <fr****@nospam. yahoo.com.au> wrote in message
news:41******** *************** @news.optusnet. com.au...

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

[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_INDE X,ARC_EVENT_DAT A)"
"Fred Nurk" <fr****@nospam. yahoo.com.au> wrote in message
news:41******** *************** @news.optusnet. com.au...

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! =-----

[Amy DBA]

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,r aw
crw-rw---- 1 arcsight db2grp1 197, 54 Aug 2 00:23 rdriver@5,3:g,r aw

"Ian" <ia*****@mobile audio.com> wrote in message
news:41******** @corp.newsgroup s.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."

[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,r aw
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,r aw

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! =-----

[Amy DBA]

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*****@mobile audio.com> wrote in message
news:41******** @corp.newsgroup s.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,r aw

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,r aw

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! =-----