oracle归档日志删除脚本,定期删除DG归档日志的脚本

#!/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.org #

#########################################################################

## load profile file

. /oracle/.bash_profile

## Path Define

main_path=/oracle/del_appl_arc

bin_path=${main_path}/bin

log_path=${main_path}/log

arc_path=/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=test

dbpwd=test

dbsid=primary

########## Main shell start here ##########

## load exisit archlog list to db

sqlplus ${dbuser}/${dbpwd}@${dbsid}</dev/null

drop table ${dbuser}.arc_log_list;

CREATE TABLE ${dbuser}.arc_log_list (arc_name VARCHAR2(2000));

exit;

EOF

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 ${dbuser}/${dbpwd}@${dbsid}</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 test.arc_log_list

intersect

select 'rm -f '||name from v\$archived_log

where DEST_ID=1 and name like '%.arc'

and SEQUENCE#

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

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