Introduction Oracle ORA-00257 `archiver error. Connect internal only, until freed` occurs when the archive log destination is full. In ARCHIVELOG mode, Oracle cannot overwrite redo log files until they are archived. When the archive destination fills up, the database stops accepting all write operations until space is freed.

Symptoms - `ORA-00257: archiver error. Connect internal only, until freed` - All INSERT, UPDATE, DELETE operations hang or fail - `V$RECOVERY_AREA_USAGE` shows `ARCHIVED LOG` at 100% - Alert log shows `ARCH: Archival stopped, error occurred` - Database appears frozen to application users

Common Causes - Archive log destination filesystem full - RMAN backups not running, preventing archive log deletion - Archive log destination size too small for the redo generation rate - Flash Recovery Area (FRA) reaching `DB_RECOVERY_FILE_DEST_SIZE` limit - Backup process failing silently, not removing archived logs

Step-by-Step Fix 1. **Check archive log destination usage": ```sql -- Check FRA usage SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files FROM v$recovery_area_usage ORDER BY percent_space_used DESC;

-- Check FRA size SHOW PARAMETER db_recovery_file_dest; SHOW PARAMETER db_recovery_file_dest_size; ```

  1. 1.**Delete archived logs using RMAN":
  2. 2.```bash
  3. 3.rman target /

-- Check what can be deleted LIST ARCHIVELOG ALL;

-- Delete archived logs older than 2 days DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';

-- Delete all archived logs that have been backed up DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE DISK;

-- Crosscheck and delete expired CROSSCHECK ARCHIVELOG ALL; DELETE EXPIRED ARCHIVELOG ALL; ```

  1. 1.**Increase the FRA size":
  2. 2.```sql
  3. 3.-- Check current size
  4. 4.SHOW PARAMETER db_recovery_file_dest_size;

-- Increase to 100GB ALTER SYSTEM SET db_recovery_file_dest_size = 100G SCOPE = BOTH; ```

  1. 1.**Add a secondary archive log destination":
  2. 2.```sql
  3. 3.ALTER SYSTEM SET log_archive_dest_2 = 'LOCATION=/u02/oradata/archive';
  4. 4.ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;
  5. 5.`
  6. 6.**If connected users are blocked, free space quickly":
  7. 7.```bash
  8. 8.# Emergency: manually remove old archive log files
  9. 9.# Only if RMAN is not available
  10. 10.rm /u01/oradata/archive/arch_1_*.dbf # Remove old files
  11. 11.# Then crosscheck in RMAN
  12. 12.rman target /
  13. 13.CROSSCHECK ARCHIVELOG ALL;
  14. 14.DELETE EXPIRED ARCHIVELOG ALL;
  15. 15.`

Prevention - Monitor FRA usage with alerting at 70%, 85%, and 95% - Schedule regular RMAN backup jobs that delete archived logs after backup - Size the FRA to hold at least 2x the daily redo generation rate - Set up monitoring on archive log generation rate (bytes per hour) - Use multiple archive log destinations for redundancy - Test backup and archive cleanup procedures regularly - Consider reducing `DB_RECOVERY_FILE_DEST_SIZE` warnings to trigger at 80%