How to use Oracle v$ views in Shell Script

It is difficult to use v$ views in Shell script to fetch any Oracle Database internal data, due to the special character $ associated with the views.But it can be achieved by using an backspace.
Below example shows how the v$ views can be used to fetch the open cursors limit and current max cursors reached in the Oracle session.

#Variable declaration
V_FILE_NAME=”Open_Cursor_Check.txt”
V_SES_TAB=”v\$sesstat”
V_STAT_TAB=”v\$statname”
V_PARAM_TAB=”v\$parameter”
V_SEARCH_TXT1=”error”
V_USERID=$FNDNAM
V_LOGIN=$GWYUID
V_DATABASE=$USID
V_PASSWORD=$1

echo “———Open Cursor Limit Check———————-” >> ${V_FILE_NAME}

V_Q_OUTPUT=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}

V_HIGH_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $1}’)
echo ‘Max cursors opened so far : ‘$V_HIGH_OC >> ${V_FILE_NAME}

V_MAX_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $2}’)
echo ‘Max cursors allowed to be opened : ‘$V_MAX_OC >> ${V_FILE_NAME}

if [ $V_HIGH_OC == $V_MAX_OC ]
then
echo “Error : SQL Open Cursors maximum limit is reached” >> ${V_FILE_NAME}
else
echo “SQL Open Cursors has not reached max limit” >> ${V_FILE_NAME}
fi
echo >> ${V_FILE_NAME}

#EoF

Shell Script to Scan files for a text

The below shell script will be useful to scan a file for a specific text and write the content to a text/log file.The usecase here is to scan the Oracle EBS Concurrent Log file for Workflow Listener for any errors and extract the error lines, write them in a log file and mail them to the appropriate team to take any action.The script is ideal to be executed in Oracle EBS environment Unix Servers ,as some variables values are fetched from then environment.

V_CUR_DT_TIME=`date +”%Y%m%d%H%M%S”`
V_FILE_NAME_P=”Test_CP_Log_”
V_FILE_NAME=”$V_FILE_NAME_P$V_CUR_DT_TIME.txt”

echo “Workflow Listener Monitor Log File ” > ${V_FILE_NAME}
echo “Instance Name : ${USID}” >> ${V_FILE_NAME}
echo “Scanning Script Run Time : $(date)” >> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}

#Variable declaration
V_SEARCH_TXT1=”error”
V_USERID=$FNDNAM
V_LOGIN=$GWYUID
V_DATABASE=$USID
V_PASSWORD=$1
V_EMAIL_ID=$2

#SQL Query to fetch current hour of the script run
V_CUR_DT=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}

# SQL Query to fetch the Workflow Listener Log file path and name
V_LOG_FILE=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}

#Get line number in the file where the Current Hour appears for first time
V_ERR_LINE=$(awk -v x=”$V_CUR_DT” ‘$0~x {print NR;exit}’ ${V_LOG_FILE})

if [ -z “${V_ERR_LINE}” ]
then
echo “No log entries found to scan for errors. ” >> ${V_FILE_NAME}
else
V_ERR_CNT=$(tail -n +${V_ERR_LINE} ${V_LOG_FILE} | grep -ic ${V_SEARCH_TXT1})
fi

#Check if search text exists and write in a log file
if [ “${V_ERR_CNT}” -eq 0 ] || [ -z “${V_ERR_CNT}” ]
then
echo “No errors found in Workflow Business Event Listener log file” >> ${V_FILE_NAME}
else
echo >> ${V_FILE_NAME}
echo “Extract of errors and exceptions logged in Workflow Listener Log file” >> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}
tail -n +${V_ERR_LINE} ${V_LOG_FILE} | grep -i ${V_SEARCH_TXT1} >> ${V_FILE_NAME}
fi
echo >> ${V_FILE_NAME}
echo “——————–End of File——————————–” >> ${V_FILE_NAME}

#Email the log file as attachment to recepients
(echo “Please find attached Workflow Business Event Listener error monitor log file for today’s run.Ignore this mail if the issues have been already fixed. – IT Support”;uuencode “${V_FILE_NAME}” “${V_FILE_NAME}”) | mailx -s “Workflow Listener Monitor Log : ${V_CUR_DT}” ${V_EMAIL_ID}

#EoF