11.2 recovery - free up spaceon DB_RECOVERY_FILE_DEST*

Make more disk space available and increase DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest_size=xG SCOPE=BOTH; -- (larger amount)

2. Move backups from the flash recovery area to tertiary storage such as tape.

RMAN>BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

3. Run DELETE for any files that have been removed with an operating system utility. If you use host operating system commands to delete files, then the database will not be aware of the resulting free space.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point ;

5. If flashback logs are enable then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL>Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN>Delete archivelog all completed before 'SYSDATE-7';

- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

- Bug 4911954

Details:
V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong.
eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different.
This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed in : 10.2.0.3 , 11.1.0.6


- Bug 5106952

Details:
Flashback logs are not reclaimed by flash recovery area when using guaranteed
restore point (after dropping guaranteed restore point) or when changing
db_flashback_retention_target to a lower value.