Oracle-Clean-Archivelog

2021-02-15

Oracle 定时清理 DG Archivelog

脚本部署路径为：/home/oracle/del_appl_arc/bin

脚本生成的日志路径：/home/oracle/del_appl_arc/log

#!/bin/sh
#########################################################################
#         This shell is for primary and standby database                #
#         to rm applied archivelog that before some day ago.            #
#                                                                       #
#       You can define "some day" in variables ${day_before}            #
#            This shell can be put in crontab for auto run              #
#                                                                       #
#            2008-01-18   writen by www.oracleblog.cn                   #
#########################################################################
## load profile file
## 填写oracle环境变量的路径
. /home/oracle/.bash_profile
## Path Define
## 填写脚本的路径
main_path=/home/oracle/del_appl_arc
bin_path=${main_path}/bin
log_path=${main_path}/log
arc_path=/u01/app/oracle/arch
cd ${bin_path}
## Initial script
touch app_arc_name.sh
chmod +x app_arc_name.sh
## rm applied archivelog that before ${day_before} day ago
## 填写删除那几天前的归档日志
day_before=1
## Db info
## 填写操作的用户账号密码
dbuser=system
dbpwd=oracle
## 若是备库修改成主库的tnsname，主库和备库都是一样的
dbsid=primary
########## Main shell start here ##########
## load exisit archlog list to db
sqlplus ${dbuser}/${dbpwd}@${dbsid}<<EOF >/dev/null
drop table ${dbuser}.arc_log_list;
CREATE TABLE ${dbuser}.arc_log_list (arc_name VARCHAR2(2000));
exit;
EOF
## 这一步你ls到的信息一定要与你实际的情况相互匹配
ls -l ${arc_path}|awk '{print $9}' |grep arc >arc_log_list.tmp
echo "load data">>arc_log.ctl
echo "infile 'arc_log_list.tmp'">>arc_log.ctl
echo "replace into table arc_log_list">>arc_log.ctl
echo "fields terminated by X'09'">>arc_log.ctl
echo "(arc_name)">>arc_log.ctl
sqlldr ${dbuser}/${dbpwd}@${dbsid} control=arc_log.ctl log=sqlldr_run.log bad=sqlldr_badfile.bad
### Create shell for rm applied archive that before some day ago
sqlplus -s "/ as sysdba"<<EOF>/dev/null
set feedback off
set pages 0
set head off
set timing off
set echo off
spool app_arc_name.tmp
select 'rm -f '||'${arc_path}/'||arc_name from ${dbuser}.arc_log_list 
intersect
select 'rm -f '||name from v\$archived_log 
where DEST_ID=1 and name like '%.arc' 
and SEQUENCE#<(select max(SEQUENCE#) from v\$archived_log where applied='YES')
and COMPLETION_TIME<=sysdate-${day_before};
spool
exit
EOF
## Exec the shell in background mode
cat app_arc_name.tmp |grep -v spooling>app_arc_name.sh
./app_arc_name.sh
chmod -x app_arc_name.sh
mv app_arc_name.sh rm_appl_arc_`date +"%Y%m%d%H%M"`.log
mv rm_appl_arc*.log ${log_path}
rm app_arc_name.tmp arc_log.ctl sqlldr_run.log arc_log_list.tmp

完成脚本后，你可以把脚本放入 crontab 中定期运行，以达到自动删除 n 天以前且已经 applied 的归档日志。

注意 crontab 的部署把 primary 和 standby 的时间错开，如果同时进行，会对 arc_log_list 表有争用。