归档空间问题处理总结

来源：互联网发布：学为贵封闭班知乎编辑：程序博客网时间：2024/06/06 22:42

问题现象：在维护项目时经常出现系统不能登录，前台提示ORA-00257,判断是数据库归档空间已满

技术分析：登录到数据库，查看归档配置

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST

查看具体归档目录

SQL>show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 10G

查看归档已占用的空间（select * from V$FLASH_RECOVERY_AREA_USAGE），归档空间已经近10G空间。

解决方案：因当时客户要求紧急，临时的解决方案是增加归档目录空间，增加后前台业务可以正常运行。

SQL> alter system set db_recovery_file_dest_size=30G scope=both;

1. 查看备份方案：因客户方已有专业的备份软件，每天备份并删除备份之前的归档日志文件，所以备份方案不需要修正。

SQL> select name,completion_time,status from v$archived_log;

2. 检查归档日志的增长情况，在网上参考修改查询语句，查询最近一周内，数据库归档的频率

SELECT TO_CHAR(first_time, 'MM/DD') DAY,

SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,

SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) || '(' ||
trim(to_char(sum(blocks * block_size) / 1024 / 1024, '99,999.9')) || 'M)' TOTAL
FROM (select max(blocks) blocks,
max(block_size) block_size,
max(first_time) first_time
from v$archived_log a
where COMPLETION_TIME > sysdate - 7
and dest_id = 1
group by sequence#)
group by TO_CHAR(first_time, 'MM/DD'), TO_CHAR(first_time, 'YYYY/MM/DD')
order by TO_CHAR(first_time, 'YYYY/MM/DD') desc

查看结果每天峰值切换频率达到近600次，

查看数据库日志文件的大小 SQL>select distinct(bytes/1024/1024) MB from v$log; 每个在线日志文件 50 MB 大小。

所以估计峰值归档日志会达到30G，因切换日志时可能日志文件未写满，所以db_recovery_file_dest_size参数暂时调整到30G。

总结方案：对系统日志归档大小要监控，估计相应的所需空间，避免日志空间满影响系统运行。

0 0