To setup the flash recovery area in your existing database, you need to set below mentioned required parameters in the database parameter file (PFILE) and also database needs a bounce or restart.
You can also set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters to setup a flash recovery area to a running/open database.
Steps to setup Flash Recovery Area?
1. Connect to SQL*Plus as sysdba
2. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH SID='*';
Where 20G is the size of the flash recovery area size, SCOPE to BOTH will make the change to both memory and server parameter file, SID=’*’ has no effect in the single instance database where as in RAC database it causes the change to take effect across all instance.
3. Decide the physical location of the flash recovery area in the file system and execute below mentioned DDL to set the location.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ' /opt/oracle/FRA' SCOPE=BOTH SID='*';
4. If the flash recovery area is in ASM (automatic storage management) disk group (eg. disk1), then you have to run the following.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';