记一次数据库报ORA-00257: archiver error. Connect internal only, until freed错误的处理办法
登录oracle数据库报错：ORA-00257: archiver error. Connect internal only, until freed
网上搜索了下，发现是归档日志把空间占满了，需要清理下
1. 用sys用户登录
sqlplus / as sysdba
2. 看看archiv log所在位置
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
3. 一般VALUE为空时，可以用archive log list;检查一下归档目录和log sequence
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 360
Next log sequence to archive 360
Current log sequence 362
4. 检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到96.62
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .13 0 1
ONLINELOG 2.93 0 3
ARCHIVELOG 96.62 0 141
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
5. 使用rman删除归档日志：
[oracle@sit01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 25 10:12:30 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SIT (DBID=1464031195)
RMAN>
6. 检查一些无用的archivelog
RMAN> crosscheck archivelog all; #检查控制文件和实际物理文件的差别
7. 删除过期的归档
RMAN> delete expired archivelog all; # delete expired删除的是那些本来RMAN以为存在但是实际上在磁盘或者磁带上已经被删除了的信息,删除的只是RMAN资料库中的记录
RMAN> delete archivelog until time 'sysdate-1' ; 删除截止到前一天的所有archivelog
8. 再次查询，发现使用率正常，已经降到23.03
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .13 0 1
ONLINELOG 2.93 0 3
ARCHIVELOG 23.03 0 36
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
