Re: Undo Tablespace Error

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