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.
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.
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.