DataGuard之二：归档日志维护

最近给生产项目上了 DataGuard, 前两天一直考虑如何清除 stadnby和 primary 库上的归档,同时必须保证所清理的归档已经应用到备库上。网上查了好多资料也没有比较好的方法，后来咨询了 David.guo, 他建议先在备库上删除已被应用的归档日志，然后在到 primary 库上删除这个日志。 听到这个想法，我顿时大悟，之前我的想法是通过 rman去批量删除，然后又不好判断哪些归档。

既然有了思路, 做起来只是写 Shell脚本了，写Shell脚本之前，月几个地方需要注意下

如何判断备机上哪些日志被应用?

可以通过查询 v$archived_log的字段 applied 字段来判断，同时加上 REGISTRAR 和 name的限制条件， REGISTRAR=’RFS’ 表示 是 Remote File Server process 进程，别外加上 name is not null 是因为，通过rman crosscheck 并删除 expired的 archivelog 后 name 字段会被置空，

–sql 如下

SELECT name, SEQUENCE#, FIRST_TIME, REGISTRAR, APPLIED, NEXT_TIME, status  
 FROM V$ARCHIVED_LOG  
where applied = 'YES'  
 and REGISTRAR = 'RFS'  
 and name is not null  
ORDER BY SEQUENCE#;

在备机删除了已归档的archivelog后，如何在远端 primary 库同时删除这个日志?

这个问题在网上查了些资料，可以通过配置公钥和私钥，配置之后，scp文件，ssh 到远程主机执行命令都可以不用输入密码，具体原理可以到网上查查，这两点克服后，就可以写脚本了。

脚本 Clear_arch_orasid.sh

------- 功能：删除备机被 applied 的归档日志，同时也到主库上删除这个归档日志。  
#!/bin/bash  
#Author Francs.tan  
#Date 2010-12-20
################################################  
#Set Result Flag  
#0-Successful  
#1-No Archived log that have been applied by standby database  
#2-ERROR:Excute file clear_primary.sh  
#3-ERROR: File clear_primary.sh delete error
#initialize variable  
RESULT_FLAG=0  
file_num='0'
export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1  
export PATH=${ORACLE_HOME}/bin:${PATH}  
export ORACLE_SID=orasid  
export script_dir=/home/oracle/script/clear_arch  
export log_dir=/home/oracle/script/clear_arch/log  
DATE=`date +%Y%m%d`
## Select the earliest archived log file that have been applied by standby database  
sqlplus -s "/as sysdba"<<EOF  
set echo off  
set head off  
set linesize 200  
set feedback off;
col name format a50;  
col REGISTRAR format a10;  
col APPLIED format a10;
spool ${script_dir}/archivelog_list.txt
select 'file_num=' || name  
 from (SELECT nvl(name, '0') as name  
 FROM V$ARCHIVED_LOG  
 where applied = 'YES'  
 and REGISTRAR = 'RFS'  
 and name is not null  
 ORDER BY SEQUENCE#)  
where rownum = 1;
spool off;  
exit  
EOF
##Delete earliest archived log file that applied by standby database  
control_file=${script_dir}/archivelog_list.txt  
. ${control_file}
if [ ${file_num} == '0' ]  
then  
 echo "WARNING: No Archived log that have been applied by standby database !" | tee -a ${log_file}  
 RESULT_FLAG=1  
 echo "Result Flag:${RESULT_FLAG}" | tee -a ${log_file}  
 exit ${RESULT_FLAG}  
fi
log_file=${log_dir}/clear_log_${DATE}.log  
echo " begin rm archivelog file = ${file_num} * " | tee -a ${log_file}  
rm -f ${file_num}
##Crosscheck archivelog  
echo "* begin Crosscheck archivelog * " | tee -a ${log_file}  
. ${script_dir}/check_arc.sh >> ${log_file}
## Delete primary archived log  
echo "* Delete primary archived log * " | tee -a ${log_file}  
echo "rm -f ${file_num}" > ${script_dir}/clear_primary.sh  
scp ${script_dir}/clear_primary.sh [oracle@xxx.xxx.xxx.xx:${script_dir](mailto:oracle@xxx.xxx.xxx.xx:${script_dir)}  
ssh [oracle@xxx.xxx.xxx.xx](mailto:oracle@xxx.xxx.xxx.xx) chmod u+x ${script_dir}/clear_primary.sh  
ssh [oracle@xxx.xxx.xxx.xx](mailto:oracle@xxx.xxx.xxx.xx) ${script_dir}/clear_primary.sh
if [ $? -ne 0 ] ####如果前一条命令执行成功,则返回非0,否则为0  
then  
 echo "*" | tee -a ${log_file}  
 echo "ERROR:Excute file clear_primary.sh ,please check it! " | tee -a ${log_file}  
 echo "*" | tee -a ${log_file}  
 RESULT_FLAG=2  
 echo "Result Flag:${RESULT_FLAG}" | tee -a ${log_file}  
 exit ${RESULT_FLAG}  
fi
##rm temp files  
echo "* Remove temp files * " | tee -a ${log_file}  
rm -f ${control_file}  
rm -f ${script_dir}/clear_primary.sh  
ssh [oracle@xxx.xxx.xxx.xx](mailto:oracle@xxx.xxx.xxx.xx) rm -f ${script_dir}/clear_primary.sh
if [ $? -ne 0 ] ####如果前一条命令执行成功,则返回非0,否则为0  
then  
 echo "*" | tee -a ${log_file}  
 echo "ERROR: File clear_primary.sh delete error ,please check it! " | tee -a ${log_file}  
 echo "*" | tee -a ${log_file}  
 RESULT_FLAG=3  
 echo "Result Flag:${RESULT_FLAG}" | tee -a ${log_file}  
 exit ${RESULT_FLAG}  
fi
echo " Remove archivelog of Primary and Standby database successfully ! *" | tee -a ${log_file}  
exit  
#end

脚本 Check_arch.sh

---- 功能：在备库上较验归档日志，并删除过期的归档日志。  
#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1  
export PATH=${ORACLE_HOME}/bin:${PATH}
rman target / <<EOF  
crosscheck archivelog all;  
delete noprompt expired archivelog all;  
exit;
EOF

然后放到 crontab 里

##Clear archivelog that have been applied by standby database  
05 5 * * * /home/oracle/script/clear_arch/clear_arc_orasid.sh >> /home/oracle/script/clear_arch/log/clear.log 2>&1

总结

通过操作系统删除已归档的日志虽然并不建议，当是在DG环境下，通过操作系统删除归档日志后然后通过RMAN crosscheck 后其实也没多在关系。最主要的是这个方法能定期清理归档日志，同时又能保证清理的归档日志已经被应用到了备库,后期只要在备库上再加个归档日志定期较验并删除已经过期的归档日志脚本即可。以上就是DG 环境下归档日志清理方法全部脚本，这些脚本得益于 david.guo的建议。