ora-00257: archiver error. connect as sysdba only until resolved.

ora-00257: archiver error. connect as sysdba only until resolved.


Error While Connecting db through SQL Developer...

Cause
​The archiver process of the oracle database received an error while trying to archive a redo log.  The most likely cause of this message is that the destination device is out of space to store the redo log file.


Solution
To resolve this issue, contact the DBA to clear archive log directory and make some room for redo log creation. Once the issue gets resolved, test the connection and run the task.


This command will show you where your archivelogs are being written to:


SQL> show parameter log_archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      /u01/archivelog/orcl/

If the ‘log_archive_dest’ parameter is empty then you are most likely using a ‘db_recovery_file_dest’ to store your archivelogs. You can run the below command to see that location.


SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/fast_recovery_area
db_recovery_file_dest_size           big integer 100G

You will see at least these two parameters if you’re on Oracle 10g, 11g, or 12c. The first parameter ‘db_recovery_file_dest’ is where your archivelogs will be written to and the second parameter is how much space you are allocating for not only those files, though also other files like backups, redo logs, controlfile snapshots, and a few other files that could be created here by default of you don’t specify a specific location.


SQL> archive log list;

Now, note thatyou can find archive destinations if you are using a destination of USE_DB_RECOVERY_FILE_DEST by:

SQL> show parameter db_recovery_file_dest;


 find out what value is being used for db_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

You may find that the SPACE_USED is the same as SPACE_LIMIT,

It is important to note that within step five of the ORA-00257 resolution,  you may also encounter ORA-16020 in the LOG_ARCHIVE_MIN_SUCCEED_DEST, and you should use the proper archivelog path and use (keeping in mind that you may need to take extra measures if you are using Flash Recovery Area as you will receive more errors if you attempt to use LOG_ARCHIVE_DEST):

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

The last step in resolving ORA-00257 is to change the logs for verification using:

SQL> alter system switch logfile;



According to the alert log, my db_recovery_file_dest was full and need to increase its size.
If you have enough space on underlying file system then simply increase the size of db_recovery_file_dest_szie otherwise first increase the size of the storage then modify this parameter.

 SQL> show parameter db_recover 

 NAME                       TYPE      VALUE 
 ------------------------------------ ----------- ------------------------------ 
 db_recovery_file_dest             string      /u01/app/oracle/fast_recovery_ 
                                area 
 db_recovery_file_dest_size        big integer 4500M 


SQL> alter system set db_recovery_file_dest_size=5G; 
 System altered.



Now you can invoke sqlplus

$sqlplus /nolog
SQL>conn / as sysdba
SQL> archive log list;
Check the Archive destination and delete all the logs

SQL> shutdown immediate 
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;


You should purge the archived logs with RMAN:

RMAN>delete archivelog all;

RMAN> crosscheck archivelog all;

RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;


SQL> select * from V$RECOVERY_AREA_USAGE;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7days;
RMAN> delete obsolete;


CONFIGURE RETENTION POLICY TO REDUNDANCY 1;


RMAN> show all;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7days;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

RMAN> Delete archivelog all completed before 'SYSDATE-1';

SQL> archive log list;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

SQL> select * from V$RECOVERY_AREA_USAGE;

SQL> select * from v$flash_recovery_area_usage;