How to switch datafiles to FRA and back again

Harry Dragstra 2

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
SELECT tablespace_name
     , file_name
     , file_id
     , '==============================================='||chr(10)
     ||' switch datafile '||file_id||' to copy in FRA  '||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)
     ||'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.

2 thoughts on “How to switch datafiles to FRA and back again

  1. 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!

  2. 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 and

Comments are closed.

Next Post

Rman 11gR2 changes tag labeling of the inc backup in incrementally updated backups

In (rman) incrementally updated backups, only incremental backups are done after the first full backup to the Fast Recovery Area. From 11gR2 on the incremental backup pieces will get the same tag as the datafile copies, and that’s actually different behaviour from pre- 11gR2 versions of the database. I stumbled […]
%d bloggers like this: