2 Replies Latest reply: Oct 12, 2012 8:04 AM by JohnWatson RSS

    Is it possible to have three Undo tablespaces for a 2 node RAC ?

    Max
      Version: 11.2.0.2/RHEL 5.4

      I was under the impression that you need only one UNDO Tablespace for each instance. But , for our 2-node RAC, I can see three Undo tablespaces. Any idea what is the third one for ?
      SQL> select tablespace_name,status from dba_tablespaces where CONTENTS = 'UNDO';
      
      TABLESPACE_NAME                STATUS
      ------------------------------ ---------
      UNDOTBS1                       ONLINE
      UNDOTBS2                       ONLINE
      UNDOTBS3                       ONLINE
      
      SQL> select distinct inst_id from gv$session;
      
         INST_ID
      ----------
               1
               2
      
      
      $ olsnodes -n
      thclst214   1
      thclst215   2
        • 1. Re: Is it possible to have three Undo tablespaces for a 2 node RAC ?
          P.Forstmann
          I think it is possible but a database instance is only able to use one undo tablespace. This is likely a mistake and you could try to drop the tablespace that is not listed in GV$PARAMETER.

          I have run the following on a 2 node RAC running 11.2.0.1:
          SQL>  select inst_id, name, value from gv$parameter where name = 'undo_tablespace';
          
             INST_ID NAME                 VALUE
          ---------- -------------------- --------------------
                   1 undo_tablespace      UNDOTBS1
                   2 undo_tablespace      UNDOTBS2
          
          SQL>  create undo tablespace undotbs3;
          
          Tablespace created.
          
          SQL> select tablespace_name, status from dba_tablespaces where contents='UNDO';
          
          TABLESPACE_NAME                STATUS
          ------------------------------ ---------
          UNDOTBS1                       ONLINE
          UNDOTBS2                       ONLINE
          UNDOTBS3                       ONLINE
          
          SQL>  drop tablespace undotbs3;
          
          Tablespace dropped.
          
          SQL> select tablespace_name, status from dba_tablespaces where contents='UNDO';
          
          TABLESPACE_NAME                STATUS
          ------------------------------ ---------
          UNDOTBS1                       ONLINE
          UNDOTBS2                       ONLINE
          
          SQL>
          • 2. Re: Is it possible to have three Undo tablespaces for a 2 node RAC ?
            JohnWatson
            There is no problem with having a spare undo tablespace, and if you have the capacity I would say it was good practice in general, and in particular if you use policy managed databases.
            Remember that in 11.x undio tablesapces are in effect "public", so rather than setting the undo_tablespace parameter you need only create a pool of undo tablespaces. Then each instance that starts up will take the next available. And in a policy managed environment, you may not know how many instances you weill have.