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

 

Error:

ORA-00257: archive error, Connect internal only, until freed.
followed by
The database is not available due to the following conditions: Stuck Archiver
followed by
Warning: Disk group +DG_UAT_FLASH_01 is 92% used

*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.

Method   1. Without increasing DB_RECOVERY_FILE_DEST_SIZE.

Approach 1: Move the archive log from the FRA to some other location to free up FRA

Step 1: Check whether the database is in archive log mode and automatic archiving is enabled

SQL> archive log list

Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5559
Current log sequence           5561

Step 2:  As the archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_UAT_FLASH_01
db_recovery_file_dest_size           big integer 110G

Note the value for db_recovery_file_dest_size. Here it is 11G.

Step 3:  Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
+DG_UAT_FLASH_01
1.1811E+11 3.0249E+10        2.7808E+10            1140

Step 4:  If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

Step 5:  Archive all the log files

SQL> alter system archive log all;

Step 6:  Just switch the logs to verify:

SQL> alter system switch logfile;

Second Approach: Delete the Archive Log , when it is not needed to free up the DB_RECOVERY_FILE_DEST
Alternatively, we can solve this type of problem without increasing DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if we are sure we have backups and the archived logs are no longer necessary.

$rman target / RMAN>delete archivelog until time 'SYSDATE-1';

Method 2:  By increasing DB_RECOVERY_FILE_DEST_SIZE.

Step 1:  See the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

Step 2: Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

Step 3: Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200g;

Step 4: Enable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘+DG_UAT_FLASH_01’;