Re: Undo Tablespace Error

Even if the datafile has hit its maxsize, I would guess that the AUTOEXTEND ON configuration would cause Oracle to set Undo Retention to the maximum.

Query v$undostat to see, at least, recent information on query length and auto-tuned undo.

Hemant K Chitale

On Mon, Sep 13, 2021 at 1:36 PM Lok P <loknath.73_at_gmail.com> wrote:

> Thanks Hemant. So will it make any difference to the undo
> behaviour/utilisation pattern if we make the Autoextend off ? As because
> currently here Autoextend on with maxsize defined and storage perspective
> it's same as Autoextend off only as maxsize is hard limit for it. So wanted
> to understand if making Autoextend off now in this case will help anyway
> better?
>
> On Mon, 13 Sep 2021, 10:10 am Hemant K Chitale, <hemantkchitale_at_gmail.com>
> wrote:
>
>> It is possible that the datafile was manually resized to 200GB by a DBA.
>> Since the current size exceed the MAXSIZE, the datafile cannot autoextend.
>>
>> Hemant K Chitale
>>
>>
>>
>>
>> On Wed, Sep 8, 2021 at 9:00 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Thank you Hemant.
>>>
>>> *to your point "Besides dba_hist_tbsp_space_usage you should also look
>>> at v$undostat"*
>>>
>>> This error occurred to us 4-5days ago. So from min(begin_time) , I am
>>> seeing it doesn't have those time data in v$undostat anymore. So is there
>>> another way to dig into history?
>>>
>>> If i see current values in v$parameter, We have UNDO_MANGEMENT set as
>>> AUTO undo_retention set as 3600. If I see current values in dba_data_files
>>> we have only one data file for the UNDo tablespace. and it has BYTES as
>>> 200GB, MAXBYTES- 160GB, USER_BYTES- 200GB, Autoextensible- YES. So in this
>>> case do you suggest just by turning OFF the AUTOEXTEND , we should be good
>>> to avoid this issue in future?
>>>
>>> But I had one question, even if we have AUTOEXTEND ON, as we have
>>> defined the MAXBYTES as ~160GB for that data file, so that is as good as
>>> AUTOEXTEND OFF having same maxBytes as ~160GB as because in both of the
>>> cases it can't go beyond 160GB. Is my understanding wrong here?
>>>
>>> On Wed, Sep 8, 2021 at 8:14 AM Hemant K Chitale <
>>> hemantkchitale_at_gmail.com> wrote:
>>>
>>>>
>>>> If you have AUTOEXTEND ON it is likely that the datafile had already
>>>> hit the MAXSIZE.
>>>> So this would result in failure 6 in Oracle Support document " Troubleshooting
>>>> ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)
>>>>
>>>> Besides dba_hist_tbsp_space_usage you should also look at v$undostat
>>>> (also in earlier versions dba_hist_tbsp_space_usage had bugs in
>>>> reporting Tablespace Usage for Undo tablespaces)
>>>>
>>>> As John says, if you have AUTOEXTEND ON, Oracle will try to keep
>>>> extending Undo retention. Setting a max size for the datafiles and then
>>>> setting AUTOEXTEND OFF is better.
>>>>
>>>> Hemant K Chitale
>>>>
>>>>
>>>>
>>>>
>>>> On Mon, Sep 6, 2021 at 2:59 AM Lok P <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> Thank You John. I understand your point that with AUTOEXTENSIBLE YES
>>>>> oracle may tend to keep the expired UNDO longer to satisfy retention
>>>>> thereby increasing the UNDO tablespace to higher size. But , If we will
>>>>> talk specifically about the exact error here , isn't it true that with
>>>>> AUTOEXTENSIBLE YES, it should not have made it errored out with Ora-30036
>>>>> rather it would have thrashed/increased UNDO tablespace size by its own?
>>>>>
>>>>> Also as I see from dba_hist_tbspc_space_usage , the UNDO
>>>>> usage(tablespace_usedsize) was ~160GB which was equal to tablespace_size
>>>>> when it failed with ora-30036 , but then it increased to ~200GB after
>>>>> 4-5hrs, yet to see if somebody has increased it manually or it increased by
>>>>> itself.
>>>>>
>>>>> On Sun, Sep 5, 2021 at 2:53 AM John Piwowar <jpiwowar_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> The “autoextensible” setting on your undo data files is possibly the
>>>>>> culprit here. Undo segments are a bit of a special case, and Oracle’s
>>>>>> decision-making about when to reclaim expired undo can be thrown off when
>>>>>> it thinks it has the option to extend a data file instead. It’s possible
>>>>>> that you might still need to resize your undo to accommodate recent changes
>>>>>> in your workload, but you’re better off with fixed-size undo datafiles,
>>>>>> followed by an examination of your automatic undo configuration
>>>>>> (undo_retention, etc).
>>>>>>
>>>>>> Sorry about the vagueness of the response; I’m on my phone and can’t
>>>>>> get decent references with my thumbs. :)
>>>>>>
>>>>>> On Sat, Sep 4, 2021 at 1:05 PM Lok P <loknath.73_at_gmail.com> wrote:
>>>>>>
>>>>>>> Hello Listers, In one of our 12.1 version databases, a few days back
>>>>>>> we encountered an error- "Ora-30036 Unable to extend segment by 128 in
>>>>>>> tablespace 'UNDOTBS1'" for a delete query. This failure has never happened
>>>>>>> before. And we were trying to see if we really have to increase the size of
>>>>>>> UNDO tablespace(current size is ~200GB) as it has other impacts like making
>>>>>>> long running queries run even longer before hitting ora-01555/snapshot too
>>>>>>> old etc. Or anyway we can track and fix the long transactions which might
>>>>>>> have consumed large UNDO during that time and then we can try to break that
>>>>>>> into small transactions?
>>>>>>>
>>>>>>> Another thing I notice in the dba_data_files is now showing
>>>>>>> autoextensible as YES, so how come this query errored out with
>>>>>>> Ora-30036/space issues?
>>>>>>>
>>>>>>> The USED space in dba_hist_tbspc_space_usage for this UNDO
>>>>>>> tablespace is sometimes showing completely full and sometimes it's a
>>>>>>> lot empty and i believe it's because of a circular buffer.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>> Regards, John P. (Typed with thumbs on a mobile device. Lowered
>>>>>> expectations appreciated)
>>>>>>
>>>>>

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