Flash Recovery Area (FRA), db_recovery_file_dest_size

Info

SQL> select * from V$RECOVERY_AREA_USAGE;			CONTROL FILE, REDO LOG, ARCHIVED LOG…

SQL> show parameter recovery

SQL>
column SPACE_LIMIT_Gb format 999
column SPACE_USED_Gb format 999
select name, SPACE_LIMIT/1024/1024/1024 SPACE_LIMIT_Gb, SPACE_USED/1024/1024/1024 SPACE_USED_Gb, SPACE_RECLAIMABLE from v$recovery_file_dest;

SQL>
column SPACE_LIMIT_MB format 999G999D00
column SPACE_USED_MB format 999G999D00
select name, SPACE_LIMIT/1024/1024 SPACE_LIMIT_Mb, SPACE_USED/1024/1024 SPACE_USED_Mb, SPACE_RECLAIMABLE from v$recovery_file_dest;	NAME               SPACE_LIMIT_GB  SPACE_USED_GB  SPACE_RECLAIMABLE
	-----------------  --------------  -------------  -----------------
	/u02/orcl/FRA          130              22               0

SQL>
column Name format a17
SELECT Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB, SPACE_USED/1024/1024/1024 Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;	NAME              SPACE_LIMIT_GB  SPACE_USED_GB  SPACE_RECLAIMABLE  NUMBER_OF_FILES
	-----------------  --------------  -------------   -----------------  ---------------
	/u02/orcl/FRA           130            21.85              0                 550

Show path

SQL> select NAME from V$RECOVERY_FILE_DEST;

Used, free, %

SQL> SELECT    TO_CHAR(SPACE_USED/1024/1024/1024, '999,999,999,999') AS "Used_Gb",    TO_CHAR(SPACE_LIMIT/1024/1024/1024 - SPACE_USED/1024/1024/1024 + SPACE_RECLAIMABLE/1024/1024/1024, '999,999,999,999') AS "Free_Gb", ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "Persent Used" FROM V$RECOVERY_FILE_DEST;

SQL> SELECT    TO_CHAR(SPACE_USED/1024/1024, '999,999,999') AS "Used_Mb",    TO_CHAR(SPACE_LIMIT/1024/1024 - SPACE_USED/1024/1024 + SPACE_RECLAIMABLE/1024/1024, '999,999,999') AS "Free_Mb", ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "Persent Used" FROM V$RECOVERY_FILE_DEST;	       Used                      Free               Persent Used
	   ----------------      ----------------          ------------
	  23,665,519,104   42,879,087,104              .2

Usage

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE WHERE PERCENT_SPACE_USED > 0 ;	FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

Set new parameter size

SQL> alter system set db_recovery_file_dest_size=18G  SCOPE=BOTH;

Set new path parameter

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='C:\ORACLE\RECOVERY_AREA' SCOPE=BOTH

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH ;

SQL> create pfile from spfile;
Add the following line to the init.ora:
        log_archive_dest_n=’’
        log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\oradata\V102\Arch'
Restart the instance using the amended pfile and recreate the spfile:
        SQL>shutdown
        SQL>create spfile from pfile;
        SQL>startup;

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;

Disabling a Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' SCOPE=BOTH;