v$recovery_file_dest and v$flash_recovery_area_usage give bad results

Question:  My flash recovery area is showing improper space usage:

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    97.96    0   44
BACKUPPIECE       0    0    0
IMAGECOPY         0    0    0
FLASHBACKLOG      0    0    0

I have removed all archive file when i took cold backup of my db , but in upper query it still shows 97% used space even though my flash recovery area is empty.  How I can free up my flash recovery area?

Answer: Bug 4911954 causes v$recovery_file_dest and v$flash_recovery_area_usage to provide an incorrect representation of exact space used.  See MOSC Doc Id 4911954.8 for additional details.

Also note that v$flash_recovery_area_usage only gives a percentage usage.  To see the actual space used, query the v$recovery_file_dest view.

---

Reader comment ( by Dmitry):

I've also noticed, that manual manipulation backupsets, using asmcmd can lead to the same problem.

To re-populate the v$recovery_file_dest correctly we should run a few rman crosschecks and deletes commands.

Example Of The Issue:

I'm running Oracle 11gR2 Enterprise Edition (11.2.0.4) RAC ASM 2 nodes.

I have ASM diskgroup +RECO, where I keep backupsets, generated by RMAN.

SQL> column name format a15
SQL> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+RECO 9663676416 663748608 0 5

SQL>

Now I'll manually remove all +RECO data using asmcmd:

[oracle@rac1 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 18426 9133 6142 1495 0 Y DATA/
MOUNTED NORMAL N 512 4096 1048576 18426 16820 6142 5339 0 N RECO/
MOUNTED HIGH N 512 4096 1048576 18426 15672 0 5224 0 N REDO/
ASMCMD> cd RECO
ASMCMD> rm -rf *
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 18426 9133 6142 1495 0 Y DATA/
MOUNTED NORMAL N 512 4096 1048576 18426 18120 6142 5989 0 N RECO/
MOUNTED HIGH N 512 4096 1048576 18426 15672 0 5224 0 N REDO/
ASMCMD>

We can see that after removing +RECO's data the Usable_file_MB column showing by lsdg was updated accordingly.

At the same time, the dictionary view v$recovery_file_dest will become out of sync; its data isn't updated:

SQL> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+RECO 9663676416 663748608 0 5

Now I'll re-populate it correctly using RMAN.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 6 13:24:23 2015

Copyright - 1982, 2015, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1377135549)

RMAN>
RUN {
change archivelog all crosscheck;
report obsolete orphan;
report obsolete;
crosscheck backup;
crosscheck copy;
crosscheck backup of controlfile;

delete noprompt expired backup;
delete noprompt expired archivelog all;
delete noprompt expired backup of controlfile;
delete force noprompt expired copy;
delete force noprompt obsolete orphan;
delete force noprompt obsolete;
}

The v$recovery_file_dest view looks Ok now; it's indicating the data updated, in sync with ASM.

SQL> /

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+RECO 9663676416 0 0 0

SQL>

The bottom line.

I'd say the general rule should be to avoid backupsets manual manipulation using asmcmd.

It should be managed by RMAN.

[http://dba-oracle.com/include_rman_book.htm]

---