6 Replies Latest reply: Feb 15, 2013 8:14 AM by Levi Pereira RSS

    Undo tablespace full confusion

    LANCERIQUE
      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
          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
            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
              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
                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
                  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
                    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