Oracle归档日志使用情况及自动清理

背景

新上的应用最近在做压力测试，测试了一天就发现登录报错了，提示：

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved

百度后，确定原因为归档日志空间不足，也查询了具体的归档日志扩容方法及清理方法，记录一下，帮助自己也帮助其他其他朋友。

归档日志使用情况查询

使用sys用户登录数据库使用如下语句可以查询归档日志的使用情况及使用率。

[oracle@ESBDatabase ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 18 14:29:25 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

# 查询归档文件位置、大小信息
SQL> select * from v$recovery_file_dest;
NAME                                         SPACE_LIMIT  SPACE_USED   SPACE_RECLAIMABLE    NUMBER_OF_FILES    CON_ID
-------------------------------------     ---------------     ----------------     -----------------------------     -------------------------     ----------
/u01/app/fast_recovery_area     2.1475E+10     8498622464		       0	                                     381	                           0

# 查询归档日志文件使用情况
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					           0			  0		  0	     0
REDO LOG					                   0			  0		  0	     0
ARCHIVED LOG				          39.88			  0		384	     0
BACKUP PIECE					            0			  0		  0	     0
IMAGE COPY					                0			  0		  0	     0
FLASHBACK LOG					        0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

归档日志扩容及清理

归档日志扩容

通过sys用户登录后使用如下语句进行容量设置：

SQL> alter system set db_recovery_file_dest_size=20G;

归档日志清理

使用RMAN方式登录，并执行如下语句进行日志清理：

# 检查所有归档日志文件
RMAN> crosscheck archivelog all;
# 删除失效的归档日志文件
RMAN> delete noprompt expired archivelog all;
# 删除七天前的归档日志文件
RMAN> delete noprompt archivelog all completed befored 'sysdate-7';

归档日志定时清理

1. 新建归档日志脚本

[oracle@localhost ~]$ vim /home/oracle/del_arch.sh
#!/bin/bash
echo "----------------------------------------`date`---------------------------------------"
source ~/.bash_profile
rman target / <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate-7';
EOF
echo -e "\n"
echo "------------------------------------ FINISHED ------------------------------------"

2. 将清理脚本添加到定时任务中

[oracle@localhost ~]$ crontab -e
* 1 * * *  sh /home/oracle/del_arc.sh