— 步骤1：检查归档日志配置
SHOW PARAMETER log_archive_dest;

— 输出结果
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest string /yashandb/fgdata/archive
log_archive_dest_1 string location=/yashandb/fgdata/archive

— 步骤2：检查归档日志使用情况
SELECT
name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM v$archived_log
GROUP BY name
ORDER BY size_mb DESC;

— 输出结果
NAME SIZE_MB
—————————————- ———-
/yashandb/fgdata/archive/arch_1_1.log 204.8
/yashandb/fgdata/archive/arch_1_2.log 187.5
/yashandb/fgdata/archive/arch_1_3.log 156.2
…

— 步骤3：配置归档日志保留策略
ALTER SYSTEM SET log_archive_retention_target = 7 SCOPE=BOTH;

— 步骤4：使用RMAN清理归档日志
RMAN> CONNECT TARGET /

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;

— 输出结果
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
List of Archived Log Copies for database with db_unique_name FGEDUDB
=====================================================================

Key Thrd Seq S Low Time
——- —- ——- – ———
1 1 1 A 2025-01-13 00:00:00
2 1 2 A 2025-01-14 00:00:00
3 1 3 A 2025-01-15 00:00:00
…

Do you really want to delete the above objects (enter YES or NO)? YES

deleted archived log
archived log file name=/yashandb/fgdata/archive/arch_1_1.log RECID=1 STAMP=1234567890
deleted archived log
archived log file name=/yashandb/fgdata/archive/arch_1_2.log RECID=2 STAMP=1234567891
deleted archived log
archived log file name=/yashandb/fgdata/archive/arch_1_3.log RECID=3 STAMP=1234567892
…

Deleted 10 archived log files

— 步骤1：查找告警日志位置
SHOW PARAMETER background_dump_dest;

— 输出结果
NAME TYPE VALUE
———————————— ———– ——————————
background_dump_dest string /yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace

— 步骤2：检查告警日志大小
# ls -lh /yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log

— 输出结果
-rw-r—– 1 yashan yashan 10M Jan 20 10:00 /yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log

— 步骤3：创建告警日志清理脚本
# vi /yashandb/app/scripts/clean_alert_log.sh

#!/bin/bash
# clean_alert_log.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

ALERT_LOG_DIR=”/yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace”
ALERT_LOG_FILE=”alert_fgedudb.log”
BACKUP_DIR=”/yashandb/backup/logs”
RETENTION_DAYS=30

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份告警日志
DATE=$(date +”%Y%m%d_%H%M%S”)
cp $ALERT_LOG_DIR/$ALERT_LOG_FILE $BACKUP_DIR/${ALERT_LOG_FILE}_$DATE

# 清理告警日志
> $ALERT_LOG_DIR/$ALERT_LOG_FILE

# 删除过期备份
find $BACKUP_DIR -name “${ALERT_LOG_FILE}_*” -mtime +$RETENTION_DAYS -delete

# 输出结果
echo “Alert log cleaned and backed up successfully”

— 步骤4：设置脚本执行权限
# chmod +x /yashandb/app/scripts/clean_alert_log.sh

— 步骤5：创建定时任务
# crontab -e

# 每月1日执行告警日志清理
0 0 1 * * /yashandb/app/scripts/clean_alert_log.sh >> /yashandb/app/scripts/clean_alert_log.log 2>&1

— 步骤1：创建日志清理监控表
CREATE TABLE fgedu.log_cleanup_monitor (
monitor_id NUMBER(10) PRIMARY KEY,
log_type VARCHAR2(50) NOT NULL,
cleanup_time TIMESTAMP DEFAULT SYSTIMESTAMP,
cleanup_status VARCHAR2(20) NOT NULL,
files_deleted NUMBER(10),
space_freed NUMBER(10),
error_message VARCHAR2(500)
);

— 步骤2：创建日志清理监控存储过程
CREATE OR REPLACE PROCEDURE fgedu.monitor_log_cleanup (
p_log_type VARCHAR2,
p_cleanup_status VARCHAR2,
p_files_deleted NUMBER DEFAULT NULL,
p_space_freed NUMBER DEFAULT NULL,
p_error_message VARCHAR2 DEFAULT NULL
) AS
v_monitor_id NUMBER;
BEGIN
— 生成监控ID
SELECT fgedu.log_cleanup_monitor_seq.NEXTVAL INTO v_monitor_id FROM DUAL;

— 插入监控记录
INSERT INTO fgedu.log_cleanup_monitor (
monitor_id, log_type, cleanup_time, cleanup_status, files_deleted, space_freed, error_message
) VALUES (
v_monitor_id, p_log_type, SYSTIMESTAMP, p_cleanup_status, p_files_deleted, p_space_freed, p_error_message
);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error monitoring log cleanup: ‘ || SQLERRM);
ROLLBACK;
END;
/

— 步骤3：创建日志清理监控视图
CREATE OR REPLACE VIEW fgedu.v_log_cleanup_monitor AS
SELECT
monitor_id,
log_type,
cleanup_time,
cleanup_status,
files_deleted,
space_freed,
error_message
FROM fgedu.log_cleanup_monitor
ORDER BY cleanup_time DESC;

— 步骤4：查看日志清理监控结果
SELECT * FROM fgedu.v_log_cleanup_monitor;

— 输出结果
MONITOR_ID LOG_TYPE CLEANUP_TIME CLEANUP_STATUS FILES_DELETED SPACE_FREED ERROR_MESSAGE
———- ————— ——————– ————– ————- ———– ————–
1 ARCHIVE_LOG 2025-01-20 10:00:00 SUCCESS 10 2048
2 ALERT_LOG 2025-01-20 00:00:00 SUCCESS 1 100
3 AUDIT_LOG 2025-01-19 00:00:00 SUCCESS 5 500

— 步骤1：创建日志清理主脚本
# vi /yashandb/app/scripts/clean_all_logs.sh

#!/bin/bash
# clean_all_logs.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 定义变量
LOG_DIR=”/yashandb/app/scripts/logs”
ARCHIVE_LOG_DIR=”/yashandb/fgdata/archive”
ALERT_LOG_DIR=”/yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace”
AUDIT_LOG_DIR=”/yashandb/fgdata/audit”
TRACE_LOG_DIR=”/yashandb/fgdata/diag/rdbms/fgedudb/fgedudb/trace”
LISTENER_LOG_DIR=”/yashandb/fgdata/diag/tnslsnr/fgedu/listener/trace”
BACKUP_DIR=”/yashandb/backup/logs”
RETENTION_DAYS=7

# 创建目录
mkdir -p $LOG_DIR
mkdir -p $BACKUP_DIR

# 清理归档日志
echo “Cleaning archive logs…”
$ORACLE_HOME/bin/rman target / << EOF DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-$RETENTION_DAYS'; EXIT; EOF # 清理告警日志 echo "Cleaning alert logs..." DATE=$(date +"%Y%m%d_%H%M%S") cp $ALERT_LOG_DIR/alert_fgedudb.log $BACKUP_DIR/alert_fgedudb_$DATE.log > $ALERT_LOG_DIR/alert_fgedudb.log

# 清理审计日志
echo “Cleaning audit logs…”
find $AUDIT_LOG_DIR -name “*.aud” -mtime +$RETENTION_DAYS -delete

# 清理跟踪日志
echo “Cleaning trace logs…”
find $TRACE_LOG_DIR -name “*.trc” -mtime +$RETENTION_DAYS -delete
find $TRACE_LOG_DIR -name “*.trm” -mtime +$RETENTION_DAYS -delete

# 清理监听日志
echo “Cleaning listener logs…”
DATE=$(date +”%Y%m%d_%H%M%S”)
cp $LISTENER_LOG_DIR/listener.log $BACKUP_DIR/listener_$DATE.log
> $LISTENER_LOG_DIR/listener.log

# 清理过期备份
echo “Cleaning expired backups…”
find $BACKUP_DIR -name “*” -mtime +30 -delete

# 输出结果
echo “All logs cleaned successfully”

— 步骤2：设置脚本执行权限
# chmod +x /yashandb/app/scripts/clean_all_logs.sh

— 步骤3：创建定时任务
# crontab -e

# 每天凌晨2点执行日志清理
0 2 * * * /yashandb/app/scripts/clean_all_logs.sh >> /yashandb/app/scripts/clean_all_logs.log 2>&1

— 步骤4：验证脚本执行
# /yashandb/app/scripts/clean_all_logs.sh

— 输出结果
Cleaning archive logs…
Cleaning alert logs…
Cleaning audit logs…
Cleaning trace logs…
Cleaning listener logs…
Cleaning expired backups…
All logs cleaned successfully