利用SharePlex内部表查询SEQNO制定Oracle归档删除策略，防止误删除SharePlex未捕获的归档

# 注意 RAC环境下需要在delete noprompt archivelog until sequence ${SEQNO} thread n;
# 指定线程号，由于不同实例序号不同，需要对不同实例单独删除归档。

#!/bin/bash
# This script was used to delete archivelog via
# obtain shareplex captured sequence number.

# Script section 1:initialize environment variable.
export ORACLE_SID=honor1                                   # Please replace value with your environment.
export ORACLE_BASE=/u01/app/oracle                         # Please replace value with your environment.
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1          # Please replace value  with your environment.
export LANG=en_US                                          # Please replace value  with your environment.
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8                 # Please replace value  with your environment.
DATE_TI=`date +%Y-%m-%d-%H:%M:%S`
USER_NAME=splex3000                                       # Please replace value  with your environment.
PASSWD=splex3000                                          # Please replace value  with your environment.

# Script section 2: obtain sequence number.
# Below section through query shareplex internal table
# to obtain shareplex captured oracle sequence number.
QUERY_SQL="SELECT SEQNO FROM SHAREPLEX_ACTID WHERE INSTANCE_NAME='${ORACLE_SID}';"
SEQNO_STR=`sqlplus -S ${USER_NAME}/${PASSWD} <<EOF
                                   set timing off;              # 关闭执行语句结束统计时间，防止后续取seqno数字错误
                                   set time off;                # 关闭显示系统时间，防止后续取seqno数字错误
                                   ${QUERY_SQL}
                                   quit;
EOF`

if [ $? != 0 ]; then
    exit 0
fi

SEQNO=$((``echo ${SEQNO_STR#*SEQNO}`|sed -n '/SEQNO/,$p'|tr -cd '0-9'` - 10))   # 调取上面SQL查询结果字符串，使用sed取SEQNO到行尾$p的所有行，利用tr取数字部分，完成提取。
      
if [ $? != 0 ]; then
    exit 0
fi
# Script section 3:delete archivelog via rman utility.
echo -e "\n"
date
echo -e "***********Begin delete***********\n"
$ORACLE_HOME/bin/rman target / log=/home/oracle/rman_delete_arch_${DATE_TI}.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until sequence ${SEQNO};
release channel c1;
release channel c2;
}
exit;
EOF
echo -e "\n"
date
echo "***********Finished delete***********"