ORA-00257:archiver error解决办法

使用pl/sql developer登录数据库时提示ORA-00257的错误，查看错误信息如下：

[oracle@test ~]$ oerr ora 00257
00257, 00000, "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.
// *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.

查看数据库的alert日志也有错误出现，具体信息如下：

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance test - Archival Error
ORA-16038: log 1 sequence# 2429 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/U01/app/oracle/fast_recovery_area/test/onlinelog/o1_mf_1_hc4z9jbx_.log'
Fri Jul 03 13:52:59 2020
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance test - Archival Error
ORA-16014: log 1 sequence# 2429 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/U01/app/oracle/fast_recovery_area/test/onlinelog/o1_mf_1_hc4z9jbx_.log'
Fri Jul 03 13:57:59 2020
Errors in file /U01/app/oracle/diag/rdbms/test/test/trace/test_arc3_33710.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************

综上，可以判断数据库的归档日志存放空间不足造成了上面的问题出现；所以先查看了下数据库的归档位置：

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


SQL> show parameter db_reco

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /U01/app/oracle/fast_recovery_
						 area
db_recovery_file_dest_size	     big integer 200G
SQL>

由上知道了oracle归档日志的位置信息和大小，查看归档空间现在的使用情况：

SQL> select file_type,'-->',PERCENT_SPACE_USED,'-->'||chr(10)  from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE	     '-- PERCENT_SPACE_USED '-->
-------------------- --- ------------------ ----
CONTROL FILE	     -->		  0 -->
REDO LOG	         -->		 .54 -->
ARCHIVED LOG	     -->	      99.42 -->
BACKUP PIECE	     -->		  0 -->
IMAGE COPY	         -->		  0 -->
FLASHBACK LOG	     -->		  0 -->
FOREIGN ARCHIVED LOG -->		  0 -->

可以发现归档空间已经使用满了，因此可以将已经归档了的日志删除，释放已经占用了的空间来解决该问题。