#! /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