FW: backup UNDO Tablespace (dba_undo_extents)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Dec 2009 06:51:08 -0500
snipped to fit
_____
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Tuesday, December 15, 2009 6:19 PM
To: 'wellmetus@xxxxxxxxx'; 'Jeremiah Wilton'
Cc: 'oracle-l List'
Subject: RE: backup UNDO Tablespace (dba_undo_extents)
Nice shell game. But backing up the online redo logs is the first step of
recovery. ALWAYS back up your online redo logs before proceeding with
recovery, preferably on line. That is the only way you get a second chance
if something goes wrong, especially if you are attempting a partial recovery
and resetlogs is one of the steps. "Oops, I meant to recover one more hour"
doesn't have to mean you're screwed.
While I agree with Tom that copies of old redo log files on backup media are
dangerous things to have around, I thought the important step of preserving
the contents of the online redo logs before starting recovery was well known
at least since whenever Lynne Thieme gave her presentation about whatever
the 7.1 version of dataguard was called at whatever OOW was called back then
and I pointed it out.
Possibly there is disagreement of the meaning of the word "backup."
If you do elect to "rotate" undo tablespaces, be quite certain to observe
the status of the former undo tablespace and take into consideration any and
all responsibilities for flashback (all legal variants) before you take it
off line. The need for the undo tablespace that you rotated out of will
persist at least until any active transaction having undo in it has
committed or rolled back, and I'm not sure exactly how the retention
guarantees interlock with rotating undo tablespaces.
I have not rotated undo tablespaces to minimize backup size, and the
compress idea earlier in the thread seems likely to be safer overall, but I
have used rotation into "special" undo tablespaces on BORING (rather than
SAME) databases to isolate undo and i/o for large monolithic transactions
and I never had a problem. But I was leaving all the tablespaces involved
online for the long haul, not flat-lining one. In case you're wondering,
"You can replace the undo tablespace with another undo tablespace while the
instance is running." (several places, including b28320.pdf, page 1-165) is
officially supported, but I'm not aware of an official advisory about just
exactly how long and why you may need to keep the old undo tablespace on
line.
The trick I used was to "rotate" into a "special" undo tablespace and
immediately kick off the known monolith and then immediately switch back. If
you can arrange a time that does not have a constant storm of transactions
that works really well. Various and sundry representatives of Oracle had
heartburn over the whole idea, but it was very effective especially where
you really couldn't stop people from constantly querying the table subject
to the monolithic transaction since the only undo in that undo tablespace
was for that object (oh and I suppose any space management spawned by the
same transaction, and if you inadvertantly got another transaction or a few
in with your monolith.)
But I digress. The point I was really trying to make is it may be hazardous
to quickly discard the "old" undo tablespace if you rotate out of it. (But
I'm not sure of the exact rules and implications. I'm pretty sure it will
stop you from droppping it to prevent you from actually breaking the
possibility of a real rollback, but that doesn't prevent actions at the OS
level.)
Finally, I don't think a giant transaction is required to run through all
the blocks of an UNDO tablespace. I think it keeps old stuff around as long
as it can by design. I think it is just going to happen over time as you
have transactions. But I haven't tested the plethora of combinations of
retention times and guarantees, and maybe someone who actually knows will
chime in. I'm just telling you what I suspect on this last point and I'm too
lazy to look it up or test it right now.
Regards,
mwf
<snip>
Other related posts:
- » FW: backup UNDO Tablespace (dba_undo_extents) - Mark W. Farnham
