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
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’; |
