Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id ABDF6100313C65 for ; Thu, 9 Sep 2021 05:39:21 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 4CB8640009; Thu, 9 Sep 2021 03:39:18 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 3F6EE3F9C0; Thu, 9 Sep 2021 03:39:18 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1631158758; bh=05c6dQCEX0IPonMvOFYagvO3HO8cuxXdBEMdnxQDqYI=; h=From:Sender:Sender:From; b=TY7+rDPUsxoYJuThnKU19Dnrv9ldRlqH1hzNhgeyGfYEUdKyt64fLZ+pXTsylDXAs f76uCpzN6JO4SHV6Ku9oNX7yWcTXVKe/di7vGhzTRAuqOdaH9Ru2H0lto3PDEL1qtT 0BvcQmN6opwABWt0ArmMp60Y+QoFDE2bwICbDlvg= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id x2fdis0SOSjR; Thu, 9 Sep 2021 03:39:18 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 14D98413E3; Thu, 9 Sep 2021 03:39:15 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1631158756; bh=05c6dQCEX0IPonMvOFYagvO3HO8cuxXdBEMdnxQDqYI=; h=From:Sender:Sender:From; b=EXi98Wv4IG0C3EGIlhD2koHqIpF3P8MkOZReiIrh2J8yrDYUHDpKSG2L/HbVBSiA1 yRNW7i7Y0T7NpaHt2629kjbknPAGAoIsrFFJN7WNcPeTHjdOWzYjM+HNb8pHm3l1DP j761PpBQXRbYBQfebWqDi/bLaa8krZtqdTVvSZeE= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 09 Sep 2021 03:39:13 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 54B0C3F9C0 for ; Thu, 9 Sep 2021 03:39:13 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=P3GZ0SUF; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id RL-SAYUoXzMd for ; Thu, 9 Sep 2021 03:39:13 +0000 (UTC) Received: from mail-yb1-f178.google.com (mail-yb1-f178.google.com [209.85.219.178]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 428E73F9BE for ; Thu, 9 Sep 2021 03:39:13 +0000 (UTC) Received: by mail-yb1-f178.google.com with SMTP id y13so1093679ybi.6 for ; Wed, 08 Sep 2021 20:39:13 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=klGXtjCRVUCeY9rczkI4mRtQwsiuyvVSN/lgOMKFVr8=; b=dOdvUbs1oy/LZrS/mCh5J0yWX2vDxTheRxrLsjD4aJwPkP2rOt2ml8kpuFN3WfWycu Idaghpmc4GihO0ChaXEk7KKVh5XYY0sQk7hU4Z+xE9l6/Vo7AocQ0jMivrb7Dn8uPr0r D7xLPkz+98QIvWM+BIOQNvlr3UOSic5syuvGTbxFsGHr1h8VTddIaUeCbaW9xl0ZVSAA 1hVCX0iDx5lOkCu4rFj2g+Bix50DgYj0rNiEtCtF9eLxQbKy0JWXG0ZRgrtIjW/1yLpb tuYyzcAOOXhf3ltAlHF7axFjd8MUlE8YhOHPiHESEbDrS2X0jozYS7axzvTEmppM/tAP 1dtQ== X-Gm-Message-State: AOAM533g4wf5ar7yGPpuoIr12SrG2WDJUEfFBPad3Y/yP4H2ASS/7lPv YkWP+vMo7JA9U/FjCsfxlm5M8wWiEJ+cnEFpN+I= X-Google-Smtp-Source: ABdhPJz05PgQuxFdjZJwFY/G+EVj+sCZjlQHKuBm4tRmXhmA+oSmu/8LjJc11ILyms3y3qe0LLGyZiL1r6bJ7eE3cCs= X-Received: by 2002:a25:cc1:: with SMTP id 184mr1021430ybm.363.1631158752675; Wed, 08 Sep 2021 20:39:12 -0700 (PDT) MIME-Version: 1.0 References: <151101d7a4bb$02dd82f0$089888d0$@rsiz.com> <156d01d7a4d8$cd223020$67669060$@rsiz.com> In-Reply-To: <156d01d7a4d8$cd223020$67669060$@rsiz.com> From: Lok P Date: Thu, 9 Sep 2021 09:08:55 +0530 Message-ID: Subject: Re: Undo Tablespace Error To: "Mark W. Farnham" Cc: Hemant K Chitale , John Piwowar , Oracle L Content-Type: multipart/alternative; boundary="000000000000068cb005cb87beb5" X-archive-position: 80893 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: loknath.73@gmail.com Precedence: normal Reply-To: loknath.73@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000068cb005cb87beb5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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, 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 ar= e > seeing seems correct behavior to me. > > > > Sigh. > > > > *From:* Lok P [mailto:loknath.73@gmail.com] > *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 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 expe= ct > 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@freelists.org [mailto: > oracle-l-bounce@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_fil= es > 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 thi= s > case do you suggest just by turning OFF the AUTOEXTEND , we should be goo= d > 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 AUTOEXTE= ND > 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 > 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 " *Troublesh= ooting > 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 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 wrote: > > The =E2=80=9Cautoextensible=E2=80=9D setting on your undo data files is p= ossibly the > culprit here. Undo segments are a bit of a special case, and Oracle=E2=80= =99s > 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=E2=80=99s p= ossible > that you might still need to resize your undo to accommodate recent chang= es > in your workload, but you=E2=80=99re better off with fixed-size undo data= files, > followed by an examination of your automatic undo configuration > (undo_retention, etc). > > > > Sorry about the vagueness of the response; I=E2=80=99m on my phone and ca= n=E2=80=99t get > decent references with my thumbs. :) > > > > On Sat, Sep 4, 2021 at 1:05 PM Lok P 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 happene= d > 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 maki= ng > long running queries run even longer before hitting ora-01555/snapshot to= o > 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 th= at > into small transactions? > > > > Another thing I notice in the dba_data_files is now showing autoextensibl= e > 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) > > --000000000000068cb005cb87beb5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you. I will try to see the alert log for more detai= ls.=C2=A0

But if my understand= ing is correct , in current scenario with maxsize being set there is no dif= ference 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. Farn= ham, <mwf@rsiz.com> wrote:

We cannot tell from the apparent infor= mation presented HOW it got to 200. But it should not be able to extend bey= ond 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.

=C2=A0

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.

=C2=A0

Sigh.

=C2=A0<= /p>

From: Lok P= [mailto:loknath.73@gmail.com]
Sent: Wednesday, September = 08, 2021 12:13 PM
To: Mark W. Farnham
Cc: Hemant K Chit= ale; John Piwowar; Oracle L
Subject: Re: Undo Tablespace Error=

=C2=A0

<= p class=3D"MsoNormal">I see it's a bigfile tablespace. And if i am corr= ect it will go beyond normal ~31Gb individual data file size limit. Correct= me if I'm wrong.

= =C2=A0

And then in this case , = can it go beyond defined max_bytes? Or is it that at the point=C2=A0when it= reaches ~160GB size it throws an ora-30036 error and then someone has incr= eased 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 wit= h defined max_size(i.e. our current setup) VS Autoextend OFF with the same = max_size?

=C2=A0=

On Wed, Sep 8, 2021 at 7:39 PM = Mark W. Farnham <mwf@rsiz.com> wrote:

If you have a single file limited to 160 and i= t is already 200, it seems to me that file cannot extend. If you have reach= ed 200, then I would expect it to go splat failing to extend with autoexten= d on, because it fails the maxbytes limit.

=C2=A0

Since the file is already b= igger than what=C2=A0 someone probably set it down to later, the maxbytes s= eems like a red herring and autoextend could only come into play if you add= ed a file.

=C2=A0

mwf

=C2=A0

From: oracle-l-bounce@freeli= sts.org [mailto:oracle-l-bounce@freelists.org] On Beh= alf Of Lok P
Sent: Wednesday, September 08, 2021 9:00 AM
<= b>To: Hemant K Chitale
Cc: John Piwowar; Oracle L
Subje= ct: Re: Undo Tablespace Error

=C2=A0

Thank you Hemant.=

=C2=A0

to your point "Besides dba_hist_tbsp_s= pace_usage you should also look at v$undostat"

=C2=A0

This error occurred=C2=A0to us 4-5days ago. So from min(begi= n_time) , I am seeing it doesn't have those time data in v$undostat any= more. So is there another way to dig into history?

=C2=A0

If i see current values in v$parameter, We have UNDO_MANGEMENT set as A= UTO=C2=A0 undo_retention set as 3600. If I see current values in dba_data_f= iles we have only one data file for the UNDo tablespace. and it has BYTES a= s 200GB, MAXBYTES- 160GB, USER_BYTES- 200GB, Autoextensible- YES. So in thi= s case do you suggest just by turning OFF the AUTOEXTEND , we should be goo= d to avoid this issue in future?=C2=A0

=C2=A0

Bu= t 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 OF= F having same maxBytes as ~160GB as because in both of the cases it can'= ;t=C2=A0go beyond 160GB. Is my understanding wrong here?

<= /div>

=C2=A0

<= p class=3D"MsoNormal">On Wed, Sep 8, 2021 at 8:14 AM Hemant K Chitale <<= a href=3D"mailto:hemantkchitale@gmail.com" target=3D"_blank" rel=3D"norefer= rer">hemantkchitale@gmail.com> wrote:

=C2=A0

If you have AUTOEXTEND ON=C2=A0 it is likely that the dat= afile had already hit the MAXSIZE.

So this would result in failure 6 in Oracle Support document=C2=A0&quo= t; Troubleshooting ORA-30036 - Unable To Extend = Undo Tablespace (Doc ID 460481.1)

=C2=A0

Besides dba_hist_tbsp_space_usage you s= hould also look at v$undostat

(also in earlier versions dba_hist_tbsp_space_usage had bugs in repor= ting Tablespace Usage for Undo tablespaces)

=C2=A0

As John says, if you have AUTOEXTEND ON,=C2=A0 Oracle will try to keep e= xtending Undo retention.=C2=A0 Setting=C2=A0a max size for the datafiles an= d then setting AUTOEXTEND OFF is better.


Hemant K Chitale=

=C2=A0

=C2=A0

On Mon, Sep 6, 2021 at 2:59 AM Lok P <lo= knath.73@gmail.com> wrote:

Thank You John. I understand your point that with = AUTOEXTENSIBLE YES oracle may tend to keep the expired UNDO longer to satis= fy retention thereby increasing the UNDO tablespace to higher size. But , I= f we will talk specifically=C2=A0about the exact error here , isn't=C2= =A0it true that with AUTOEXTENSIBLE YES, it should=C2=A0not have made it er= rored out with Ora-30036 rather it would have thrashed/increased UNDO table= space size by its own?

=C2=A0<= u>

Also as I see from dba_h= ist_tbspc_space_usage , the UNDO usage(tablespace_usedsize) was ~160GB whic= h 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=C2= =A0it manually or it increased by itself.

=C2=A0

On Sun, Sep 5, 2021 at 2:53 AM John Piwowar <jpiwowar@gmail.com>= wrote:

Th= e =E2=80=9Cautoextensible=E2=80=9D setting on your undo data files is possi= bly =C2=A0the culprit here. Undo segments are a bit of a special case, and = Oracle=E2=80=99s 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=E2=80=99s possible that you might still need to resize your undo to acco= mmodate recent changes in your workload, but you=E2=80=99re better off with= fixed-size undo datafiles, followed by an examination of =C2=A0your automa= tic undo configuration (undo_retention, etc).=C2=A0

=

=C2=A0

Sorry about the vagueness of the response; I=E2=80=99m on my pho= ne and can=E2=80=99t get decent references with my thumbs. :)=

=C2=A0

On Sat, Sep 4, 2021 at 1:05 PM Lok P <loknath.73@g= mail.com> wrote:

Hello Listers, In one of our=C2=A012.1 version databases, a = few days back we encountered an error- "Ora-30036 Unable to extend seg= ment 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=C2=A0like making long running queries run even longer bef= ore 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?

=C2=A0

Another thing I notice in the dba_data_files is now showing = autoextensible as YES, so how come this query errored out=C2=A0with Ora-300= 36/space issues?

=C2=A0<= u>

The USED space in d= ba_hist_tbspc_space_usage for this UNDO tablespace is sometimes showing com= pletely full and sometimes it's a lot=C2=A0empty and i believe=C2=A0it&= #39;s because of a circular buffer.

=C2=A0

=C2= =A0

--

Regards, John = P. (Typed with thumbs on a mobile device. Lowered expectations appreciated)=

= --000000000000068cb005cb87beb5-- -- http://www.freelists.org/webpage/oracle-l