I don’t know whether there is a different error fall back order. I thought the
error was failing to extend, so I can conceive that it might not try to extend
with autoextend off and tell you it ran out of space in undo instead of trying
to extend and failing.
It would give me the willies to have maxbytes smaller than the actual size, but
that is just because it doesn’t make sense to me. Without the source code the
only way to determine the behavior of the running code is to do an enumerative
case test seeing what happens.
My experiments with UNDO were long ago and I never bothered with the cases that
didn’t make sense to me to allow, so I cannot definitively answer your question.
In your situation, and assuming space is available, I would create a new UNDO
that was for sure internally consistent with the values you want and then alter
that online and wait for the current one to be completely inactive and
eventually drop it. After that, with all the values making sense and being
internally consistent, I would expect behavior to be easily predictable.
IF your shop is somehow attached to the nomenclature of the current UNDO, you
can replace UNDO twice to get back to the original name, but be patient with
the complete clearing of the one you’re going to drop and recreate (it won’t
drop until all transactions are complete, but it might let you kill read only
queries to drop it, the same as running out of retention time.) I’m a bit rusty
on this.
Other than running away from file corruption errors, the only other reason to
rotate UNDO was back when a variety of storage performance classes made sense
with very large history that didn’t need super speed and intermittent large
transaction batches that benefitted from very fast (like SSD versus spinning
rust) speeds. Then you briefly switched to the fast stuff, started the batch
transaction, and immediately switched back. That would put your performance
requiring transaction on the fast stuff (possibly along with a few other things
that happened to start before the switch back) and then do the mundane stuff on
the routine UNDO. The operations control to mesh that was rarely worth the
trouble and some folks from Oracle mentioned they had deeply tested switching
but had not deeply tested switching back. It never failed that I saw in some
cases that were bigger than a bread box. In the days when SSD was extremely
expensive compared to spinning rust, it was sometimes worth it to do more
complex operations to buy less of it and get the same benefit.
I doubt any of that applies to you. I suppose with the enormous RAM sizes we
have now and private designations, if you have an operating system with duplex
RAM for error control putting some UNDO on RAM might be useful. If Oracle wants
me to do that experiment for them, I’m all ears.
Good luck.
From: Lok P [mailto:loknath.73@xxxxxxxxx] ;
Sent: Wednesday, September 08, 2021 11:39 PM
To: Mark W. Farnham
Cc: Hemant K Chitale; John Piwowar; Oracle L
Subject: Re: Undo Tablespace Error
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)
