如何实现自动删除归档日志的脚本
更新:HHH   时间:2023-1-7


这篇文章主要介绍如何实现自动删除归档日志的脚本,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!

自动删除归档日志的脚本(尤其是dataguard环境)

已有 236 次阅读2011-12-16 21:02 |个人分类:oracle data guard

自动删除归档日志的脚本(尤其是dataguard环境)

在归档模式下,要时刻注意磁盘空间不要被归档撑爆,尤其在dataguard环境中,更是需要定期清理已经apply的日志,以免把硬盘撑爆。

在自动删除日志需要考虑几点:
1. 日志必须是已经被apply的
2. 日志备份已经被备份过的
3. 为了保证一定的管理余地,不要apply后马上删除,而应该根据实际情况设定一个删除策略。
4. 脚本要能够兼容primary和standby两种状态,且自动判断状态并执行不同的逻辑,否则在切换后,如果忘记修改脚本,那就可能杯具了。

以下是我用于删除归档日志的一个脚本,运行这个脚本需要输入一个参数来控制日志的保留时间。
这个脚本可用于primary端也可用于standby端,
1. 对于standby端,只要在保存周期内且被apply的归档都会被删除
2. 对于primary端,除了满足保存周期以及被apply条件外,还要保证归档已经被备份过才会被删除

对于dataguard环境,虽然备份可以选择在primary和standby端执行,但如果压力不是非常大的话,为了管理方便,更建议在primary端执行。

详细脚本如下:

[oracle@dwapp1 DBA]$ cat delete_arch.sh
#!/bin/bash

##################################################################################################################
#
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
#
##################################################################################################################

source /home/oracle/.bash_profile

#####################
usage()
{ #usage
echo " USAGE: `basename $0` $retention"
exit 2
}


ArgNum=1

if [ ! $# -eq $ArgNum ];then
echo " "
echo " Incorrect parameter"
usage
fi


retention=$1

script=`basename $0`

dir=/tmp
tmpf=$dir/.$script.tmp

# get archived log list for standby database
function GetLogListForStandby
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from(
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn from v$archived_log a
where completion_time <sysdate-$retention
and a.resetlogs_id in (
select i.resetlogs_id from v$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1
order by sequence#;
exit
EOF
return
}

function GetDBRole
{
sqlplus -S /nolog <<EOF
connect / as sysdba
set head off
set feedback off
set pages 0
select controlfile_type from v$database;
exit
EOF
return
}

# get archived log list for primary database
function GetLogListForPrimary
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from(
select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
sum(backup_count) over(partition by a.sequence# ) bk_cnt,
count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
from v$archived_log a where completion_time <sysdate-$retention
and a.resetlogs_id in (
select i.resetlogs_id from v$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1 and bk_cnt>0
order by sequence#;
exit
EOF
return
}

function GetDBRole
{
sqlplus -S /nolog <<EOF
connect / as sysdba
set head off
set feedback off
set pages 0
select controlfile_type from v$database;
exit
EOF
return
}



# check database role
DBROLE=`GetDBRole`

NUM=0

if [ $DBROLE = "CURRENT" ];then
echo "It's a primary database ......"
# get archived log list for primary
GetLogListForPrimary

elif [ $DBROLE = "STANDBY" ];then
echo "It's a standby database ......"
# get archived log list for standby
GetLogListForStandby
fi

echo "deleting archived log files ......"

if [ -n $tmpf ]; then
for ARCH in `cat $tmpf`;do
if [ -f $ARCH ];then
NUM=`expr $NUM + 1`
rm -f $ARCH
fi
done
fi

rm -f $tmpf

echo "finished deleting $NUM files"


使用测试:需要输入一个参数,用于设定保存周期。以下例子是删除3天前的归档

[oracle@dwapp1 DBA]$ ./delete_arch.sh 3
It's a primary database ......
deleting archived log files ......
finished deleting 12 files

设定定时任务自动执行

1 */4 * * * /home/oracle/DBA/delete_arch.sh 2


当然,对于非dataguard环境或者dataguard环境的primary端,更建议使用RMAN来管理归档了。

以上是“如何实现自动删除归档日志的脚本”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注天达云行业资讯频道!

返回开发技术教程...