#!/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 的归档日志。
