Skip to Main Content
  • Questions
  • Oracle doesn't release undo even if time is way past the undo_retention value

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: June 24, 2020 - 12:07 am UTC

Answered by: Connor McDonald - Last updated: June 29, 2020 - 5:51 am UTC

Category: Database Administration - Version: 12.2.0.1

Viewed 100+ times

You Asked

Good Evening,

I'm hoping that I can finally find out how Oracle decides when it is time to release the UNDO.

In this database, the UNDO_RETENTION is set to 600 seconds. However, it has been over two hours since the huge purge job that used up just over 60G of Undo tablespace ran. There aren't any new purge jobs, but still the database shows that the undo retention is at over 60G used. Shouldn't the database set that space to free space two hours later? What is going to trigger the database to release that Undo space from the data files? Thank you for your help.


Here is the query that I used to get the space allocated, used and free for the undo tablespace:
19:54:57 ora > get fs2
1 column file_name format a70
2 column name format a50
3 column member format a50
4 break on report
5 compute sum of allocated_mb used_mb free_space_mb on report
6 SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
7 trunc((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
8 trunc(NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) free_space_mb
9 FROM dba_data_files df, dba_free_space dfs
10 WHERE df.FILE_ID = dfs.file_id(+)
11 and df.file_name like '%undo%'
12 GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes
13* ;
19:55:00 ora > @fs2

FILE_NAME ALLOCATED_MB USED_MB FREE_SPACE_MB
---------------------------------------------------------------------- ------------ ---------- -------------
/u202/oradata/ora/undotbs01.dbf 19456 19456 0
/u203/oradata/ora/undotbs02.dbf 32740 32740 0
/u203/oradata/ora/undotbs03.dbf 12824 12789 34
------------ ---------- -------------
sum 65020 64985 34

Notice how it still claims that it has 64985 MB in use, but I know that there isn't anymore purge jobs running. Those complete about 2 hours ago.

UNDO PARAMETER VALUES:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 600
undo_tablespace string UNDOTBS1

Also, I see that there is way that Oracle provides to to shrink the undo but it is a quirky solution. Oracle needs to provide a consistent way for the DBA to have it released UNDO for transactions that have been committed hours ago.

How to Shrink the datafile of Undo Tablespace (Doc ID 268870.1)

The steps to accomplish the goal are:
Create a new undo tablespace with a smaller size:
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size <new size>;
Set the new tablespace as the undo tablespace to be used: (Note: If Data Guard Managed configuration is used, the below parameter modification needs to executed on any physical standbys serviced by this production database)
SQL> alter system set undo_tablespace=undo_rbs1;
Drop the old undo tablespace:
SQL> drop tablespace undo_rbs0 including contents;
NOTE: Dropping the old tablespace may give ORA-30013: undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted. The disk space will remain "allocated" from the OS perspective until the database restart.

Thanks for your help!

John

and we said...

Check out this video - its about flashback, but I've jumped to the section about how we handle undo_retention. In a nutshell, we pretty much ignore it

https://youtu.be/lmrWmH1vMPc?t=860

and you rated our response

  (3 ratings)

Reviews

June 24, 2020 - 1:50 am UTC

Reviewer: A reader

Thanks, Connor. I'll take a look.

BTW: three hours later, NOW the used space has been freed up .... sigh, I hate not being able to control that.


@fs2

FILE_NAME ALLOCATED_MB USED_MB FREE_SPACE_MB
---------------------------------------------------------------------- ------------ ---------- -------------
/u202/oradata/ora/undotbs01.dbf 19456 83 19372
/u203/oradata/ora/undotbs03.dbf 12824 78 12745
/u203/oradata/ora/undotbs02.dbf 32740 5 32734
------------ ---------- -------------
sum 65020 166 64851


select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status order by status;

STATUS MB
--------- ----------
EXPIRED 76.6875
UNEXPIRED 87.875

Connor McDonald

Followup  

June 26, 2020 - 4:14 am UTC

You can have additional level of control via underscore parameters, but you'd need to liaise with Support to get their guidance on that one

viewed the youtube video and have a follow-up question

June 24, 2020 - 2:09 am UTC

Reviewer: John Cantu

If the Oracle database can/will ignore the UNDO_RETENTION value, how can DBA ever know if the UNDO tablespace will run out of space? Let me elaborate. Say I have three UNDO datafiles sized 20 G each, and each is set to auto allocate up to a max size of 30G. A user runs a purge job that removes 100 million records with commits executed every 100k rows removed. Then the DBA see each of the datafiles keep increasing in size until they are all sized close to 30G. Are we at risk of that purge job failing at some point because Oracle decided on it's own to keep COMMITTED undo data? Or will Oracle allow the purge transaction to succeed by releasing undo space by removing committed data from the undo?

It is nerve wrecking to see the UNDO tablespace continue to grow to the limit and upsetting to find out that Oracle has gone rogue by ignoring the DBA's request how much old committed data should be saved in the DBA's UNDO_RETENTION setting.
Connor McDonald

Followup  

June 26, 2020 - 4:24 am UTC

Well...its really just a mindset no?

I mean, after all, you've been happily running with a TEMP tablespace that typically reports 100% full since Oracle 7.3.

If you want to *cap* your undo size, you can make the undo datafiles non-extendable. We will choose to purge unneeded data before returning a "no more undo available".

June 26, 2020 - 2:50 pm UTC

Reviewer: A reader

Great point, Conner!

It would still be a nice feature if Oracle would at least change the undo extents to EXPIRED once the DBA set UNDO_RETENTION was reached.

In one case, even AFTER 2 HOURS after a huge purge job completed, "used_mb" showed that the database was still holding on to those UNDO EXTENTS used for the purge job.

This is the query I used:
SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
trunc((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
trunc(NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes
UNION ALL
select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) - (user_bytes/1024/1024)) "free_space_mb"
from dba_temp_files
order by used_mb
;

Any thoughts on this?
Connor McDonald

Followup  

June 29, 2020 - 5:51 am UTC

It would still be a nice feature if Oracle would at least change the undo extents to EXPIRED


By the same token, I'm sure we'd then have people on AskTOM then telling us: "Why is background process XXX using CPU/IO when it doesn't need to" :-)

In terms of 2 hours, it could hold them "forever" for all I care (in the sense that...if no-one else needs that space right now, then why go to the effort of cleaning it up).

That is sort of a general "trend" in the database. "Delay if you can, what you can do today until tomorrow, because hopefully tomorrow never comes".

eg every time we change a datafile block, we don't write it to disk, because hopefully another change will come along and we'll just keep changing in the buffer cache until we absolutely *must* flush it to disk.


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database