Links
• Home
• Oracle DBA Forum
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
Trying to drop old undo tablespace that needs recovery

Trying to drop old undo tablespace that needs recovery

2005-10-26       - By Ken Naim

Reply:     1     2     3     4     5     6     7  

Since it isn't the undo tablespace that is being used and you have
successfully shut it down and brought it up. I would bring the database down
and recreate the control file deleting all mention of the unrecoverable undo
tablespace. Of course I'd test this by cloning the key tablespaces and one
data tablespace onto another server first and backing up the original
database before and after is a must



Ken Naim



-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of Strickland, Mark
Sent: Wednesday, October 26, 2005 5:19 PM
To: oracle-l@(protected)
Subject: Trying to drop old undo tablespace that needs recovery



I've opened a TAR, but I wanted to also solicit ideas from the list.  I
inherited a database that has an old undo tablespace "UNDO2" that had a
datafile that has needed recovery since August 9th.  I don't have archived
logs going back that far.  The tablespace isn't being used.  A new undo
tablespace called "UNDO" was created sometime back and is being used.  The
database has been shutdown and restarted successfully since the problem
started.  Every night when the tablespaces are put in backup mode, errors
are thrown in the alert log for the  undo2 tablespace because the file that
needs recovery is offline.  The tablespace has offline undo segments.  They
don't show a status of needing recovery.  I researched it in Metalink and
believed that I could offline drop the datafiles in that old tablespace and
then drop the tablespace.   I was able to execute the alter database offline
drop statements successfully, but when I tried to drop the tablespace, I got
an ORA-00600 (See ORA-00600.ora-code.com).  Anybody got any ideas?



Mark Strickland

Seattle, WA




<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">


<meta name=Generator content="Microsoft Word 10 (filtered)">
<title>Message</title>

<style>
<!--
/* Font Definitions */
@(protected)
  {font-family:Tahoma;
  panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
  {margin:0in;
  margin-bottom:.0001pt;
  font-size:12.0pt;
  font-family:"Times New Roman";}
a:link, span.MsoHyperlink
  {color:blue;
  text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
  {color:purple;
  text-decoration:underline;}
span.EmailStyle17
  {font-family:Arial;
  color:navy;}
@(protected) Section1
  {size:8.5in 11.0in;
  margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
  {page:Section1;}
-->
</style>

</head>

<body lang=EN-US link=blue vlink=purple>

<div class=Section1>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Since it isn&#8217;t the undo tablespace
that
is being used and you have successfully shut it down and brought it up. I would
bring the database down and recreate the control file deleting all mention of
the unrecoverable undo tablespace. Of course I&#8217;d test this by cloning the
key tablespaces and one data tablespace onto another server first and backing
up the original database before and after is a must </span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>&nbsp;</span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Ken Naim</span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>&nbsp;</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-- --Original Message-- --<br>
<b><span style='font-weight:bold'>From:</span></b>
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Strickland, Mark<br>
<b><span style='font-weight:bold'>Sent:</span></b> Wednesday, October 26, 2005
5:19 PM<br>
<b><span style='font-weight:bold'>To:</span></b> oracle-l@(protected)<br>
<b><span style='font-weight:bold'>Subject:</span></b> Trying to drop old undo
tablespace that needs recovery </span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"
><span
style='font-size:12.0pt'>&nbsp;</span></font></p>

<div>

<div>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>I've opened a TAR, but I wanted to
also solicit ideas from the&nbsp;list.&nbsp;&nbsp;I inherited a database that
has&nbsp;an old undo tablespace &quot;UNDO2&quot;&nbsp;that had a datafile that
has needed recovery since August 9th.&nbsp; I don't have archived logs going
back that far.&nbsp; The tablespace isn't being used.&nbsp;&nbsp;A new undo
tablespace called &quot;UNDO&quot;&nbsp;was created sometime back and&nbsp;is
being used.&nbsp;&nbsp;The database has been shutdown and restarted
successfully since the problem started.&nbsp; Every night when the tablespaces
are put in backup mode, errors are thrown in the alert log for the&nbsp; undo2
tablespace because the file that needs recovery is offline.&nbsp; The
tablespace has offline undo segments.&nbsp; They don't show a status of needing
recovery.&nbsp; I researched it in Metalink and believed that I could offline
drop
the datafiles in that old tablespace and then drop the tablespace.&nbsp;&nbsp;
I was able to execute the alter database offline drop statements successfully,
but when I tried to drop the tablespace, I got an ORA-00600 (See ORA-00600.ora-code.com).&nbsp; Anybody got
any ideas?</span></font></p>

</div>

<div>

<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"
><span
style='font-size:12.0pt'>&nbsp;</span></font></p>

</div>

<div>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Mark Strickland</span></font></p>

</div>

<div>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Seattle, WA</span></font></p>

</div>

<div>

<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"
><span
style='font-size:12.0pt'>&nbsp;</span></font></p>

</div>

</div>

</div>

</body>

</html>