Re: backup UNDO Tablespace (dba_undo_extents)

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_at_rsiz.com>
To: oracle-l <oracle-l_at_freelists.org>
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_at_rsiz.com ] 
Sent: Tuesday, December 15, 2009 6:19 PM
To: ' wellmetus_at_gmail.com '; '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>


      

--
http://www.freelists.org/webpage/oracle-l