To The Point....
Ganesh, September 09, 2001 - 4:20 am UTC
Tom... But do u mean oracle is eliminating Rollback segments in 9i.
Why do i need Non Standard Block Sizes.
Hope to get this cleared.
September 09, 2001 - 8:21 am UTC
An undo tablespace is an alternative -- rollback segments are still present. You will pick whether you want to use an UNDO tablespace (default) or rollback segments.
You might want a non-standard blocksize to support a mixed mode database (mixed OLTP -- they want small blocks with DSS -- they want big blocks).
You would need a non-standard blocksize to transport datafiles from a 4k database to an 8k blocksize database.
You might want a non-standard blocksize to test what the effect of using a 16k blocksize over a 2k blocksize on storage and performance might have without creating an entire new database.
Great Thomas.
Ganesh, September 09, 2001 - 9:15 am UTC
Nothing More to SAY !!!
And Pls Look into the Indian Reprint of u'r book of which we are hearuing rave reviews.
Regards,
Ganesh R
UNDO Tablespace
Neeraj Mehta, February 25, 2002 - 10:28 pm UTC
Its useful for beginers to understand the concepts
I am using UNDO, but still getting rollback errors
Dean, December 02, 2002 - 5:28 pm UTC
I've checked several times. I am running in AUTO mode. I checked V$UNDOSTAT and undo is being generated. I can even see my UNDO tablespace changing size.
I have a package:
if I do
alter package body compile;
no errors
if I save the source code to a file and try
create or replace package body
I get
create or replace PACKAGE BODY pk_mypkg
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3:
'I:\DB\my\DBFILES\ROLLBACK\my_RBS_SMALL01.DBF'
I have taken all the rollback segments except SYSTEM (and their datafiles even) offline. So I know what the error means, I just don't know why I got it. Sorry, this one has me in a pickle.
December 02, 2002 - 7:05 pm UTC
what does
select owner, segment_name, segment_type
from dba_segments
where tablespace_name = (select tablespace_name
from dba_data_files
where upper(file_name) like '%MY_RBS_SMALL01.DBF' )
return (and you have start a TAR with support right?)
I will now
Dean, December 03, 2002 - 9:53 am UTC
Owner Segment_Name Segment_type
SYS RBS01 ROLLBACK
SYS RBS02 ROLLBACK
SYS RBS03 ROLLBACK
SYS RBS04 ROLLBACK
SYS RBS05 ROLLBACK
FYI the database was upgraded from 8.1.7
December 03, 2002 - 10:26 am UTC
don't know -- you'll have to work through this one with support -- sorry.
thanks anyway
Dean, December 03, 2002 - 10:42 am UTC
I found that there's no problem with shorter source code, but when trying to compile a large package (2500 lines), it bombs.
Do I get a sticker if it's a bug!
Thanks.
A reader, December 03, 2002 - 2:23 pm UTC
Hi Tom,
Will rollback segments be deprecated in favor of Undo Tablespace in future releases of Oracle.
December 03, 2002 - 2:24 pm UTC
hard to say but if I had to guess.... (in some future release, i could certainly see it happening yes)
Rollback vs UNDOTBS
Denise, November 14, 2003 - 3:53 pm UTC
Tom
just got my 9i up and running at work on my 'toy' server.
I tried setting up rollback segments(carryover from 8i)
and got the ora-30019 error(illegal Rollback Operation..blah,blah).
So what I am understanding is that Rollback Segments have
been replaced with a tablespace called UNDOTBS...and in
that tablespace are .dbf files(like regular datafiles)..
that function as rollback segments and are automatically managed by the system if UNDO_Management=AUTO in init.ora file.
So if I needed more undo(rollback)storage I would simply create another .dbf file(like in any other Tablespace)
and don't have to worry about "Snapshot too old" errors any more??..the undo will "automatically" shrink and grow as needed...(man that is so cool!)
am I thinking this correctly?
thanks..
November 15, 2003 - 8:56 am UTC
read up on it in the admin/concepts guide.
your concepts are correct.
How to drop undo tablespace
Arun, March 07, 2004 - 11:40 am UTC
hi Tom
by mistake size of the undo tablespace given 20G ,we analysed it when space was not left on the system,we tried to reduce the size but it gives error.we have created a new undo tablespace and mentioned in the init ora file. old undo tablespace took offline and deleted undo datafile (physically) but oracle using old undo tablespace.I have tried to remove it from OEM but it shows some segments dependency and status of those segment is null instead of offline(status seen from dba_rollback_segs).
How can i put these segments offline and drop old undo tablespace
March 07, 2004 - 12:23 pm UTC
ouch -- hope you really didn't do what it sounds like you did.
first, you should have just (assuming 9i AUM here)
a) created new
b) alter system set undo_tablespace=new_one;
c) drop tablespace old_one including datafiles (might have to wait for active transactions to complete)
if you erased the OS files without dropping the tablespace -- well, you could be in a bad way. This is time to call support and get an analysis of where you are and what you've done.
SYSTEM Rollback segment
Shankar, March 07, 2004 - 3:46 pm UTC
(1) Is there any relation between SYSTEM rollback segment and UNDO_TABLESPACE undo segments containing user transactions?
(2) When the transaction tables on the header of undo segments in UNDO_TABLESPACE are created, does oracle use SYSTEM rollback segment to keep before image values of these changes as well? Also, whenever the transaction table is modified in the users undo segments headers, the before images are also saved in SYSTEM rollback segments?
I was just curious what is the role of SYSTEM rollback segment? Thanks as always. You are the best.
March 07, 2004 - 4:52 pm UTC
1) no -- other then one is a rollback segment and the other is a tablespace that contains other rollback segments. It'll not use the system rollback segment except for things it must (mostly exceptional cases and system transactions -- like holding undo for in doubt distributed transactions so as to not jam up the regular undo segments)
see
</code>
http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=%22system+rollback%22&book=&preference= <code>
for more info on them.
2) the internal system transactions to create an undo tablespace and the segments therein, system rollback. after that, its all about "itself".
overwriting it ??
Michal, March 07, 2004 - 4:05 pm UTC
I admit that I havent read a concept so you can consider it as a waste question.
However Im little bit worried about "please retain undo for 20minutes before overwriting it". Lets say that I set a retention period for 20 minutes that should be sufficient for the most of my DMLs.
What happen when I run an unusual, long running DML and it run out of 20 minutes timeout? I suppose that Oracle behaves consistently and raise an error (an equivalent of ORA-01555: snapshot too old (rollback segment too small)" and roll back my transaction.
I dont expect that itll really overwrite another rollback information within an undo tablespace (preventing different transaction to use an undo information for potential rollback), doesnt it?
Thanks.
March 07, 2004 - 4:49 pm UTC
it will overwrite rollback data from COMMITED transactions only. it is "safe".
Possible to specify an Undo Tablespace?
Sea, July 04, 2004 - 9:24 pm UTC
I am using Oracle 9i and I need to do a 33 hours process to populate 2 tables. My actual preference is not to use any undo tablespace if possible, but it seems like I cannot specifiy NOLOGGING for my update and insert statements. I wonder whether it is possible to specify a particular Undo Tablespace for that process so that it will not affect other production users, and how do you do it?
Another thing is, I have 3 undo tablespaces in my database:
SYS_UNDOTBS, UNDOTBS, and UNDO_LARGE. The SYS_UNDOTBS
is autoextensible but it looks like the size stays at 7,480MB without increasing. However, the UNDOTBS seems to be increasing instead whenever a large process is being executed. How do I find out what is happening with these undo tablespaces and which one is the default undo tablespace? Is it possible to assign different undo tablespaces to different roles/users?
Thanks!
July 05, 2004 - 9:56 am UTC
you might have 3 undo tablespaces -- but you have only one undo tablespace of any meaning -- the one that is actually being used
show parameter undo
will help you figure out which one that is
the entire purpose of AUM (automatic undo management) is the AUTO part. If you want to pick a specific rbs -- you have to go back to manual undo managment -- but the net effect will be identical -- one rbs will be used by you and everyone else, the other n-1 will be used by everyone else. I don't see this approach buying you anything.
Unless you are building from scratch a 5 terabyte database using inserts/updates -- I'd really be looking at code that takes 33 hours to run to do inserts/updates. Seems less than optimal.
Drop undo tablespace that segments needs recovery
Sami, June 06, 2006 - 10:00 am UTC
Env : 9.2.0.6 , 3 Node RAC,Sun OS 2.9
<Asktom>
a) created new
b) alter system set undo_tablespace=new_one;
c) drop tablespace old_one including datafiles (might have to wait for active
transactions to complete)
</Asktom>
Tom,
As you mentioned, in step (a) and (b)
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1_NEW
We want to drop this undo tablespace UNDOTBS1 which is not part on any instance.
But when we try to drop the tablespace it is throwing "ORA-01548:
active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace". Is there a way to overcome this?
The datafile for UNDOTBS1 segments got corrupted and in "NEEDS
RECOVERY" state for more than a month. We created new undo tablespace
(UNDOTBS1_NEW) that time and UNDOTBS1 tablespace not required any
more.
But we are getting an error when we try to do step (c).
Alert_log
=======
Tue Jun 6 13:22:40 2006
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
.
.
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Tue Jun 6 13:22:41 2006
Errors in file /opt/oracle/admin/pwpb4/bdump/pwpb41_smon_26754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/oradata/pwpb4/undo01.dbf'
SQL>select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1
_SYSSMU11$ ONLINE UNDOTBS2
_SYSSMU12$ ONLINE UNDOTBS2
_SYSSMU13$ ONLINE UNDOTBS2
_SYSSMU14$ ONLINE UNDOTBS2
_SYSSMU15$ ONLINE UNDOTBS2
_SYSSMU16$ ONLINE UNDOTBS2
_SYSSMU17$ ONLINE UNDOTBS2
_SYSSMU18$ ONLINE UNDOTBS2
_SYSSMU19$ ONLINE UNDOTBS2
_SYSSMU20$ ONLINE UNDOTBS2
_SYSSMU21$ ONLINE UNDOTBS3
_SYSSMU22$ ONLINE UNDOTBS3
_SYSSMU23$ ONLINE UNDOTBS3
_SYSSMU24$ ONLINE UNDOTBS3
_SYSSMU25$ ONLINE UNDOTBS3
_SYSSMU26$ ONLINE UNDOTBS3
_SYSSMU27$ ONLINE UNDOTBS3
_SYSSMU28$ ONLINE UNDOTBS3
_SYSSMU29$ ONLINE UNDOTBS3
_SYSSMU30$ ONLINE UNDOTBS3
_SYSSMU31$ ONLINE UNDOTBS1_NEW
_SYSSMU32$ ONLINE UNDOTBS1_NEW
_SYSSMU33$ ONLINE UNDOTBS1_NEW
_SYSSMU34$ ONLINE UNDOTBS1_NEW
_SYSSMU35$ ONLINE UNDOTBS1_NEW
_SYSSMU36$ ONLINE UNDOTBS1_NEW
_SYSSMU37$ ONLINE UNDOTBS1_NEW
_SYSSMU38$ ONLINE UNDOTBS1_NEW
_SYSSMU39$ ONLINE UNDOTBS1_NEW
_SYSSMU40$ ONLINE UNDOTBS1_NEW
41 rows selected.
SQL> select file_name,TABLESPACE_NAME,status from dba_data_files
where TABLESPACE_NAME like 'UNDO%'
2 ;
FILE_NAME TABLESPACE_NAME STATUS
----------------------------------------
------------------------------ ---------
/u06/oradata/pwpb4/undo01.dbf UNDOTBS1
AVAILABLE
/u06/oradata/pwpb4/undo02.dbf UNDOTBS2
AVAILABLE
/u06/oradata/pwpb4/undo03.dbf UNDOTBS3
AVAILABLE
/u06/oradata/pwpb4/undo01_new.dbf UNDOTBS1_NEW
AVAILABLE
SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace
June 06, 2006 - 11:17 am UTC
sorry - these were "what you should do instead of ERASING FILES WILLY NILLY IN THE FILE SYSTEM"
this is not about getting rid of undo that is in need of recovery - since WE ACTUALLY NEED that undo to make the database "whole" again.
STOP - contact support, do not hit enter, don't touch that keyboard.
rollback to undo
A reader, November 03, 2006 - 2:19 am UTC
My database is running with rollback segments and I want to switch to undo tablespace and I know that how to do this. But in database lot of objects have hard-coded in their statements to use Rollback segments.
[1] Still is the possible for me to switch to UNDO tablespace.
[2] What will happen to those objects which are with hardcoded with to use particular rollback segments. Will they continue to work normally after switching from rollback to undo.
November 03, 2006 - 9:22 am UTC
yes.
if you are on 9i, you'll set an init.ora parameter:
ops$tkyte%ORA9IR2> set transaction use rollback segment foo;
set transaction use rollback segment foo
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ops$tkyte%ORA9IR2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
ops$tkyte%ORA9IR2> alter session set undo_suppress_errors = true;
Session altered.
ops$tkyte%ORA9IR2> set transaction use rollback segment foo;
Transaction set.
In 10g, you have no need, it will just IGNORE it for you:
ops$tkyte%ORA10GR2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
ops$tkyte%ORA10GR2> set transaction use rollback segment foo;
Transaction set.
rollback to undo
A reader, November 13, 2006 - 8:54 am UTC
Apologies Tom,
Not able to understand.
[1] Do developers need to change the objects or
e.g DBA just need to set these parameters in init.ora
set transaction use rollback segment foo
alter session set undo_suppress_errors = true
What is foo in this case ? Is this the name of undotablespace ?
[2] Can I run with UNDO in automatic mode.
[3] What is the signifacance of "set transaction use rollback segment foo" if I am switching to UNDO
November 14, 2006 - 4:00 am UTC
umm, I showed in 9i - need parameter or alter session.
In 10g - need nothing.
foo was the name of a rollback segment.
2) sure
3) the question was "what happens to applications that use set transaction to use a specific rollback segment when using automatic undo". I was simply showing what happens.
why I still got ORA_1555 using big undo tablespace
tony, January 12, 2007 - 12:39 pm UTC
Hi, Tom:
We have set a undo tablespace in 12G and init.ora like this:
*.undo_management='AUTO'
*.undo_retention=10800
pcsa1.undo_tablespace='UNDOTBS1'
Why i still got error about rollback segment? I thought it's replaced by the undo tablespace.
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$"
Me Too.
Richard, May 12, 2008 - 9:55 am UTC
Re: why I still got ORA_1555 using big undo tablespace (post before this one):
I, too, have seen this with Automatic Undo (10.1.0.3 on RedHat)). Oracle Support's advice was to destroy the existing Undo tablespace & recreate. I started another job before getting the chance to do that, so never found out if it was a true fix, or not.
The issue only ever reared its ugly head when I tried to use Flashback. So, the question has to be: how can Oracle get "confused" like this? Only "oddity" was that the database had been upgraded from 9i.
May 12, 2008 - 1:48 pm UTC
you do not say if you had undo retention set properly. It does not matter how large your undo tablespace is if the retention is not set properly
Use of undo tablespace while package compilation
Nilanjan Ray, September 15, 2008 - 8:27 am UTC
Hi Tom,
I have this unusual nagging issue. There is a particular package in our system which when we try to compile takes unusually long (at times more that a couple of hours). We have ensured that there is no process accessing the package or its referenced objects.
v$session wait shows
SQL> ;
1 SELECT sid
2 ,EVENT
3 ,P1TEXT
4 ,P1
5 ,P1RAW
6 ,P2TEXT
7 ,P2
8 ,P2RAW
9 ,P3TEXT
10 ,P3
11 ,P3RAW
12 ,SECONDS_IN_WAIT
13 ,STATE
14 FROM v$session_wait
15* WHERE sid = 15
stowner@STPS2.CHANNEL4.LOCAL> /
SID EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SECONDS_IN_WAIT STATE
---------- -------------------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------- ---------- ---------------- --------------- ------------
-------
15 db file sequential file# 1 0000000000000001 block# 67332 0000000000010704 blocks 1 0000000000000001 5829 WAITED KNOWN
TIME
read
The extent turned out to be one of the undo segments:
SQL> ;
1 select
2 owner,
3 segment_name,
4 segment_type
5 from
6 dba_extents
7 where
8 file_id = &P1
9 and
10* &P2 between block_id and block_id + blocks -1
stowner@STPS2.CHANNEL4.LOCAL> /
Enter value for p1: 1
old 8: file_id = &P1
new 8: file_id = 1
Enter value for p2: 67332
old 10: &P2 between block_id and block_id + blocks -1
new 10: 67332 between block_id and block_id + blocks -1
OWNER SEGMENT_NA SEGMENT_TYPE
---------- ---------- ------------------
SYS IDL_UB1$ TABLE
We are on 10g:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
We are using locally managed tablespaces.
Any idea what might be causing this?
Many thanks
Nilanjan
September 16, 2008 - 10:04 pm UTC
the output was not really readable - does that say 5000 plus seconds in a wait for IO?
but that extent is not undo - that is sys.idl_ub1$ - a dictionary table where we store compiled plsql code....
A reader
A reader, May 19, 2009 - 3:27 am UTC
Hi tom,
Does insert statements generated undo? because i had insert into a table and the insert failed due to unable to extend undo tablespace.
Please clear.
May 23, 2009 - 11:29 am UTC
sure, inserts need to be undone (rolled back)
why do you think they wouldn't?
an insert generates "delete+rowid" at least into undo
an insert into a table with indexes will generate undo for the indexes as well.
ops$tkyte%ORA10GR2> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select used_ublk from v$transaction;
no rows selected
ops$tkyte%ORA10GR2> insert into t select * From all_objects;
49747 rows created.
ops$tkyte%ORA10GR2> select used_ublk from v$transaction;
USED_UBLK
----------
24
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> truncate table t;
Table truncated.
ops$tkyte%ORA10GR2> create index t_idx on t(owner,object_type,object_name);
Index created.
ops$tkyte%ORA10GR2> select used_ublk from v$transaction;
no rows selected
ops$tkyte%ORA10GR2> insert into t select * From all_objects;
49748 rows created.
ops$tkyte%ORA10GR2> select used_ublk from v$transaction;
USED_UBLK
----------
692
24 undo blocks without indexes, 692 undo blocks with
many many many thanks like always
A reader, May 25, 2009 - 4:14 am UTC
a basic question
Rish Gupta, July 08, 2009 - 7:17 pm UTC
Tom,
When I query the v$parameter view
SELECT * FROM V$PARAMETER
WHERE NAME LIKE 'undo%'
I see that that
undo_management =AUTO
undo_tablespace =UNDO_TBS_01
When I run this query
select * from dba_tablespaces
where tablespace_name = 'UNDO_TBS_01'
I see that the segment_space_management is MANUAL.
I'm unable to reconcile the difference between the AUTO undo management and MANUAL segment_space_management.
I thought that whole point of AUM was that one does not need to manage segments, yet the segment_space_management is manual. I'm confused.
Obviously these are 2 different parameters, but I'm unable to grasp the difference. Can you explain?
Thanks in advance.
Rish G.
July 08, 2009 - 7:49 pm UTC
... I'm unable to reconcile the difference between the AUTO undo management and
MANUAL segment_space_management.
...
why? they have nothing to do with eachother.
auto undo management - we figure out how many rollback segments to create, how big they should be, and try not to overwrite data for undo_retention seconds (it is all about undo)
segment space management is how space is managed in a tablespace - either with freelists/freelist groups (manual) or with bitmap freelists stored in the blocks of individual segements (automatic). It has *nothing* to do with undo.
... I thought that whole point of AUM was that one does not need to manage
segments, yet the segment_space_management is manual. ...
that is so far from true, I'm not sure how you would even arrive at that conclusion. Here is what automatic undo management is about:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1469 and this compares manual versus automatic segment space management
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN10065
Perfect
Rish Gupta, July 09, 2009 - 1:47 pm UTC
Tom,
Thank you for your explanation. My confusion arose from wrongly understanding the terms AUTO and MANUAL within their respective contexts. I incorrectly thought that AUTO UNDO MANAGEMENT and AUTO SPACE SEGMENT MANAGEMENT implied the same thing, i.e how space is managed in tablespaces.
Thanks for the lucid explanation.
A reader, July 09, 2009 - 5:48 pm UTC
Hi Sir,
I am using Oracle 9i,manuel undo management,and not using undo tablespace, using old fashion rollback segments.
I am using external tool to monitor oracle and it sometimes raises the below warning:
"Rollback Buffer Wait has increased to a Critical state."
What should I do to stop this warning? Shall I add more rollback segment?
July 14, 2009 - 4:56 pm UTC
you would think that if a 3rd party tool raised a custom error message like that, they would tell you what to do.
How about you create an undo tablespace, set undo management to automatic, set your undo retention to a value that works well for you.
And then you can forget about it, as we will increase the number of online rollback segments automatically as the load increases to avoid these waits.
UNDO segment
A reader, August 03, 2009 - 1:19 am UTC
Hi Tom
what my understanding about UNDO is
whenever a DML or DDL (i am not sure about DDL, please confirm) statement is executed, an undo-statement is generated in UNDO segment corresponding to that statement.
My question is
1. Suppose in a multiueser environment, at a particular time frame, more than one user are using the same undo segment. Suppose some of the users has commited their transactions and some of the users has not yet committed their transactions. Suppose at this moment UNDO buffer, and UNDO file in the disk both becomes full.
My question is, at this moment how oracle decides which entries in the UNDO buffer or in the UNDO file in the disk, will be overwritten.
2.Suppose none of the users has committed their transactions and UNDO buffer and UNDO file in the disk both becomes full. So what happenes, if at this moment a user executes a DML statement. Becauser at this moment there is no space to generate undo statement.
Ani
August 04, 2009 - 1:44 pm UTC
this is bigger than a bread box as we like to say - meaning - it takes many pages of text to cover this properly. For the full story on this, in my words - I'll have to refer you to Expert Oracle Database Architecture.
In short, in an Undo tablespace - there are many undo segments. Each undo segment consists of a circular ring of extents (which are collections of blocks). The undo in a segment is used in a circular fashion - we move from extent 1 into extent 2, into extent 3, ... and then back into extent 1 - IF AND ONLY IF extent 1 has only committed transactions. If extent 1 has non-committed transactions we cannot reuse it - we have to WAIT. If we would like to move into extent 1 but cannot (because there is an uncommitted transaction) we will attempt to
a) allocate a new extent and stick it in the ring - making that undo segment larger. You say "this cannot be done, disk is full". So then....
b) we look at the other undo segments and see if they have any extra extents they are not using right now. If they do - we steal it from them.
c) if they did not, we look around for any extents we can expire prematurely (extents that have only committed transactions and are needed for read consistency based on your undo retention setting - but are not needed to rollback anything anymore) and expire them. Then we can steal them.
If we do (c), you might get ora-1555's snapshot too old as we will have reused undo before the undo retention period expires.
If a, b and c fail to find space - then (assuming you are not using resumable statements...) we fail the transaction that tried to advance the undo segment into extent 1 (which, if it was the cause of us not being able to advance - IT was the uncommitted transaction in extent 1 - that'll fix the problem for everyone :) )
A reader, August 04, 2009 - 9:47 pm UTC
Respected Sir;
Is there any important difference between automatic undo management and manuel undo management,
other than:
In AUM:
.Rollback segments are created automatically according to size of the undo tbs
.Undo retention parameter.
2-)Is round robin method also used in AUM?
August 05, 2009 - 8:48 am UTC
those are HUGE differences making manual and automatic semi "incomparable"
Automatic:
rollback segments are created automatically
and un-created automatically
and are sized dynamically in support of your undo retention (will grow based on a timeframe, not just because we have uncommitted transactions)
can steal (move) extents from rollback segment to rollback segment if possible in support of undo retention
can make undo retention a guarantee, not just a "we will try"
Manual:
you pick the number
you pick the size
you lose, none of the above list under automatic is true for manual, none of it
UNDO
vinod, October 09, 2009 - 6:49 am UTC
Respected Tom,
want a clarification on undo tablespace.if we change any value in database the old value will be stored in the undo tablespace.how the space is allocated in the undo to store the old value.will it be in blocks if so i have created undo with 8k blocksize and if change a value in database which resides in 16k blocksize how undo accomadates the 16k value ?
rollback and Undo
A reader, December 20, 2010 - 8:20 pm UTC
Hi Tom,
Can you please elaborate about the difference between undo and rollback segments? I thought both stores same undo blocks but in different states (Automatic Undo Mgmt and Manual Undo Mgmt respectively). If yes then what is the disadvantage of using Rollback segments?
December 21, 2010 - 6:59 am UTC
undo segments are rollback segments are undo segments under the covers.
we call them undo segments when Oracle creates and manages them with automatic undo management.
we call them rollback segments when YOU create and manage them.
but they serve the same purpose - there are things we can do internally to undo segments that you cannot do to rollback segments (like reassign extents) - but under the covers they are pretty much the same concept.
OLTP data
A reader, January 17, 2011 - 6:35 am UTC
Hi,
I have few basic questions:
1) Does Undo block contain any query or statement to update back to the prior stage? or only previous data is stored there?
eg: if
update emp set ename='Scott' where ename='Harry';
is issued then does undo block contain the
update emp set ename='Harry' where ename='Scott'
like statement?
2) If in a OLTP system any continious update is happening then can we retrieve the data on sudden crash if not committed? (DB in NOARCHIVELOG mode)
If yes then will undo block come into play in this case?
January 23, 2011 - 3:12 pm UTC
1) no, it is binary data that we process in order to figure out what to do.
2) you have to replay the uncommitted transaction, there is no way to reconstruct it - most of it was in memory and not on disk - after a crash, it is quite simply GONE
Query regarding contents of Rodo log,Undo and data block
A reader, January 30, 2011 - 3:25 am UTC
Hi Tom,
I read from somewhere and made some assumptions:-
1) A data block contains a transaction table in the header of it(I'm leaving free,data space of the block out of this discussion). This transaction table holds the undo block# and slot# in which its prior or copy of that data resides before a DML change. Is it right?
2) Undo is also having corresponding status(Active,Committed etc)of that row with its required data in that very slot based on the DML executed. Is it correct?
3) Redo log stores the data block changes,changes in undo block and undo block location details, transaction table for Undo segment. Is it correct? Is the data block changes include the statements executed as well? Is the transaction table of Undo is the same transaction table that undo block holds in discussion #2?
February 01, 2011 - 4:20 pm UTC
1) more or less. The transaction table points to undo where the transactions are listed, then these transaction tables point to the undo blocks that make up that transaction
2) not really. Just think of undo as either being:
a) part of an active transaction
b) part of a committed transaction
The transaction record is what is marked as "done" or "not done", not all of the undo blocks. It is not managed on a per row basis.
3) redo stores redo. Some of the redo it stores is used for UNDO blocks, some for other blocks. It doesn't directly store transactions tables etc - it just stores "redo" and that redo can be applied to any block - an undo block, a data block, whatever.
Undo segments Count
A reader, November 29, 2012 - 10:17 am UTC
IS there any max limit for undo_segments in undo tablespace ?
10g R2
November 30, 2012 - 3:52 am UTC
yes - but you need not worry about it, it is automatically managed for you, the automatic internal limit is just that, an internal limit that the database obeys but isn't really exposed to you and me.
xiaowei, January 18, 2013 - 10:26 pm UTC
it's no difference between rollback and undo,is it correct?8i version is called rollback and 9i is called undo.
January 21, 2013 - 3:25 am UTC
rollback segments contain undo.
undo segments contain undo.
rolling back is performed by applying undo (also known as rollback) data.
undo segments are automatically managed by the database in a special undo tablespace.
rollback segments were manually managed by DBAs in permanent tablespaces.
rollback and undo can be considered synonymous for purposes of discussion
Undo tablespace
Gopal, January 29, 2013 - 10:56 pm UTC
Hello Tom,
In one of my database, I see that my undo tablespace shows 100% used all the time. I see that all the undo segments are in unexpired status and none for active and expired segs. The retention time is set to 60mins. Even, after 60mins of retention time, undo tablespace is not releasing the unexpired segments.
What is causing it to not to release the undo segments as expired or free up space in the undo tablespace?
January 31, 2013 - 9:49 am UTC
if you had a resource to manage - would you give it up, just to have to reallocate it later?
allocating things is expensive.
deallocating things is expensive.
we have to allocate something at least once - but also at most once.
So, once undo space is allocated - we are going to keep it, there is no point in freeing up the space - it isn't like anything else could use it. there would be no point in it.
What perceived problem are you attempting to solve - a full undo tablespace (like a fully allocated TEMP tablespace) are normal, expected and desirable.
want to delete one of the UNDO tablespace data file.
girish, July 11, 2013 - 1:14 pm UTC
Hi Tom,
I want to delete one of the datafile from UNDO tablespace without restarting database. How can I do it.
July 16, 2013 - 4:00 pm UTC
suggest you create a new undo tablespace and switch over to it and then drop the old including contents and datafiles. You'd have to get the datafile totally empty and you really have no control over that with an undo tablespace
if the datafile has any extents in it - you won't be able to drop it.
undo relation with ORA-1555
Chakra, September 10, 2013 - 6:22 am UTC
Hi Tom:
1) Update query started at T time
2) After T1 time, some of its older image in undo has overwritten
3) Now at T2, I issued rollback.
What will happen now? (will it end with ORA-155?)
Please help me on this.
September 10, 2013 - 10:18 am UTC
#2 is not possible. you cannot overwrite undo for an active transaction - never.
undo can only be reused AFTER a transaction has committed. never ever before.
Offline Undo segments
Vignesh, January 05, 2015 - 1:30 pm UTC
When i switch the undo tbs from old to new. I still find that few segments of old undo tbs are unexpired though they are offline. No active transaction using it. It has been 15 days since the switch over but still few segments are unexpired
Rollback and Undo Segment
Mansi Raval, June 03, 2019 - 11:52 am UTC
Regarding this answer fro tom
<<<rollback segments contain undo.
undo segments contain undo.
rolling back is performed by applying undo (also known as rollback) data.
undo segments are automatically managed by the database in a special undo tablespace.
rollback segments were manually managed by DBAs in permanent tablespaces.
rollback and undo can be considered synonymous for purposes of discussion >>>>
What if my database parameter shows both this segment available. which one will be used to store the data until we rollback or commit the transaction.
June 05, 2019 - 12:11 am UTC
"What if my database parameter shows both this segment available"
Not sure what you mean there.
Redo vs undo
Darek, October 29, 2019 - 11:05 am UTC
I still doesn't understand difference between storage of undo and redo.
1. Undo - it's something we need to undo our uncommited changes in case of rollback transaction, it's correct?
2 Redo it's something we need to redo(execute one more time) operation in case of for example using backup/after failure of database, it's correct?
And here is my question. If ASSM is turn on, where undo data and redo data are stored on physical and logical level of database?
3. Undo data are stored in undo file (undo%.dbf) in undo tablespace?
4. Redo data are stored in the same datafiles in the same tablespace as undo (point 3)?
5. Ealier in disqusion Tom said, that for purpose of this talk redo and undo are synonyms. But they are quite different (see point 1 and 2). Could you explain it?
For bettter understanding it will be nice if you could write some statment, which list undo, redo datafiles wiht their locations.
Thanks in advance
October 29, 2019 - 11:43 am UTC
Let me do these in reverse
2 Redo it's something we need to redo(execute one more time) operation in case of for example using backup/after failure of database, it's correct?
Yes. Every single change to anything in the database goes into the redo. Think of it like instructions on a map. If I started in San Francisco and now I am in New York, the redo would let me repeat the journey *exactly* including any "backtracks" I made, thus the redo would be:
- start San Fran
- went to las vegas
- went to Phoenix
- whoops that was a mistake, went back to las vegas
- went to denver
- went to kansas
- went to new york
1. Undo - it's something we need to undo our uncommited changes in case of rollback transaction, it's correct?
Yes. And the redo *contains* all the undo as well. Notice the above "journey" - I made a mistake by going to phoenix and I "rolled back" to las vegas. Even when I undo something, the redo has a record of that.
I also need to use undo for *queries*. Search this site for "read consistency".
Redo vs undo
Darek, October 29, 2019 - 12:41 pm UTC
Thank for you answer.
1. What if the operation "from Las Vegas to Phoenix" wasn't commited? This information will be stored as undo permanently?
a.I thought that undo information are stored only for duration of transaction and eventually retention period of flashback..
b. what's sense of storing uncommited operation history for finished(commited/rollback) transaction?
2. Doe's it mean that undo tablespace contains undo%.dbf files which contains both information (redo data und undo data)?
3. If 2 is correct, wha'ts the format of redo log files (undo%.dbf)? Each operation (record) in this file shouldn't have some flag saying - this operation was commited and this not?
Best regards, Darek
Thanks, for your advice. I'm familar with flashback query :)
October 30, 2019 - 4:27 am UTC
1. What if the operation "from Las Vegas to Phoenix" wasn't commited? This information will be stored as undo permanently?
Not permanently but we don't throw away stuff just for sake of extra work. It will be flushed out in due course.
b. what's sense of storing uncommited operation history for finished(commited/rollback) transaction?
because an uncommitted transaction might *still* have those changed blocks flushed to disk, so another session still needs to see the undo for that uncommitted information to get a consistent read.
Doe's it mean that undo tablespace contains undo%.dbf files which contains both information (redo data und undo data)?
Other way around. Redo logs contain all changes to the database including any changes to undo blocks in the database.
I'm familar with flashback query :)
Dont forget that *every* query is basically a flashback query