When need we UNDO & Need we backup online Redo?
Frank Zhang, September 28, 2002 - 9:11 am UTC
Thanks Tom! I like your answer always with the real example!!!
Another related question:
1)Are 'shutdown abnormal' or system failure the ONLY cases that may leave the uncommitted data in datafiles therefore the rollback/Undo is needed while recovering?
2)I believe that : Latest DATA = Backup DATA + Archlogs + Current Redo.
Is 'backup online redo' a good thing, i prefer multiplexing it than making backup since it's easy to mistakenly restore the redo from the old backup and overwrite the current redo, am i right?
September 28, 2002 - 9:22 am UTC
1) You ALWAYS want to have backups of UNDO information. The procedure here is a "whoops, I've done something really bad, how can i get out of it".
do not, repeat, do not take this as a way to get out of backing up UNDO, just consider UNDO as important as your own tables. Always back it up. ALWAYS.
2) never never never backup online redo logs. They only thing that could result from doing that is you accidently restore last weeks redo over this weeks current redo -- losing committed work during a restore. NEVER back them up (online redo logs) in any case, no matter what.
about UNDO again
Frank Zhang, September 28, 2002 - 9:42 am UTC
Tom, I didnt mean to omit the backup of UNDO. I just wanted to know how oracle does and what is the case that we have no way out without UNDO data in my question1, could you explain it. Of course, in the real world , i will back up the UNDO as well as DB files, control files, etc. Answer to question2 was clear, i disagree with other DBAs approach to backup the online redo and even in some Oracle published books, author suggests to back up online redo, now i'm confident with my oppinion, thanks.
is it possible on 8.1.7.0.0
Alvin, June 03, 2003 - 5:50 am UTC
I've "show parameter undo" my 8i and there was no rows selected.
1. Can the same thing be done on 8i ?
2. Why the need to "bounce" instead of just issuing "alter database open" ?
June 03, 2003 - 7:39 am UTC
UNDO tablespaces are a new 9i feature, did not exist in 8i.
why not bounce? 6 one way, 1/2 dozen the other. I didn't check to see if an alter database open would suffice, but I know a clean startup would so I used that.
Undo tablespace missing...
Alvin, June 03, 2003 - 10:00 pm UTC
hmm... can an 8i db recover the way 9i recovered with the missing rbs tablespace ?
June 04, 2003 - 7:47 am UTC
if you had a NORMAL shutdown, yes.
log seq number
Reader, June 07, 2003 - 1:16 pm UTC
My understanding is that each datafile header contains the most recent log sequence number (LSN) that is written at the time the datafile was being written. Oracle uses this LSN info on datafile headers to know from which log file to start to perform recovery incase i have restored the datafile from backup.
(1) I was wondering which dynamic view could give me the LSN for each datafile in my database. I was querying v$datafile_header and LSN info is missing from that view although it gives me checkpoint_change# for each datafile.
(2) also, control file keeps all of these info. Is there a view that DBA can use to see how these values match with control file info? Thanks.
June 07, 2003 - 2:29 pm UTC
in concept what you say it "sort of true". it is perfectly ok to conceptualize it that way.
v$datafile_header is the dynamic view that is the datafile header. it works by checkpoint change numbers. v$archived_log has the change numbers covered by a logfile.
It makes sense!
Reader, June 07, 2003 - 3:23 pm UTC
Thanks a bunch. After seeing your answer, I refered v$archive_log definition and the doc says, "This view displays archived log information from the control file" and it makes sense now that controlfile has all of the info about archived log files including SEQUENCE#, FIRST_CHANGE#, and NEXT_CHANGE#.
So, Is this how controlfile guides oracle to determine which log file needs to be applied with respect to checkpoint_change# recorded in the datafile header?
Now, i have another question, if i may.
The number of entries the controlfile can keep is related to MAXLOGHISTORY and for my database it is set to 226. Assuming that the database exceeded log seq 226, and say the present log seq number is 300, now the entries are overwritten in the controlfile. Next time if i restored the file for recovery assuming that file header has a checkpoint number corresponding to the FIRST_CHANGE# in the archivelog file (say seq 5), how my current controlfile knows as the entry is overwritten. Thanks in advance. I am learning so much from your answers.
June 07, 2003 - 3:46 pm UTC
it knows it is overwritten cause it is not there?
it'll "suggest" what the file name probably is for the log if it needs it during recovery.
control file record keep time
Jeeva, June 07, 2003 - 6:15 pm UTC
Is there any relation between control_file_record_keep_time and how long controlfile keeps records of archived log history?
dropped both: undo and the file
Serge F., December 20, 2004 - 11:28 am UTC
I'm very interested in such a scenario, because I'm facing a similar problem.
By mistake the UNDO tablespace has been dropped and also the physical file removed.
I can only startup mount the database (version 9.2.0.5).
It is also working with a PFILE not SPFILE.
Could you please, Tom, tell me how to recover my database ?
Thank you.
December 20, 2004 - 1:54 pm UTC
what error are you getting? I just dropped mine and all is "well" -- make sure undo_management is manual, startup -- fix your problem and then restart.
Recovering Undo tablespace with database up & running
Ashok, February 07, 2005 - 1:45 am UTC
Hi Tom,
The Scenerio that you explained is loss of all datafiles belonging to an UNDO tablespace after a Normal shutdown.
I am eager to know how would we proceed for a recovery in case the database (oracle 9i) is up & running and there is a media failure and one of the datafile belonging to the UNDO tablespace is lost (or maybe all datafiles belonging to the UNDO tablespace are lost). What will happen? Will the Database crash? or hang? How would we recover from such a situation with database still up?
I have checked a lot of reference materials but none explains this scenerio? You would be the best person to explain this scenerio (by example, if possible).
Thanks in Advance
February 07, 2005 - 4:47 am UTC
database will not necessarily "crash", but people will get lots of errors (we use rbs for read consistency -- queries will fail. we use rbs for rolling back).
so the database would somewhat unusable for anyone getting assigned or needing the rbs that was affected.
recovery after losing UNDO tablespace (with Database up)
Ashok, February 08, 2005 - 1:13 am UTC
Hi Tom,
Thanks for the reply.I understand if a datafile related to undo tablespace is lost, users will start experiencing problems (in DML & queries). I am very eager to know if I can recover from the "LOSS OF A DATAFILE RELATED TO UNDO tablespace", with the database up so that I do not need to shutdown the database and perform online recovery (restoring the datafile and recover). Is it possible? Does it require special attention and procedure? Would you be able to explain by example?
Many Thanks in Advance
February 08, 2005 - 1:31 am UTC
it is possible, people would just be getting errors while you are doing so.
it is just a segment, system is the tablespace that would bring you crashing down.
for all intents and purposes however, you could consider your database pretty much "down" with a damaged undo tablespace. (you could test this out on your test system -- it is pretty easy, just dd over the undo tablespace and see what happens - be good practice!)
recovery of database
SUDHIR DHAWAN, February 22, 2005 - 8:55 am UTC
LOSS OF A DATAFILE RELATED TO UNDO
Prashant, April 12, 2005 - 8:16 pm UTC
We can not see an entry for RBS datafile in v$datafile table say for file#=9. This entry is there in v$recover_file table. But whenever we are trying to delete that datafile or start the database in restrict mode. We get errors:ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: ''
ORA-27036: translation error, unable to expand file name Additional information: 7202 Additional information: 2 nfdb_dbw0_3659.trc (END)
The strange thing is there is no file path for file# 9 above. Can anyone help me?
April 13, 2005 - 9:00 am UTC
please contact support for recovery assistance.
insufficient data here, you don't want advice, you want to know what to do correctly. work with support, let them get all of the relevant information (eg: HOW you go to this position)
UNDO TBS Corruption due to CTAS NOLOGGING session being killed forcefully
Devopam Mittra, April 30, 2005 - 2:18 pm UTC
Hi Tom,
This might sound a little out of context here, but I could not find a better place to fit it in.
Situation: A CTAS is being executed from a session with NOLOGGING and suitable PARALLEL degree option. DB does not have partitioning. v9.2.0.5
Since the session is taking too much time (~days), I try to kill this session so that the table creation should end.
My observation for the past three instances is that the UNDO tbs fails to clean up the space (release) after this activity.
Tried couple of times, and every time the UNDO tbs started eating up space like anything, and wont release the space despite lowering down the undo retention parameter drastically as well.
My interpretation of the functionality is that as soon as the CTAS session is killed, the rollback for the new extents created should happen and subsequently the space should be released. But how it corrupts the UNDO tbs to such an extent that the only solution is to recreate UNDO tbs , go for a bounce.
Please put in your thoughts on the sequence of events and subsequent behavior of the DB.
regards
Devopam
April 30, 2005 - 2:23 pm UTC
but a CTAS doesn't generate significant undo, in fact, hardly *any* (like tiny)
are you sure about this analysis?
how are you measuring your undo utilization?
UNDO TBS Corruption due to CTAS NOLOGGING session being killed forcefully
Devopam Mittra, May 01, 2005 - 8:41 pm UTC
Hi Tom,
The ideal situation says that the CTAS should have no relation with UNDO tbs per se (but for the small data dictionary modification part).
But in practice, when I tried to kill a CTAS running as a background process from an AIX 5.1 unix box, seems this problem occurs quite frequently.
At the time of UNDO tbs being filled up, the other procedures running on the db start crashing with the following message:
"ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDO_DATA_TS'"
And while checking the tablespace size , I refer to dba_data_files for calculating the space left in the DB.
The problem occurs for huge tables ~50 Gigs.
Hope these findings help you in assessing the problem further.
regards
Devopam
May 01, 2005 - 8:45 pm UTC
does the CTAS "go away" or not? does the transaction it was in "go away"
if not, we cannot reuse the very small bit of data it has allocated in the undo segment - and it is no different then if you log in, insert a single row, don't commit and just sit there (eventually - you'll have a problem using manual UNDO)
so, when you "kill" the ctas, does it go away, does it's transaction go away (more importantly)
UNDO TBS Corruption due to CTAS NOLOGGING session being killed forcefully
Devopam Mittra, May 03, 2005 - 11:29 am UTC
The transaction does go away. No longer you can see the SID, SPID in the session/ transaction tables.
The table is not created and there is no immediate effect on the system at that very moment. But within few hours the UNDO tbs will stop getting released of the space and then there is no way out but recreating the UNDO tbs and bounce the DB.
I know that the whole idea is pretty funny and difficult to accept.
But the fact is that it happens...
A similar example:
There is a huge table with a couple of indexes . ~70 Gigs.
A DDL command is fired to drop few columns.
After an hour and a half, a ^C interrupt command is fired.
This also does not come out in two hours.
In the meanwhile , one can still find the whole table with all the columns through another session.
We decide to get the session killed. Request the DBA to do so.
The session is killed out clean, but the UNDO tbs suddenly starts growing and shows no signs of release, although the active sessions are very few and doing almost no heavy activity.
Hope this helps simulate the problem.
regards
Devopam
May 03, 2005 - 2:30 pm UTC
I'll have to ask you to work with support for I cannot reproduce the issue -- nor have I heard of anything associated.
I don't know how you were measuring things, you would be able to see who has what in the undo tablespace.
but please work with support on this to find the root cause
Backing up UNDO
Cheema, June 15, 2005 - 5:50 pm UTC
Tom,
I am confused about usefullness of backing up UNDO because
--- UNDO does not seem to be backed up by RMAN and still we can recover from any disaster provided RMAN backups are safe and archived redo are available
--- How often to backup undo because let us say I back up every day but my transaction volume forces UNDO to be overwritten every four hours or so. Point I am trying to make is that unlike redo there is no concept of archiving UNDO so invariably some UNDO is bound to be missing.
Regards
June 16, 2005 - 3:36 am UTC
back up, where do you get the idea that undo is not backed up by rman?????
Backing up UNDO
Cheema, June 16, 2005 - 1:58 pm UTC
Hi Tom,
My idea that RMAN does not backup UNDO was a mistake.
Could you kindly answer the second part otf the question namely How often to back up keeping in view that there is no archiving for UNDO.
Regards,
June 16, 2005 - 2:46 pm UTC
ok, second question -- why do you believe there is no "archiving" of undo?
ok, scenario:
total database failure, all disks just gone. you must restore full backup and roll it forward.
Ask yourself "how many archive redo logs do I want to have to apply to the undo tablespace in order to bring it up to date"
Say you generate 50gig of undo a day. Do you want to apply all of the redo to replay that 50gig of changes for 1 day? 2 days? 10 days?
It is all a matter of "how many archives do you want to apply in order to recover"
Backup undo as often as you backup everything else. It is just as important (if not more so!)
Backing up UNDO
Cheema, June 16, 2005 - 2:56 pm UTC
Hi Tom,
Thanks. Cobwebs cleared. I was mistakenly equating UNDO with REDO instead of thinking of UNDO as any other data file which can be recovered from any older copy by applying all the applicable archived logs.
Regards,
OK, but how is it in a cluster System
José Luis, July 18, 2005 - 7:41 am UTC
Hello Tom,
I have this problem but in a 2 Host cluster:
Oracle Enterprise 9.2.0.3 (RAC)
Windows 2003 Server Enterprise.
One Undo is corrupted; the other I think is fine,
I follow these steps from the UNDO owner host,
But when I shutdown after “alter database datafile offline drop”, I can’t startup it (ORA-01092:), only I can mount it.
Thanks Tom.
July 18, 2005 - 8:13 am UTC
please utilize support -- way too little data here to give any sort of safe advice.
These steps where for a NORMAL SHUTDOWN, I doubt that was your case here.
You should have either
a) restored from backup and point in time recovered the undo tablespace
b) called support to get guidance
This advice was not for "undo segment corruption" or anything even remotely close.
stuck with active rollback segment
cto, September 07, 2005 - 8:54 am UTC
thanks a lot
we just got stuck when dropping the tablespace getting the error 1548
idle> drop tablespace undotbs1;
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace
The solution to that was to drop the segment after restarting the instance with the segment offline.
To do so
1) shutdown the instance
2) edit pfile and add the parameter *._offline_rollback_segments="_SYSSMU1$", "_SYSSMU2$", <other segments...>
(if you use a spfile instead of a pfile, you can try to create a pfile from the spfile by only keepping text and dropping binary stuff)
3) startup the instance with parameter pfile=<pfileini.ora>
4) excute a drop rollback segment "_SYSSMU1$"
5) finally drop the tablespace
for more info, see
</code>
http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm http://www.dba-oracle.com/t_fix_undo_log_corruption.htm <code>
September 07, 2005 - 1:40 pm UTC
you did note the somewhat understated CAVEAT that your data integrity is *gone* right?
You need to rebuild that database.
Oh wait, perfect, the second link doesn't even warn you. sigh.
If you use support they will actually fill you in on all of the details, such as:
...
Be aware that the use of _OFFLINE_ROLLBACK_SEGMENTS may lead to the recreation
of the database, depending on whether there were active transactions in the
dropped undo segments. If so, then this may lead to logical corruption, and
hence to the recreation of the database. Be aware that the use of _CORRUPTED_ROLLBACK_SEGMENTS requires the recreation of the database.
.........
so, understated in one and not stated in the other - ouch.
Please use support for things like this - you only have one chance with your data, it is important stuff.
Lost UNDO datafile
Alex, September 15, 2005 - 9:21 am UTC
System crashed and I lost the my undo datafile. Googled "how to recreate a undo datafile in ORACLE" saw this page and I am completed recovered.
Excellent help.
THANKS!
Alex
September 15, 2005 - 9:49 am UTC
you might have just gotten lucky -- this approach here is for a "normal shutdown"
Normally, we *need* that undo information to recover from a system crash - glad it worked but nothing but nothing beats
A BACKUP!!!!
Redo & Undo
Alay, September 21, 2005 - 9:58 am UTC
Hi Tom,
What is the difference between redo and undo? Are both same or not? If not please tell me when and for what purpose they need?
September 21, 2005 - 7:32 pm UTC
redo redoes - it rolls forward.
undo undoes - it rolls back.
redo is used to restore data after a crash.
undo is used to PUT the data BACK the way it was after a rollback.
If you are interested in a much more verbose description - my new book (see homepage) goes over that in detail
Undo lost while active
Enayet, September 22, 2005 - 4:07 pm UTC
<Tom>
Ok, here we go (don't try this at home, well, maybe - do try this at home cause you probably are not running a "real" system at home ;)
</Tom>
Question:
How could I recover if I have lost my undo tablespace while I have active transation (midst of a transation rollback, for example); considering it's a long running DML statement? (Making sitation more grave, if archive logs are missing; shutdown the database and restarting again ...)
I do know oracle reads undo$ entries to find the transations need to be rolled back and reads undo tablespace and rolls back the data as needed.
Since we lost all our undo tablespace (which was running for an 1.5 hours in PQ mode for some 65 millions of rows prior we lost our undo and abrupt db down due to power failure) and archive logs, can we rebuild the database without data loss (perhaps minimal loss) in a consistent state?
Much appreciated.
September 22, 2005 - 6:11 pm UTC
... Making sitation more grave, ....
that doesn't make it more grave, that makes it untenable!
You would have to do a point in time recovery from your backups using whatever archives you had (stopping when you get to a missing one).
how did a power failure "lose your undo"??
and you are working with support right?
Undo lost while active transactions
Enayet, September 23, 2005 - 12:10 pm UTC
You would have to do a point in time recovery from your backups using whatever archives you had (stopping when you get to a missing one).
-- This is the best option we have under our sleeve. Since we lost few logs in between, I was thinking to use logMiner to mine data. But that's very labour intensive proposition!! I was wondering for some more aggressive suggestions (maybe hidden or miracle) Oracle can offer (no pun intended ..).
how did a power failure "lose your undo"??
-- Abrupt power failure caused disarray in our storage media; take a toll on undo, archive logs filesystem.
and you are working with support right?
-- :)
September 23, 2005 - 8:31 pm UTC
I didn't understand the -- :) part - you should be.
If you want "magic", that will be the source of magic, I will not describe magic here. Magic can happen only when you work with support. The problem with "magic" and undocumented stuff is --
well, it is magic and undocumented stuff and has to be looked at on a CASE BY CASE basis. Period.
question
amit poddar, October 12, 2005 - 4:12 pm UTC
Hi,
I still don't understand why does the undo tablespace needs to be backed during cold backups which are done after doing a shutdown immediate.
When I do a shutdown immediate it kills all the sessions which rolls back all the current transactions. Rolling back the transaction would change all the blocks so that their ITL entries do not point to the undo segments. After a shutdown immediate the only case where block's itl entries would point to a rollback segment is in the case of delayed block clean out.
If my line of thinking is correct then we can easily recreate the undo segments after restoring from cold backup.
Could you please tell me where am I going wrong in this whole thought process ?
October 13, 2005 - 10:10 am UTC
technically, you could get away with it - but so what? why make it harder to restore (you know, when you are feeling cold already, but yet you are sweating for some reason).
You would be in noarchivelog mode probably too - as you would lose the ability to do complete recoveries, point in time recoveries.
If you are in noarchivelog mode - then maybe you could, but I would say resoundingly "so what", during recovery, you want things to "go smooth", be easy, not be a 50 step "do this, do that, do the other thing".
If you are in archivelog mode - you need the rollback stuff. Else you would find it "difficult" to roll forward.
Just back it up.
Unrecoverable database because don't have undo
juancarlosreyesp@yahoo.com, October 13, 2005 - 10:31 am UTC
Hi, Tom, please
Which is the possibility to get an
Unrecoverable database because don't have undo tablespace.
In a database about 2-6 gigabytes, with a almost null posibility of a transaction with a lot of undo the moment of the shutdown immediate.
anyway I'm thinking to shutdown, start, shutdown to avoid that situation.
Then I'll do a backup without temp and undo tablespace.
Thanks.
October 13, 2005 - 1:05 pm UTC
don't do it, just don't do it. just keep saying "no, I won't do that"
totally up to you, but please don't come back after trying to do a recovery or anything and say 'help'.
just backup the stuff, just do it.
penny wise, pound foolish as they say.
A reader, October 13, 2005 - 2:01 pm UTC
Thanks Tom, could you please give the foundations to your position.
And please the answer to the question ;)
Is posible to get an unrecoverable database without temp and undo datafile?
My explnanation
In the way we work compressing and uncompression full backup (this will save a lot of time), AND our database are very small 2-6G.
I understand definetively is not too wise, I understand this will be mad for a big database, but I think for our reality and in the way we work is "reasonable".
Now I'm analyzing the idea, but I need fact about the situation of getting a unrecoverable database, please Tom I ask you.
My theory is: If I do a backup without undo and tempfile, following some procedure (for exapmle shutdown, start, shutdow) (somthing like normal shutdown, maybe using some trigger, etc. etc.) I will not need to backup temp and undo file.
Because I always will reach a situation where I will can recreate undo and tempfile files.
If there is some transaction will be too small to create problem.
Thanks Tom.
October 13, 2005 - 4:18 pm UTC
if you are in archivelog mode, not backing up undo is going to remove your ability to fully recover or point in time recover, yes. Period.
I would not under any circumstances recommend your approach, period.
That is all I'm going to say. I think I've made my points above.
more questions
amit poddar, October 13, 2005 - 3:28 pm UTC
Hi,
I am not trying to not backup undo tablespace. My questions are just for my understanding of how oracle works.
So lets say I do a cold backup without the undo tablespace after doing a shutdown immediate on the database. So there is possibly some blocks whose ITLs point to the rollback segment headers because of delayed block clean out.
Now when I restore without the undo tablespace and recreate the undo tablespace, and then try to read data from the blocks which point to the undo segments (which do not exist) How will oracle resolve this ? will it give an error or assume that data was commited (even then it cannot decide the commit SCN since the undo segment is non existent)
October 13, 2005 - 6:18 pm UTC
I suppose there could be a change of a 1555 due to delayed block cleanout and the undo having been removed - never tested that theory though (sort of like you can get a 1555 on a READ ONLY tablespace - that I have tested...)
A reader, October 13, 2005 - 5:00 pm UTC
Thanks Tom, your advice is correct.
If I don't do undo backup I could have reapplying redo logs in recovery, what is important for me .
Anyway I think there should be some kind of shutdown like shutdown immediate no_undo_pending no_temp_pending?
To allow backups without undo and temp tablespace, which allow redo log forward application and would automaticaly recreate redo and temp tablespaces.
Backing up this files can take a important amount of time.
October 13, 2005 - 9:08 pm UTC
(it is only 2-6 gig of data - i drag that much data back and forth in simple files these days....)
Anyway, in ARCHIVELOG MODE - there is quite simply *no reason* to backup cold - shutting down is such a waste - therefore the duration of the backup (especially using rman) is sort of meaningless. And should hopefully be measured in minutes with 2-6 gig of data.
A reader, October 14, 2005 - 8:37 am UTC
Your are right Tom, we are going to start to use rman in the next release, I planned that several months ago, but we didn't had time to test, I know is much better.
We have started to full cold backups, and then archivelogs, but not all the customers are intesrested.
But any way, you must know i'm the worst dba's nightmare.
I am a developer doing dba's work
muahahahaha
So I don't give full time to database administration, only when I got a free time.
:)
Great help!
Seetha, November 20, 2005 - 2:24 am UTC
I was doing some load testing of our application, with all the table spaces set to extend unlimited. The 69GB disk maxed out and I had to kill oracle. While administering the tablespaces and moving the files around I got into a predicament.
Followed your instructions pretty much, but kept getting ORA-01157 errors for many DBF files I had added to other tablespaces during my load test. After dropping all of them one by one, and after several shutdowns/startups my database is good to go. Thanks!
Thanks
A reader, January 13, 2006 - 9:03 am UTC
I was desperaded;
Drop "NEED RECOVERY" undo segment and tablespace
A reader, February 01, 2006 - 7:34 pm UTC
Save my day!
I have lost a datafile on one of the redo, and was unable to drop that undo tablespace due to ORA-01548: active rollback segment '_SYSSMU19$' found, terminate dropping.
Thanks so much Tom
Thanks Tom!!!
Mark Dickinson, March 17, 2006 - 3:25 pm UTC
I had a very similar issue recently with a few corrupt blocks in the UNDO tablespace. I tried for a few hours on my own to fix it, to no avail. Once I found your procedure above, I followed it to the letter and....problem solved.
As a DBA with only about a year of full-time experience, I find your site the most helpful.
James Xia, May 02, 2006 - 12:59 pm UTC
We are not backing up the undo tablespace by accident. However, I try to follow this thread to recover database with no lock. Hence I have created iTAR 5383299.992. The support analyst seems to belive that without a good backup of undo tablespace, I will not be able to open the database which is true to us. So who is to believe. Do we really need to recreate control file? we are on RDBMS 9.2.0.7
May 02, 2006 - 4:02 pm UTC
this question started with "and we shutdown normal"
meaning "a clean shutdown - we don't need undo upon restart"
soooooo - what about your backup - what state was it in.
and how didn't you discover this during your recovery tests???? I mean, we don't test backups, but we ALWAYS test the ability to recover don't we???
New Recovery Question in this thread.
Avi, May 02, 2006 - 5:44 pm UTC
Hi Tom,
First of all my apology for starting new question in this thread but I don't know how to start a new thread. Really I don't know. Anyway my question is - Suppose my database is up and needs to be down because of some server problem say on 7 pm. When server gets up somebody changes system clock back to ,say 2 pm. Now a user truncates a tables on 5 pm. and current time on server is 9 pm. Suppose archive logs were generating very fast on database so it is not known till what sequence no. recovery is needed. User has told that it happened at 5 pm so I have to go for time based recovery. So my questions are -
1. How to go for time based recovery in this scenario because new archive logs will be having same time as old archive logs had?
2. How will oracle recognize which archive logs it need for recovery?
Please elaborate in detail if you have some time for this.
Thanks.
May 03, 2006 - 1:41 am UTC
I've always thought the little box on the home page:
<quote>
Sorry I have a large backlog right now, please ask a question later
See the "other resources" tab above and to the left for recommendations of places to go to get answers to questions!
</quote>
to be unambigous :)
when using time based recovery - given your utter mess of a situation, you would have to sort of figure it out (eg: your archives likely have a timestamp representing their creation - read them, find the 5pm you want to use) (eg: use logminer and peek at the logs).
I don't know how a user truncates a table at 5pm but it is 9pm on the server given it was 7pm a second ago.
But actually, since nothing funky was done with the clock until 7pm - a time based recovery to 5pm would be all you need to do here - restore backup taken before 5pm, recover until 5pm.
Time based Recovery
Avi, May 03, 2006 - 4:23 pm UTC
Hi Tom,
May be I was unable to put my question correctly. Please consider this way:
1. Server went down at 7 pm, monday(database time is same as server's time).
2. Server started up with new time 2 pm, monday as somebody reset the clock and put it back(actual time is 7 pm, monday now).
3. User truncates table at 5 pm, monday(server time) but did not informed(actual time is 10pm, monday).
4. When user informed it was 9 pm,monday (actual time is 2am, tuesday)
Now I have to recover database till 10 pm, monday(actual time) but on Database, time was 5pm, monday. I have archive logs which have actual timestamp and archive logs created after server reboot which also have same timestamp (as server's time was changed to back).
So here I have many archive logs which have timestamp of 2pm-7pm. Few of them got created when database was running with actual time (log seq. no. 2001-2500) and few which got created when database was started with back time (log seq. no.2501-2800). Though they hace differnt log seq. no. but timestamp is same.
Hope this time I'm able to put my question correctly. Your patience will be appriciated.
Thanks.
May 03, 2006 - 4:41 pm UTC
find the archive you want to go to (you have them all, they have timestamps, you know what one you want to get to - dont you?)
you can sort the archives by name to get them in actual creation order - you have their timestamps, you know when clocks were messed with..
Recover corrupt block on UNDO tablespace
Jeremias, December 18, 2006 - 7:19 am UTC
Hi Tom,
First of all, thanks for your help and finally I have a problem with UNDO tablespace. Trying to make a backup with RMAN the log file says me that there is a problem with the UNDO TBS("ORA-19566 exceeded limit of ... ") and operation breakss down. I know that I can up my tolerance for "corrupt blocks" but...
I tried to recover the UNDO tbs but the backup is too old. Can I drop the tablespace and re-create it? Which is the impact for doing this?
Thanks
December 18, 2006 - 8:50 am UTC
stop typing, do not type anything more..
contact support and work through your options right now - stop typing.
UNDO MANAGEMENT MODES
Ik, May 17, 2007 - 8:35 am UTC
Tom,
I wanted to know how the following scenario would get handled.
start a new instance in nomount with init parameter undo_management=auto and create database without specifying an undo tablespace (and datafile) - iam not on OMF. Will there be a failure or will my subsequent alter database open fail?
Got confused - some places i read that database would automatically create an undo tablespace sys_undotbs and few other places i read that there would be a failure.
This is 10GR2.
Please let me know.
Thanks,
May 17, 2007 - 11:28 am UTC
try it, let us know.
getting offline/online undo tablespace datafile
Lukasz, August 28, 2007 - 1:44 pm UTC
on Oracle 10.2.0.2 in automatic undo mode
i exec
ALTER DATABASE DATAFILE '/home/oradataosf/OSFDEVEL/undotbs01.dbf' OFFLINE;
I know it's stupid it's datafile of my undo tablespace,
now database return some errors like
1. ORA-01113: plik 2 wymaga zastosowania przywracania nosnikow
2. ORA-00603: ORACLE server session terminated by fatal error
Im interested in getting back my application data.
Database is open but expdp return error:
UDE-00008: operation generated ORACLE error 22303
OCI-22303: type "SYS"."KU$_STATUS1020" not found
I have contact with metalink but we can't find way.
now ok
Lukasz, August 28, 2007 - 3:02 pm UTC
ok
simply shutdown abort
and then open database works ok
even no recovery needed
UNDO corruption
Sabino, September 19, 2007 - 12:20 pm UTC
On alert log file I have frequently error
ORA-01578: ORACLE data block corrupted (file # 48, block # 148935)
ORA-01110: data file 48: 'E:\ORACLE\ORADATA\SESTANTE\UNDOTBS02.DBF'
So I was thinking that I would drop, then recreate the undo tablespace, so I was able to create the placeholder undo tablespace (called it undotbs2).
And the "alter system set undo_tablespace = undotbs2" went OK.
But when I went to drop undotbs1, it complained with....
ORA-01548: active rollback segment '_SYSSMU4$' found, terminate dropping
tablespace
To check for database corruption I used export (full=y)
The SQL 'alter system dump datafile 48 block 148935;' cause a crash of db.
Does not exist a method in order to force the cancellation of the tablespace of UNDO?
September 19, 2007 - 1:19 pm UTC
please utilize support.
Another way
Saurav Mishra, April 05, 2011 - 1:59 am UTC
Dear Tom
what I did was:
1.shutdown immediate
2.startup mount
3.alter database datafile '/home/oracle/oradata/undotbs.dbf' offline;
4.Alter database open.
5.Create new undotablespace and set it as the default undo tablespace.
We can use this process as well isn't it?
April 12, 2011 - 12:10 pm UTC
Not if that was an ACTIVE UNDO tablespace - there is stuff in there we need to put the database right.
Same advice as above applies fully.
Saurav Mishra, April 05, 2011 - 2:00 am UTC
Dear Tom
what I did was:
1.shutdown immediate
2.startup mount
3.alter database datafile '/home/oracle/oradata/undotbs.dbf' offline;
4.Alter database open.
5.Create new undotablespace and set it as the default undo tablespace.
We can use this process as well, isn't it?
a bit different scenario
andrey, May 29, 2011 - 9:18 am UTC
Greetings,
I followed most of the posts here regarding the recovery of a corrupted UNDO TBS, and i had a bit different outcome, though the DB was back and running successfully. so for.
The Linux VM server that housed the DB bounced a couple of times during the night (yeah, i know...) and as a result the undo tbs datafiles became corrupt (probably due the fact that heavy delta was running when the server bounced). I had a relatively updated backup (cold, few hours old) but it was a DVLP ENV, and restore would take like 4-5 hours so i decided to "fix" the undo tbs manually, just for kicks.
set undo to manual, set DBF to offline but could not drop TBS, because of "active rollback segment" found. checked which were corrupt and had to drop them manually. so far nothing new, but after i dropped all the corrupted segments (10 total) i noticed that the DB started to work as intended again. so i backed on undo_management to auto and brought the DBF online and the DB was working again, but the only problem now is that i deleted like 10 rollback segments (had 20 total), and my question is how it can impact on the DB? should i manually create them again? or, as i have seen in the reference, when working undo_management=auto rollback segments do not really matter?
Thanks in advance.
May 31, 2011 - 9:49 am UTC
please utilize support for something like this.
You will likely want to recreate this database from scratch as there are likely internal inconsistencies that will rear their ugly heads at the worst possible moments.
Lost Datafile and now unable to start Database
Nilesh Kumar, November 15, 2011 - 3:45 am UTC
Hello Tom,
We had a power fluctuation, and so my UNDO datafile got corrupted, and i was getting error "ORA-01110: data file 2: '/archlogs/oradata/undotbs01.dbf'", so when i googled this error, i got to this page and tried to drop the undo_tablespace undotbs1, but got another error message "ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace", so i followed the comments above the reviewer "Lost UNDO datafile September 15, 2005 - 9am Central time zone Reviewer: Alex from USA" and followed the link "
http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm", then i created second undo_tablespace undotbs2 and dropped the first (undotbs1).
but now, my database is not coming in open mode, though it is getting in mount mode, and is giving error "
http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm" The output from alert log is as given
bash-2.05# tail -60 bdump/alert_DOTSOFT.log
Tue Nov 15 11:34:21 2011
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Nov 15 11:34:21 2011
ARC1: Archival started
ARC1: Thread not mounted
Tue Nov 15 11:34:21 2011
ARC0: Thread not mounted
Tue Nov 15 11:34:21 2011
ALTER DATABASE MOUNT
Tue Nov 15 11:34:25 2011
Successful mount of redo thread 1, with mount id 3641993661.
Tue Nov 15 11:34:25 2011
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Nov 15 15:23:38 2011
alter database open
Tue Nov 15 15:23:38 2011
Beginning crash recovery of 1 threads
Tue Nov 15 15:23:38 2011
Started first pass scan
Tue Nov 15 15:23:38 2011
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Tue Nov 15 15:23:38 2011
Started recovery at
Thread 1: logseq 12143, block 3, scn 2673.1781980959
Recovery of Online Redo Log: Thread 1 Group 2 Seq 12143 Reading mem 0
Mem# 0 errs 0: /redogrp2/oradata/DOTSOFT/redo02a.log
Mem# 1 errs 0: /redogrp3/oradata/DOTSOFT/redo02b.log
Tue Nov 15 15:23:38 2011
Ended recovery at
Thread 1: logseq 12143, block 3, scn 2673.1782000960
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Tue Nov 15 15:23:39 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 12144
Thread 1 opened at log sequence 12144
Current log# 1 seq# 12144 mem# 0: /redogrp1/oradata/DOTSOFT/redo01a.log
Current log# 1 seq# 12144 mem# 1: /redogrp2/oradata/DOTSOFT/redo01b.log
Successful open of redo thread 1.
Tue Nov 15 15:23:39 2011
ARC0: Evaluating archive log 2 thread 1 sequence 12143
ARC0: Beginning to archive log 2 thread 1 sequence 12143
Creating archive destination LOG_ARCHIVE_DEST_1: '/archlogs/oradata/DOTSOFT/archive/1_1214
3.dbf'
Tue Nov 15 15:23:39 2011
SMON: enabling cache recovery
Tue Nov 15 15:23:39 2011
ARC0: Completed archiving log 2 thread 1 sequence 12143
Tue Nov 15 15:23:39 2011
Errors in file /oracle9/admin/DOTSOFT/udump/dotsoft_ora_26357.trc:
ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], []
Tue Nov 15 15:23:41 2011
Errors in file /oracle9/admin/DOTSOFT/udump/dotsoft_ora_26357.trc:
ORA-00600: internal error code, arguments: [25012], [1], [2], [], [], [], [], []
Tue Nov 15 15:23:41 2011
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 26357
ORA-1092 signalled during: alter database open...
Also i an using Oracle9i, and now-a-days their is no help from oracle for 9i, atlest in part of world. So pl don't advice me to go to oracle for help.
November 15, 2011 - 8:49 am UTC
Your database is toast.
Oracle 9i is still supported/supportable - so please don't tell me not to tell you to go to support.
For that is exactly what I'm going to suggest (and I suggest you try not to use that other site - you probably have done more damage by doing that). Oh wait, I already said that the first time someone posted that link.
If you do not have a backup, you are probably dead in the water, please utilize support - they might be able to get you to a point where you can scrape out some data and rebuild your database from scratch.
Lost Datafile and now unable to start Database
Nilesh Kumar, November 19, 2011 - 5:51 am UTC
Hello Tom,
Though your last response was not as expected, but such is the faith i have developed in you over time, that i had to come to continue my question.
Now after much hardwork i found that my new undo_tablespace "undotbs2" was offline
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
FILE# STATUS ERROR REC TABLESPACE NAME
----- ------- ---------- --- ---------- ------------------------------
57 OFFLINE YES UNDOTBS2 /archlogs/oradata/undotbs02.dbf
so i made it online
alter database datafile '/archlogs/oradata/undotbs02.dbf' online;
and now, but now this database file undotbs02.dbf needs recovery, with sequence number #12142, but i don't have this sequence no archive file.
SQL> recover database;
ORA-00279: change 11482229543162 generated at 11/15/2011 10:54:23 needed for
thread 1
ORA-00289: suggestion : /archlogs/oradata/DOTSOFT/archive/1_12142.dbf
ORA-00280: change 11482229543162 for thread 1 is in sequence #12142
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/archlogs/oradata/DOTSOFT/archive/1_12144.dbf'
ORA-00310: archived log contains sequence 12144; sequence 12142 required
ORA-00334: archived log: '/archlogs/oradata/DOTSOFT/archive/1_12144.dbf'
Also, i do have export backup, but i don't want to just apply the backup and be back to from where i started (i mean corrupted untotbs1 undo_tablespace). And i dont mind losing data in undo datafile.
So pl suggest what can be done.
November 21, 2011 - 1:40 pm UTC
Also, i do have export backup,
no, you have an export. exports are not backups. Not even close. backups are backups, exports are logical data extracts that can sometimes be used to reload some data via inserts. You cannot "restore" an export.
YOU DO MIND losing the data in the undo datafile - because WE MIND. Your database is toast.
I told you what to do. Please do that. Do not read random web sites (ESPECIALLY when you already read here that the 'advice' given by that wed site was particularly dangerous and would lead to further problems - I have no clue why you read what I wrote and then did that anyway?)
Please contact support and they'll try to help you scrape some of your data out of your database.
You can have all of the faith you want - but when you've done what you did here, well, faith ain't going to cut it. I don't take a faith based approach to databases.
Getting Error ORA-01578: ORACLE data block corrupted (file # 2, block # 247430)
Nilesh Kumar, February 03, 2012 - 6:30 am UTC
Dear Tom,
While i was working on the system, then suddenly i got error
ORA-01578: ORACLE data block corrupted (file # 2, block # 247430)
ORA-01110: data file 2: '/archlogs/oradata/undotbs01.dbf', and their was no interruption in the system, but am still getting this error, also i have checked dba_rollback_segs for corrupt_tablespace but all the segments are showing online, and non are showing 'NEEDS RECOVERY', so pl guide what has gone wrong in this case.
Also i had a cold backup and had solved the above problem with that backup.
February 03, 2012 - 10:10 am UTC
please utilize support for an obvious support issue like this. Please do not take forum advice on this one - you could cause serious irreparable damage. This is something you want to do after support collects all of the supporting information.
Do NOT follow directions you see on any web site, forum, etc. You'll end up messing it up worse than it already is.
Recovered without UNDO?
Khalid, March 13, 2012 - 3:43 pm UTC
Tom,
I am an Oracle user for several years now, but I still don't understand something seemingly simple.
Yesterday, I performed a simpler test than what you have, but I don't understand how Oracle did it
1) took an online RMAN backup of a small database (Time=t1)
2) Generated some transactions, one of them I left uncommited
3) shutdown abort
4) Renamed the datafile corresponding to UNDO
5) Mounted, Restored, recovered, alter database opened, didn't have to do any resetlogs (Time=t5)
How did the Roll Forward + Rollback happen?
Roll Forward I understand, the REDO and the archives were sitting right there, but where did the UNDO come from to do any rollback at Time=t5?
/**************** Lost UNDO, then recovered test ******
RMAN> recover datafile 2;
Starting recover at 03/13/2012 01:06:16
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
starting media recovery
media recovery complete, elapsed time: 00:00:08
Finished recover at 03/13/2012 01:06:25
RMAN> alter database open;
database opened
****************/
March 13, 2012 - 5:14 pm UTC
we recovered the undo from the redo. You backed up your undo tablespace, you restored it, we recovered it using redo and then rolled back.
undo is protected by redo, anything we cache in the buffer cache would have to be.
Great
Khalid, March 13, 2012 - 8:38 pm UTC
As usual, I learn something new every time I ask.
help...
cosmin, July 16, 2012 - 7:47 pm UTC
Hi,
We have a
Windows Server 2008 R2 Enterprise x64
Oracle 10.2.0.3
Adempiere - ERP
1. We had a power breakdown at 03.00 AM.
2. At 07.30 AM we started the server
3. At 08.00 our colleagues observed that the ERP is not functioning
4. We started Adempiere
5. ERP was not able to connect to Oracle DB
6. We entered EM Console and we checked the DB status
7. The listener was functional - up and running
8. DB was shotdown in progress
9. We tried to abort shot down via SQL Plus using command :
shutdown abort
10. Nothing happen
11. We tried shutdown - no result
12. We tried shutdown immediate - no result
13. Then we made Windows Shutdown from start menu
14. After restart of the server we checked in Windows Services.MSC the Oracle running services
15. Oracle JoobscheduleORCL was disabled, the rest were running. We set this one on automatic
16. The listener was functional - up and running
17. DB was shotdown in progress
18. We tried to abort shot down via SQL Plus using command :
shutdown abort
19. Nothing happen
20. We tried shutdown - no result
21. We tried shutdown immediate - no result
22. We tried to restart the Oracle Services with no result
23. We searched on the forum.oracle.com and we found out that the problem could be that the undotablespace is corrupted.
24. We tried to fix the table
25. We used the following command line :
alter database datafile 'xxxxx\undotbs01.dbf' offline drop;
http://www.dbas-oracle.com/2011/06/recovery-from-undo-tablespace-block.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582
26. The current situation is :
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> startup
ORA-01031: insufficient privileges
July 17, 2012 - 8:06 am UTC
ugh, I don't believe it. why - WHY did you do that.
That only works after a *normal* shutdown. We need that undo.
step 23 should have been "because we didn't know what was going on, we opened a P1 down production database SR with support"
You have pretty much toasted your database - I hope for your companies sake you can restore a backup you have and roll it forward.
googling around trying to fix something like this is just about the last thing you ever want to do.
Your current situation is you have removed from the database an undo segment that is NEEDED by the database to open. You have pretty much toasted this one.
Time to practice your recovery techniques.
Even if you do get this database open, the only thing you'll be able to do reliably (if that) will be a full export and a rebuild of the database. And the consistency of your data will be questionable.
UNDO Recover question
Reader, July 30, 2012 - 5:09 am UTC
Hi Tom,
As you have mentioned in your first response of this Thread - The way to make a
9i Database stand up again on its feet after it detects problem with UNDO TBS while startup -
1.Changing the UNDO management to Manual from AUTO
2.Dropping the UNDO Tablespace
3.Adding a new UNDO Tablespace
4.Changing the UNDO Management to AUTO
Suppose the Database is running(RMAN Full Hot backup every evening, all archive logs available) at peak business time - Lots of Transactions are being
executed by the DB and somehow the UNDO gets corrupted due to which DB goes down.
How should I approach this situation?( I have Last day's RMAN Hot Backup with all the archive logs)
Would the restore database followed by recover database will work in this case?
Not sure if the Steps 1-4 will work in this case as the UNDO TBs is corrupted while DB was running.
Thanks,
Ankit
July 30, 2012 - 12:07 pm UTC
this is ONLY for a "normal shutdown database". It will not work on a 'crashed' instance (shutdown abort)
this is NOT a fix for a corrupted undo tablespace. it was "I have a cold backup of a shutdown normal database and I'm missing my undo". That is the only thing this is good for.
if you have corrupted undo due to some sort of media failure, you would just restore from your last hot backup and recover as normal.
do not use this for anything other than "my undo tablespace is missing from my cold backup of a shutdown normal database"
undo tbs cannot be dropped due to ORA-00376
Tibor, February 27, 2016 - 11:01 pm UTC
Dear Tom,
Thank you for sharing your knowlegde and for this great site.
I have a tiny test 11g db in noarchivlelog mode. I would like to reproduce your steps, but I got stucked as follows:
After shutdown immediate, I removed undo01.dbf from OS (the only datafile of undotbs1). Then:
SQL> connect / as sysdba
SQL> startup
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change string 11.2.0.1
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_management = manual scope=spfile;
System altered.
SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
SQL> alter database datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
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: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
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: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
... SO, the database is up, but I cannot get rid of the deleted datafile.
Status of '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' in V$DATAFILE is 'RECOVER'.
I even created another undo tbs and set that as undo_tablespace and set undo_management back to "auto", but still undotbs1 cannot be dropped.
Colud you please tell me how to drop the tablespace?
Thank you,
Tibor
Perfect
Kiril Kirilov, March 14, 2016 - 7:51 am UTC
I have just tested it with 12.1.0.2.0.
It is working perfect.
Tom if it is not a problem for you could you show to us, he same situation lost UNDO, no archivelog mode but system was aborted?
March 14, 2016 - 11:51 am UTC
Notice all the examples are here are after a normal shutdown.
Things are probably going to go astray if you do this with an abnormal shutdown, because you'll have inflight transactions (and hence undo for them)