Change Oracle Flash Recovery Area location

November 3, 2010  |  dba, security

Oracle’s default Flash Recovery Area (FRA) location default location is at ORACLE_BASE/flash_recovery_area. Most of the times you want to take it from the default storage where you install oracle software to another storage device. That’s what’ we’ll see today. But first…

Flash Recovery Area feature allows you to set up a location on disk where your database can create/manage different kinds of backup and recovery files for you. Using a FRA eases the database administration by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery, and erase them when they are no longer needed to restore your database and/or space is needed for some backup and recovery purpose.

To move yours database FRA to a new location,  start SQLPlus to modify the DB_RECOVERY_FILE_DEST initialization parameter.


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/flash_recovery_area' SCOPE=BOTH SID='*';
-- or if you use ASM:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';

After you change this parameter, all new FRA files will be created in the new location.

The permanent files (control files and online redolog files), flashback logs and transient files can be left in the old FRA location. The database will delete the transient files from the old FRA location as they become eligible for deletion.

For FLASHBACK logfiles to be able to to pick up the new DB_RECOVERY_FILE_DEST location, the flashback option needs to be restarted like this:

SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

If you want to move the other permanent files (controlfile and redo log) to the new FRA just follow these steps:

CONTROLFILE

Moving controlfiles from the old to the new FRA requires you to modify (init.ora) the location in the parameter CONTROL_FILES to the new location and restart the instance in NOMOUNT state.

-- Create pfile from the running spfile...
-- replace $ORACLE_HOME with your OH path
SQL> CREATE pfile FROM spfile;
-- Now edit the pfile created at $ORACLE_HOME/dbs and change
-- the CONTROL_FILES parameter to setup the new location
-- After doing that you can continue your SQLPlus session and
-- startup the database
SQL> CREATE SPFILE FROM PFILE=$ORACLE_HOME/dbs/init.ora
SQL> STARTUP NOMOUNT
SQL> exit

Now with RMAN “copy” the controfile from the old location

RMAN> RESTORE CONTROLFILE FROM 'oldcontrolfilename';

ONLINE REDO LOGS

Add new redo log files to the new FRA to each group member and drop the old ones.

SQL> ALTER DATABASE ADD LOGFILE SIZE 50m;
SQL> ALTER DATABASE DROP LOGFILE 'oldredologfile';

Your FRA old files will be delete automatically as they become unusable for any backup and restore process.

Cheers,
Joao


1 Comment


  1. Thanks man, halps a lot!

Leave a Reply