ORA-16038, ORA-19809, ORA-00312

I got the below error while open my test database. 


SQL> startup
ORACLE instance started.

Total System Global Area  188321792 bytes
Fixed Size                  1343780 bytes
Variable Size             134221532 bytes
Database Buffers           50331648 bytes
Redo Buffers                2424832 bytes
Database mounted.
ORA-16038: log 2 sequence# 400 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '+DATA/node/onlinelog/group_2.262.827631727'

SQL> !oerr ora 16038

16038, 00000, "log %s sequence# %s cannot be archived"

// *Cause:  An attempt was made to archive the named file, but the

//          file could not be archived. Examine the secondary error

//          messages to determine the cause of the error.

// *Action: No action is required.

Excerpt of the alert log

Errors in file /u01/app/oracle/diag/rdbms/node/node2/trace/node2_ora_1452.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% us                                                                             ed, 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.

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

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARCH: Error 19809 Creating archive log file to '+BACKUP'

Abort recovery for domain 0

Errors in file /u01/app/oracle/diag/rdbms/node/node2/trace/node2_ora_1452.trc:

ORA-16038: log 2 sequence# 400 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1: '+DATA/node/onlinelog/group_2.262.827631727'

ORA-16038 signalled during: ALTER DATABASE OPEN...

Soon I realized that my Archive log location got full due to which Oracle failed to archive the new redo log file due to which the database throws the error. 

The same I cross checked through the sql queries.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           399

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

-------------------- ------------------ ------------------------- ---------------

CONTROL FILE                          0                         0               0

REDO LOG                              0                         0               0

ARCHIVED LOG                      98.98                         0             119

BACKUP PIECE                        .23                         0               1

IMAGE COPY                            0                         0               0

FLASHBACK LOG                         0                         0               0

FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL> select SPACE_USED/1024/1024,SPACE_RECLAIMABLE/1024/1024,SPACE_LIMIT/1024/1024,NAME from v$recovery_file_dest;

SPACE_USED/1024/1024 SPACE_RECLAIMABLE/1024/1024 SPACE_LIMIT/1024/1024 NAME

-------------------- --------------------------- --------------------- -------

                5080                           0                  5120 +BACKUP

Solution.

Need to delete or move the archived files.

[oracle@node2 ~]$ mkdir -p /u01/arch_bkp/

RMAN> backup archivelog all format '/u01/arch_bkp/bkp_%U' delete input;

Starting backup at 29-OCT-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 instance=node2 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

archived log file name=+BACKUP/node/archivelog/2013_10_26/thread_1_seq_399.364.829870245 RECID=122 STAMP=829870248

Finished backup at 29-OCT-13

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> list backup

2> ;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

2       11.74M     DISK        00:00:02     17-OCT-13

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20131017T014229

        Piece Name: +DATA/node/backupset/2013_10_17/annnf0_tag20131017t014229_0.272.829014149

  List of Archived Logs in backup set 2

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    286     14533501   17-OCT-13 14577062   17-OCT-13

  2    118     14572745   17-OCT-13 14573244   17-OCT-13

  2    119     14573962   17-OCT-13 14577058   17-OCT-13

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

3       2.48G      DISK        00:02:58     29-OCT-13

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20131029T180020

        Piece Name: /u01/arch_bkp/bkp_0bonktto_1_1

  List of Archived Logs in backup set 3

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    287     14577062   17-OCT-13 14624495   17-OCT-13

  1    288     14624495   17-OCT-13 14686574   17-OCT-13

.....

.....

  2    121     14686699   17-OCT-13 14801035   17-OCT-13

  2    122     14835055   17-OCT-13 14925112   18-OCT-13

  2    123     14925112   18-OCT-13 14994936   18-OCT-13

  2    124     14994936   18-OCT-13 15101617   18-OCT-13

  2    125     15101617   18-OCT-13 15101690   18-OCT-13

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

4       2.44G      DISK        00:02:53     29-OCT-13

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20131029T180020

        Piece Name: /u01/arch_bkp/bkp_0conku46_1_1

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    340     15584080   20-OCT-13 15607836   20-OCT-13

  1    341     15607836   20-OCT-13 15638717   20-OCT-13

  1    342     15638717   20-OCT-13 15657959   20-OCT-13

  .....

......

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE

-------------------- ------------------ ------------------------- --------------

CONTROL FILE                          0                         0

REDO LOG                              0                         0

ARCHIVED LOG                          0                         0

BACKUP PIECE                        .23                         0

IMAGE COPY                            0                         0

FLASHBACK LOG                         0                         0

FOREIGN ARCHIVED LOG                  0                         0

7 rows selected.

Now I can  open my test database.

SQL> alter database open;

Database altered.