Home » RDBMS Server » Server Administration » Shrink UNDO tablespace
Shrink UNDO tablespace [message #247556] Tue, 26 June 2007 06:45 Go to next message
oraclenewbie07
Messages: 4
Registered: June 2007
Junior Member
I am totally new to oracle (just joined as a junior trainee DBA). Any kind of explanation will be highly appreciated.

I have a couple of instances each on 2 servers. Two 2 node RAC clusters on 2 servers. 10.2.0.2.0 on RHEL 3.

In database1:
TABLESPACE_NAME GB
---------- --------------------
UNDOTBS1 4.90625
UNDOTBS2 15.9136736

Instance1:
NAME TYPE VALUE
--------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Instance2:
NAME TYPE VALUE
------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2



In database2:
TABLESPACE_NAME GB
---------- ---------------------
UNDOTBS1 15.2107041
UNDOTBS2 11.0957684

Instance1:
NAME TYPE VALUE
----------------------------- --------------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Instance2:
NAME TYPE VALUE
--------------------------- ------------- -----------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

From what I read last few weeks, in oracle 10g, if we use automatic undo management and undo retention, oracle will automatically allocate and deallocate segments when required and we do not need to bother about it growing once we set a proper size. But later read that if we do not set the MAX_FILE_SIZE (i think this is what it is), it will keep growing and allocate segments all over the file system and occupy space.

these are my questions:
1. When undo TS is 15GB, does it mean that these 15GB is taken from the disk space and sitting here "useless".
2. Do I have to shrink it (to re-claim space)?
3. What are the steps to do it? I read in one form thread that I can create a new tablespace and change that to be the undo tablespace and drop the old one.
4. what other information about my DB or server should I provide to complete my question?

Can someone kindly explain the procedure? Reference to a documentation will also help. But if someone can list the steps, that will be really kind.
Re: Shrink UNDO tablespace [message #247566 is a reply to message #247556] Tue, 26 June 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. If the size grows till 15GB then it once needed it. So is it "useless"? Maybe it will need it in the future. Only you can answer this.
2. Do you need the space?
3. Correct.
4. None

Regards
Michel
Re: Shrink UNDO tablespace [message #247570 is a reply to message #247556] Tue, 26 June 2007 07:20 Go to previous messageGo to next message
oraclenewbie07
Messages: 4
Registered: June 2007
Junior Member
Thanks much. Appreciate the quick reply.

1) Is this the way to re-create a new undo tablespace and switch it?
1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS3" DATAFILE '/u01/oradata/xxxx/undotbs301.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;

2. issue the alter system command to point to the temporary tablespace (I believe this is dynamic)
alter system set undo_tablespace = UNDOTBS3 ;

3. drop the original undo tablespace including datafile
drop tablespace "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES ;

4. recreate the original one using the original name(optional and size according to your needs )
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oradata/xxxx/undotbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;

5. Repeat step 2 to switch back to the original named tablespace

6. drop the tablespace created in step 1 using the syntax in step 3

2) Now, if I specify MAXSIZE, then there is a potential chance of trasactions failing(and eventually rolling back) if undo TS cannot extend right?

Thanks.
Re: Shrink UNDO tablespace [message #247587 is a reply to message #247570] Tue, 26 June 2007 07:49 Go to previous messageGo to next message
oraclenewbie07
Messages: 4
Registered: June 2007
Junior Member
Oh, It was already created with maxsize set

(found(learnt) a way to get the DDL) Smile

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle01/oradata/DB1/undotbs01.dbf' SIZE 209715200
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'/oracle01/oradata/DB1/undotbs01.dbf' RESIZE 4194304000

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
'/oracle01/oradata/DB1/undotbs02.dbf' SIZE 209715200
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'/oracle01/oradata/DB1/undotbs02.dbf' RESIZE 14994636800

and 80GB of 100GB allocated to /oracle01 is already used up. (Now I see that we need to re-claim this space). And having the datafile maxsize for each TS to 32GB may be disastrous, right?
Re: Shrink UNDO tablespace [message #247593 is a reply to message #247587] Tue, 26 June 2007 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
having the datafile maxsize for each TS to 32GB may be disastrous, right?

Query v$undostat and/or DBA_HIST_UNDOSTAT to see what is your undo consumption.
If your current size is 15GB that is your undo tablespace HWM then 32GB seems to be safe.
Adjust undo_retention parameter to fit in 32GB.

Regards
Michel
Re: Shrink UNDO tablespace [message #247704 is a reply to message #247556] Tue, 26 June 2007 22:58 Go to previous messageGo to next message
abcindiaxyz
Messages: 13
Registered: March 2005
Location: Indonesia
Junior Member
Check out this script for the stats:
----------------------------------------------
set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off


Set Heading Off
Set Termout Off


Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint
Column Var_UNDO_RETENTION new_value Var_UNDO_RETENTION noprint

Select
block_size Var_DB_BLOCK_SIZE
from
dba_tablespaces
where
tablespace_name in (select value from V$PARAMETER where name='undo_tablespace')
;

select
value Var_UNDO_RETENTION
from
V$PARAMETER
where
name='undo_retention'
;

Set Termout On
Set Heading On



prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Undo Segment Stats ---

prompt -- ----------------------------------------------------------------------- ---

prompt

column Begin_Time heading "Begin|Time" format A16
column End_Time heading "End|Time" format A16
column UndoTSN heading "Tsn" format 999
column UndoBLKS heading "Blks" format 99999
column TxnCount heading "TxnCount" format 99999999
column MAXQUERYLEN heading "Max|Query|Len" format 9999
column MAXCONCURRENCY heading "Max|Conc." format 9999
column UNXPSTEALCNT heading "Un|Xpsteal|Cnt" format 9999
column UNXPBLKRELCNT heading "UnXp|Blkrel|Cnt" format 9999
column UNXPBLKREUCNT heading "UnXp|Blkreu|Cnt" format 9999
column EXPSTEALCNT heading "Exp|Steal|Cnt" format 9999
column EXPBLKRELCNT heading "Exp|Blkrel|Cnt" format 9999
column EXPBLKREUCNT heading "Exp|Blkreu|Cnt" format 9999
column SSOLDERRCNT heading "Sso|Lderr|Cnt" format 9999
column UNXPBLKREUCNT heading "UnXp|Blkreu|Cnt" format 9999
column NOSPACEERRCNT heading "No|Space|Errcnt" format 9999

Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From
V$Undostat
where
begin_time > sysdate - 1/24
Order
by Begin_Time
;


Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From
V$Undostat
where
begin_time > sysdate - 1/24*3
Order
by Begin_Time
;



prompt -- Undo blocks / Second

prompt -- ----------------------------------------------------------------------- ---

prompt

column UndoBLKS600 heading "Blks|Second" format 99999


Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoBLKS/600 UndoBLKS600
From
V$Undostat
Order by Begin_Time
;


prompt -- Max Undo blocks / Second

prompt -- ----------------------------------------------------------------------- ---

prompt

column maxUndoBLKS600 heading "Max Blks|Second" format 99999


Select max(UndoBLKS/600) maxUndoBLKS600
From
V$Undostat
;


prompt -- Avg Undo blocks / Second

prompt -- ----------------------------------------------------------------------- ---

prompt

column AvgUndoBLKS600 heading "Avg Blks|Second" format 99999


Select avg(UndoBLKS/600) AvgUndoBLKS600
From
V$Undostat
;




prompt -- Avg Undo blocks / Second (median value of the last hour)

prompt -- ----------------------------------------------------------------------- ---

prompt

column SumUndoBLKS600 heading "Avg Blks|Second" format 99999


Select
count(*)
, sum(UndoBLKS)/3600 SumUndoBLKS600
From
V$Undostat
where
begin_time > sysdate - 1/24
;


column Sumsize600 heading "Avg space|Second (Mb)" format 99999

Select
count(*)
, sum(UndoBLKS)/3600*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From
V$Undostat
where
begin_time > sysdate - 1/24
;



prompt -- Avg Undo blocks / Second (median value of the last day)

prompt -- ----------------------------------------------------------------------- ---

prompt

column SumUndoBLKS600 heading "Avg Blks|Second" format 99999


Select
count(*)
, sum(UndoBLKS)/(3600*24) SumUndoBLKS600
From
V$Undostat
where
begin_time > sysdate - 1
;

column Sumsize600 heading "Avg space|Second (Mb)" format 99999

Select
count(*)
, sum(UndoBLKS)/(3600*24)*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From
V$Undostat
where
begin_time > sysdate - 1
;



prompt -- Avg Undo blocks / Second (median value of the last three days)

prompt -- ----------------------------------------------------------------------- ---

prompt

column SumUndoBLKS600 heading "Avg Blks|Second" format 99999


Select
count(*)
, sum(UndoBLKS)/(3600*24*3) SumUndoBLKS600
From
V$Undostat
where
begin_time > sysdate - 3
;


column Sumsize600 heading "Avg space|Second (Mb)" format 99999999

Select
count(*)
, sum(UndoBLKS)/(3600*24*3)*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From
V$Undostat
where
begin_time > sysdate - 3
;




prompt -- Max space used

prompt -- ----------------------------------------------------------------------- ---

prompt


column spaceused heading "Max Space used (Mb)" format a40


Prompt

Select ' Since start DB : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
Union
Select ' Last hour : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 1/24
Union
Select ' Last day : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 1
Union
Select ' Last three days : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 3
;


prompt -- Avg space used

prompt -- ----------------------------------------------------------------------- ---

prompt


column spaceused heading "Avg Space used (Mb)" format a40


Prompt

Select ' Since start DB : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
Union
Select ' Last hour : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 1/24
Union
Select ' Last day : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 1
Union
Select ' Last three days : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From
V$Undostat
where
begin_time > sysdate - 3
;



prompt -- Was the desired undo retention retained?

prompt -- ----------------------------------------------------------------------- ---

prompt

Prompt -- Remedy: Increasing the size of the undo-tablespace or reducing the undo retention.

prompt


select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
unxpstealcnt "Unexpired"
from v$undostat
where UNXPSTEALCNT > 0 or
UNXPBLKRELCNT > 0 or
UNXPBLKREUCNT > 0;



prompt -- How often and when does "Snapshot too old" (ORA-01555) occur?

prompt -- ----------------------------------------------------------------------- ---

prompt

Prompt -- Depending on the result: Increase the undo retention

prompt


select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
SSOLDERRCNT "ORA-1555"
from v$undostat
where SSOLDERRCNT > 0;



prompt -- When and how often was the undo-tablespace too small?

prompt -- ----------------------------------------------------------------------- ---

prompt

Prompt -- Remedy: Making more space available for the undo-tablespace.

prompt

select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
nospaceerrcnt "Space Err"
from v$undostat
where nospaceerrcnt > 0;



prompt -- How long did the longest database query take (in seconds) during the period covered by v$undostat?

prompt -- ----------------------------------------------------------------------- ---

prompt

Prompt -- Remedy: Making more space available for the undo-tablespace.

prompt


Select max(MAXQUERYLEN)
From
V$Undostat
;


Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From
V$Undostat
Order
By MAXQUERYLEN
;





Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From
V$Undostat
Order
By UndoBLKS
;

Re: Shrink UNDO tablespace [message #248034 is a reply to message #247704] Wed, 27 June 2007 17:37 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
you did lot of work.

it would seems awesome if you fromatted it.
Re: Shrink UNDO tablespace [message #248131 is a reply to message #247704] Thu, 28 June 2007 02:30 Go to previous messageGo to next message
oraclenewbie07
Messages: 4
Registered: June 2007
Junior Member
This is excellent. Thats a lot of work and understanding right there.
I ran few of the scripts in parts and it makes more sense when I see the output of these scripts. Really appreciate that.
I will try to understand everything I see and get back for my next post. Smile
Thanks much
Re: Shrink UNDO tablespace [message #341650 is a reply to message #248034] Tue, 19 August 2008 16:07 Go to previous message
steppnav
Messages: 1
Registered: August 2008
Location: Tampa, FL
Junior Member

Razz

Vim formatted it nicely when I cut and pasted it.
Previous Topic: ORA-01219: database not open: queries allowed on fixed tables/views only
Next Topic: Creating default roles
Goto Forum:
  


Current Time: Thu Dec 08 00:12:53 CST 2016

Total time taken to generate the page: 0.05901 seconds