#! /bin/ksh
#############################################
# Script to delete ODS archived logs from ASM
#############################################
export TIMESTAMP=`date "+%y%m%d_%H%M%S"`
set -a
set -x
SLEEPTIME=1800
date
exec >/users/oracle/Mani/logdel_${TIMESTAMP}.log 2>&1
#############################################################
# DECLARE STREAMS AND NON-STREAMS DATABASES IN THIS ARRAY

#set -A STREAMS_ODS_DB_LIST nwc2 nwd2 nwe2 nwf2 nwo2 nwb2 nwa2 nwk2 nww2 nwt2 nwu2 nwr2 nwx2 nwy2 nwa5 nwc5 nwe3 nwd5 nwe5 nwf3 nwf5 nwh3 nwi3 nwx2 nwj3
set -A STREAMS_ODS_DB_LIST nwb3 nwa2 nwa5 nwb2 nwc2 nwc5 nwd2 nwd5 nwe2 nwe3 nwe5o nwf2 nwf3 nwf5 nwh3 nwi3 nwj3 nwk2 nwo2 nwr2 nwt2 nwu2 nww2 nwx2 nwy2 nwz2 nwk3

function DELNWE5O {
        export ORACLE_HOME=/ora00/app/oracle/product/11.2.0/grid_1
        export ORACLE_SID=+ASM
        export PATH=${ORACLE_HOME}/bin:${PATH}
        DG=`sqlplus -s '/as sysasm' <<EOF
        set pagesize 0 feedback off verify off heading off echo off;
        select NAME from v\$asm_client ac ,v\$asm_diskgroup ad where ac.GROUP_NUMBER=ad.GROUP_NUMBER and ac.INSTANCE_NAME like 'nwe5o' and NAME like '%ARCH3';
        exit;
        EOF`
        export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
        export ORACLE_SID=$ORACLE_SID
        export PATH=${ORACLE_HOME}/bin:${PATH}
        LIBPATH=${ORACLE_HOME}/lib
        sqlplus -s '/as sysdba' <<EOF
        SET PAGES 0 LINES 175 HEAD OFF FEED OFF
        spool /users/oracle/Mani/delete_list_ods_nwe5o.sql
        SELECT  --+ NO_MERGE(C)
        'ALTER DISKGROUP ${DG}  DROP FILE '''||R.NAME||'''; '
        FROM DBA_REGISTERED_ARCHIVED_LOG R, DBA_CAPTURE C, V\$ASM_FILE F
        WHERE R.CONSUMER_NAME = C.CAPTURE_NAME
        AND R.NEXT_SCN < C.REQUIRED_CHECKPOINT_SCN
        AND F.FILE_NUMBER = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,1)+1,INSTR(R.NAME,'.',1,2)-INSTR(R.NAME,'.',1,1)-1)
        AND F.INCARNATION = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,2)+1);
        spool off
        EXIT
        EOF
}

# DATABASES NOT IN THE ABOVE LISTS ARE NOT IN ARCHIVELOG MODE
#############################################################
#SLEEPTIME=40
while :; do
# Delete downstream archivelogs for Streams ODS databases
echo
echo "-------------------------------------------------------"
echo "Deleting Downstream Logs for Streams ODS Databases ...."
date
echo "-------------------------------------------------------"
#rm -f /tmp/delete_list_ods.log
if [[ ! -z ${STREAMS_ODS_DB_LIST[@]} ]]; then
echo ${STREAMS_ODS_DB_LIST[@]}
        for ORACLE_SID in `echo ${STREAMS_ODS_DB_LIST[@]}`; do
                if [[ `ps -ef | grep "ora_smon_${ORACLE_SID}" | grep -v 'grep' | wc -l |awk '{ print $1 }'` != 0 ]]; then
                ORAENV_ASK=NO
                export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
                export ORACLE_SID=$ORACLE_SID
                export PATH=${ORACLE_HOME}/bin:${PATH}
                LIBPATH=${ORACLE_HOME}/lib
                echo "Deleting Logs For SID : ${ORACLE_SID} ...."
                if [[ ${ORACLE_SID} == "nwe5o" ]]
                then
                DELNWE5O
                fi
                sqlplus -s '/as sysdba' <<EOF
                        SET PAGES 0 LINES 175 HEAD OFF FEED OFF
                        spool /users/oracle/Mani/delete_list_ods.sql
                        SELECT  --+ NO_MERGE(C)
                        'ALTER DISKGROUP ${ORACLE_SID}ARCH3  DROP FILE '''||R.NAME||'''; '
                        FROM DBA_REGISTERED_ARCHIVED_LOG R, DBA_CAPTURE C, V\$ASM_FILE F
WHERE R.CONSUMER_NAME = C.CAPTURE_NAME
                        AND R.NEXT_SCN < C.REQUIRED_CHECKPOINT_SCN
                        AND F.FILE_NUMBER = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,1)+1,INSTR(R.NAME,'.',1,2)-INSTR(R.NAME,'.',1,1)-1)
                        AND F.INCARNATION = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,2)+1);
                        spool off
                        EXIT
EOF
# If there are no ORA- errors from the previous SQL*Plus
# command, delete the logs
if [[ `grep -c 'ORA-' /users/oracle/Mani/delete_list_ods.sql` = 0 ]]; then
        export ORACLE_HOME=/ora00/app/oracle/product/11.2.0/grid_1
        export ORACLE_SID=+ASM
        export PATH=${ORACLE_HOME}/bin:${PATH}
sqlplus -s '/as sysasm' <<EOF
spool delete_list_ods.log
select sysdate from dual;
spool off
exit
EOF
else
echo "  ORA- Error"
cat /users/oracle/Mani/delete_list_ods.sql
fi
#rm -f /tmp/delete_list_ods.sql
fi
done
else
echo "No Streams ODS databases found"
fi
cat delete_list_ods.log
echo
echo "++++ Sleeping For ${SLEEPTIME} Seconds ...."
echo
sleep ${SLEEPTIME}
done