[oracle@stageddb]$ sqlplus scott
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 13:44:17 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
You have got ORA-00257 because archive log destination was full. After clean up the archive logs you get ORA-16020 error.
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
Cause:
Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.
SQL> select dest_id, status from v$archive_dest;
DEST_ID STATUS
---------- ---------------------------
1 ERROR
2 VALID
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 INACTIVE
11 INACTIVE
Solution
Option 1:
You can provide a valid location for the archive logs which will immediately start the archive log process.
Example:
SQL> alter system archive log all to '/opt/oracle/oradata/EMPDB/archive';
Option 2:
Change the REOPEN attribute of the LOG_ARCHIVE_DEST_n init<SID>.ora parameter. REOPEN=n sets the minimum number of seconds before ARCn try reopen a failed destination. The default value for n is 300 seconds, so if you have the default value for REOPEN you can wait 300 seconds to start it automatically.
SQL> alter system set LOG_ARCHIVE_DEST_n = '<path> REOPEN=30';
Option 3:
Start and stop archive log.
SQL> alter system archive log stop;
SQL> alter system archive log start;
Option 4:
Shutdown and restart the database which will reset the archive log destination parameter to be valid.