请教下主备库的归档日志如何被删除才安全? delete才为YES
我有一套RAC 架构的数据库 是ORACLE 11G R 2 11.2.0.1.0以前采用脚本删除
cat del_arc.sh
#! /bin/bash
source ~/.bash_profile
exec >> /home/oracle/dbscripts/logs/del_arch`date +%F_%H`.log
$ORACLE_HOME/bin/rman target / <<EOF
report obsolete;
delete noprompt obsolete;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
crosscheck archivelog all;
exit;
EOF
由于最近业务日志产生很多,如果改成sysdate-1的话, 我又担心主库把日志给删除了,而备库没有应用到.造成日志GAP!
这个情况本地测试部门的数据库遭遇一次,搞得重新搭建备库! 这算是小事故. 如果生产环境发生这事算是中级事故!
因此写个脚本 根据V$ARCHIVE_LOG表中的APPLIED=YES AND DELETED=NO 为判断依据
cat build_del_archivelog.sh
sqlplus -s shark/235619<<EOF
set heading off
set linesize 100
set echo off
set feedback off
set timing off
set termout on
set trimout off
set trimspool on
set newpage none
set recsep off
col logname format a50
select distinct 'rm -f /arch/'||Thread#||'_'||sequence#||'_822142156.dbf;' as logname
from v\$archived_log
where sequence# in
(
select sequence#
from v\$archived_log
where applied='YES'
and deleted='NO'
and completion_time < trunc(sysdate)
and completion_time > trunc(sysdate)-3
group by sequence#
having count(recid) > 1
)
and applied='YES'
and deleted='NO'
and completion_time < trunc(sysdate)
and completion_time > trunc(sysdate)-3;
EOF
然后再用脚本去交叉检验下
cat crosscheck_archivelog.sh
#! /bin/bash
source /home/oracle/.bash_profile
exec >> /home/oracle/dbscripts/logs/del_arch`date +%F_%H`.log
$ORACLE_HOME/bin/rman target / <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF
可发现 每次查询V$ARCHIVEd_LOG时候很慢 需要5分钟时间!
而经检查发现V$archvied_log 有8619条未删除的记录,最早时间是5月26日
select name,sequence#,deleted,status,al.FIRST_TIME,al.COMPLETION_TIME
from v$archived_log al
where applied='YES' and deleted='NO'
order by al.COMPLETION_TIME desc
实际上 归档目录只保留了6月6日和6月7日的归档日志376条,并且通过RMAN 也得376条记录
RMAN> crosscheck archivelog all;
Crosschecked 376 objects
我知道了 大约是我这里有两个备库
主库需要等 cross check archivelog all; 并且delete expried archived log
才能把主库本地的归档值为已删除,而备库的归档是否删除要由备库返回消息 可以参考老熊那篇文章。
页:
[1]