Ok, what is Oracle automatically controlling in your FRA – here is how to look at it.
Note: if you put format commands in your rman backups – even if you write them to the FRA – Oracle will not likely see them as part of the FRA – yes the backups will be recognized in the controlfile and or the rman catalog for rman operations (restore/recover etc.) – but not as part of the FRA and not subject to automatic management of the FRA.
Issue the following command to determine.
rman target /
catalog recovery area
— review the output from the command – self evident
— And what is being managed by flash
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
— bottom line if your backups etc. are not being managed – then you probably have a format command either in you rman config (show all) or in the backup scripts directly
— if you have to change FRA characteristics
— ALTER SYSTEM SET db_recovery_file_dest_size=##G;
— How to change the FRA to a new location (new archives will be created to this new location):
— ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’/u02/…’;
set lines 200
col name format a48
set wrap on
select
name,
floor(space_limit / 1024 / 1024 / 1024) “SizeInGB”,
ceil(space_used / 1024 / 1024 / 1024) “UsedInGB”
from v$recovery_file_dest;
— Size and Location of the FRA
show parameter db_recovery_file_dest
— Provide the size, used, reclaimable statistics about FRA
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;