Oracle FlashBack Error : ORA-16014: log 3 sequence not archived, no available destinations

Number of my systems are on Oracle 10g with flashback Area allocated. The Archive logs , redo logs & backups are destined at this area. This morning users complained about system being down and when I looked at alrtlog I could see archive error.

SQL> show parameter  recovery

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest                string      /u07/backup/oat/flash_recovery_area

db_recovery_file_dest_size     big integer 50G

recovery_parallelism                 integer     0

Let’s have a look at Alrtlog file

SQL> show parameter background

NAME                                 TYPE        VALUE

———————————— ———– ——————————

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/product/10.2.0/admin/oat/bdump

Tail alrtlog

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 99.97% used, and has 14042624 remaining bytes available.

Tue Jun 15 09:52:12 2010

Errors in file /u01/app/oracle/product/10.2.0/admin/at/bdump/oat_arc4_7353.trc:

ORA-16038: log 1 sequence# 7603 cannot be archived

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 99.97% used, and has 14042624 remaining bytes available.

Tue Jun 15 08:03:57 2010

************************************************************************

You have following choices to free up space from flash recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

51200                  51200

Quick Fix :

$ du /u07/backup/oat/flash_recovery_area/OAT/archivelog/  — To locate space used

$ cd /u07/backup/oat/flash_recovery_area/OAT/archivelog/

$ find -name ‘*.arc’ -mtime +2 -exec rm {} \; — Delete archive files older than 2 days

Just deleting archives is no good and we  need to update catalog with deleted file details

$ rman target / nocatalog

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

2932.44385                   51200

OR Add more space

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

3227.13867                    4032

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =20G scope=Both sid=’*';

System altered.

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)

———————- ———————–

3941.9248                   20480