RAC Undo Tablespace

[peterx]

hi Tim,

I have a production Oracle 11gR2 RAC, vendor suggested the undo tablespace with Auto Extend = NO

My undo setting:

Code: Select all

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>


I found that:

Code: Select all

BREAK ON REPORT
COMPUTE SUM OF MB ON REPORT
COMPUTE SUM OF PERC ON REPORT
COMPUTE SUM OF FULL ON REPORT

select status,
 round(sum_bytes / (1024*1024), 0) as MB,
 round((sum_bytes / undo_size) * 100, 0) as PERC,
 decode(status, 'UNEXPIRED', round((sum_bytes / undo_size * factor) * 100, 0),
                'EXPIRED',   0,
                             round((sum_bytes / undo_size) * 100, 0)) FULL
from
(
 select status, sum(bytes) sum_bytes
 from dba_undo_extents
 group by status
),
(
 select sum(a.bytes) undo_size
 from dba_tablespaces c
 join v$tablespace b on b.name = c.tablespace_name
 join v$datafile a on a.ts# = b.ts#
 where c.contents = 'UNDO'
 and c.status = 'ONLINE'
),
(
 select tuned_undoretention, u.value, u.value/tuned_undoretention factor
 from v$undostat us
 join (select max(end_time) end_time from v$undostat) usm
    on usm.end_time = us.end_time
 join (select name, value from v$parameter) u
    on u.name = 'undo_retention'
);


the output always like:

Code: Select all

STATUS            MB       PERC       FULL
--------- ---------- ---------- ----------
EXPIRED            2          0          0
UNEXPIRED       7826         98          0
ACTIVE             1          0          0
          ---------- ---------- ----------
sum             7829         98          0


I want to ask is what is the pros and cons of auto-extend=NO ?
The Unexpired part always full or over 90%.

And, from Cloud Control or from above sql, i also find that the undo tablespace is always full.
How can I impove this ?

thanks.

[Tim...]

Hi.

The potential problem with autoextend on is you can have a runaway process that uses lots of undo and makes the file grow to full size (32G). This can be a problem if you don't have enough disk space to hold the maximum size file.

Personally, I prefer to let it auto extend, but I set a max size that is now going to blow my file system.

If a process does affect the size, you can easily recreate it.

http://www.oracle-base.com/articles/mis ... tablespace

The tablespace will probably always be full as no undo is removed unless it needs to be overwritten. Not sure why you have nothing marked as expired. Maybe it has already been overwritten with new undo...

Cheers

Tim...

[peterx]

hi Tim,

Code: Select all

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;


What will happen, if i drop the undotbs1(old) after a new one undoths2 is using while some undo segment is in the undotbs1
Since, the system is online and transaction is non-stopping ?

thanks.

[Tim...]

Hi.

You won't be allowed to drop the undo tablespaces if any uncommited transactions or open cursors are using it. If there are none, you will be allowed to drop it.

Remember, by dropping it you will lose the undo necessary for flashback operations (up to your flashback retention period).

Cheers

Tim...