Skip to content

Flash Recovery Area In Oracle

    Flash Recovery Area in Oracle is a directory that contains Oracle datafiles, archived redo logs, backup pieces, the control file, and image copies.

    We can set Flash recovery area for following areas

    A single directory
    An entire file system
    Automatic storage management (ASM)

    Following files stored in the Flash Recovery Area (FRA)

    • Datafile and control file copies
    • Current control file
    • Online redo logs
    • Backup pieces
    • Archived redo logs
    • Control file auto backups
    • Flashback logs
    • Foreign archived redo log
    • Image copy
    • Auxiliary datafile copy

    Before enabling Flash Recovery area check the status

    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

    Enable Flash Recovery area

    SQL> alter system set db_recovery_file_dest_size=5G scope=both sid=’*’;
    System altered.
    
    SQL> alter system set log_archive_dest_1=’LOCATION=+DATA’ scope=both sid=’*’;
    System altered.

    After enabled Flash Recovery area check the status

    SQL> archive log list
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination +DATA
    Oldest online log sequence 7
    Next log sequence to archive 8
    Current log sequence 8
    
    SQL> select * from v$flash_recovery_area_usage;
    
    FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
    ------------ ------------------ ------------------------- ---------------
    CONTROLFILE                   0                         0               0
    ONLINELOG                     0                         0               0
    ARCHIVELOG                39.86                     39.86              24
    BACKUPPIECE               59.19                     28.93               4
    IMAGECOPY                     0                         0               0
    FLASHBACKLOG                  0                         0               0

    Switch Archivelog from database level and double check the archivelog

    [[email protected] ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 19.0.0.0.0 – Production on Thu Dec 26 11:00:58 2019
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0
    
    SQL> alter system switch logfile;
    System altered.
    [[email protected] ~]$ . .grid.env
    [[email protected] ~]$ asmcmd
    ASMCMD> lsdg
    State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
    MOUNTED EXTERN N 512 512 4096 4194304 40956 38036 0 38036 0 N DATA/
    MOUNTED EXTERN N 512 512 4096 4194304 12284 11904 0 11904 0 Y OCR_VOTING/
    ASMCMD> cd data
    ASMCMD> ls
    ORADBWR/
    ASMCMD> cd ORADBWR/
    ASMCMD> ls
    ARCHIVELOG/
    AUTOBACKUP/
    CONTROLFILE/
    DATAFILE/
    ONLINELOG/
    PARAMETERFILE/
    PASSWORD/
    TEMPFILE/
    ASMCMD> cd ARCHIVELOG/
    ASMCMD> ls
    2019_12_25/
    2019_12_26/
    ASMCMD> cd 2019_12_26/
    
    ASMCMD> ls -lrt
    
    Type Redund Striped Time Sys Name
    ARCHIVELOG UNPROT COARSE DEC 26 07:00:00 Y thread_2_seq_2.270.1028014821
    ARCHIVELOG UNPROT COARSE DEC 26 09:00:00 Y thread_1_seq_6.274.1028021127
    ARCHIVELOG UNPROT COARSE DEC 26 09:00:00 Y thread_1_seq_7.273.1028021131
    ARCHIVELOG UNPROT COARSE DEC 26 09:00:00 Y thread_2_seq_4.275.1028020093
    ARCHIVELOG UNPROT COARSE DEC 26 09:00:00 Y thread_2_seq_5.276.1028020589
    ARCHIVELOG UNPROT COARSE DEC 26 09:00:00 Y thread_2_seq_6.271.1028022787
    ARCHIVELOG UNPROT COARSE DEC 26 11:00:00 Y thread_1_seq_8.277.1028026869

    Percentage of space usable in the flash recovery area can be identified by:

    SQL> SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)FROM v$flash_recovery_area_usage;
    (100-SUM(PERCENT_SPACE_USED))+SUM(PERCENT_SPACE_RECLAIMABLE)
    ------------------------------------------------------------
                                                           69.74

    Also See:

    Oracle ASM Cluster File System (ACFS)

    Oracle Flash Recovery Area Backups