Re: backup UNDO Tablespace (dba_undo_extents)
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Dec 2009 07:07:51 -0800 (PST)
Backup 'compression' (only backup populated or once-populated blocks) applies
to datafiles, and last I checked the UNDO tablespace is created with a specific
command (CREATE UNDO TABLESPACE ...) which, in my mind, tells Oracle this is
not an ordinary tablespace. As such I would expect RMAN to backup the entire
UNDO tablespace regardless of how full or empty it may be; this is evidenced by
the original post to this thread. It's only logical (in my mind, anyway) to
backup the entire UNDO tablespace as recovery may require the entire UNDO
tablespace even though transactional activity in the database may not.
David Fitzjarrell
________________________________
From: Mark W. Farnham <mwf@xxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Wed, December 16, 2009 6:51:08 AM
Subject: FW: backup UNDO Tablespace (dba_undo_extents)
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:
