Oracle 10gRAC删除归档日志脚本

功能：在归档日志备份完成且被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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/228190/viewspace-682940/