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> <FONT SIZE="+0"> <DIV> <FONT FACE="Courier"> <DIV> <FONT FACE="Courier"> <DIV> <FONT FACE="Courier"> <DIV> <FONT FACE="Times New Roman"> <!-- Converted from text/rtf format -->
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New">There were two questions.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New">Can we leave the event in place?</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New">If< /FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New"> we set the</FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New"> tablespaces</FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New"> to read</FONT></SPAN><SPAN LANG="en-us"> <FONT COLOR="#0000FF" FACE= "Courier New">write</FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE= "Courier New">, will we be able to change them back to read</FONT></SPAN><SPAN LANG="en-us"> <FONT COLOR="#0000FF" FACE="Courier New">only</FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New">?</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Courier New" >Database is 8.1.6 on Solaris 5.8(Pretty sure).</FONT></SPAN><SPAN LANG="en-us"> </SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">Database came up about 2:21 on Tuesday morning. Maybe a shadow copy was taken the night before.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">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.</FONT></SPAN></P> <UL><UL> <P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Courier New">Tue Apr 25 10 :37:57 2006</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Courier New">Errors in file /u000/home/oracle/admin/prodwhs/bdump/prodwhs_smon_465.trc:</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Courier New">Instance terminated by SMON, pid = 465</FONT></SPAN></P> </UL></UL> <P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">We tried to bring the database up, but it would immediately die. Then we knew we had a serious problem:</FONT></SPAN></P> <UL><UL> <P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Courier New">Tue Apr 25 11 :53:00 2006</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Courier New">Instance terminated by SMON, pid = 29523</FONT></SPAN></P> </UL></UL> <P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">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.</FONT></SPAN><SPAN LANG="en-us">< /SPAN><SPAN LANG="en-us"></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">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.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">(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.)</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><B><U><FONT SIZE=4 FACE="Courier New">HOPEFUL SOLUTION AFTER BACKUP:</FONT></U></B></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">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. </FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">Then we will need to make sure we get yet another backup as it's too many changes not too.< /FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=4 FACE="Courier New">Sure open to other suggestions.</FONT></SPAN></P> <BR>
</FONT> </DIV> <DIV> <FONT FACE="Times New Roman"> </FONT> </DIV> <DIV> <FONT FACE="Times New Roman"> </FONT> </DIV> <DIV> <FONT FACE="Times New Roman"> </FONT> </DIV> <DIV> <FONT FACE="Times New Roman">LEGAL DISCLAIMER<BR>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.<BR> <BR>Seeing Beyond Money is a service mark of SunTrust Banks, Inc.<BR>[ST:XCL]</FONT> </DIV> </FONT> </DIV> </FONT> </DIV> </FONT> </DIV> </FONT> </DIV></BODY></HTML>
