ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10gRAC删除归档日志脚本

Oracle 10gRAC删除归档日志脚本

原创 Linux操作系统 作者:cqubityj 时间:2010-12-31 03:01:59 0 删除 编辑
功能:在归档日志备份完成且被DataGuard备库应用完成后,从主库删除。

#################################################################################
# Copyright 2010 BII-ERG Limited, All rights reserved
#
# Name:
#
# Version:
#
# Ver           Date            Author          Description
# 1.0           28/10/2010      Yu jun          Created
#
# Purpose:
#   28/10/2010--
#       Delete the archive logs after they have been backuped and the DataGuard Database
#       has applied them.
#       This is the script. which should be used on Primary database. The scripts
#       used by Standby Database is different from this one.
#
################################################################################

#!/usr/bin/bash

export ORACLE_SID=mlcprcdb1
export ORACLE_HOME=/app/oracle/product/10.2
export PATH=$PATH:$ORACLE_HOME/bin

TDATE=`/usr/bin/date '+%Y%m%d%H'`
#set -vx
###########################################################
### get the max applied log sequence on dataguard for each thread
###########################################################
APPSEQ1=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=2 and thread#=1 and applied='YES';
exit;
EOF)

APPSEQ2=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=2 and thread#=2 and applied='YES';
exit;
EOF)

###########################################################
### get the max backuped log sequence for each thread
###########################################################
BAKSEQ1=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=1 and thread#=1 and backup_count >= 1;
exit;
EOF)

BAKSEQ2=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=1 and thread#=2 and backup_count >= 1;
exit;
EOF)

###########################################################
### get the min sequence between max applied sequence and max backuped sequence
###########################################################
if [ $APPSEQ1 -lt $BAKSEQ1 ]; then
  DELSEQ1=$APPSEQ1
else
  DELSEQ1=$BAKSEQ1
fi

if [ $APPSEQ2 -lt $BAKSEQ2 ]; then
  DELSEQ2=$APPSEQ2
else
  DELSEQ2=$BAKSEQ2
fi

###########################################################
### get the max deleted log sequence for each thread
###########################################################
LDELSEQ1=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=1 and thread#=1 and deleted='YES';
exit;
EOF)

LDELSEQ2=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select max(sequence#) from v\$archived_log where dest_id=1 and thread#=2 and deleted='YES';
exit;
EOF)

###########################################################
### check if all archived log are backuped before delete  
###########################################################
CANDEL1=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select count(1) from v\$archived_log where dest_id=1 and thread#=1 and sequence# > $LDELSEQ1 and sequence# <= $DELSEQ1 and backup_count < 1;
exit;
EOF)

CANDEL2=$(sqlplus -silent "/ as sysdba" << EOF
set pages 0 feedback off verify off heading off echo off
select count(1) from v\$archived_log where dest_id=1 and thread#=2 and sequence# > $LDELSEQ2 and sequence# <= $DELSEQ2 and backup_count < 1;
exit;
EOF)


##########################################################
### use rman to delete the logs
##########################################################
if [ $CANDEL1 -eq 0 ] ; then
rman target / msglog=/app/oracle/log/rman1_${TDATE}.log << EOF
delete archivelog until sequence $DELSEQ1 thread 1;
YES
exit;
EOF
fi

if [ $CANDEL2 -eq 0 ] ; then
rman target / msglog=/app/oracle/log/rman2_${TDATE}.log << EOF
delete archivelog until sequence $DELSEQ2 thread 2;
YES
exit;
EOF
fi

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-682940/,如需转载,请注明出处,否则将追究法律责任。

上一篇: block change tracking
请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    435827