How to free Oracle FRA space properly

Oracle Flash Recovery Area is full will made your database stop working for normal user and produce ORA-19815 warnings, ORA-00257: archiver error. Connect internal only, until freed.

So, you need to free up more space for Oracle FRA.

First of all, NEVER try to delete archive logs or backups MANUALLY with system command like “rm -rf”. In some articles, they guide people to do that but please DON’T. You need to delete the archive logs or backups using RMAN. Follow below steps to do that properly.

Step 1: Check your configured DB_RECOVERY_DEST_SIZE and increase it if available

Note: you can ignore this step and go straight to step 2 if you don’t want/don’t have enough space to take for FRA.

Using these commands:

$ sqlplus / as sysdba
SQL> show parameter DB_RECOVERY_FILE_DEST;

The result will be something likes this:

NAME                                 TYPE        VALUE
 
 db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                  _area
 db_recovery_file_dest_size           big integer 1000G

Here you can see DB_RECOVERY_FILE_DEST_SIZE equals 1TB and located in DB_RECOVERY_FILE_DEST: /u01/app/oracle/flash_recovery.

Related:  how to insert iso-8601 type date into oracle database

If you have more space, increase this size with following command:

alter system set DB_RECOVERY_FILE_DEST_SIZE=1010G scope=both;

Now your database will have more 10G to operate until it’s all filled up.

Step 2: Delete old archivelogs and backups in FRA

Using these commands:

cd /u01/app/oracle/flash_recovery_area/DBNAME
du -sh ./*

The result will be something like this:

$ du -sh ./*
 426G    ./archivelog
 173G    ./backupset
 17M     ./control02.ctl
 29G     ./flashback
 0       ./onlinelog

So you can see, the archivelog folder has a very large file size. We need to clear the archivelog.

RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired archivelog all;

If you see not too much of archive log deleted and you archive log file size is still too big. Then:

RMAN> backup as compressed backupset archivelog until time 'sysdate-365';
RMAN> delete noprompt archivelog until time 'sysdate-365' backed up 1 times to device type disk;
RMAN> backup as compressed backupset archivelog until time 'sysdate-100';
RMAN> delete noprompt archivelog until time 'sysdate-100' backed up 1 times to device type disk; 
RMAN> backup as compressed backupset archivelog until time 'sysdate-1';
RMAN> delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk; 

Please note: the “backup as compressed backupset archivelog all;” will take you a lot of time and disk space to complete if you have a big size archivelog. So you have to divide into smaller periods.

Related:  Trying to build static CGO executable with oracle libraries on Linux/Ubuntu

Those above commands will delete most of archive logs until yesterday therefore, you will free significant amount of space.

Now check the space again:

du -sh ./*
 640M    ./archivelog
 197G    ./backupset
 17M     ./control02.ctl
 29G     ./flashback
 0       ./onlinelog

You can see, we saved more than 200GB by deleting the archive log.