ORA-16038: log cannot be archived ORA-19809: limit exceeded for recovery files

When I was making backup with rman it was throwing error:

ORA-16038: log cannot be archived 
ORA-19809: limit exceeded for recovery files

firts we need to check alert.log that shows next:

ARC3: Error 19809 Creating archive log file to '+DISKS'
Errors in file /u0/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc0_3481.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, 
and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************

Now we know what is problem, we have flash recovery area which is full, check it:

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DISKS
db_recovery_file_dest_size           big integer 4977M

select * from v$flash_recovery_area_usage;

result:

flash recovery area is full therefore, we have two option to solve this problem:
1) add size to flash_recovery_area if we have free disk space.

SQL> alter system set db_recovery_file_dest_size=XG; (larger amount)

2) delete some archivelogs for free up flash_recovery_area.

RMAN> crosscheck archivelog all;
....
RMAN> delete expired archivelog all;
....
RMAN> delete obsolete;

that's all.
Thank you :)

Forgotten restore point and flash recovery logs

Few  years ago we started to use this database for a new project. Previous one was expired and abandoned.
Flash recovery area was set quite big (1500GB) comparing to the database size (200GB) and we missed the fact that the flash area had a significant space used over the years.

SQL> SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                 .03                         0               6
IMAGECOPY                 13.13                         0              17
FLASHBACKLOG              44.04                         0            7019

44.04% used by FLASHBACKLOG  - - -

PERCENT_SPACE_RECLAIMABLE  - - -  says nothing to delete from flash.

db_flashback_retention_target is one day. database size is 200GB. flashback log size 660GB  -  seems quite wasteful.

database control:

 Current size of the flashback logs(GB) 660.563
 Lowest SCN in the flashback data 1318377092383
 Flashback Time Sep 10, 2010 5:18:35 PM

here we've got the key - flashback time - which indicates that there should be a restore point keeping all flashback before 2010.

select guarantee_flashback_database gua, storage_size, time, name
  from v$restore_point;

GUA  STORAGE_SIZE  TIME                                 NAME
------  ------------    -------------------------------      -----------------
YES    7.0927E+11  10-SEP-10 05.18.35.000000000 PM      BEOFRE_DELTA_POST

So, it is the guaranteed restore point to 10-SEP-2010. We just drop it since no need for any purpose (at least in a new project);

SQL> DROP RESTORE POINT BEOFRE_DELTA_POST; 
Restore point dropped.

as a result old logs removed from flash area.

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                 .03                         0               6
IMAGECOPY                 13.13                         0              17
FLASHBACKLOG                  0                         0               1

Never forget guaranteed restore points. They are not being deleted automatically.

Thanks,