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

How to FIX ORA-00257: 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.

ORA-00257 is a common error in Oracle. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery area (FRA), or db_recovery_file_dest_size .

First, make sure your automatic archiving is enabled. To check the archive lo made, try:

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2671 Next log sequence to archive 2676 Current log sequence 2676

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

SQL> show parameter db_recovery_file_dest; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      /oracle/fast_recovery_area db_recovery_file_dest_size           big integer 32G

The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST; NAME                                     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ---------------------------------------- ----------- ---------- ----------------- ---------------    /oracle/fast_recovery_area                3.8174E+10 3.8174E+10                 0             327

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

The first step to fixing this issue would be to log into your server and see if you have run out of physical space on one of your disks or mounts. If you have run out of physical space you have a few options.

You can backup your archivelogs and delete input, or

# rman target / RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

you can just delete the archivelogs if you won’t need them to restore your database.

# rman target /     RMAN> DELETE NOPROMPT ARCHIVELOG ALL;

you can Increase db_recovery_file_dest_size

SQL> alter system set db_recovery_file_dest_size=64g scope=both;

After all you can restart database

SQL> shutdown immediate; SQL> startup