How To Increase Db_recovery_file_dest_size

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

SQL> select * from 5$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 common cold backup of my db , just in upper query it all the same shows 97% used infinite even though my flash recovery area is empty. How I can gratuitous upwards my flash recovery area?

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

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

Reader comment ( past Dmitry):

I've also noticed, that transmission manipulation backupsets, using asmcmd tin can pb to the aforementioned problem.

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

Example Of The Consequence:

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

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

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

Proper noun SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+RECO 9663676416 663748608 0 5

SQL>

Now I'll manually remove all +RECO information 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 Proper noun
MOUNTED NORMAL N 512 4096 1048576 18426 9133 6142 1495 0 Y Information/
MOUNTED NORMAL N 512 4096 1048576 18426 16820 6142 5339 0 N RECO/
MOUNTED HIGH Due north 512 4096 1048576 18426 15672 0 5224 0 N REDO/
ASMCMD> cd RECO
ASMCMD> rm -rf *
ASMCMD> lsdg
State Blazon Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Proper name
MOUNTED NORMAL Northward 512 4096 1048576 18426 9133 6142 1495 0 Y Information/
MOUNTED NORMAL N 512 4096 1048576 18426 18120 6142 5989 0 N RECO/
MOUNTED Loftier N 512 4096 1048576 18426 15672 0 5224 0 N REDO/
ASMCMD>

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

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

SQL> select * from v$recovery_file_dest;

Proper name SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+RECO 9663676416 663748608 0 5

At present I'll re-populate it correctly using RMAN.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Product on Fri Jun half dozen 13:24:23 2015

connected to target database: ORCL (DBID=1377135549)

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

delete noprompt expired fill-in;
delete noprompt expired archivelog all;
delete noprompt expired fill-in of controlfile;
delete force noprompt expired re-create;
delete forcefulness noprompt obsolete orphan;
delete force noprompt obsolete;

}

The v$recovery_file_dest view looks Ok now; information technology'southward 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.

Information technology should be managed by RMAN.

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