Re: Undo Tablespace issue

Thank You Timur and Paul for this feedback.

Below is the output from the blog which Timur pointed to. And the job finished after we kept increasing the UNDO and it's now at ~2TB. And the job ran for 20hrs+. I ran the query by passing awr_snapshot_count as ~50 and then ~100 and in both cases I got the similar output as below. But yes as in our case we were not hitting Ora-01555 but Ora-30036 which is different , so how can we interpret this result and find the culprit query/session for Ora-30036?

As mentioned the v$transaction was only showing ~300GB of UNDO used by that specific session.

The job which was failing multiple times on UNDO was running on Node-1 on this four node database.

INST_ID CURRENT_SIZE_MB IS_AUTOEXTENSIBLE UNDO_RETENTION UNDO_SIZE_MIN_MB UNDO_SIZE_GUARANTEE_MB LONGEST_SQL LONGEST_SQL_ID MAX_ORA1555_CNT MAX_NO_SPACE_CNT
1 2356086 YES 3 991027.8125 5836862 42.48055556 8yp4vr3aqhnxa 3 0 2 798968 YES 3 39969.25 6178679 50.92833333 8pnuydbnxhbhj 3 0 3 322301 YES 3 120706.5625 424632 206.5272222 0uypm2w7jxtvk 2 0 4 445988 YES 3 130544.75 1729500 72.07694444 ds172hnn0044p 10 0

On Wed, Jul 5, 2023 at 3:33 PM Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:

> Hi,
>
> Have you checked MOS Doc 460481.1
> Also can you run a script from this post
> https://timurakhmadeev.wordpress.com/2018/02/05/undo-sizing/ and share
> its output?
> It's targeted more for handling ORA-1555 errors yet still can be useful.
> The input should be a number of AWR snapshots covering target
> undo_retention.
>
> On Tue, Jul 4, 2023 at 5:59 PM yudhi s <learnerdatabase99_at_gmail.com>
> wrote:
>
>> Hello All,
>>
>> Its Oracle version 19C(19.15) and using AUTO undo management. We have a
>> job keep failing suddenly with UNDO error as below, after running for
>> 6-7hours. It used to run fine in past months. But this time its failing
>> multiple times even we rerun. I understand its a big transaction without
>> commit in between. However checking (used_ublk) from gv$transaction showing
>> the size its consuming is ~200GB only , however the full size of UNDO
>> tablespace is ~1.5TB. And then checking the details out of
>> DBA_UNDO_EXTENTS, we see majority of the blocks in the UNDO in "UNEXPIRED"
>> status as below.
>>
>> Does it mean that it may be that one odd transaction/session (or it may
>> be a SELECT query) is holding all the UNDO and not letting it to mark as
>> EXPIRED as its active? But then when doing a select on v$session for that
>> exact node to which this UNDO tablespace is aligned and doing
>> "logon_time desc" won't show any such long running sessions? Also checked
>> GV$UNDOSTAT order by tuned_undoretention desc, but not seeing any such
>> session standing out apart from the currently running one.
>>
>> Any other possible way to get hold of the culprit session/user which we
>> can kill to get back the "Unexpired UNDO blocks" back to the "Expired"
>> bucket such that, that can be usable?
>>
>> ORA-30036: unable to extend segment by 128 in undo tablespace 'UNDOTBS1'
>>
>> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024/1024, COUNT(*) FROM
>> DBA_UNDO_EXTENTS where tablespace_name='UNDOTBS1' GROUP BY STATUS;
>>
>> STATUS SUM(BYTES)/1024/1024/1024 COUNT(*)
>> UNEXPIRED 1130.301331 150047
>> EXPIRED 0.1328125 3
>> ACTIVE 594.6703491 44210
>>
>>
>> Regards
>>
>> Yudhi
>>
>
>
> --
> Regards
> Timur Akhmadeev
>

--
http://www.freelists.org/webpage/oracle-l