If you, like me, like to use (rman) incrementally updated backups, a copy of all datafiles will be present in the Fast Recovery Area. That becomes quite handy if you are in sudden need of extra disk space for your database, and the FRA still has ample space left. By switching one or more datafiles to the copy in FRA, you can very quickly use this extra disk space for your database, while not even having to shut down. I developed a view to generate all the rman commands to execute the switch to FRA, and the switch back to it’s original location and name. Use it at your own discretion.
CREATE OR REPLACE FORCE VIEW rman_switch_datafiles
AS
SELECT tablespace_name
, file_name
, file_id
, '==============================================='||chr(10)
||' switch datafile '||file_id||' to copy in FRA '||chr(10)
||'==============================================='||chr(10)
||'run'||chr(10)
||'{'||chr(10)
||'sql ''alter tablespace '||tablespace_name||' offline'';' ||chr(10)
||'switch datafile '||file_id||' to copy;' ||chr(10)
||'recover datafile '||file_id||';' ||chr(10)
||'sql ''alter tablespace '||tablespace_name||' online'';'||chr(10)
||'}'||chr(10) rman_switch2copy
, '======================================================='||chr(10)
||' switch datafile '||file_id||' in FRA back to original '||chr(10)
||'======================================================='||chr(10)
||'run'||chr(10)
||'{'||chr(10)
||'backup as copy datafile '||file_id||' format '''||file_name||''';'||chr(10)
||'sql ''alter tablespace '||tablespace_name||' offline'';' ||chr(10)
||'switch datafile '||file_id||' to copy;' ||chr(10)
||'recover datafile '||file_id||';' ||chr(10)
||'sql ''alter tablespace '||tablespace_name||' online'';' ||chr(10)
||'}'||chr(10) rman_switchback2original
FROM dba_data_files
WHERE tablespace_name IN ( select tablespace_name
from dba_tablespaces
where contents = 'PERMANENT'
and tablespace_name != 'SYSTEM' )
ORDER BY 1,2,3
/
One remark… if a datafile is switched to the FRA copy, your controlfile is updated , and the original path and filename is lost. So, before switching the datafile to FRA, it’s prudent to save the ‘back to original” stat.
In my opinion this feature is quite handy in case of a sudden lack of disk space.. say an import that generates a bit more data than planned, or a not anticipated growth of UNDO or even TEMP. After a switch to FRA, use the extra space to finish your import, and fix the lack of disk space immediately; then switch your datafiles back.
If you care about the name of your datafile … not everyone uses DB_CREATE_FILE_DEST…. you better save it before the switch to FRA, but in case you are using DB_CREATE_FILE_DEST for your datafiles, you are quite right…. setting DB_RECOVERY_FILE_DEST to be temporarily the same as DB_FILE_CREATE_DEST is an elegant way of making your backup copy to its original location.
BTW. I’ve seen your posts before… nice!
Wow, that is a neat ‘n cool feature to keep in mind. But I have a question about your last remark: Assume you will be running from a datafile copy in the FRA temporarily, you will have to backup that file to another copy, preferably located in the DB_FILE_CREATE_DEST. You can temporarily set your DB_RECOVERY_FILE_DEST to be exactly the same as DB_FILE_CREATE_DEST (from the top of my head, you should be able to do this on session level, within a RMAN script?), take a backup as copy and switch again to the copy. No need to remember where it was, ’cause it is all in the parameters…
Also see my post at http://technology.amis.nl/blog/3371/recovering-very-large-databases-in-the-least-amount-of-time and http://oraclever.blogspot.com/2009/10/using-rman-backup-as-copy-as-base-for.html