Home » RDBMS Server » Server Administration » Shrink UNDO tablespace
| Shrink UNDO tablespace [message #247556] |
Tue, 26 June 2007 06:45  |
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 #247570 is a reply to message #247556] |
Tue, 26 June 2007 07:20  |
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 #247704 is a reply to message #247556] |
Tue, 26 June 2007 22:58 |
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
;
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 19:42:04 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
