Oracle 归档日志满导致数据库无法连接处理方法和设置自动删除数据库归档

记录 Oracle 数据库因归档日志满了导致数据库无法连接问题处理过程

使用 rman 删除归档

-- 进入RMAN
rman target /
-- 显示无用的archivelog
crosscheck archivelog all;
-- 删除过期的归档
delete expired archivelog all;
-- 或者删除七天前日志
delete archivelog until time 'sysdate-7';
-- 删除7天前归档及磁盘文件
delete archivelog all completed before 'SYSDATE-7';

配合 rman 备份脚本实现归档自动删除

#!/bin/bash
# 加载 oracle 用户环境变量
source /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# 设置其他必要的环境变量
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

# 定义备份相关变量
backup_root_dir="/backup/rmanbackup"
date_suffix=$(date +%Y%m%d_%H%M%S)
backup_dir="$backup_root_dir/$date_suffix"  # 在备份根目录下创建以日期为名称的子目录
logfile="$backup_dir/rman_backup_$date_suffix.log"
rman_window=7

# 创建今天的备份目录
mkdir -p "$backup_dir/dbfullbak"
mkdir -p "$backup_dir/ctlbak"
mkdir -p "$backup_dir/spfbak"
mkdir -p "$backup_dir/archbak"

#Start logging rman runs.
echo $(date +"%Y-%m-%d %H:%M:%S") "RMAN备份开始。" > $logfile
#确定数据库是否运行
sqlplus -S  "/ as sysdba" <<EOF
whenever sqlerror exit 1
whenever oserror exit 1
select sysdate from dual;
exit
EOF
if [ $? -ne 0 ]; then
   echo "数据库未运行！！" >> $logfile
   echo $(date +"%Y-%m-%d %H:%M:%S") "RMAN备份结束。" >> $logfile
   exit
fi

# 执行RMAN备份
rman target / LOG "$logfile" append <<EOF
RUN {
  allocate channel c1 type disk;
  allocate channel c2 type disk;
  allocate channel c3 type disk;
  allocate channel c4 type disk;
  configure retention policy to recovery window of $rman_window days;
  configure backup optimization on;
  configure controlfile autobackup off;
  configure device type disk parallelism 4 backup type to backupset;
  backup as compressed backupset database tag='db_full_bak' format='$backup_dir/dbfullbak/db_%d_%T_%s_%p.bkp';
  sql 'alter system archive log current';
  backup as compressed backupset archivelog all delete input tag='arch_bak' format='$backup_dir/archbak/log_%d_%T_%s_%p.bkp';
  backup current controlfile tag='ctl_bak' format='$backup_dir/ctlbak/ctl_%d_%T_%s_%p.bkp';
  backup spfile tag='spfile_bak' format='$backup_dir/spfbak/spfile_%d_%T_%s_%p.bkp';
  crosscheck backup;
  report obsolete device type disk;
  delete noprompt obsolete device type disk;
  delete noprompt backupset completed  before 'sysdate-$rman_window' device type disk;
  delete noprompt expired backup device type disk;
  release channel c1;
  release channel c2;
  release channel c3;
  release channel c4;
}
list backup summary;
EXIT;
EOF

#删除7天前的trace log
adrci exec="set homepath diag/rdbms/cdb1/cdb1;purge -age 10080"
#删除文件夹
find $backup_root_dir -type d -ctime +$rman_window -exec rm -rf {} \;

# 检查备份是否成功
if grep -q 'RMAN-00569' "$logfile"; then
  echo "RMAN备份失败，请查看日志文件：$logfile"
else
  echo "RMAN备份成功"
fi

echo $(date +"%Y-%m-%d %H:%M:%S") "RMAN备份结束。" >> $logfile