Monday, March 22, 2010

Freeing up db_recovery_file_dest reclaimable space.

I temporarily reduced db_recovery_file_dest_size to force the database to free reclaimable space from recovery_file_dest.

This reduced disk usage of ASM_ORADATA217 by over 320GB.


SYS>show parameter recovery

NAME TYPE VALUE
----------------------------------- ------------------------- ---------------------
db_recovery_file_dest string +ASM_ORADATA217
db_recovery_file_dest_size big integer 375G

SYS>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 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 88.7 83.73 1991


SYS>alter system set db_recovery_file_dest_size=20G;

System altered.

SYS>alter system set db_recovery_file_dest_size=375G;

System altered.

SYS>alter system switch logfile;

System altered.

SYS>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 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 5.32 .35 135