FAQ
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

Search Discussions

  • Riyaj Shamsudeen at Mar 16, 2006 at 9:50 pm
    ('binary' encoding is not supported, stored as-is)

    Is it possible that few blocks of abc_stage table is not clean ? Meaning
    block clean out is not complete for those blocks ? Oracle might be
    trying to check whether the pending transactions in those blocks are
    committed or not, by accessing the old undo segment header. Since undo
    tablespace is offline, this error is thrown.

    BTW check whether sys.undo$ table has entries for those old undo segments.

    To confirm you could trace (10046) the statement, find the block read
    just before this error, dump the block to see whether any ITL entries
    pointing to old undo segments.

    Better yet, you could just alter the tablespace online, create index and
    then drop the undo tablespace.

    Thanks


    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    "No implied or explicit liability"

    Sami Seerangan wrote:
    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


    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.
  • Giovanni Cuccu at Mar 17, 2006 at 7:56 am
    Hi Sami,

    I was hitting a very similar issue on 9.2.0.4 on linux (non RAC). I
    posted a similar problem on this list and I got some very interesting
    answers. IIRC it seems that the only solution is to drop the old undo
    tablespace(s) (after a clear shutdown and startup in order to be sure
    that none needs undo info from the old tablespace).
    Hope it helps,

    Giovanni
    On 3/16/06, Sami Seerangan wrote:
    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

    --
    --------------------------------------------------------------------
    Another free oracle resource profiler
    http://sourceforge.net/projects/oraresprof/
    Now version 0.9
    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Mar 17, 2006 at 9:59 am
    I got several requests for the function so I decided to post it.
    I do not remember what ts_ranges does, probably a copy for tests.

    This was written years ago so there must be room for improvements.
    It does mostly comparison so compiling it will bring great benefit.

    The function gets 2 parameters:
    1) ranges list. from1_to1,from2_to2,.....
    2) Value to check.

    For example: select Range('0-100,101-200,201-300',balance) from account;
    will divide the records to negative balance, one of the ranges or more
    then 300.

    I am keeping the last 2 range lists in memory to avoid parsing of the
    range list each time.

    As you can see you can use and copy it, but please retain the copyright.

    If you make any enhancements please tell me so I can enhance my copy.

    USE IT ON YOUR OWN RISK.

    CREATE OR REPLACE PACKAGE "RANGE" AS

    --

    Developed by: Yechiel Adar, Mehish Computer services.
    --
    E-mail: _adary__at_mehish.co.il
    adar666_at_inter,net.il

    All Rights Reserved.
    You can use, give away and modify this package freely,
    just retain the credits.
    Send any modifications and changes back to me so I can enhance my copy.
    --
    TYPE R_vec_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    current_ptr NUMBER DEFAULT 0;
    R_low R_vec_type;
    R_high R_vec_type;
    no_of_ranges NUMBER DEFAULT 0;
    SAVE_PARAM VARCHAR2(300) DEFAULT NULL;

    R_low1 R_vec_type;
    R_high1 R_vec_type;
    no_of_ranges1 NUMBER DEFAULT 0;
    SAVE_PARAM1 VARCHAR2(300) DEFAULT NULL;

    R_low2 R_vec_type;
    R_high2 R_vec_type;
    no_of_ranges2 NUMBER DEFAULT 0;
    SAVE_PARAM2 VARCHAR2(300) DEFAULT NULL;

    FUNCTION RANGES(RANGE_LIST IN VARCHAR2, FORMULA_V IN NUMBER) RETURN

    VARCHAR2 PARALLEL_ENABLE;

    FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2, FORMULA_V IN NUMBER)
    RETURN VARCHAR2 PARALLEL_ENABLE;

    PRAGMA RESTRICT_REFERENCES (RANGES, WNDS, RNDS);

    END RANGE;

    /

    CREATE OR REPLACE PACKAGE BODY RANGE IS

    -- PRAGMA SERIALLY_REUSABLE;

    FUNCTION RANGES(RANGE_LIST IN VARCHAR2,FORMULA_V IN NUMBER) RETURN

    VARCHAR2

    IS
    list_p VARCHAR2(300);
    obligo_p NUMBER;
    len NUMBER;
    s1 VARCHAR2(300);
    BEGIN

    IF SAVE_PARAM = RANGE_LIST THEN

    CURRENT_PTR := current_ptr;
    ELSIF SAVE_PARAM1 = RANGE_LIST THEN

    SAVE_PARAM := SAVE_PARAM1;

    FOR I IN 1..NO_OF_RANGES1 LOOP

    R_LOW(I) := R_LOW1(I);

    R_HIGH(I) := R_HIGH1(I);

    END LOOP;

    NO_OF_RANGES := NO_OF_RANGES1;

    ELSIF SAVE_PARAM2 = RANGE_LIST THEN

    -- dbms_output.put_line('2 ' || current_ptR);
    SAVE_PARAM := SAVE_PARAM2;

    FOR I IN 1..NO_OF_RANGES2 LOOP

    R_LOW(I) := R_LOW2(I);

    R_HIGH(I) := R_HIGH2(I);

    END LOOP;

    NO_OF_RANGES := NO_OF_RANGES2;

    ELSE

    -- dbms_output.put_line('1 ' || CUrrent_ptR);
    SAVE_PARAM := range_list;
    list_p := range_list;
    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW(I) := NULL;

    R_HIGH(I) := NULL;

    END LOOP;

    NO_OF_RANGES := 0;

    FOR i IN 1..LENGTH(list_p) LOOP

    len := INSTR(list_p,',');
    IF len IS NULL THEN

    EXIT;

    END IF;

    no_of_ranges := no_of_ranges + 1;
    s1 := SUBSTR(list_p,1,len);
    list_p := SUBSTR(list_p,len+1);

    len := INSTR(s1,'_');
    R_low(i) := SUBSTR(s1,1,len-1);
    R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);

    END LOOP;

    IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN

    SAVE_PARAM1 := SAVE_PARAM;

    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW1(I) := R_LOW(I);

    R_HIGH1(I) := R_HIGH(I);

    END LOOP;

    NO_OF_RANGES1:= NO_OF_RANGES;

    CURRENT_PTR := 1;

    ELSE

    SAVE_PARAM2 := SAVE_PARAM;

    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW2(I) := R_LOW(I);

    R_HIGH2(I) := R_HIGH(I);

    END LOOP;

    NO_OF_RANGES2 := NO_OF_RANGES;

    CURRENT_PTR := 2;

    END IF;

    END IF;

    -- dbms_output.put_line(current_ptr || save_param);
    obligo_p:=FORMULA_V;
    IF obligo_p < R_low(1) THEN
    RETURN ' 0 ) <' ||R_low(1);
    END IF;

    FOR j IN 1..no_of_ranges LOOP
    IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
    IF j < 10 THEN

    RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
    ELSE

    RETURN j||' ) '||R_low(j)||'-'||R_high(j);
    END IF;

    END IF;

    END LOOP;

    IF obligo_p > R_high(no_of_ranges) THEN

    IF no_of_ranges+1 < 9 THEN
    RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
    ELSE

    RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
    END IF;

    END IF;

    RETURN obligo_p || ' - NOT IN RANGE !!';
    END RANGES;

    FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2,FORMULA_V IN NUMBER) RETURN
    VARCHAR2

    IS
    list_p VARCHAR2(300);
    obligo_p NUMBER;
    len NUMBER;
    s1 VARCHAR2(300);
    BEGIN

    IF SAVE_PARAM = RANGE_LIST THEN

    CURRENT_PTR := current_ptr;
    ELSIF SAVE_PARAM1 = RANGE_LIST THEN

    SAVE_PARAM := SAVE_PARAM1;

    FOR I IN 1..NO_OF_RANGES1 LOOP

    R_LOW(I) := R_LOW1(I);

    R_HIGH(I) := R_HIGH1(I);

    END LOOP;

    NO_OF_RANGES := NO_OF_RANGES1;

    ELSIF SAVE_PARAM2 = RANGE_LIST THEN

    -- dbms_output.put_line('2 ' || current_ptR);
    SAVE_PARAM := SAVE_PARAM2;

    FOR I IN 1..NO_OF_RANGES2 LOOP

    R_LOW(I) := R_LOW2(I);

    R_HIGH(I) := R_HIGH2(I);

    END LOOP;

    NO_OF_RANGES := NO_OF_RANGES2;

    ELSE

    -- dbms_output.put_line('1 ' || CUrrent_ptR);
    SAVE_PARAM := range_list;
    list_p := range_list;
    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW(I) := NULL;

    R_HIGH(I) := NULL;

    END LOOP;

    NO_OF_RANGES := 0;

    FOR i IN 1..LENGTH(list_p) LOOP

    len := INSTR(list_p,',');
    IF len IS NULL THEN

    EXIT;

    END IF;

    no_of_ranges := no_of_ranges + 1;
    s1 := SUBSTR(list_p,1,len);
    list_p := SUBSTR(list_p,len+1);

    len := INSTR(s1,'_');
    R_low(i) := SUBSTR(s1,1,len-1);
    R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);

    END LOOP;

    IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN

    SAVE_PARAM1 := SAVE_PARAM;

    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW1(I) := R_LOW(I);

    R_HIGH1(I) := R_HIGH(I);

    END LOOP;

    NO_OF_RANGES1:= NO_OF_RANGES;

    CURRENT_PTR := 1;

    ELSE

    SAVE_PARAM2 := SAVE_PARAM;

    FOR I IN 1..NO_OF_RANGES LOOP

    R_LOW2(I) := R_LOW(I);

    R_HIGH2(I) := R_HIGH(I);

    END LOOP;

    NO_OF_RANGES2 := NO_OF_RANGES;

    CURRENT_PTR := 2;

    END IF;

    END IF;

    -- dbms_output.put_line(current_ptr || save_param);
    obligo_p:=FORMULA_V;
    IF obligo_p < R_low(1) THEN
    RETURN ' 0 ) <' ||R_low(1);
    END IF;

    FOR j IN 1..no_of_ranges LOOP
    IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
    IF j < 10 THEN

    RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
    ELSE

    RETURN j||' ) '||R_low(j)||'-'||R_high(j);
    END IF;

    END IF;

    END LOOP;

    IF obligo_p > R_high(no_of_ranges) THEN

    IF no_of_ranges+1 < 9 THEN
    RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
    ELSE

    RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
    END IF;

    END IF;

    RETURN obligo_p || ' - NOT IN RANGE !!';
    END ts_RANGES;

    END RANGE;

    /

    Adar Yechiel
    Rechovot, Israel

    --
    http://www.freelists.org/webpage/oracle-l
  • Tonions_at_medquist.com at Mar 17, 2006 at 3:57 pm
    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"
    To
    Sent by: ORACLE-L
    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
  • Yechiel Adar at Mar 20, 2006 at 9:02 am
    Thanks for the input.

    This function is used by programmers only so no great need for error
    handling.
    You are right.
    The function keeps in memory the last 2 range strings.
    Only if the new string does not match then it do the parse and save
    the new string.
    I suppose that if you have many concurrent users you might want to
    save more then the last 2 strings.

    Adar Yechiel
    Rechovot, Israel

    Mindaugas Navickas wrote:
    Hi Adar,

    Few suggestions regarding the program
    1. I would suggest to enhance error handling - e.g. when list contains not
    valid characters, etc.
    2. A working example (corresponding to your code) would be (note underscore
    instead of dash):
    select range.ranges('0_100,101_200,201_300',balance) from account;
    3. the function will parse a string (list of ranges) for every row in account
    table. Unfortunatly string operations in PL/SQL are slow and also Oracle will
    suffer from SQL/PL-SQL context switches. If performance is important in this
    case, I would populate some temporary or in-memory table (parsing just once)
    and then would use it joining it with account table.

    Let me know if those suggestions makes sense to you.

    Regards
    Mindaugas Navickas
    OCP DBA


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 16, '06 at 9:25p
activeMar 20, '06 at 9:02a
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase