db_recovery_file_dest_size满了处理(ORA-19815)附带清理alert日志和trace日志
背景
oracle上生产后常见问题
错误截图
ORA-19809: ▒▒▒▒▒˻ָ▒▒ļ▒▒▒▒▒▒▒▒
ORA-19804: ▒▒▒▒▒▒ 926934528 ▒ֽڴ▒▒̿ռ▒ (▒▒ 15955132416 ▒ֽ▒▒▒▒▒▒▒)
2025-02-05T14:24:35.312123+08:00
ARC0: Error 19809 Creating archive log file to '/apps/oracle/oracle/flash_recovery_area/UPP/archivelog/2025_02_05/o1_mf_1_20_%u_.arc'
2025-02-05T14:24:35.338011+08:00
Errors in file /apps/oracle/oracle/diag/rdbms/upp/upp/trace/upp_arc2_27494.trc:
ORA-19815: ▒▒▒▒: db_recovery_file_dest_size ▒ֽ▒ (▒▒ 15955132416 ▒ֽ▒) ▒▒ʹ▒▒ 100.00%, ▒▒▒▒ 0 ▒ֽڿ▒▒á▒
2025-02-05T14:24:35.338040+08:00
分析
查看配置的大小
SHOW PARAMETER db_recovery_file_dest_size;
查看使用情况
SELECT
name,
space_limit AS "Limit (MB)",
space_used AS "Used (MB)",
space_reclaimable AS "Reclaimable (MB)",
number_of_files AS "Files"
FROM v$recovery_file_dest;
查看归档日志空间使用率
SELECT space_limit/1024/1024/1024 AS "Quota_G",space_used/1024/1024 AS "Used_M",space_used/space_limit*100 "Used_%",space_reclaimable AS reclaimable,number_of_files AS files FROM v$recovery_file_dest ;
处理
脚本处理(清理归档)
[oracle@localhost oracle]$ cat del_arch.sh
#!bin/sh
source /home/oracle/.bash_profile
rman target / cmdfile=/apps/oracle/del_arch.sql
[oracle@localhost oracle]$
[oracle@localhost oracle]$ cat del_arch.sql
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
crosscheck archivelog all;
delete expired archivelog all;
[oracle@localhost oracle]$
[oracle@localhost oracle]$
[oracle@localhost oracle]$ crontab -l
0 5 * * * /apps/oracle/del_arch.sh
手动修改配置处理(扩容)
shutdown immediate;
startup nomount;
ALTER SYSTEM SET db_recovery_file_dest_size=20g scope=both;
alter database mount;
alter database open;
show parameter db_recovery_file_dest_size;
select * from v$recovery_file_dest;
处理过程中发现alert日志和trace日志爆满,随带处理
[oracle@localhost oracle]$ adrci
ADRCI: Release 12.2.0.1.0 - Production on Wed Feb 5 15:09:55 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
ADR base = "/apps/oracle/oracle"
adrci>
adrci>
adrci> show homes
ADR Homes:
diag/rdbms/upp/upp
diag/tnslsnr/localhost/listener
可以看到adrci的主目录有两个,选择其中一个并做清理
adrci> set home diag/rdbms/upp/upp
adrci>
adrci> PURGE -age 1440 -type trace
adrci> PURGE -age 1440 -type alert
写成脚本:
[oracle@localhost oracle]$ cat del_alert_trace_log.sh
#!/bin/bash
# 设置 Oracle 环境变量
export ORACLE_HOME=/apps/oracle/oracle/product/12c/dbhome_1
export ORACLE_SID=upp
export PATH=$ORACLE_HOME/bin:$PATH
# 设置日志保留天数
RETENTION_DAYS=15
# 设置 ADRCI 的诊断目录
DIAG_HOME=diag/rdbms/upp/upp
# 清理 alert 日志
echo "Cleaning alert logs older than $RETENTION_DAYS days..."
adrci exec="set home $DIAG_HOME; purge -age $((RETENTION_DAYS * 1440)) -type alert"
# 清理 trace 日志
echo "Cleaning trace logs older than $RETENTION_DAYS days..."
adrci exec="set home $DIAG_HOME; purge -age $((RETENTION_DAYS * 1440)) -type trace"
echo "Log cleanup completed."
[oracle@localhost oracle]$
[oracle@localhost oracle]$ pwd
/apps/oracle
[oracle@localhost oracle]$ crontab -l
0 5 * * * /apps/oracle/del_alert_trace_log.sh
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2023-02-05 mysql_gtid学习笔记
2023-02-05 windows处理C盘空间不够
2023-02-05 NFS磁盘共享(centos)
2023-02-05 Nginx常见漏洞处理