Tag: select * from v$flash_recovery_area_usage;

1)Check and confirm Flash Recovery Area is not enabled.

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select * from V$RECOVERY_FILE_DEST;
no rows selected
SQL>

2) Enable Flash Recovery Area.

SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.

SQL>alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.

SQL>  show parameter db_recovery_file
NAME                         TYPE        VALUE
---------------------------- ----------- ----------------------
db_recovery_file_dest        string      /u01/app/oracle/fra
db_recovery_file_dest_size   big integer 10G

SQL>  select * from V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
---- ----------- ---------- ----------------- --------------- -------
/u01/app/oracle/fra 1.0737E+10       0       0         0       0

3) Configure  archive log destination to use flash recovery area.

SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32

SQL> select * from v$flash_recovery_area_usage;

FILE_TY PENT_SPACE_USED  PCENT_SPACE_REBLE NUMBER_OF_FILES    CON_ID
------------------------ ------------------ ---------------  ---------
CONTROL FILE     0                   0               0          0
REDO LOG         0                   0               0          0
ARCHIVED LOG     .59                 0               1          0
BACKUP PIECE     0                   0               0          0
IMAGE COPY       0                   0               0          0
FLASHBACK LOG    0                   0               0          0
FOREIGN ARCHIVED LOG  0              0               0          0
AUXILIARY DATAFILE COPY  0           0               0          0
8 rows selected.