Script to TRACE Oracle Session

In order to simplify the steps of enabling, stopping the trace on Oracle sessions and make the output trace data readable, I've written two scripts, start_tracing.sh will let you enable tracing on a specific session, and stop_tracing.sh to stop tracing the session and provide the original trace file along with another readable version of the trace file using TKPROF utility.

Download links:

start_tracing.sh
https://www.dropbox.com/s/dkhjteqckcuykqd/start_tracing.sh?dl=0

stop_tracing.sh
https://www.dropbox.com/s/41krahxx8m67uyg/stop_tracing.sh?dl=0

Those scripts are part of the DBA bundle which equipped with many of easy to use scripts that simplify day to day database administration tasks:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".

start_tracing.sh

	# #####################################################################################################
	# Script to Enable the trace on an Oracle Session.
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 24-12-11 # # # # #
	#
	# Modified: 31-12-13 Customized the script to run on various environments.
	# 04-05-14 Enhanced trace file search criteria.
	# 16-06-20 Enhanced trace file search criteria.
	#
	# #####################################################################################################
	# ###########
	# Description:
	# ###########
	echo
	echo "=================================================="
	echo "This script Enables tracing for an Oracle Session."
	echo "=================================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
	# ###########################
	# Listing Available Databases:
	# ###########################
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the ORACLE_SID:[Enter the number]"
	echo ---------------------
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	if [ -z "${REPLY##[0-9]*}" ]
	then
	export ORACLE_SID=$DB_ID
	echo Selected Instance:
	echo
	echo "********"
	echo $DB_ID
	echo "********"
	echo
	break
	else
	export ORACLE_SID=${REPLY}
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
	fi
	# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
	fi
	# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
	fi
	# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	CURR_USER=`whoami`
	if [ ${ORA_USER} != ${CURR_USER} ]; then
	echo ""
	echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	echo "Script Terminated!"
	exit
	fi
	# #########################
	# Getting BDUMP Location:
	# #########################
	VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 feedback off;
	prompt
	select value from V\$DIAG_INFO where NAME='Diag Trace';
	exit;
	EOF
	)
	BDUMP=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
	export BDUMP
	if [ ! -d ${BDUMP} ]
	then
	VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 feedback off;
	prompt
	SELECT value from v\$parameter where NAME='user_dump_dest';
	exit;
	EOF
	)
	BDUMP=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
	export BDUMP
	fi
	# #################################
	# SQLPLUS: Start Tracing a Session:
	# #################################
	# Variables:
	# #########
	echo ""
	echo "Please enter the Username you want to trace its session:"
	echo "========================================================"
	read USERNAME
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set linesize 143
	set pagesize 1000
	set feedback off
	set trim on
	set echo off
	col USERNAME for a35
	col MODULE for a30
	Select username,module,SQL_ID "Curr_SQLID",prev_sql_id "Prev_SQLID",status,sid,serial#
	from v\$session
	where username like upper ('%$USERNAME%');
	EOF
	# Unlock Execution part:
	echo
	echo "Enter the session SID:"
	echo "---------------------"
	read SESSIONID
	if [ -z "${SESSIONID}" ]
	then
	echo No Value Entered!
	echo Script Terminated.
	exit
	fi
	echo "Enter the session SERIAL#:"
	echo "-------------------------"
	read SESSIONSERIAL
	if [ -z "${SESSIONSERIAL}" ]
	then
	echo "No Value Entered!"
	echo "Script Terminated."
	exit
	fi
	VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	begin
	dbms_monitor.session_trace_enable (
	session_id => '$SESSIONID',
	serial_num => '$SESSIONSERIAL',
	waits => true,
	binds => true
	);
	end;
	/
	EOF
	)
	VAL2=`echo $VAL1| grep "successfully completed"`
	if [ -z "${VAL2}" ]
	then
	echo
	echo "The Session with Provided SID & SERIAL# is NOT EXIST!"
	echo "Script Terminated."
	echo
	exit
	fi
	echo
	VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT p.spid FROM v\$session s,v\$process p WHERE p.addr = s.paddr and s.sid='$SESSIONID' and s.serial#='$SESSIONSERIAL';
	EOF
	)
	VAL22=`echo $VAL11| awk '{print $NF}'`
	VAL33=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT INSTANCE_NAME FROM V\$INSTANCE;
	EOF
	)
	VAL44=`echo $VAL33| awk '{print $NF}'`
	echo "TRACING has been ENABLED for session SID:${SESSIONID} / SERIAL#:${SESSIONSERIAL}"
	TRACEFILE=`find ${BDUMP}/${VAL44}_ora_${VAL22}.trc -mmin -10|tail -1`
	sleep 1
	echo
	echo "Trace File Location:"
	echo "-------------------"
	if [ -z ${TRACEFILE} ]
	then
	echo "Once the session start doing activities, try to find the TRACE FILE using the following command:"
	echo "find ${BDUMP}/${VAL44}_ora_${VAL22}.trc -mmin -10"
	else
	echo "Trace File is: ${TRACEFILE}"
	fi
	echo
	sleep 2
	echo -e "\033[33;9mDon't forget to STOP the Tracing once you Finish, Using 'stoptrace' Script.\033[0m"
	echo
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: <mahmmoudadel@hotmail.com>.
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

view raw start_tracing hosted with ❤ by GitHub

stop_tracing.sh

	# #################################################
	# Script to STOP tracing an Oracle Traced Session.
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 24-12-11 # # # # #
	# Modified: 31-12-13
	# Customized the script to run on
	# various environments.
	# 04-05-14 Enhanced search criteria
	# for generated trace file.
	#
	# #################################################
	# ###########
	# Description:
	# ###########
	echo
	echo "================================================="
	echo "This script STOP Tracing a TRACED Oracle Session."
	echo "================================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
	# ###########################
	# Listing Available Databases:
	# ###########################
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the ORACLE_SID:[Enter the number]"
	echo ---------------------
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	if [ -z "${REPLY##[0-9]*}" ]
	then
	export ORACLE_SID=$DB_ID
	echo Selected Instance:
	echo
	echo "********"
	echo $DB_ID
	echo "********"
	echo
	break
	else
	export ORACLE_SID=${REPLY}
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
	fi
	# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
	fi
	# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
	fi
	# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	CURR_USER=`whoami`
	if [ ${ORA_USER} != ${CURR_USER} ]; then
	echo ""
	echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	echo "Script Terminated!"
	exit
	fi
	# #########################
	# Getting BDUMP Location:
	# #########################
	VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 feedback off;
	prompt
	select value from V\$DIAG_INFO where NAME='Diag Trace';
	exit;
	EOF
	)
	BDUMP=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
	export BDUMP
	if [ ! -d ${BDUMP} ]
	then
	VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set pages 0 feedback off;
	prompt
	SELECT value from v\$parameter where NAME='user_dump_dest';
	exit;
	EOF
	)
	BDUMP=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
	export BDUMP
	fi
	# ###############################
	# SQLPLUS: Stop Tracing a Session:
	# ###############################
	echo ""
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set linesize 143
	set pagesize 1000
	set feedback off
	set trim on
	set echo off
	col USERNAME for a35
	col MODULE for a30
	PROMPT The following are the session that are being TRACED:
	select username,module,status,sid,serial# from v\$session where sql_trace='ENABLED';
	EOF
	# Unlock Execution part:
	echo
	echo "Enter the User's session SID:"
	echo "============================="
	read SESSIONID
	if [ -z "${SESSIONID}" ]
	then
	echo No Value Entered!
	echo Script Terminated.
	exit
	fi
	echo
	echo "Enter the User's session SERIAL#:"
	echo "================================="
	read SESSIONSERIAL
	if [ -z "${SESSIONSERIAL}" ]
	then
	echo "No Value Entered!"
	echo "Script Terminated."
	exit
	fi
	VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT p.spid FROM v\$session s,v\$process p WHERE p.addr = s.paddr and s.sid='$SESSIONID' and s.serial#='$SESSIONSERIAL';
	EOF
	)
	VAL22=`echo $VAL11| awk '{print $NF}'`
	VAL33=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT INSTANCE_NAME FROM V\$INSTANCE;
	EOF
	)
	VAL44=`echo $VAL33| awk '{print $NF}'`
	VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	begin
	dbms_monitor.session_trace_disable (
	session_id => '$SESSIONID',
	serial_num => '$SESSIONSERIAL');
	end;
	/
	EOF
	)
	VAL2=`echo $VAL1| grep "successfully completed"`
	if [ -z "${VAL2}" ]
	then
	echo
	echo "The Session with Provided SID & SERIAL# Is NOT Being Traced!"
	echo "Script Terminated."
	echo
	exit
	fi
	echo
	sleep 1
	echo "Tracing Has been STOPPED Successfully."
	TRACEFILE=`find ${BDUMP}/${VAL44}_ora_${VAL22}.trc -mmin -10|tail -1`
	echo
	echo "Trace File Location:"
	echo "-------------------"
	if [ -z ${TRACEFILE} ]
	then
	echo "You can find the TRACE file Under: ${BDUMP}"
	else
	sleep 1
	echo "${TRACEFILE}"
	${ORACLE_HOME}/bin/tkprof ${TRACEFILE} ${TRACEFILE}_tkprofed.log sys=no waits=yes
	sleep 3
	echo "The TKPROFED version [Readable version] of the TRACE FILE is:"
	echo "--------------------"
	echo "${TRACEFILE}_tkprofed.log"
	fi
	echo
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: <mahmmoudadel@hotmail.com>.
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

view raw stop_tracing hosted with ❤ by GitHub