Undo_retention Causes Space Problems

Hai, this article will explain how to fix UNDO_TABLESPACE issue for Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit.

Here in following example the Undo_TS is not getting free even after defined retention time. Whereas Undo_TS is designed to be reused; just like TEMP space.

Please read the below note it will provide more detail explanation

Full UNDO Tablespace In 10gR2 and above (Doc ID 413732.1)

SQL> show user

USER is "SYS"

SQL>

SQL>

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

PL/SQL Release 11.1.0.7.0 - Production

CORE    11.1.0.7.0      Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

SQL>

Check the usage of “UNDO_TABLESPACE”

SQL>

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME                    SIZEMB    USAGEMB     FREEMB

------------------------------ ---------- ---------- ----------

APPS_UNDOTS1                         1730  1729.0625   0.9375

SQL>

Check the value of max(tuned_undoretention)

SQL>

SQL> select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;

MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)

---------------- ------------------------

            8338                   859634

SQL>

The value for tuned_undoretention is high. So, check parameter _smu_debu_mode is set or not

SQL>

SQL> show parameter _smu_debug_mode

SQL>

SOLUTION:

As observed , you have large value for TUNED_UNDORETENTION, this can be caused by two reasons :

1. Using non-autoextensible UNDO data files which can be resolved by one of the following :

- Set _smu_debug_mode=33554432 (recommended) <<<<<<<<<<<<<<<<<<<

With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace.

instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

Please set this parameter and monitor the undo tablespace.

Please read the below note it will provide more detail explanation

Automatic Tuning of Undo_retention Causes Space Problems ( Doc ID 420525.1 )

             

This article helps those who face issue with “UNDO_TABLESPACE”

Thanks for Reading.

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	RAC DataGuard	EBS DataGuard
		RMAN DB CLONE	RAC DB Clone	R1226 Clone
	ASMCMD ASM Migrt	RMAN ASM Migrt		RMAN Clone
	11204 M-Upgrade			11202 EBS Upgrade
	12102 M-Upgrade			12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics