Your database is in archive log mode and archiving works fine until suddenly connecting via SQLPLUS generates the following error:
ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed
When connecting via sqlplus as internal and issuing:
sql> alter system archive log all;
Then the following error message is generated:
ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST
If you issue:
sql> select * from v$archive_dest;
then the result shows status ‘error’ the following error message is generated:
ORA-19504: failed to create file %s
When space becomes available again after archives have been moved to tape (or elsewhere) automatic archiving is not resumed.
Let’s see an example:
sql> select * from v$archive_dest; V$ARCHIVE_DEST.STATUS=ERROR V$ARCHIVE_DEST.ERROR=ORA-19504: failed to create file ""
When you check the corresponding alertfile you will see something like:
ARC0: Error 19504 creating archivelog file '[PATH]/backup/arch_1_384.arc' ARC0: Archiving not possible: error count exceeded ARC0: Failed to archive log# 1 seq# 384 ARCH: Archival stopped, error occurred. Will continue retrying
When you check your file system you may see something like (Check your filesystem where your archive destination points to. The filesystem is full, make space available):
$ df -h /mount_point_name
When space becomes available again after archives have been moved to tape (or elsewhere) automatic archiving is not resumed. In that case use the below method:
For each destination give correct the archivelogpath and issue:
sql> alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';
Related Error Codes
00257, 00000, "archiver error. Connect internal only, until freed." // *Cause: The archiver process received an error while trying to archive // a redo log. If the problem is not resolved soon, the database // will stop executing transactions. The most likely cause of this // message is the destination device is out of space to store the // redo log file. // *Action: Check archiver trace file for a detailed description // of the problem. Also verify that the // device specified in the initialization parameter // ARCHIVE_LOG_DEST is set up properly for archiving.
16020, 00000, "less destinations available than specified by LOG_ARCHIVE_MIN_SU" // *Cause: With automatic archiving enabled, the number of archive log // destinations that could be used for the database was less // than the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value. // *Action: Either adjust the settings of the log archive destination // parameters, or lower the value of LOG_ARCHIVE_MIN_SUCCEED_DEST
19504, 00000, "failed to create file \"%s\"" // *Cause: call to create file returned an error // *Action: check additional messages, check access permissions