Thank you. I will try to see the alert log for more details.
But if my understanding is correct , in current scenario with maxsize being
set there is no difference in undo behaviour if we keep the data file as
either Autoextend ON or OFF. Correct me if wrong?
On Wed, 8 Sep 2021, 11:12 pm Mark W. Farnham, <mwf@xxxxxxxx> wrote:
We cannot tell from the apparent information presented HOW it got to 200.
But it should not be able to extend beyond the set MAXBYTES. The behavior I
would have built is to complain if you try to set maxbytes lower than an
already existing size for a file. If you keep logs of the manual
intervention sections (at least) or complete alert logs for at least the
system, then you might discover how it got to 200.
Yes, you ARE within the limits of bigfile, presuming that more space is
available on the storage volume. NO, it should not autoextend if it is at
or beyond the MAXBYTES defined for the file, and tossing the error you are
seeing seems correct behavior to me.
Sigh.
*From:* Lok P [mailto:loknath.73@xxxxxxxxx]
*Sent:* Wednesday, September 08, 2021 12:13 PM
*To:* Mark W. Farnham
*Cc:* Hemant K Chitale; John Piwowar; Oracle L
*Subject:* 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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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)
