Fix a full FRA / archive destination

Command

-- Check usage:
SELECT name, space_limit/1024/1024/1024 AS limit_gb,
       space_used/1024/1024/1024  AS used_gb,
       ROUND(space_used/space_limit*100,1) AS pct_used
FROM   v$recovery_file_dest;

-- Fix A) Increase the FRA size:
ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;

-- Fix B) Clean up already backed-up archives with RMAN:
-- RMAN> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK COMPLETED BEFORE 'SYSDATE-3';
-- If there is no backup (CAREFUL): RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

What it does

When the Fast Recovery Area or archive destination fills up, the database hangs (ORA-00257). It is fixed by enlarging the space or cleaning up archives.

When to use it

ORA-00257 archiver error, when the DB stops accepting new transactions.

Caution

Deleting archives that have not been backed up destroys recoverability. Take an RMAN backup first, then delete. Do not delete with rm from the OS; that requires an RMAN CROSSCHECK. While the FRA is full, back up archives to a destination outside the FRA (FORMAT '/backup/arc_%U'). With Data Guard, do not delete archives not yet shipped/applied to the standby; otherwise a gap forms on the standby. Use CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY.