Re: How to size UNDO for a database?

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 25 Mar 2013 14:34:27 -0600
Message-ID: <5150B4D3.8020908_at_evdbt.com>

Ann,
Make the UNDO tablespace autoextensible, starting with a relatively small size, incrementing in relatively small "chunks", with a maximum file size that is standard for your environment.

For example, assuming that I'm running in an environment where the largest datafile I wish to backup (and restore) is 32 Gb, and I've decided to allocate Oracle-managed datafiles on ASM or file-system space in 128 Mb chunks, I'll use something like...

    create undo tablespace undo01 datafile size 128m autoextend on next     128m maxsize 32768m;

This allows you to start with something small, then over time to let it size itself, so that after a few days, weeks, or months of normal operation, you'll be able to look at V$UNDOSTAT and have an excellent idea of what normal undo usage is for the workload hitting your database.

Nothing is better than actual usage information for your environment.

If you fear running out of space even so, set the parameter RESUMABLE_TIMEOUT to a non-negative value, then monitor error messages to your "alert.log" file. If a resumable error is output to your alert.log, then a resumable_timeout setting of 1800 will give you 1,800 seconds (i.e. 30 minutes) to either increase AUTOEXTEND MAXSIZE or add another datafile or otherwise deal with the issue. Again, notice that the idea is to use these mechanisms to find the proper setting for your specific workload, which nobody else on this list can begin to guess at.

Hope this helps!

Thanks!

Tim Gorman

consultant -> Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035-2151 USA
web/blog   => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1 (303) 885-4526
twitter    => timothyjgorman

president  -> Rocky Mtn Oracle Users Group (www.RMOUG.org)
board      -> Oracle Developers Tools Users Group (www.ODTUG.com)
advisor    -> Northern California Oracle Users Group (www.NoCOUG.org)
secretary  -> Project SafeGuard (www.PSGHelps.org)
member     -> OakTable Network (www.OakTable.net)

Oracle ACE Director (www.oracle.com/technetwork/community/oracle-ace)

Lost Data? => www.ora600.be/ for info about DUDE...

On 3/25/2013 1:02 PM, Apps DBA wrote:
> Hi Gurus,
> I would like to know how to analyze and size undo for a busy database or
> while design itself. As the transactions increasing for a busy DB like
> reporting or DW DB we usually face lot of jobs failing with snapshot too
> old error. Please share your knowledge.
>
> Thanks,
> Ann
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 25 2013 - 21:34:27 CET