Re: How to size UNDO for a database?
Date: Mon, 25 Mar 2013 19:18:38 -0700 (PDT)
Message-ID: <1364264318.73546.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>
I understand your point but there are those reading this who would take the example as-is and run it. Presuming an 8k block size (pretty standard on the dbs I support) the command would fail. I didn't want someone to take that command as gospel and wonder why it didn't work.
David Fitzjarrell
From: Tim Gorman <tim_at_evdbt.com>
To: ORACLE-L <oracle-l_at_freelists.org> Sent: Monday, March 25, 2013 3:31 PM
Subject: Re: How to size UNDO for a database?
That particular error has as much to do with block size as max size;
you're using 8kb blocksize. If DB_BLOCK_SIZE is 16kb, then you can
create up to 64 Gb (minus some); if DB_BLOCK_SIZE is 32kb, then you can
create up to 128 Gb (minus some).
You can prove it by creating a small Buffer Cache pool for the
appropriate block size, then adding the BLOCK SIZE attribute to the
CREATE TABLESPACE command.
Besides, the figure of 32 Gb was just illustrative, not directive. :-)
On 3/25/2013 2:47 PM, David Fitzjarrell wrote:
>> You can't create a tablespace with autoextend maxsize 32768M (I
>> already have a good UNDO tablespace so I created another, for
>> illustrative purposes):
>> SQL> create tablespace wamboonie datafile
>> 'C:\ORADB\ORADATA\SMEDLEY\WAMBOONIE.DBF' size 128m autoextend on next
>> 2 128m maxsize 32768m;
>> create tablespace wamboonie datafile
>> 'C:\ORADB\ORADATA\SMEDLEY\WAMBOONIE.DBF' size 128m autoextend on next
>> *
>> ERROR at line 1:
>> ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause
>> is out of
>> range
>>
>> SQL>
>> You can do this:
>> SQL> create tablespace wamboonie datafile
>> 'C:\ORADB\ORADATA\SMEDLEY\WAMBOONIE.DBF' size 128m autoextend on next
>> 2 128m maxsize 32767m
>> 3 /
>> Tablespace created.
>> SQL>
>> The largest you can go is 32767M; 32G won't work, either, for the
>> same reason.
>> Other than that small niggle Tim's advice is excellent; I do the same
>> thing for databases I manage and I get a clear picture of the UNDO
>> usage at a glance.
>> David Fitzjarrell
>>
>> *From:* Tim Gorman <tim_at_evdbt.com>
>> *To:* oracle-l_at_freelists.org
>> *Sent:* Monday, March 25, 2013 2:34 PM
>> *Subject:* Re: How to size UNDO for a database?
>>
>> 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 26 2013 - 03:18:38 CET