Archivelog ORA-16014 log sequence not archived…

The original article

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 ErrorOracle 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.
view sourceprint?
1 [oracle@app oracle]$ cd /u01/app/oracle/flash_recovery_area/mydb/
2 [oracle@app oracle]$ rm archivelogs*
3 [oracle@app oracle]$ rman target /
4 connected to target database: MYDB (DBID=1649570311)
5 RMAN> crosscheck archivelog all
6 RMAN> delete expired archivelog all

Solution 2 – Connect RMAN to backup and then delete your archivelogs… this is a much better solution.
view sourceprint?
1 [oracle@app oracle]$ rman target /
2 connected to target database: MYDB (DBID=1649570311)
3 RMAN> backup archivelog until logseq delete all input;
4 or
5 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.
view sourceprint?
1 [oracle@app oracle]$ sqlplus "/ as sysdba"
2 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