This discussion is archived
6 Replies Latest reply: Feb 15, 2013 6:14 AM by Levi-Pereira RSS

Undo tablespace full confusion

LANCERIQUE Newbie
Currently Being Moderated
Hi Gurus,

We have a 2 node RAC on RHEL 6.2 (11gR2). We have 2 undo tablespaces UNDOTBS1 and UNDOTBS2. Which checking the stats from DBA_UNDO_EXTENTS it gives different results. Please help me in figuring out the difference for below query outputs. Also, if there any chance of any ORA-01555 or ORA-30036??



On Node 1 -

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'UNDOTBS1' group by status
2 ;

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 57352.1875 6087
EXPIRED 64.25 5
ACTIVE 841 35

1* SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'UNDOTBS2' group by status
SQL> /

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 9090.8125 1240
ACTIVE 1374 46
EXPIRED 47323.75 7125


SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS group by status;

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 66185 7322
EXPIRED 47380 7129
ACTIVE 2600 94



On Node 2 -

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'UNDOTBS1' group by status
2 ;

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 57281.4375 6090
ACTIVE 1096 45

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'UNDOTBS2' group by status
2 /

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 56404.5625 8362
ACTIVE 1320 48
EXPIRED 72 2

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS group by status;

STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------- -------------------- ----------
UNEXPIRED 113575 14454
ACTIVE 2543 93
EXPIRED 64 1


Why there is so much of difference in the output. Please help

Regards,
Nikhil Mehta.
  • 1. Re: Undo tablespace full confusion
    asahide Expert
    Currently Being Moderated
    Hi,
    Please help me in figuring out the difference for below query outputs.
    Sorry, I don't know..
    Also, if there any chance of any ORA-01555 or ORA-30036??
    Usually, V$UNDOSTAT will be used for investigating 1555 or 30036.

    - Master Note for ORA-1555 Errors [ID 1307334.1]
    - Common Diagnostic Scripts for AUM problems [ID 746173.1]

    Regards,
  • 2. Re: Undo tablespace full confusion
    Niket Kumar Pro
    Currently Being Moderated
    Please check again on node 1....might be you get same result as node 2.... and post the output if its have much difference as its is now....
  • 3. Re: Undo tablespace full confusion
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,
    It is a bug due the dependent parameters for undo retention are instance-specific and really time-sensitive.
    It's difficult to come up with a perfect 'global' solution. So there is no fix for this.

    So use the workaround of note bellow to determine the correct result.

    *DBA_UNDO_EXTENTS shows wrong results from different nodes of RAC [ID 1492313.1]*

    Regards,
    Levi Pereira
  • 4. Re: Undo tablespace full confusion
    LANCERIQUE Newbie
    Currently Being Moderated
    Hi Niket,

    Thanks for your response. I have already posted output of both the nodes and there is a huge difference
  • 5. Re: Undo tablespace full confusion
    LANCERIQUE Newbie
    Currently Being Moderated
    Hi Levi,

    Thanks for the correct response. Work around mentioned by ORACLE is to get information from active undo. But in our case both the undo tablespaces are active. So how to pro actively monitor UNDO usage.

    As of now on node 2, we can see that UNEXPIRED blocks are very high as compared to expired and active blocks. UNDO_RETENTION is set to 36000 (As per application requirement i.e. remedy 7.2).

    Node 1

    SQL> select status, sum(bytes)/1024/1024/1024 sum_bytes
    from dba_undo_extents
    group by status
    /
    2 3 4
    STATUS SUM_BYTES
    --------- ----------
    UNEXPIRED 65.4638672
    EXPIRED 46.5273438
    ACTIVE 1.7421875


    Node 2

    1 select status, sum(bytes)/1024/1024/1024 sum_bytes
    2 from dba_undo_extents
    3* group by status
    SQL> /

    STATUS SUM_BYTES
    --------- ----------
    UNEXPIRED 111.803711
    ACTIVE 1.8046875
    EXPIRED .125


    Regards,
    Nikhil Mehta.
  • 6. Re: Undo tablespace full confusion
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,

    You must connect on each Instance and execute SQL Query below:

    e.g Instance 1
    sqlplus sys/<passoword>@node1:1521/<SID> as sysdba
    SQL> 
    select status, sum(bytes)/1024/1024/1024 sum_bytes
    from dba_undo_extents
    where TABLESPACE_NAME in (select value from v$parameter where name = 'undo_tablespace')
    group by status
    Regards,
    Levi Pereira

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points