Re: Undo tablespace offline and ORA-00376

From: <tonions_at_medquist.com>
Date: Fri, 17 Mar 2006 15:57:20 +0000
Message-ID: <OFDDEC57AE.520D63EE-ON80257134.00576735-80257134.0057A59B@medquist.com>

Sami

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_at_gmail.c                                             
             om>                                                        To 
             Sent by:                  ORACLE-L <oracle-l_at_freelists.org>   
             oracle-l-bounce_at_f                                          cc 
             reelists.org                                                  
                                                                   Subject 
                                       Undo tablespace offline and         
             16/03/2006 21:25          ORA-00376                           
                                                                           
                                                                           
             Please respond to                                             
             dba.orcl_at_gmail.co                                             
                     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: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: 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

Received on Fri Mar 17 2006 - 09:57:20 CST