Archivelog ORA-16014 log sequence not archived

October 11, 2008  |  dba

One common mistake in 10g, when we enable archivelog mode, happens when we use the flash recovery area default settings as the repository for our archived redo log’s… The mistake consequences will happen 20-30 days after the database archivelog mode been enabled.

By default Oracle 10g Database Configuration Assistant (DBCA) sets flash recovery area size to 2GB (db_recovery_file_dest_size parameter) and when we enable DB archivelog mode it will use the default db_recovery_file_dest parameter for the destination of our archivelogs unless we set any other directory as the destination for them.

A common small 10g database generates an average 100MB of archivelog files daily… Sometimes even at weekend days when most applications DML is almost zero. Why this happens? The reason is Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM). AWR collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. This generates lot’s of DML and consequently lot’s of redo data.

So, after some days, your flash recovery area will reach the default 2GB because we have our default settings database archiving there… When this happens our database will be unable to archive due to flash recovery area went full. This happens even if we have lot’s of disk space!

In our alert log file we’ll see something like this:

ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log string thread string: 'string: '/u01/app/oracle/oradata/mydb/redo01.log'
Sat Oct 11 10:43:56 2008
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Oct 11 10:43:56 2008
ORACLE Instance neo - Archival Error

Oracle saves all the information about what we place in the flash recovery area in the RMAN repository/controlfile. If it determines that there is not sufficient space in the recovery file destination, as set by db_recovery_file_dest_size then it will fail. Just deleting the old backups and archive logs from disk is not sufficient as it’s the rman repository/controlfile that holds the space used information.

How to fix this? If we google we see lot’s of places saying to execute “delete archivelog all” in RMAN. This is the fast easy, but dirty solution, as we don’t want to delete all our precious archivelogs by endanger the ability to do future database recovery. The fix can be done with any of the following solutions:

Solution 1 – Delete unwanted archive log files at OS side, then crosscheck archivelogs to marks the controlfile that the archives have been deleted and deleting expired ones.

[oracle@app oracle]$ cd /u01/app/oracle/flash_recovery_area/mydb/
[oracle@app oracle]$ rm archivelogs*
[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> crosscheck archivelog all
RMAN> delete expired archivelog all

Solution 2 – Connect RMAN to backup and then delete your archivelogs… this is a much better solution.

[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> backup archivelog until logseq delete all input;
or
RMAN> backup archivelog until time 'sysdate-15' delete all input;

Solution 3 – increase dynamically (without shutdown/startup database) the parameter db_recovery_file_dest_size.

[oracle@app oracle]$ sqlplus "/ as sysdba"
SQL> alter system set db_recovery_file_dest_size=4G

Then, if needed (usually with solution 1 and 2), just shutdown and startup your database and you’ll get you database again up. We may have to shutdown abort if the database does not shutdowns normally.

To avoid the same problem in future when the archivelog size reachs the db_recovery_file_dest_size we should set the following parameters to set an alternate place to archive the redo logs.

log_archive_dest_1=’LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2′
log_archive_dest_2=’LOCATION=/other_destination_for_archiving’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’alternate’
db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area/mydb/’
db_recovery_file_dest_size=2G

Have a nice loving day 🙂


10 Comments


  1. I can’t promise that I will not execute that “delete archivelog all” command again!

    Sorry 😉

  2. IS that not dangerous to delete
    and active archive log, and start from the
    beginning.

  3. is there an answer?

  4. I only had a small DB to deal with and was able to correct the issue with your steps. Thanks.

  5. What if you run a database that doesn’t need any point-in-time recovery with RMan? This is not so unusual for test databases. Then, “delete archivelog all” may still be a valid and useful approach – which is not so different from solution 1, imho.

    • Ah, and if you don’t back up your archive logs and still want to keep recent ones (e.g. for flashing back), then you might want to use the rman command

      delete noprompt archivelog all completed before ‘[date]’; — e.g. ‘sysdate-7’

      This way, archive logs will get purged from the FRA without having to back them up first.

  6. Just saved my start of the week!!! 1st hit on google http://www.google.nl/search?q=ORA-16014 & right on the spot.

    thanks.

  7. alter system set db_recovery_file_dest_size=4G
    Increase the size dynamically.
    Email : training@unirac.in

  8. Muy buena nota, me ayudó muchisimo..

    Saludos..

  9. Very helpful among the mass of not so helpful results! What I hate is how many of the results fail to realize is that in a real situation there is a good chance you don’t want to delete your archive files!

Leave a Reply