ksh scripting

Hi All,

Can you please let me know what I missed in the below function? Whenever I try to execute the script i am getting

syntax error at line 101 : `fi' unexpected

Function is

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
}

I call the above function from a while loop. The below code is not the complete one, i took a portion from the while loop

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

I tried just putting return 0 in the function and the script runs. So i believe the issue in the function code.

function DELNWE5O {
                return 0
}

Please let me know your suggestion.

Thanks,
Mani

Without seeing the rest of the script we can't be sure, but the if statements marked in red do not have matching fi s. And the while ... do statement does not have a matching done . Since ksh is complaining about a missing fi , one of the if s with no matching fi would seem to be your immediate problem.

Thanks For the reply.

The below code is the complete script except the function. I already mentioned the function

# 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

Thanks,
Mani

You tell us the Korn shell tells you you have an error on line 101 in your script.

You show us a script that contains 65 lines from which a 27 line function has been removed.

So, you have shown us 92 lines of a script that contains an error that has been detected on the 101st line in that script. I am sorry that I am unable to help you detect what is wrong with the code that comes 9 lines after the code you have shown us. If you refuse to give us code and the actual diagnostics that come from an attempt to execute that code, I don't see how we can help you.

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

Sorry about that..... The above is the code.

Thanks,
Mani

Use indention to 'visualy highlight' code blocks and their 'depth'.
Fixed and marked the possible candidate in red.

[s]```text
removed

\(EDIT: removed code since i've overseen a starting if... and below that is already fixed.

Also, you might be missing a  ``` = ```  on line 86 \(the if with grep\).

hth

Can I suggest a couple of things:

Indent all your if,do,while blocks you will find issues like this much easier

make use of <<-"EOF" when using here-documents

This allows you to indent the EOF and makes your script easier to read. You also don't need to escape dollar ($) symbols as no shell expansion is done.

I believe the here-document starting on line 30 wasn't ended correctly because the EOF was indented.

Here is the corrected and nicely formatted code:

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

Thanks..That worked....But dont know..unix variable is not taking....

I am getting below error

SELECT 'ALTER DISKGROUP ' $DG ' DROP FILE '''||R.NAME||'''; '
                          *
ERROR at line 1:
ORA-00911: invalid character

Tried different combinations like ${DG}, $DG, "${DG}".

Can you please suggest?

Function :-

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`
        echo ${DG} > /ora00/grid/Mani/dg.log
        export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
        export ORACLE_SID=nwe5o
        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 /ora00/grid/Mani/delete_list_ods_nwe5o.sql
                SELECT '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
}

The quotes around EOF stop any variable expansion. If you need the variable expanded you will need to remove the quotes and use backslash for any dollar $ characters you need in the passed SQL code.

        sqlplus -s '/as sysdba' <<-EOF
                SET PAGES 0 LINES 175 HEAD OFF FEED OFF
                spool /ora00/grid/Mani/delete_list_ods_nwe5o.sql
                SELECT '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

Thanks Chubler..Appreciate you help..that worked....