当前位置：首页 > news >正文

Oracle定时清理归档日志

news 2025/7/23 10:17:46

线上归档日志满了，系统直接崩了，为解决这个问题，创建每月定时清理归档日志。
创建文件名 delete_archivelog.rman

CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;
RUN {ALLOCATE CHANNEL c1 TYPE DISK;DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7';RELEASE CHANNEL c1;
}

创建文件名 archive_cleanup.bat

@echo off
:: 设置 Oracle 实例的环境变量
set ORACLE_SID=EMR
set ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%:: 设置日志文件路径（可选，用于记录日志）
set LOGFILE=%~dp0%archive_cleanup.logecho [%date% %time%] 开始删除7天前的归档日志... >> "%LOGFILE%":: 调用 RMAN 并执行脚本文件
"%ORACLE_HOME%\bin\rman" target / @delete_archivelog.rman >> "%LOGFILE%" 2>&1if %errorlevel% == 0 (echo [%date% %time%] 归档日志清理完成，未发现错误。 >> "%LOGFILE%"
) else (echo [%date% %time%] 警告：归档日志清理过程中发生错误，错误代码：%errorlevel% >> "%LOGFILE%"
)echo 清理完成，请查看日志文件：%LOGFILE%
pause

查询归档日志，是否删除。

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, BLOCKS * BLOCK_SIZE AS BYTES, DELETED
FROM V$ARCHIVED_LOG 
WHERE FIRST_TIME < SYSDATE  -- 这里以7天前为例子
ORDER BY SEQUENCE# DESC;SELECT TRUNC(SUM(blocks * block_size) / 1024 / 1024) AS "Archived Log Size (MB)"FROM v$archived_logWHERE deleted = 'NO';