Someone deleted a bunch of tables from a read only tablespace

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Someone deleted a bunch of tables from a read only tablespace

Someone deleted a bunch of tables from a read only tablespace

2006-04-26 - By Kline.Michael

There were two questions.

Can we leave the event in place?

If we set the tablespaces to read write, will we be able to change them
back to read only?

Database is 8.1.6 on Solaris 5.8(Pretty sure).

Michael Kline
Database Administration
Outside 804.261.9446
Cell 804.744.1545
3-9446

__ ____ ____ ____ ____ ____ ____ ____ ____ __
From: Kline.Michael
Sent: Wednesday, April 26, 2006 8:42 AM

Database came up about 2:21 on Tuesday morning. Maybe a shadow copy was
taken the night before.

Sometime that morning tables were dropped from various read only
tablespaces. It's not necessarily known just how far these drops got.
Then we took the SMON hit at 10:37.
Tue Apr 25 10:37:57 2006
Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_smon_465.trc:
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [kcbgcur_9],
[3976202121], [4], [4294966224], [1040], [], [], []
Tue Apr 25 10:38:00 2006
SMON: terminating instance due to error 600
Tue Apr 25 10:38:00 2006
Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_p002_475.trc:
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [15784],
[600], [], [], [], [], [], []
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [], [], [],
[], [], [], [], []
Instance terminated by SMON, pid = 465

We tried to bring the database up, but it would immediately die. Then we
knew we had a serious problem:
Tue Apr 25 11:53:00 2006
alter database open
(...)
Tue Apr 25 11:53:43 2006
Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_smon_29523.trc:
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [kcbgcur_9],
[3976202121], [4], [4294966224], [1040], [], [], []
Tue Apr 25 11:53:47 2006
SMON: terminating instance due to error 600
Tue Apr 25 11:53:47 2006
Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_p003_29535.trc:
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [15784],
[600], [], [], [], [], [], []
ORA-00600 (See ORA-00600.ora-code.com): internal error code, arguments: [], [], [],
[], [], [], [], []
Instance terminated by SMON, pid = 29523

So, after seeing several things in metalink, we determined we could set
an event, get the database up, and then set the tablespaces to "read
write" so smon could clean up. We tried that, but then we took the
database down, removed the event, and tried to bring it up. It crashed.

EVENT="10061 trace name context forever"

It is highly suspected that the list of tables dropped is missing a
table pointing to another, yet unknown, "read only" tablespace. This
could be something as simple as an index that was not in
"tablespace_idx" but perhaps pointing some place else, maybe even a
keying error.

(It was also thought that maybe the clean up was taking longer than
expected, but according to the docs, setting the tablespace to read
write was supposed to be all that was needed.)

HOPEFUL SOLUTION AFTER BACKUP:

It is hoped that putting ALL tablespaces to read/write will allow the
areas to be cleaned up, and that once that is done we may be able to put
the tablespaces back to read only.

Then we will need to make sure we get yet another backup as it's too
many changes not too.

Sure open to other suggestions.

Michael Kline
Database Administration
Outside 804.261.9446
Cell 804.744.1545
3-9446

LEGAL DISCLAIMER
The information transmitted is intended solely for the individual or entity to
which it is addressed and may contain confidential and/or privileged material.
Any review, retransmission, dissemination or other use of or taking action in
reliance upon this information by persons or entities other than the intended
recipient is prohibited. If you have received this email in error please
contact the sender and delete the material from any computer.

Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
[ST:XCL]

<HTML xmlns:eXclaimer="http://www.exclaimer.co.uk">
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-16">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-16">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Someone deleted a bunch of tables from a read only tablespace</TITLE>
</HEAD><BODY ><DIV>

<DIV>

<DIV>

<DIV>

<DIV>



There
were two questions.

Can
we leave the event in place?

If we set
the
tablespaces to read write, will we be able to change them back to read only?

Database is 8.1.6 on Solaris 5.8(Pretty sure).


<A NAME="">Michael Kline</A>

Database Administration

Outside 804.261.9446

Cell 804.744.1545

3-9446

 



__ ____ ____ ______
__ ____ ____ ____ ____ ___ 
From: Kline.Michael 
Sent: Wednesday, April 26, 2006 8:42 AM 


Database came
up about 2:21 on Tuesday morning. Maybe a shadow copy was taken the night
before.

Sometime that
morning tables were dropped from various read only tablespaces. It's not
necessarily known just how far these drops got. Then we took the SMON hit at 10
:37.
<UL><UL>
Tue Apr 25 10
:37:57 2006

Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_smon_465.trc:

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [kcbgcur_9], [3976202121], [4], [4294966224],
[1040], [], [], []

Tue Apr 25 10
:38:00 2006

SMON:
terminating instance due to error 600

Tue Apr 25 10
:38:00 2006

Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_p002_475.trc:

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [15784], [600], [], [], [], [], [], []

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [], [], [], [], [], [], [], []

Instance
terminated by SMON, pid = 465
</UL></UL>
We tried to
bring the database up, but it would immediately die. Then we knew we had a
serious problem:
<UL><UL>
Tue Apr 25 11
:53:00 2006

alter database
open

(…)

Tue Apr 25 11
:53:43 2006

Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_smon_29523.trc:

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [kcbgcur_9], [3976202121], [4], [4294966224],
[1040], [], [], []

Tue Apr 25 11
:53:47 2006

SMON:
terminating instance due to error 600

Tue Apr 25 11
:53:47 2006

Errors in file
/u000/home/oracle/admin/prodwhs/bdump/prodwhs_p003_29535.trc:

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [15784], [600], [], [], [], [], [], []

ORA-00600 (See ORA-00600.ora-code.com):
internal error code, arguments: [], [], [], [], [], [], [], []

Instance
terminated by SMON, pid = 29523
</UL></UL>
So, after
seeing several things in metalink, we determined we could set an event, get the
database up, and then set the tablespaces to "read write" so smon
could clean up. We tried that, but then we took the database down, removed the
event, and tried to bring it up. It crashed.

EVENT
="10061 trace name context forever"

It is highly
suspected that the list of tables dropped is missing a table pointing to
another, yet unknown, "read only" tablespace. This could be something
as simple as an index that was not in "tablespace_idx" but perhaps
pointing some place else, maybe even a keying error.

(It was also
thought that maybe the clean up was taking longer than expected, but according
to the docs, setting the tablespace to read write was supposed to be all that
was needed.)

HOPEFUL
SOLUTION AFTER BACKUP:

It is hoped
that putting ALL tablespaces to read/write will allow the areas to be cleaned
up, and that once that is done we may be able to put the tablespaces back to
read only. 

Then we will
need to make sure we get yet another backup as it's too many changes not too.

Sure open to
other suggestions.
 

Michael Kline

Database
Administration

Outside 804
.261.9446

Cell 804.744
.1545

3-9446


</DIV>
<DIV>
 
</DIV>
<DIV>

 </DIV>
<DIV>

 </DIV>
<DIV>
LEGAL DISCLAIMER The information transmitted is
intended solely for the individual or entity to which it is addressed and may
contain confidential and/or privileged material. Any review, retransmission,
dissemination or other use of or taking action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you have received this email in error please contact the sender
and delete the material from any computer.   Seeing Beyond Money is
a service mark of SunTrust Banks, Inc. [ST:XCL]
</DIV>

</DIV>

</DIV>

</DIV>

</DIV></BODY></HTML>