This forum is currently locked. You can't register or post questions at this time. (read more)

RAC Undo Tablespace

Questions relating to Oracle Real Application Clusters (RAC) and Clusterware.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

RAC Undo Tablespace

Postby peterx » Sun Dec 02, 2012 2:42 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: RAC Undo Tablespace

Postby Tim... » Mon Dec 03, 2012 7:59 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: RAC Undo Tablespace

Postby peterx » Wed Dec 05, 2012 6:18 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: RAC Undo Tablespace

Postby Tim... » Wed Dec 05, 2012 8:55 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle RAC and Clusterware”

Who is online

Users browsing this forum: No registered users and 1 guest

cron