As per your other respondent - I had the same issue and was told that Oracle is still trying to check UNDO in the offlined tablespace. If the tablespace had been dropped then Oracle would not have bothered, making the assumption that the drop would not have taken place if there was still data in the UNDO tablespaces. So the solution is as posted if you feel confident to do so (I must admit I did not and ended up shrinking the old UNDO as small as possible and leaving it on-line but not used).
HTH
Tim
Tim Onions Head Of Oracle Development Phone: +44 (0) 1684 312364 ext. 364 Cell: +44 (0) 7736 634556 www.medquist.com
This electronic mail transmission contains confidential information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. If you are not the intended recipient of this e-mail, promptly delete it and all attachments.
"Sami Seerangan" <dba.orcl@(protected) om> To Sent by: ORACLE-L <oracle-l@(protected)> oracle-l-bounce@(protected) cc reelists.org Subject Undo tablespace offline and 16/03/2006 21:25 ORA-00376 (See ORA-00376.ora-code.com)
Please respond to dba.orcl@(protected) m
2 Node RAC,9.2.0.6, Sun OS 2.8
We created new undo tablespaces (UNDO3, UNDO4) for each node1 and node2 respectively and changed default undo tablespace to UNDO3 and UNDO4. Then we put old UNDO tablespaces offline.
After 2 days we are trying to create a new index and it gives the following error.
Any idea?
SQL> CREATE UNIQUE INDEX ABC_KEY_IDX01 ON ABC_STAGE (SESSN_KEY, PART_KEY,SUB_PART_KEY) TABLESPACE STAGE_INDEX LOCAL NOPARALLEL; * ERROR at line 1: ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1 ORA-00376 (See ORA-00376.ora-code.com): file 2 cannot be read at this time ORA-01110 (See ORA-01110.ora-code.com): data file 2: '/u01/oradata/pdw/undotbs01.dbf'
Luckily the tablespace is still offline and I can bring it online. But I just wanted to know teh reason behind this?
Thanks Sami -- http://www.freelists.org/webpage/oracle-l
<html><body> <p>Sami<br> <br> As per your other respondent - I had the same issue and was told that Oracle is still trying to check UNDO in the offlined tablespace. If the tablespace had been dropped then Oracle would not have bothered, making the assumption that the drop would not have taken place if there was still data in the UNDO tablespaces. So the solution is as posted if you feel confident to do so (I must admit I did not and ended up shrinking the old UNDO as small as possible and leaving it on-line but not used).<br> <br> HTH<br> <br> Tim<br> <b><font size="4" face="Arial"><br> Tim Onions</font></b><font face="Arial"><br> Head Of Oracle Development<br> Phone: +44 (0) 1684 312364 ext. 364<br> Cell: +44 (0) 7736 634556</font><u><font color="#0000FF" face="Arial"><br> </font></u><a href="http://www.medquist.com/"><u><font color="#0000FF" face= "Arial">www.medquist.com</font></u></a><font face="Arial"><br> </font><img src="cid:10__=0FBBFBA7DFC4E1A58f9e8a93df9@(protected)" width="248" height="38" alt=""> <p><font face="Arial">This electronic mail transmission contains confidential information intended only for the person(s) named.<br> Any use, distribution, copying or disclosure by another person is strictly prohibited.<br> If you are not the intended recipient of this e-mail, promptly delete it and all attachments.</font><br> <img src="cid:20__=0FBBFBA7DFC4E1A58f9e8a93df9@(protected)" width="16" height= "16" alt="Inactive hide details for "Sami Seerangan" <dba.orcl @(protected)>">"Sami Seerangan" <dba.orcl@(protected)><br> <br> <br>
<table border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="58"><img src="cid:40__=0FBBFBA7DFC4E1A58f9e8a93df9 @(protected)" border="0" height="1" width="1" alt=""></td><td width="336"><img src="cid:40__=0FBBFBA7DFC4E1A58f9e8a93df9@(protected)" border="0" height="1" width="1" alt=""></td></tr> </table> </td></tr> </table> <br> <tt>2 Node RAC,9.2.0.6, Sun OS 2.8<br> <br> We created new undo tablespaces (UNDO3, UNDO4) for each node1 and<br> node2 respectively and changed default undo tablespace to UNDO3 and<br> UNDO4. Then we put old UNDO tablespaces offline.<br> <br> After 2 days we are trying to create a new index and it gives the<br> following error.<br> <br> Any idea?<br> <br> SQL> CREATE UNIQUE INDEX ABC_KEY_IDX01 ON ABC_STAGE (SESSN_KEY,<br> PART_KEY,SUB_PART_KEY) TABLESPACE STAGE_INDEX LOCAL NOPARALLEL;<br> *<br> ERROR at line 1:<br> ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1<br> ORA-00376 (See ORA-00376.ora-code.com): file 2 cannot be read at this time<br> ORA-01110 (See ORA-01110.ora-code.com): data file 2: '/u01/oradata/pdw/undotbs01.dbf'<br> <br> <br> Luckily the tablespace is still offline and I can bring it online. But<br> I just wanted to know teh reason behind this?<br> <br> <br> Thanks<br> Sami<br> --<br> </tt><tt><a href="http://www.freelists.org/webpage/oracle-l">http://www .freelists.org/webpage/oracle-l</a></tt><tt><br> <br> </tt><br> </body></html>
