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
posted @   海yo  阅读(12)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 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常见漏洞处理
点击右上角即可分享
微信分享提示