Re: Undo Tablespace Error

I see it's a bigfile tablespace. And if i am correct it will go beyond normal ~31Gb individual data file size limit. Correct me if I'm wrong.

And then in this case , can it go beyond defined max_bytes? Or is it that at the point when it reaches ~160GB size it throws an ora-30036 error and then someone has increased the size to ~200GB but it's not updated in dba_data_files? But in any scenario, is behaviour of UNDO different if we have AUTO EXTEND ON with defined max_size(i.e. our current setup) VS Autoextend OFF with the same max_size?

On Wed, Sep 8, 2021 at 7:39 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> If you have a single file limited to 160 and it is already 200, it seems
> to me that file cannot extend. If you have reached 200, then I would expect
> it to go splat failing to extend with autoextend on, because it fails the
> maxbytes limit.
>
>
>
> Since the file is already bigger than what someone probably set it down
> to later, the maxbytes seems like a red herring and autoextend could only
> come into play if you added a file.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Lok P
> *Sent:* Wednesday, September 08, 2021 9:00 AM
> *To:* Hemant K Chitale
> *Cc:* John Piwowar; Oracle L
> *Subject:* Re: Undo Tablespace Error
>
>
>
> 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