:

ORA-16014 No Available Destinations

ora-16014 no available destinations

I arrived at work today to find a few emails in my inbox saying that one of the development environments appeared to be down. My first check was to try logging on as one of the application users, which failed with the error:

ORA-00257: archiver error. Connect internal only, until freed

I’ve written about this ORA-00257 error message previously, so read that for some of the details, but in this post I would like to cover a little part this I missed from that previous post. This part is related to the following error:

SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16014: log 1 sequence# 161 not archived, no available destinations
ORA-00312: online log 1 thread 1:
'X:\ORADATA\DEV\REDO\REDO01.LOG'

SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST

The first error above gives you a clue as to what the problem is, and this 2nd error above really lets you know the problem; the destination that you are trying to write out to is not available, is the short answer. This is because after a certain number of tries the log archive destination that you have configured gets set to unavailable and therefore nothing can be written to it, even if you have cleared enough space.

A Quick Solution

SQL> alter system set log_archive_dest_state_1='ENABLE' scope=both;

System altered.

SQL> alter system archive log all;

System altered.

It’s just a simple case of re-enabling the archiving destination to let the database know that it is valid and enabled. That’s what I did with the alter system command above.

Note: Check to make sure you are enabling the correct log_archive_dest. Remember, you must enclose the ENABLE value with single quotation marks.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *