• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Identify Different File types and space used in Flash Recovery Area

By admin

Question: How to find out the existence of different types of files and backups available with the usage of space in the Flash Recovery Area?

Starting with Oracle 10G Release 2, a new view has been made available: V$FLASH_RECOVERY_AREA_USAGE. V$FLASH_RECOVERY_AREA_USAGE shows the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

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

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

V$RECOVERY_FILE_DEST shows the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area:

SQL> select * from V$RECOVERY_FILE_DEST;

NAME
----------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
[Flash recovery area (same as values of parameter db_recovery_file_dest)]
 2147483648 2075840512        1477189632              27

Filed Under: oracle, Oracle 10g, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. Oracle RMAN 11g New Feature – MultiSection Backups
  2. New Oracle Net features in version 12c
  3. Oracle RMAN: Monitoring Recovery Manager Jobs
  4. Oracle RMAN 12c – New Features
  5. Oracle Database 18c new feature – Scalable Sequences
  6. Difference between using srvctl vs using sqlplus for start/stop one or more Oracle Database Instances
  7. Oracle Database – How to Recover from a Lost or Deleted Datafile with Different Scenarios
  8. Oracle Scheduler 12c New Features and Enhancements
  9. Oracle Database interview questions – Privileges And Roles
  10. IPv4 Subnet Basics and Oracle Clusterware

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary