#!/bin/sh

# Shell script to collect Oracle data.
# Copyright (C) 2001-2004 Open Challenge B.V.
# Copyright (C) 2004 OpenEyeT Professional Services.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2, or (at your option)
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; see the file COPYING.
# If not, write to the Free Software Foundation,
# 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#
# Contact information: www.OpenEyeT.nl/scc/index.html 

# This is a system module of scc, to call it separately in the
# proper environment, use: scc-collect -i -e <module_name>

# SCC-release:	1.6.26
# file-version:	$Revision: 1.21 $
 
. ${SCC_BIN}/scc_modules/scc_utils

fixclass="fix:software:oracle"

if [ -z "${ORATAB}" ]
then
	if [ -f /etc/oratab ]
	then
		ORATAB=/etc/oratab
	elif [ -f /var/opt/oracle/oratab ]
	then
		ORATAB=/var/opt/oracle/oratab
	fi
fi

ORATAB_DIR="${ORATAB%/*}"

scc_check_file "${ORATAB}" "${fixclass}:${ORATAB}::" "#"
if [ -d "${ORATAB_DIR}" ]
then
	for ORAFILE in listener.ora tnsnames.ora sqlnet.ora tnsnav.ora
	do
		scc_check_file ${ORATAB_DIR}/${ORAFILE} "${fixclass}:sql-net:${ORATAB_DIR}/${ORAFILE}::" "#"
	done
fi

# Store the directories that we checked for the listener.ora, tnsnames.ora, ....
echo "${ORATAB_DIR}" > ${TMP1_FILE}

if [ -n "${TNS_ADMIN:-}" -a "${TNS_ADMIN:-}" != "${ORATAB_DIR}" ]
then
	for ORAFILE in listener.ora tnsnames.ora sqlnet.ora tnsnav.ora
	do
		scc_check_file ${TNS_ADMIN}/${ORAFILE} "${fixclass}:sql-net:${TNS_ADMIN}/${ORAFILE}::" "#"
	done
	echo "${TNS_ADMIN}" >> ${TMP1_FILE}
fi

if [ -f "${ORATAB}" ]
then
	# Use the oratab to determine a list of ORACLE_SID and ORACLE_HOME
	sed	-e 's/:/ /'		\
		-e 's/:.*//'		\
		-e 's/[#*].*//'		\
		-e '/^[ 	]*$/d'	\
			${ORATAB}			|
	while read sid home
	do
		# On AIX a space is appended to the processname (indicated by Rick Veenstra).
		user="$(grep "ora_pmon_${sid} *$" ${PROC_FILE} | awk 'NR == 1 { print $1 }')"
		if [ -n "${user}" ]
		then
			echo "${user} ${sid} ${home}"
		fi
	done
else
	# No oratab, try a default ORACLE_HOME and retrieve user/sid combination(s) from the process list.
	# To be extended later:
	# - according to OFA: /*/app/oracle/product/[89]* /*/app/oracle/product/10*/db*
	# - how do we combine the homes with the SID's when there are several homes and SID's?
	for dir in /opt/oracle
	do
		if [ -x "${dir}/bin/sqlplus" ]
		then
			sed -n	-e 's/^[ 	]*//'			\
				-e "s|[ 	]*$| ${dir}|"		\
				-e 's/[ 	].*ora_pmon_/ /p'	\
					<${PROC_FILE}
		fi
	done
fi			|
sort -u -f -k 2		|
while read ORACLE_USER ORACLE_SID ORACLE_HOME
do
	export ORACLE_HOME ORACLE_SID ORACLE_USER

	class="${fixclass}:${ORACLE_SID}:general"

	echo "${class}::\$ORACLE_SID:	${ORACLE_SID}"
	echo "${class}::\$ORACLE_HOME:	${ORACLE_HOME}"
	echo "${class}::Unix user:	${ORACLE_USER}"

	# Use the oracle user for executing svrmgrl and sqlplus.
	# When they are executed as root, they are spawned as user daemon,
	# which may result in file and directory permission problems.
	if [ -x "${ORACLE_HOME}/bin/svrmgrl" ]
	then
		echo "${class}::"

		su ${ORACLE_USER} -c "${ORACLE_HOME}/bin/svrmgrl command=exit 2>/dev/null" |
		sed	-e '/^[ 	]*$/d'	\
			-e '/Copyright/d'	\
			-e "s/^/${class}::svrmgrl:	/"
	fi

	ORA_VERSION="?"
	if [ -x "${ORACLE_HOME}/bin/sqlplus" ]
	then
		echo "${class}::"

		export force_sqlplus_query="yes"
		scc_oracle_query "prompt Copyright"             |
		sed -n -e "s/^\(SQL.*\) - Production on .*/${class}::sqlplus:   \1/p"
		force_sqlplus_query=""

		ORA_VERSION="$(${ORACLE_HOME}/bin/sqlplus -?						|
			awk '/Plus: Release/ { split( $3, p, "." ); printf( "%s%s\n", p[ 1 ], p[ 2 ] )}')"
	fi
	echo "${class}::oracle version:	${ORA_VERSION}"

	# list init.ora and init<SID>.ora
	for file in init.ora init${ORACLE_SID}.ora
	do
		scc_check_file ${ORACLE_HOME}/dbs/${file} "${fixclass}:${ORACLE_SID}:init file:${file}::" "#"
	done

	# list files included within init-files
	sed -n -e 's/^[ 	]*ifile.*=//p'		\
		${ORACLE_HOME}/dbs/init.ora		\
		${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora 2>/dev/null	|
	while read ORAFILE
	do
		scc_check_file ${ORAFILE} "${fixclass}:${ORACLE_SID}:init file:${ORAFILE}::" "#"
	done

	# check if .ora files are listed already, if not, list them
	# Check whether the current ${ORACLE_HOME} is already checked for *.ora.
	if [[ $(grep -c "^${ORACLE_HOME}\$" ${TMP1_FILE}) -eq 0 ]]
	then
		for ORAFILE in listener.ora tnsnames.ora sqlnet.ora tnsnav.ora
		do
			file="${ORACLE_HOME}/network/admin/${ORAFILE}"
			scc_check_file ${file} "${fixclass}:${ORACLE_SID}:sql-net:${file}::" "#"
		done
		echo "${ORACLE_HOME}" >> ${TMP1_FILE}	# Mark as checked
	fi

	##################################################################
	SP_FILE="${ORACLE_HOME}/scc_sp_$$"
	scc_oracle_query "create pfile='${SP_FILE}' from spfile;" >/dev/null 2>/dev/null

	class="${fixclass}:${ORACLE_SID}:spfile::"
	if [ -f ${SP_FILE} ]
	then
		sed	-e 's/--*$/-/'		\
			-e "s/^/${class}/"	\
				<${SP_FILE}
		rm -f ${SP_FILE}
	else
		echo "${class}unable to create spfile"
	fi

	##################################################################
	scc_oracle_query "show sga"	|
	sed -e "s/^/${fixclass}:${ORACLE_SID}:SGA::/"

	##################################################################
	cat >${TMP3_FILE} <<_X_
		select
			*
		from
			sys.v\$option
		order by
			PARAMETER;
_X_
	class="${fixclass}:${ORACLE_SID}:option::"

	scc_oracle_query "${TMP3_FILE}"				|
	sed	-e 's/--*$/-/'		\
		-e "s/^/${class}/"

	scc_help_info "${class}" <${TMP3_FILE}
	rm -f ${TMP3_FILE}

	##################################################################
	cat >${TMP3_FILE} <<_X_
		select
			substr( NAME, 1, 30 )	NAME,
			VALUE
		from
			sys.v\$parameter
		order by
			NAME;
_X_
	class="${fixclass}:${ORACLE_SID}:parameter::"

	scc_oracle_query "${TMP3_FILE}"				|
	sed	-e 's/--*$/-/'		\
		-e "s/^/${class}/"

	scc_help_info "${class}" <${TMP3_FILE}
	rm -f ${TMP3_FILE}

	##################################################################
	# Refer to OracleFAQ for this query (and more).
	cat >${TMP3_FILE} <<_X_
		select
			GRANTEE,
			PRIVILEGE,
			ADMIN_OPTION
		from
			SYS.DBA_SYS_PRIVS 
		where
			grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
					'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
					'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
					'IFSSYS\$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
					'TIMESERIES_DBA')
		order by
			1, 2, 3;
_X_
	class="${fixclass}:${ORACLE_SID}:privilege::"

	scc_oracle_query "${TMP3_FILE}"				|
	sed	-e 's/--*$/-/'		\
		-e "s/^/${class}/"

	scc_help_info "${class}" <${TMP3_FILE}
	rm -f ${TMP3_FILE}

	##################################################################
	cat >${TMP3_FILE} <<_X_
		select
			substr( PRODUCT, 1, 40 )	PRODUCT,
			substr( VERSION, 1, 15 )	VERSION,
			substr( STATUS, 1, 15 )		STATUS
		from
			PRODUCT_COMPONENT_VERSION
		order by
			1;
_X_
	class="${fixclass}:${ORACLE_SID}:product::"

	scc_oracle_query "${TMP3_FILE}"				|
	sed	-e 's/--*$/-/'		\
		-e "s/^/${class}/"
	rm -f ${TMP3_FILE}

	##################################################################
	class="${fixclass}:${ORACLE_SID}:installed products::"
	if [ -x ${ORACLE_HOME}/orainst/inspdver ]
	then
		${ORACLE_HOME}/orainst/inspdver 2>/dev/null		|
		sed -e "s/^/${class}/"
	else
		scc_check_file "${ORACLE_HOME}/install/unix.rgs" "${class}" "#"
	fi

	##################################################################
	cat >${TMP3_FILE} <<_X_
		select
			*
		from
			sys.v\$version
		order by
			1;
_X_
	class="${fixclass}:${ORACLE_SID}:version::"

	scc_oracle_query "${TMP3_FILE}"				|
	sed	-e 's/--*$/-/'		\
		-e "s/^/${class}/"
	rm -f ${TMP3_FILE}

	##################################################################
	query="\
		select
			TABLESPACE_NAME,
			STATUS,
			AUTOEXTENSIBLE,
			MAXBYTES,
			MAXBLOCKS,
			INCREMENT_BY,
			FILE_NAME
		from
			DBA_DATA_FILES
		order by
			1, 7;"
	class="${fixclass}:${ORACLE_SID}:database files::"

	# FILE_NAME is a "long" column, reduce multiple "-" from the header.
	scc_oracle_query "${query}"		|
	sed	-e "s/--*$/-/"		\
		-e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

	##################################################################
	extra_col=""
	case "${ORA_VERSION}" in
	81*)	do_query=1;
		;;
	9*)	do_query=1;
		extra_col=", SEGMENT_SPACE_MANAGEMENT"
		;;
	1*)	do_query=1;
		extra_col=", SEGMENT_SPACE_MANAGEMENT, BIGFILE"
		;;
	*)	do_query=0
		;;
	esac
	if [ ${do_query} -eq 1 ]
	then
		cat >${TMP3_FILE} <<EOF
			select
				TABLESPACE_NAME,
				CONTENTS,
				INITIAL_EXTENT,
				NEXT_EXTENT,
				ALLOCATION_TYPE ${extra_col}
			from
				DBA_TABLESPACES
			order by
				1,2;
EOF
		class="${fixclass}:${ORACLE_SID}:tablespace::"

		scc_oracle_query "${TMP3_FILE}"		|
		sed -e "s/^/${class}/"

		echo "${query}"				|
		scc_help_info "${class}"
	fi

	##################################################################
	query="\
		select
			TABLESPACE_NAME,
			TABLE_NAME,
			OWNER,
			CLUSTER_NAME
		from
			ALL_TABLES
		where
			( OWNER			!= 'SYSTEM'	) AND
			( OWNER			!= 'SYS'	) AND
			( TABLESPACE_NAME	is NOT NULL	) AND
			( TABLESPACE_NAME	!= 'SYSTEM'	)
		order by
			1, 2;"
	class="${fixclass}:${ORACLE_SID}:table::"

	scc_oracle_query "${query}"		|
	sed -e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

	##################################################################
	query="\
		select
			OWNER,
			SEGMENT_NAME,
			TABLESPACE_NAME,
			STATUS,
			INITIAL_EXTENT,
			NEXT_EXTENT,
			MIN_EXTENTS,
			PCT_INCREASE
		from
			DBA_ROLLBACK_SEGS
		order by
			1, 2;"
	class="${fixclass}:${ORACLE_SID}:rollback segments::"

	scc_oracle_query "${query}"		|
	sed -e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

	##################################################################
	query="\
		select
			*
		from
			DBA_SYS_PRIVS
		where
			( ADMIN_OPTION	= 'YES'		) AND
			( GRANTEE	!= 'DBA'	)
		order by
			1, 2;"
	class="${fixclass}:${ORACLE_SID}:admin privileges::"

	scc_oracle_query "${query}"		|
	sed -e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

	##################################################################
	query="\
		select
			TABLE_OWNER,
			TABLE_NAME,
			TRIGGER_NAME,
			TRIGGER_TYPE,
			STATUS,
			TRIGGERING_EVENT
		from
			DBA_TRIGGERS
		order by
			1, 2, 3;"
	class="${fixclass}:${ORACLE_SID}:triggers::"

	scc_oracle_query "${query}"		|
	sed	-e "s/--*$/-/"		\
		-e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

	##################################################################
	if [ ${ORA_LIST_USERS:-1} = 1 ]
	then
		scc_oracle_query "select USERNAME from ALL_USERS order by USERNAME;" |
		sed -e "s/^/${fixclass}:${ORACLE_SID}:users::/"
	fi

	##################################################################
	query="\
		select
			OWNER,
			OBJECT_NAME,
			OBJECT_TYPE,
			STATUS
		from
			ALL_OBJECTS
		where
			STATUS='INVALID'
		order by
			OWNER, OBJECT_NAME;"
	class="${fixclass}:${ORACLE_SID}:invalid objects::"

	scc_oracle_query "${query}"		|
	sed -e "s/^/${class}/"

	echo "${query}"				|
	scc_help_info "${class}"

done	# while read ORACLE_USER ORACLE_SID ORACLE_HOME
rm -f ${TMP1_FILE}

exit 0
