Unable to obtain current patch information due to error: 16000

Error:

When starting up a standby database in open mode I noticed the following error in the alertlog:

Unable to obtain current patch information as the database or pluggable database was opened for read-only access.
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 16000
===========================================================


Analysis:

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

 

Fix:

Unfortunately there is no fix for the time being as of now, but the following bug is already being worked by the development team:

Bug 21098077 - PACKAGES USED TO OBTAIN DATABASE PATCH INFORMATION SHOULD HANDLE READ-ONLY DB

Meanwhile, This error is minor and doesn't indicate any problem with the database, so it can be safely ignored.

SQL Tuning Script

 This shell script will help you tune a SQL Statement in Oracle by doing the following functions:

It will ask you for the SQL Statement sqlid, then it will do the following:

1- Display the SQL Statement Statistics like: Executions, ELAPSED/CPU time, Waits, I/O stats:

 

2- Display the SQL TEXT along with populating the bind variables:

 

 

3- Display the current Execution Plan:

4- Display the history of Execution Plans and execution statistics [if found]. [This section quoted from Tim Gorman's sqlhistory.sql script after taking his permission (many thanks to Tim)]

This will help the user investigate the statement performance over the time by checking the change to the execution plan.

5- Display the SQL Plan Baseline, if the statement is already using a baseline for execution plan stability. [SQL Plan Baseline is an 11g new feature maintains the execution plan stability by storing the execution plans for a statement and use the best plan out of them in case the user didn't fix a plan yet]

6- If the statement is not part of SQL Plan Baseline, the script will offer the option of fixing an execution plan by creating a new baseline for this statement: [If the DB version is 11g+]

 

 7- If the statement is already in a SQL Plan Baseline, it will check the number of the available plans for this statement, if the plans are more than 1 plan, the script will display all the plans details, and will offer the option to fix a plan:

 

 You can view the plan details in a tabular format using the shown Select statement: [in 12c+]

select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));

 

 Then you can get back to the script and decide which plan to FIX for this statement:

Then it will show the statement to use in case you want to rollback the change by dropping the Baseline (yes we can disable the baseline but disabling the baseline will not eliminate the optimizer of using it, disabling the baseline cannot be considered as a complete rollback here).

8- Finally the script will display the option of tuning the SQL Statement by submitting a tuning task using SQL Tuning Advisor feature [This feature is a licensed feature, please don't use it unless your company is already acquired the Diagnostic & Tuning License to avoid licensing audit troubles in the future]:

This script is provided "AS IS" without any warranty. This script is tested on Linux environments but you have to test it first on a test environment before start to use it on production.

 Special thanks to Farrukh Salman "Karkoor" for his contribution in this script.

You can Download the script from this link:

https://www.dropbox.com/s/fll8e7ybrjgsexo/sql_tune.sh?dl=0

GitHub version:

	# ########################################################################################################################################
	# Retrieve the SQLTEXT + BIND VARIABLES + EXEC PLAN + PLAN HISTORY + BASELINE + FIX PLAN + TUNING ADVISOR
	VER="[3.6]"
	# # # #
	# Authors: Mahmmoud ADEL # # # # # #
	# Farrukh Salman # # # ####
	# Created: 24-12-11 # # # # #
	# Modified: 31-12-13 Customized the script to run on various environments.
	# 06-05-14 Getting the Bind Variable info for the SQLID
	# 05-11-15 Fix Divided by Zero error
	# 16-06-16 Added SQL Tuning Option
	# 26-02-17 Added Execution History quoted from sqlhistory.sql written by: Tim Gorman (Evergreen Database Technologies, Inc.)
	# 14-11-17 Added a check for available tuning tasks
	# 26-07-18 Enhanced the display of Execution Plan
	# 11-09-18 Display SQL Advisor recommendation for the statement if it was already been vetted by SQL Advisor.
	# 28-08-19 Added Post Tuning note for hard parsing enforcement command.
	# 10-06-20 Adjust page formatting.
	# 02-07-20 Added Execution Plan History.
	# 05-10-20 Added SQL PLAN BASELINE Details.
	# 06-10-20 Added the feature of fixing an EXECUTION PLAN by creating a new BASELINE.
	# 06-10-20 Added the feature of fixing an EXECUTION PLAN from an EXIST BASELINE [if found].
	# 11-10-20 Added a hint to help the user display the complete execution plan for a plan inside the BASELINE.
	# 13-02-21 Search the Execution Plans in AWR if the SQLID is not loaded in Cursor Cache.
	# 15-02-21 Show Drop Baseline SQL command for the user.
	# 19-09-21 Add the option of manually adding a plan to the SQL PLAN BASELINE using plan HASH VALUE.
	# 19-09-21 Control the SQLID displayed statistics history in days using HIST_DAYS variable.
	# 13-07-22 Setting SQLPLUS WRAP to ON to show the complete SQL statement.
	# ########################################################################################################################################
	# ###########
	# Description:
	# ###########
	echo
	echo "====================================================================================================="
	echo "This script display SQLTEXT, BIND VARIABLES, EXEC PLAN, BASELINE, FIX PLAN, SQL TUNING of a SQLID ..."
	echo "====================================================================================================="
	echo
	ORACLE_OWNER_VFY="N"
	SKIPDBS="ASM\|MGMTDB|APX"
	HIST_DAYS=10 # Define how many DAYs back the script will dig for SQLID statistics.
	# #######################################
	# 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 is Running !"
	echo
	return
	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
	integ='^[0-9]+$'
	if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ] || [ ${REPLY} -eq 0 ]
	then
	echo
	echo "Error: Not a valid number!"
	echo
	echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
	echo "----------------------------------------------"
	else
	export ORACLE_SID=$DB_ID
	echo
	printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
	echo
	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}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep -i "^${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:
	export PGREP=`which pgrep`
	export PWDX=`which pwdx`
	if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
	then
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID} 2>/dev/null|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	fi
	# 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
	fi
	# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`dbhome "${ORACLE_SID}"`
	export ORACLE_HOME
	fi
	# ATTEMPT4: 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
	fi
	# ATTEMPT5: 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
	fi
	# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	if [ -f /usr/bin/locate ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	fi
	fi
	export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	case ${ORACLE_OWNER_VFY} in
	"Y")
	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;;
	esac
	# ########################################
	# SQLPLUS: Check SQL FULLTEXT & EXEC PLAN:
	# ########################################
	# Variables
	echo
	echo "Enter the SQL_ID:"
	echo "================"
	while read SQLID
	do
	VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set feedback off
	SELECT COUNT(*) FROM V\$SQL WHERE SQL_ID='${SQLID}';
	EOF
	)
	VAL2=`echo ${VAL1}| awk '{print $NF}'`
	if [ ${VAL2} -gt 0 ]
	then
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set feedback off
	set linesize 156 pages 50000 long 2000000000
	col SQL_FULLTEXT for a140
	PROMPT
	PROMPT *************************
	PROMPT Statement Info: [Per Execution | in Seconds]
	PROMPT *************************
	SET LONG 999999999 PAGESIZE 10000 LINESIZE 200
	col "ELAPSED|CPU TIME" for a16
	col "PLSQL|JAVA TIME" for a24
	col "APP|USR_IO|CLS WAIT" for a19
	col "BUF_GET|DISK_R|DIRECT_W" for a23
	col P_MEM_MB for 99999999
	col "ROWS" for 999999999
	col MODULE for a15
	col FIRST_LOAD_TIME for a19
	col LAST_LOAD_TIME for a19
	col sql_plan_baseline for a30
	col plan_hash_value for 999999999999999
	col signature for 99999999999999999999
	col SQL_PROFILE for a35
	VARIABLE A REFCURSOR;
	DECLARE
	l_cursor SYS_REFCURSOR;
	BEGIN
	open :A for
	select executions EXEC,round(ELAPSED_TIME /1000000/(case when executions=0 then 1 else executions end),2) ||' | '|| round(CPU_TIME /1000000/(case when executions=0 then 1 else executions end),2) "ELAPSED|CPU TIME",
	--round(d.ELAPSED_TIME /1000000/(case when executions=0 then 1 else executions end),2) ELAPSED_TIME,
	round(APPLICATION_WAIT_TIME /1000000/(case when executions=0 then 1 else executions end),2) ||' | '|| round(USER_IO_WAIT_TIME /1000000/(case when executions=0 then 1 else executions end),2) ||' | '|| round(CLUSTER_WAIT_TIME /1000000/(case when executions=0 then 1 else executions end),2) "APP|USR_IO|CLS WAIT",
	--round(PLSQL_EXEC_TIME /1000000/(case when executions=0 then 1 else executions end),2)||' | '||round(JAVA_EXEC_TIME/1000000/(case when executions=0 then 1 else executions end),2) "PLSQL|JAVA TIME",
	round(ROWS_PROCESSED /(case when executions=0 then 1 else executions end),1)"ROWS",
	round(BUFFER_GETS /(case when executions=0 then 1 else executions end),2) ||' | '|| round(DISK_READS /(case when executions=0 then 1 else executions end),2) ||' | '|| round(DIRECT_WRITES /(case when executions=0 then 1 else executions end),2) "BUF_GET|DISK_R|DIRECT_W",
	--round(PERSISTENT_MEM/1024/1024,2) "P_MEM_MB",
	substr(MODULE,1,15)"MODULE", FIRST_LOAD_TIME, LAST_LOAD_TIME
	--,SQL_PLAN_BASELINE, SQL_PROFILE
	from v\$sql where SQL_ID='${SQLID}';
	END;
	/
	PRINT A;
	/
	PROMPT
	PROMPT *************************
	PROMPT BIND VARIABLES + SQL TEXT:
	PROMPT *************************
	set heading off wrap on
	SET LONG 999999999 PAGESIZE 10000 LINESIZE 200
	select 'VARIABLE '||trim (leading ':' from name)||' '||case when datatype_string= 'DATE' then 'VARCHAR2(60)' else datatype_string end||';' from v\$sql_bind_capture
	where SQL_ID='${SQLID}' and CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}');
	select 'EXECUTE '||name||' := '||''''||value_string||''''||';' from v\$sql_bind_capture
	where SQL_ID='${SQLID}' and CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}');
	select sql_fulltext from v\$sql where sql_id='${SQLID}' and CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql where SQL_ID='${SQLID}');
	set heading on
	PROMPT
	set heading off
	select 'Notes: (11g Onwards)' from dual;
	PROMPT -------
	select
	decode(IS_BIND_SENSITIVE,'Y','- The Bind Variables for this statement are Being CHANGED.','N','- The Bind Variables for this statement have NEVER CHANGED.'),
	decode(IS_BIND_AWARE,'Y','- Adaptive Cursor Sharing CHANGED the initial execution plan for that SQL_ID at least one time.','N',''),
	' Child Number: '||CHILD_NUMBER
	from v\$sql where sql_id='${SQLID}' and CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql where SQL_ID='${SQLID}');
	set heading on
	/*
	PROMPT
	PROMPT
	PROMPT *********************
	PROMPT BIND VARIABLE VALUES:
	PROMPT *********************
	col BIND_VARIABLE for a20
	col VALUE for a100
	col DATATYPE for a20
	select name BIND_VARIABLE,value_string VALUE,datatype_string DATATYPE from v\$sql_bind_capture
	where SQL_ID='${SQLID}' and CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}');
	PROMPT SEARCH STATSPACK TABLES: [May take very long time]
	PROMPT ***********************
	select SQL_TEXT from STATS\$SQLTEXT where sql_id='${SQLID}';
	select executions from STATS\$SQL_SUMMARY where sql_id='${SQLID}';
	*/
	PROMPT
	PROMPT
	PROMPT *********************
	PROMPT EXECUTION PLAN:
	PROMPT *********************
	col PLAN_TABLE_OUTPUT for a156
	--SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('${SQLID}')));
	select plan_table_output from table(dbms_xplan.display_cursor('${SQLID}',null,'all'));
	PROMPT
	PROMPT ******************************
	PROMPT Execution Plan History Summary: [Change of PLAN_HASH_VALUE means a new Execution Plan]
	PROMPT ******************************
	select distinct to_char(TIMESTAMP,'DD-MON-YY HH24:MI:SS') TIMESTAMP,SQL_ID,PLAN_HASH_VALUE from DBA_HIST_SQL_PLAN where sql_id='${SQLID}' group by TIMESTAMP,SQL_ID,PLAN_HASH_VALUE order by TIMESTAMP;
	PROMPT
	PROMPT
	PROMPT ***********************
	PROMPT EXECUTION PLAN History: Written By: Tim Gorman (Evergreen Database Technologies, Inc.)
	PROMPT ***********************
	/**********************************************************************
	* File: sqlhistory.sql
	* Type: SQL*Plus script
	* Author: Tim Gorman (Evergreen Database Technologies, Inc.)
	* Date: 29sep08
	*
	* Description:
	* SQL*Plus script to query the "history" of a specified SQL
	* statement, using its "SQL ID" across all database instances
	* in a database, using the AWR repository. This report is useful
	* for obtaining an hourly perspective on SQL statements seen in
	* more aggregated reports.
	*
	* Modifications:
	* TGorman 29sep08 adapted from the earlier STATSPACK-based
	* "sphistory.sql" script
	*********************************************************************/
	set echo off
	set feedback off timing off verify off linesize 200 pages 50000 recsep off echo off
	set serveroutput on size 1000000
	col phv heading "Plan|Hash Value"
	col snap_time format a12 truncate heading "Snapshot|Time"
	col execs format 999,999,990 heading "Execs"
	col lio_per_exec format 999,999,999,990.00 heading "Avg Logical IO|Per Exec"
	col pio_per_exec format 999,999,999,990.00 heading "Avg Physical IO|Per Exec"
	col cpu_per_exec format 999,999,999,990.00 heading "Avg|CPU (secs)|Per Exec"
	col ela_per_exec format 999,999,999,990.00 heading "Avg|Elapsed (secs)|Per Exec"
	col sql_text format a64 heading "Text of SQL statement"
	clear breaks computes
	ttitle off
	btitle off
	--variable v_nbr_days number
	declare
	cursor get_phv(in_sql_id in varchar2, in_days in integer)
	is
	select ss.plan_hash_value,
	min(s.begin_interval_time) min_time,
	max(s.begin_interval_time) max_time,
	min(s.snap_id) min_snap,
	max(s.snap_id) max_snap,
	sum(ss.executions_delta) sum_execs,
	sum(ss.disk_reads_delta) sum_disk_reads,
	sum(ss.buffer_gets_delta) sum_buffer_gets,
	sum(ss.cpu_time_delta)/1000000 sum_cpu_time,
	sum(ss.elapsed_time_delta)/1000000 sum_elapsed_time
	from dba_hist_sqlstat ss,
	dba_hist_snapshot s
	where ss.dbid = s.dbid
	and ss.instance_number = s.instance_number
	and ss.snap_id = s.snap_id
	and ss.sql_id = in_sql_id
	/* and ss.executions_delta > 0 */
	and s.begin_interval_time >= sysdate-in_days
	group by ss.plan_hash_value
	order by sum_elapsed_time desc;
	--
	cursor get_xplan(in_sql_id in varchar2, in_phv in number)
	is
	select plan_table_output
	from table(dbms_xplan.display_awr(in_sql_id, in_phv, null, 'ALL -ALIAS'));
	--
	v_prev_plan_hash_value number := -1;
	v_text_lines number := 0;
	v_errcontext varchar2(100);
	v_errmsg varchar2(100);
	v_display_sql_text boolean;
	--
	begin
	/*
	v_errcontext := 'query NBR_DAYS from DUAL';
	select decode('100','',10,to_number(nvl('100','10')))
	into :v_nbr_days
	from dual;
	*/
	v_errcontext := 'open/fetch get_phv';
	--for phv in get_phv('${SQLID}', :v_nbr_days) loop
	for phv in get_phv('${SQLID}', ${HIST_DAYS}) loop
	--
	if get_phv%rowcount = 1 then
	--
	dbms_output.put_line('+'||
	rpad('-',12,'-')||
	rpad('-',10,'-')||
	rpad('-',10,'-')||
	rpad('-',12,'-')||
	rpad('-',15,'-')||
	rpad('-',15,'-')||
	rpad('-',12,'-')||
	rpad('-',12,'-')||'+');
	dbms_output.put_line('|'||
	rpad('Plan HV',12,' ')||
	rpad('Min Snap',10,' ')||
	rpad('Max Snap',10,' ')||
	rpad('Execs',12,' ')||
	rpad('LIO',15,' ')||
	rpad('PIO',15,' ')||
	rpad('CPU',12,' ')||
	rpad('Elapsed',12,' ')||'|');
	dbms_output.put_line('+'||
	rpad('-',12,'-')||
	rpad('-',10,'-')||
	rpad('-',10,'-')||
	rpad('-',12,'-')||
	rpad('-',15,'-')||
	rpad('-',15,'-')||
	rpad('-',12,'-')||
	rpad('-',12,'-')||'+');
	--
	end if;
	--
	dbms_output.put_line('|'||
	rpad(trim(to_char(phv.plan_hash_value)),12,' ')||
	rpad(trim(to_char(phv.min_snap)),10,' ')||
	rpad(trim(to_char(phv.max_snap)),10,' ')||
	rpad(trim(to_char(phv.sum_execs,'999,999,990')),12,' ')||
	rpad(trim(to_char(phv.sum_buffer_gets,'999,999,999,990')),15,' ')||
	rpad(trim(to_char(phv.sum_disk_reads,'999,999,999,990')),15,' ')||
	rpad(trim(to_char(phv.sum_cpu_time,'999,990.00')),12,' ')||
	rpad(trim(to_char(phv.sum_elapsed_time,'999,990.00')),12,' ')||'|');
	--
	v_errcontext := 'fetch/close get_phv';
	--
	end loop;
	dbms_output.put_line('+'||
	rpad('-',12,'-')||
	rpad('-',10,'-')||
	rpad('-',10,'-')||
	rpad('-',12,'-')||
	rpad('-',15,'-')||
	rpad('-',15,'-')||
	rpad('-',12,'-')||
	rpad('-',12,'-')||'+');
	--
	v_errcontext := 'open/fetch get_phv';
	--for phv in get_phv('${SQLID}', :v_nbr_days) loop
	for phv in get_phv('${SQLID}', ${HIST_DAYS}) loop
	--
	if v_prev_plan_hash_value <> phv.plan_hash_value then
	--
	v_prev_plan_hash_value := phv.plan_hash_value;
	v_display_sql_text := FALSE;
	--
	v_text_lines := 0;
	v_errcontext := 'open/fetch get_xplan';
	for s in get_xplan('${SQLID}', phv.plan_hash_value) loop
	--
	if v_text_lines = 0 then
	dbms_output.put_line('.');
	dbms_output.put_line('========== PHV = ' ||
	phv.plan_hash_value ||
	'==========');
	dbms_output.put_line('First seen from "'||
	to_char(phv.min_time,'MM/DD/YY HH24:MI:SS') ||
	'" (snap #'||phv.min_snap||')');
	dbms_output.put_line('Last seen from "'||
	to_char(phv.max_time,'MM/DD/YY HH24:MI:SS') ||
	'" (snap #'||phv.max_snap||')');
	dbms_output.put_line('.');
	dbms_output.put_line(
	rpad('Execs',15,' ')||
	rpad('LIO',15,' ')||
	rpad('PIO',15,' ')||
	rpad('CPU',15,' ')||
	rpad('Elapsed',15,' '));
	dbms_output.put_line(
	rpad('=====',15,' ')||
	rpad('===',15,' ')||
	rpad('===',15,' ')||
	rpad('===',15,' ')||
	rpad('=======',15,' '));
	dbms_output.put_line(
	rpad(trim(to_char(phv.sum_execs,'999,999,999,990')),15,' ')||
	rpad(trim(to_char(phv.sum_buffer_gets,'999,999,999,990')),15,' ')||
	rpad(trim(to_char(phv.sum_disk_reads,'999,999,999,990')),15,' ')||
	rpad(trim(to_char(phv.sum_cpu_time,'999,999,990.00')),15,' ')||
	rpad(trim(to_char(phv.sum_elapsed_time,'999,999,990.00')),15,' '));
	dbms_output.put_line('.');
	end if;
	--
	if v_display_sql_text = FALSE and
	s.plan_table_output like 'Plan hash value: %' then
	--
	v_display_sql_text := TRUE;
	--
	end if;
	--
	if v_display_sql_text = TRUE then
	--
	dbms_output.put_line(s.plan_table_output);
	--
	end if;
	--
	v_text_lines := v_text_lines + 1;
	--
	end loop;
	--
	end if;
	--
	v_errcontext := 'fetch/close get_phv';
	--
	end loop;
	--
	exception
	when others then
	v_errmsg := sqlerrm;
	raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
	end;
	/
	break on report
	compute sum of execs on report
	compute avg of lio_per_exec on report
	compute avg of pio_per_exec on report
	compute avg of cpu_per_exec on report
	compute avg of ela_per_exec on report
	ttitle center 'Summary Execution Statistics Over Time'
	select to_char(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,
	ss.executions_delta execs,
	ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec,
	ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec,
	(ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,
	(ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec
	from dba_hist_snapshot s,
	dba_hist_sqlstat ss
	where ss.dbid = s.dbid
	and ss.instance_number = s.instance_number
	and ss.snap_id = s.snap_id
	and ss.sql_id = '${SQLID}'
	/* and ss.executions_delta > 0 */
	--and s.begin_interval_time >= sysdate - :v_nbr_days
	and s.begin_interval_time >= sysdate - ${HIST_DAYS}
	order by s.snap_id;
	clear breaks computes
	set verify on echo on feedback on
	ttitle off
	PROMPT
	PROMPT ******************************
	PROMPT Execution Plan History Summary: [Change of PLAN_HASH_VALUE means a new Execution Plan]
	PROMPT ******************************
	select distinct to_char(TIMESTAMP,'DD-MON-YY HH24:MI:SS') TIMESTAMP,SQL_ID,PLAN_HASH_VALUE from DBA_HIST_SQL_PLAN where sql_id='${SQLID}' group by TIMESTAMP,SQL_ID,PLAN_HASH_VALUE order by TIMESTAMP;
	/*
	PROMPT
	PROMPT ***********************
	PROMPT SQL PLAN BASELINE: [For ${SQLID}]
	PROMPT ***********************
	col signature format 99999999999999999999
	col sql_handle format a25
	col plan_name format a30
	col ELAPSED_TIME for 9999999.99
	col CPU_TIME for 9999999.99
	col BUFFER_GETS for 9999999.99
	col enabled format a5
	col accepted format a5
	col fixed format a5
	select distinct d.plan_name, d.sql_handle, d.signature, d.OPTIMIZER_COST COST, d.executions,
	round(d.ELAPSED_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) ELAPS_TIME,
	round(d.CPU_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) CPU_TIME,
	round(d.BUFFER_GETS /1000000/(case when d.executions=0 then 1 else d.executions end),2) BUFFER_GETS,
	d.enabled, d.accepted, d.fixed,
	to_char(d.LAST_EXECUTED,'DDMONYY HH24:MI:SS') LAST_EXECUTED
	from dba_sql_plan_baselines d, v\$sql s
	where s.sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	*/
	EOF
	# ######################################
	# Checking the Existance of SPM Feature:
	# ######################################
	CHK_SPM_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	SELECT count(*) from v\$option where parameter='SQL Plan Management' and value='TRUE';
	exit;
	EOF
	)
	CHK_SPM_OPTION=`echo ${CHK_SPM_OPTION_RAW} | awk '{print $NF}'`
	# IF SPM feature is available offer the option of fixing the SQL Plan through creating a BASELINE:
	if [ ${CHK_SPM_OPTION} -eq 1 ]
	then
	BASELINE_PLAN_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT COUNT(*)
	from dba_sql_plan_baselines d, v\$sql s
	where s.sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	exit;
	EOF
	)
	BASELINE_PLAN_COUNT=`echo ${BASELINE_PLAN_COUNT_RAW}| awk '{print $NF}'`
	# IF no Baseline found, offer to create a new baseline from current available plans from CURSOR CACHE and fix the plan:
	if [ ${BASELINE_PLAN_COUNT} -le 0 ]
	then
	echo ""
	echo "Do you want to FIX a plan for [${SQLID}] by creating a NEW BASELINE: [Y|N]"
	echo "========================================================================="
	while read INPUT
	do
	case ${INPUT} in
	y|Y|yes|YES|Yes)
	echo ""
	echo "Creating a BASELINE for [${SQLID}] containing the current loaded plans from cursor cache"
	PLANS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	set serveroutput on
	declare
	plans_loaded pls_integer ;
	begin
	plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '${SQLID}' ) ;
	dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
	end ;
	/
	exit;
	EOF
	)
	PLANS_COUNT=`echo ${PLANS_COUNT_RAW}| awk '{print $NF}'`
	if [ ${PLANS_COUNT} -gt 0 ]
	then
	echo ""
	echo "Loaded Plans: ${PLANS_COUNT}"
	echo ""
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 1000 lines 169 pages 50000 long 2000000000
	col signature format 99999999999999999999
	col sql_handle format a25
	col plan_name format a30
	col ELAPSED_TIME for 9999999.99
	col CPU_TIME for 9999999.99
	col BUFFER_GETS for 9999999.99
	col enabled format a5
	col accepted format a5
	col fixed format a5
	PROMPT
	PROMPT CURRENT PLANS INSIDE THE NEW BASELINE for [${SQLID}]:
	PROMPT
	select distinct d.plan_name, d.sql_handle, d.signature, d.OPTIMIZER_COST COST, d.executions,
	round(d.ELAPSED_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) ELAPSED_TIME,
	round(d.CPU_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) CPU_TIME,
	round(d.BUFFER_GETS /1000000/(case when d.executions=0 then 1 else d.executions end),2) BUFFER_GETS,
	d.enabled, d.accepted, d.fixed
	from dba_sql_plan_baselines d, v\$sql s
	where sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	EOF
	read -p "Enter the PLAN_NAME for the plan you want to fix [From the above output]: " PLAN_NAME
	read -p "Enter the SQL_HANDLE for the plan you want to fix [From the above output]: " SQL_HANDLE
	echo ""
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 1000 feedback off lines 169 long 2000000000
	col signature format 99999999999999999999
	col sql_handle format a25
	col plan_name format a30
	col ELAPSED_TIME for 9999999.99
	col CPU_TIME for 9999999.99
	col BUFFER_GETS for 9999999.99
	col enabled format a5
	col accepted format a5
	col fixed format a5
	set serveroutput on
	declare
	plans_loaded pls_integer ;
	begin
	plans_loaded := dbms_spm.alter_sql_plan_baseline(
	sql_handle => '${SQL_HANDLE}',
	plan_name => '${PLAN_NAME}',
	attribute_name => 'fixed',
	attribute_value =>'YES'
	) ;
	dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
	end ;
	/
	PROMPT
	select distinct d.plan_name, d.sql_handle, d.signature, d.OPTIMIZER_COST COST, d.executions,
	round(d.ELAPSED_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) ELAPSED_TIME,
	round(d.CPU_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) CPU_TIME,
	round(d.BUFFER_GETS /1000000/(case when d.executions=0 then 1 else d.executions end),2) BUFFER_GETS,
	d.enabled, d.accepted, d.fixed
	from dba_sql_plan_baselines d, v\$sql s
	where sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	set escape on feedback off echo off pages 0
	-- Set SQL_HANDLE:
	column SQL_HANDLE NEW_VALUE SQL_HANDLE;
	select distinct d.sql_handle SQL_HANDLE from dba_sql_plan_baselines d, v\$sql s where sql_id='${SQLID}' and d.signature = s.exact_matching_signature;
	PROMPT
	PROMPT -- TO ADD a PLAN to the BASELINE: [From SQLPlus] [Provide the PLAN HASH VALUE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.load_plans_from_cursor_cache(sql_id => '${SQLID}', plan_hash_value => '\&PLAN_HASH_VALUE', sql_handle => '&SQL_HANDLE');;
	PROMPT
	PROMPT -- TO DROP a PLAN: [From SQLPlus] [Provide the PLAN NAME]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(plan_name => '\&PLAN_NAME');;
	PROMPT
	PROMPT -- TO DROP the BASELINE: [From SQLPlus] [FOR ROLLBACK PURPOSE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE');;
	PROMPT
	EOF
	fi
	break;;
	*) break;;
	esac
	done
	fi
	# IF SQL BASELINE is already exist, but having more than one plan, offer the option to change/fix the plan from the list of baseline plans:
	#if [ ${BASELINE_PLAN_COUNT} -gt 1 ]
	if [ ${BASELINE_PLAN_COUNT} -ge 1 ]
	then
	#echo ""
	#echo -e "\033[33;5mMultiple Plans Detected for this SQLID ...\033[0m"
	echo ""
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	PROMPT
	PROMPT ***********************
	PROMPT SQL PLAN BASELINE: [For ${SQLID}]
	PROMPT ***********************
	set pages 1000 lines 169 pages 50000 long 2000000000
	col signature format 99999999999999999999
	col sql_handle format a25
	col plan_name format a30
	col ELAPSED_TIME for 9999999.99
	col CPU_TIME for 9999999.99
	col BUFFER_GETS for 9999999.99
	col enabled format a5
	col accepted format a5
	col fixed format a5
	col LAST_EXECUTED format a13
	select distinct d.plan_name, d.sql_handle, d.signature, d.OPTIMIZER_COST COST, d.executions,
	round(d.ELAPSED_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) ELAPS_TIME,
	round(d.CPU_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) CPU_TIME,
	round(d.BUFFER_GETS /1000000/(case when d.executions=0 then 1 else d.executions end),2) BUFFER_GETS,
	d.enabled, d.accepted, d.fixed,
	to_char(d.LAST_EXECUTED,'DDMONYY HH24:MI') LAST_EXECUTED
	from dba_sql_plan_baselines d, v\$sql s
	where s.sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	set escape on feedback off echo off pages 0
	-- Set SQL_HANDLE:
	column SQL_HANDLE NEW_VALUE SQL_HANDLE
	select distinct d.sql_handle SQL_HANDLE from dba_sql_plan_baselines d, v\$sql s where sql_id='${SQLID}' and d.signature = s.exact_matching_signature;
	PROMPT
	PROMPT -- TO ADD a PLAN to the BASELINE: [From SQLPlus] [Provide the PLAN HASH VALUE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.load_plans_from_cursor_cache(sql_id => '${SQLID}', plan_hash_value => '\&PLAN_HASH_VALUE', sql_handle => '&SQL_HANDLE');;
	PROMPT
	PROMPT -- TO DROP a PLAN: [From SQLPlus] [Provide the PLAN NAME]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(plan_name => '\&PLAN_NAME');;
	PROMPT
	PROMPT -- TO DROP the BASELINE: [From SQLPlus] [FOR ROLLBACK PURPOSE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE');;
	EOF
	echo ""
	echo "Do you want to FIX a plan for [${SQLID}] in the CURRENT exist BASELINE: [Y|N]"
	echo "============================================================================"
	while read ANS
	do
	case ${ANS} in
	y|Y|yes|YES|Yes)
	echo ""
	read -p "Enter the PLAN_NAME for the plan you want to fix [From the above query output]: " PLAN_NAME
	read -p "Enter the SQL_HANDLE for the plan you want to fix [From the above query output]: " SQL_HANDLE
	echo ""
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 1000 feedback off lines 169 pages 50000 long 2000000000
	col signature format 99999999999999999999
	col sql_handle format a25
	col plan_name format a30
	col ELAPSED_TIME for 9999999.99
	col CPU_TIME for 9999999.99
	col BUFFER_GETS for 9999999.99
	col enabled format a5
	col accepted format a5
	col fixed format a5
	set serveroutput on
	declare
	plans_loaded pls_integer ;
	begin
	plans_loaded := dbms_spm.alter_sql_plan_baseline(
	sql_handle => '${SQL_HANDLE}',
	plan_name => '${PLAN_NAME}',
	attribute_name => 'fixed',
	attribute_value =>'YES'
	) ;
	dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
	end ;
	/
	PROMPT
	select distinct d.plan_name, d.sql_handle, d.signature, d.OPTIMIZER_COST COST, d.executions,
	round(d.ELAPSED_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) ELAPSED_TIME,
	round(d.CPU_TIME /1000000/(case when d.executions=0 then 1 else d.executions end),2) CPU_TIME,
	round(d.BUFFER_GETS /1000000/(case when d.executions=0 then 1 else d.executions end),2) BUFFER_GETS,
	d.enabled, d.accepted, d.fixed
	from dba_sql_plan_baselines d, v\$sql s
	where sql_id='${SQLID}'
	and d.signature = s.exact_matching_signature
	--and s.CHILD_NUMBER = (select max(CHILD_NUMBER) from v\$sql_bind_capture where SQL_ID='${SQLID}')
	;
	set escape on feedback off echo off pages 0
	-- Set SQL_HANDLE:
	column SQL_HANDLE NEW_VALUE SQL_HANDLE
	select distinct d.sql_handle SQL_HANDLE from dba_sql_plan_baselines d, v\$sql s where sql_id='${SQLID}' and d.signature = s.exact_matching_signature;
	PROMPT
	PROMPT -- TO ADD a PLAN to the BASELINE: [From SQLPlus] [Provide the PLAN HASH VALUE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.load_plans_from_cursor_cache(sql_id => '${SQLID}', plan_hash_value => '\&PLAN_HASH_VALUE', sql_handle => '&SQL_HANDLE');;
	PROMPT
	PROMPT -- TO DROP a PLAN: [From SQLPlus] [Provide the PLAN NAME]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(plan_name => '\&PLAN_NAME');;
	PROMPT
	PROMPT -- TO DROP the BASELINE: [From SQLPlus] [FOR ROLLBACK PURPOSE]
	PROMPT var pls number
	PROMPT exec :pls := dbms_spm.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE');;
	PROMPT
	EOF
	break;;
	*) break;;
	esac
	done
	fi
	fi
	VAL_TUN_TASK_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	SELECT COUNT(*) FROM DBA_ADVISOR_TASKS where TASK_NAME='${SQLID}_Tuning_Task';
	EOF
	)
	VAL_TUN_TASK=`echo ${VAL_TUN_TASK_RAW}| awk '{print $NF}'`
	case ${VAL_TUN_TASK} in
	1) echo
	echo "-----------------------------------------------------------------------------------------------"
	# echo -e "\033[33;5mA Tuning Task already been found for SQLID [${SQLID}] You can view this task result using:\033[0m"
	echo -e "\033[33;5mA Tuning Task already been found for SQLID [${SQLID}]\033[0m"
	echo "Displaying the Recommendations:"
	echo "-----------------------------------------------------------------------------------------------"
	# echo "SET LONG 2000000000 pages 10000 lines 200"
	# echo "SELECT DBMS_SQLTUNE.report_tuning_task('${SQLID}_Tuning_Task') AS recommendations FROM dual;";;
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	SET LONG 999999999 PAGESIZE 10000 LINESIZE 167
	SELECT DBMS_SQLTUNE.report_tuning_task('${SQLID}_Tuning_Task') AS recommendations FROM dual;
	EOF
	;;
	esac
	echo ""
	printf "`echo "Do you want to Tune SQLID [${SQLID}] Using SQL Tuning Advisor? (NO/YES) Default is [NO] |"` `echo -e "\033[33;5m[Licensed Feature]\033[0m"` \n"
	echo "==================================================================="
	echo "Note: Please make sure that Diagnostic & Tuning License is already acquired before using this feature."
	while read ANS1
	do
	case ${ANS1} in
	""|n|N|no|NO|No) break ;;
	""|y|Y|yes|YES|Yes)
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	--PROMPT
	--PROMPT SQL Statement Full Text:
	--PROMPT -----------------------
	--SET LONG 2000000000 PAGESIZE 10000 LINESIZE 200
	--col SQL_FULLTEXT for a200
	--select SQL_FULLTEXT from V\$SQL where SQL_ID='${SQLID}';
	PROMPT
	PROMPT Creating SQL Tuning Task: "${SQLID}_Tuning_Task" ...
	DECLARE
	l_sql_tune_task_id VARCHAR2(100);
	BEGIN
	l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
	sql_id => '${SQLID}',
	scope => DBMS_SQLTUNE.scope_comprehensive,
	time_limit => 3600,
	task_name => '${SQLID}_Tuning_Task',
	description => 'Tuning task for statement ${SQLID}');
	DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
	END;
	/
	PROMPT Executing TUNING Task: "${SQLID}_Tuning_Task" ...
	PROMPT
	PROMPT Please Wait! This May Take Several Minutes ...
	EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '${SQLID}_Tuning_Task');
	PROMPT
	PROMPT SQL Tuning Recommendations:
	PROMPT --------------------------
	spool ${SQLID}_Tuning_Task_details.log
	SET LONG 999999999 PAGESIZE 10000 LINESIZE 200
	SELECT DBMS_SQLTUNE.report_tuning_task('${SQLID}_Tuning_Task') AS recommendations FROM dual;
	PROMPT
	PROMPT Post Tuning: You may need to enforce HARD PARSING this statement to enforce using the new execution plan:
	PROMPT -----------
	PROMPT SELECT CHILD_NUMBER,ADDRESS,HASH_VALUE,LAST_LOAD_TIME FROM V\$SQL WHERE SQL_ID='${SQLID}';;
	PROMPT EXEC FOR I IN (SELECT ADDRESS,HASH_VALUE,USERS_EXECUTING,SQL_TEXT FROM V\$SQLAREA WHERE SQL_ID='${SQLID}') LOOP SYS.DBMS_SHARED_POOL.PURGE(I.ADDRESS||','||I.HASH_VALUE,'C'); END LOOP;;
	PROMPT SELECT CHILD_NUMBER,ADDRESS,HASH_VALUE,LAST_LOAD_TIME FROM V\$SQL WHERE SQL_ID='${SQLID}';;
	PROMPT
	PROMPT For Dropping Tuning Task "${SQLID}_Tuning_Task" Use this SQL command:
	PROMPT ------------------------
	PROMPT EXEC dbms_sqltune.drop_tuning_task(task_name => '${SQLID}_Tuning_Task');;
	PROMPT
	spool off
	EOF
	break ;;
	*) echo "Please enter a VALID answer [N|Y]" ;;
	esac
	done
	break
	else
	echo
	echo "SQLID [${SQLID}] is not found in the SHARED POOL!"
	echo
	echo "Searching the AWR ..."
	echo
	AWR_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set feedback off
	select count(*) from table(dbms_xplan.display_awr('${SQLID}'));
	EOF
	)
	AWR_COUNT=`echo ${AWR_COUNT_RAW}| awk '{print $NF}'`
	if [ ${AWR_COUNT} -gt 0 ]
	then
	echo "SQLID found in AWR."
	echo
	echo "Displaying the Execution Plans History ..."
	echo
	${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set feedback off
	set linesize 159 pages 50000 long 2000000000
	col PLAN_TABLE_OUTPUT for a156
	select * from table(dbms_xplan.display_awr('${SQLID}'));
	EOF
	else
	echo "The given SQLID is NOT found neither in the Shared Pool nor in AWR!"
	echo ""
	fi
	break
	fi
	done
	# #############
	# 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 sql_tune.sh hosted with ❤ by GitHub

Unable to obtain current patch information due to error: 20013 When Starting up a DB Instance

Error:

When Starting a RAC DB instance I noticed the following error in the alertlog which was hampering the display of installed patches list:

QPI: opatch executable does not exist, opatch
QPI: OPATCH_INST_DIR not present:/u01/oracle/12.2.0.3/db/OPatch
Unable to obtain current patch information due to error: 20013, ORA-20013: DBMS_QOPATCH ran mostly in non install area
ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
ORA-06512: at "SYS.DBMS_QOPATCH", line 854
ORA-06512: at "SYS.DBMS_QOPATCH", line 634
ORA-06512: at "SYS.DBMS_QOPATCH", line 2309


Analysis:

The top of the error message is clearly saying:

OPATCH_INST_DIR not present:/u01/oracle/12.2.0.3/db/OPatch

The right location of OPatch in my environment is /u01/oracle/12.2.0.3/OPatch, not as mentioned in the error message: /u01/oracle/12.2.0.3/db/OPatch

Checking the OPATCH directory location in DBA_DIRECTORIES:

SQL> set lines 170
           col DIRECTORY_NAME for a30
           col DIRECTORY_PATH for a100
           select DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like 'OPATCH%';

It's not only OPATCH_INST_DIR which pointing to the wrong directory, but the other OPATCH directories as well like; OPATCH_SCRIPT_DIR and OPATCH_LOG_DIR.


Solution:

As per note 2033620.1, the right OPATCH directories path should be as the following:

OPATCH_INST_DIR:       $ORACLE_HOME/OPatch     
OPATCH_SCRIPT_DIR:  $ORACLE_HOME/QOpatch
OPATCH_LOG_DIR:       $ORACLE_HOME/QOpatch

So let's correct those paths using the following commands:

SQL> CREATE OR REPLACE DIRECTORY OPATCH_INST_DIR   AS '/u01/oracle/12.2.0.3/OPatch';
SQL> CREATE OR REPLACE DIRECTORY OPATCH_SCRIPT_DIR AS '/u01/oracle/12.2.0.3/QOpatch';
SQL> CREATE OR REPLACE DIRECTORY OPATCH_LOG_DIR    AS '/u01/oracle/12.2.0.3/QOpatch';

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like 'OPATCH%';

To verify that everything is OK after correcting the directory location:

Run the following command, where it should run successfully without returning any errors:

# $ORACLE_HOME/OPatch/datapatch  -prereq

References:
Datapatch fails with "ORA-20009:" “ORA-20013: DBMS_QOPATCH ran mostly in non install area” ORACLE_HOME is a SYMBOLIC LINK Doc ID 2033620.1

New Sections in 19c AWR In Comparison With 11g's AWR

AWR report in 19c is packed up with many new sections. In this post I'll highlight the new sections comparing with 11g's AWR report.

Starting with "Report Summary" sections:

- Top ADDM Findings: 

- TOP 10 Foreground Wait Events: (Instead of TOP 5 in 11g)

- Wait classes: (new table)

 

- IO Profile (new table)

 

- Recovery Progress: [In case the report is taken from a Standby DB]

- In-Memory Area new item (Cache Size)

Moving to "Main Report" section:

Drilling down to each of these brand-new sections:

Goldengate related statistics are now available in the AWR: 

AQ's stats are new in AWR as well:

And here is the nice part, in 19c the ASH report is now embedded inside the AWR report, whereas you don't have to generate ASH reports separately anymore

Same like ASH, ADDM report is also available:

That's not all; there is still some minor new details in the 19c's AWR report I didn't cover in this post to make it briefed.

CRS-2974: unable to act on resource

Problem:

On a 19c RAC DB when shutting down any instance gracefully using srvctl command it throws this error:

$ srvctl stop instance -d proto -i proto2
PRCD-1131 : Failed to stop database proto and its services on nodes r6ttnode2
PRCR-1133 : Failed to stop database proto and its running services
PRCR-1132 : Failed to stop resources using a filter
CRS-2974: unable to act on resource 'ora.proto.db' on server 'r6ttnode2' because that would require stopping or relocating resource 'ora.proto.jdbcapp.svc' but the appropriate force flag was not specified

Analysis:

If you have created a service on RAC 19c with -preferred option, SRVCTL command will not shutdown the instance unless you use -force parameter, which will not allow the transactions to failover to the other available node in case you are using TAF policy (Transparent Application Failover).

Workarounds:

- [Recommended Solution] First stop the services on the instance you want to shut down, then shutdown the instance gracefully using SRVCTL command: [This will allow the QUERIES to failover to the other available node]

$ srvctl stop service  -d proto -i proto2
$ srvctl stop instance -d proto -i proto2

- [or you can follow this Less recommended solution] Shutdown the instance gracefully from the SQLPLUS console: [This will allow the QUERIES to failover to the available node]

$ export ORACLE_SID=proto2
SQL> SHUTDOWN IMMEDIATE;

Note: If you don't have ADG or GoldenGate setup then SHUTDOWN ABORT of a RAC instance is considered a safe procedure. I personally start with SHUTDOWN IMMEDIATE as the first option.

- [or you can follow this Least recommended] Shutdown the instance forcefully using SRVCTL command: [Current Running QUERIES will not failover to the other instance]

$ srvctl stop instance -d proto -i proto2 -force

Note:
This SRVCTL error doesn't show up when stopping the whole DB (all instances at once): using: $ srvctl stop database -d proto

Script to Monitor and Report Audit Trail Records in sys.aud$

 

Script Description:

This script monitors the major audit trail records and failed login attempts, it can be easily deployed and customized. So far, it has been tested on Linux Environment.

Script Category: Auditing & Security

How it works:

You have to schedule this script to run in the OS scheduler "crontab", you can decide how fast you should receive a notification whenever a new "major" audit event get created. Then set the same schedule interval in minutes inside the script to determine the window of time which the script will fetch the data from sys.aud$.

e.g. If you schedule the script to run every 30 minutes in the crontab, you should set the Threshold "MINUTES" inside the scripts to 30.

The script uses sendmail as the only notification method, so you have to set the following parameter replacing the pattern youremail@yourcompany.com to your actual Email:

EMAIL="youremail@yourcompany.com"

In addition, the script has many parameters to help you narrow down the scope of audit actions monitoring, you can change the values in green color:

 HTMLENABLE=Y      # Enable HTML Email Format [Default Enabled].
 RECORDSNUM=1     # Send an Email if the sum of audit records >= the threshold [Default 1 record].
 REPORT_FAILED_LOGINS=Y    # Enable the reporting of failed login attempts. [Default Enabled].
 REPORT_AUDIT_RECORDS=Y  # Enable the reporting of audit records [Default Enabled].
 EXCLUDE_DBUSERS="'dba_bundleexp7'"  # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
 EXCLUDE_OSUSERS="'user1'"    # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
 EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude specific AUDIT EVENTS from reporting.

EXCLUDE_ACTIONS is an important parameter to help you exclude specific audit actions from getting reported. This reporting mechanism should cover only crucial activities that need immediate attention like DROP, TRUNCATE, ALTER, ... etc for example; minor activities like Log In, Log Off and SELECT shouldn't be in the scope of such monitoring mechanism; otherwise you will get your mailbox filled unnecessarily.

I've already excluded common audit actions from the scope of getting reported like:

SELECT, SET ROLE, LOGON, LOGOFF, LOGOFF BY CLEANUP, EXPLAIN, PL/SQL EXECUTE, SYSTEM AUDIT

Let's suppose you want to exclude DML's from getting reported; modify the parameter as follows:

EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','INSERT','UPDATE','DELETE','SESSION REC'"

Ensure that you enclose each audit action between "single quotation" '' and separating between them using "comma" ,

To display all the available actions names that you can use for that parameter, run this statement:

SQL> select distinct action_name from dba_audit_trail order by 1;  

Feel free to add more actions to be excluded to EXCLUDE_ACTIONS parameter, and I encourage you to do so.

Precautions:

The last and the most important remaining point here is indexing sys.aud$ table:

Your audit trail table sys.aud$ may have millions/billions of rows, querying this table frequently can degrade your database performance and most probably it can bring the server on its knees; especially if it has humble resources!

If reporting audit records is important for you, then you must create the following index before start using this script and to avoid any performance degradation it can cause by this script: (Create it with online option to avoid getting the DB hung)

SQL> CREATE INDEX sys.idx_ntimestamp# ON sys.aud$(ntimestamp#) ONLINE;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

When the script run, it will check if sys.aud$ (ntimestamp#) is indexed, if it's not; it will display a warning message along with the recommended index creation statement to execute.

It is worth to mention that Oracle doesn't recommend creating any indexes on sys.aud$ table as it may degrade the performance of record insertions to sys.aud$, in addition to other minor reasons mentioned in this Note: The Effect Of Creating Index On Table Sys.Aud$ (Doc ID 1329731.1)

From my experience; I use this script along with the creation of the above index on very busy OLTP databases without noticing any performance degradation. Creating an index on sys.aud$ table is like creating an index on any other "busy table"; Yes it will add an extra overhead (to maintain the index) when audit records are inserted, but still Oracle can handle this efficiently.

Indeed, you are the right one to decide whether adding an index to sys.aud$ table will degrade the performance or not; based on the daily volume of audit data that get inserted to sys.aud$, along with the hardware resources of your DB server.

One last thing, you may ask why I coded the script to select directly from sys.aud$ instead of using DBA_AUDIT_TRAIL; the answer is that I've noticed in many cases that selecting from DBA_AUDIT_TRAIL will force the optimizer to not use the above mentioned indexes leading to an expensive full table scans, this is why I avoided using it.

Please feel free to share your thoughts and suggestions.

To download the script:

https://www.dropbox.com/s/vja3hptpzy7a3in/monitor_audit_records.sh?dl=0

After you click on that link, click on the down arrow at the top-right side of the page:

GitHub Version:

	# #####################################################################################################################################################
	# Monitor audit records [Failed login attempts & MAJOR AUDIT RECRODS] on audit trail table
	VER="[1.0]"
	SCRIPT_NAME="monitor_audit_records"
	# #####################################################################################################################################################
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 28-09-20 # # # # #
	#
	#
	#
	#
	#
	# ######################################################################################################################################################
	EMAIL="youremail@yourcompany.com"
	# #########################
	# THRESHOLDS:
	# #########################
	# Modify the THRESHOLDS to the value you prefer:
	HTMLENABLE=Y # Enable HTML Email Format [Default Enabled].
	MINUTES=10 # Check audit records in the last N number of minutes. [Default 5 minutes].
	RECORDSNUM=1 # Send an Email if the sum of audit records >= the threshold. [Default 1 record].
	REPORT_FAILED_LOGINS=Y # Enable the reporting of failed login attempts. [Default Enabled].
	REPORT_AUDIT_RECORDS=Y # Enable the reporting of audit records. [Default Enabled].
	EXCLUDE_DBUSERS="'dba_bundleexp7'" # Exclude DB user from reporting their activities [In lowercase]. e.g. EXCLUDE_DBUSERS="'sys','scott'"
	EXCLUDE_OSUSERS="'user1'" # Exclude OS user from reporting their activities [In lowercase]. e.g. EXCLUDE_OSUSERS="'oracle','grid'"
	EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT'" # Exclude AUDIT EVENTS from reporting
	# e.g. To exclude all previous Audit Events along with DMLs (inserts, updates, deletes):
	# EXCLUDE_ACTIONS="'SELECT','SET ROLE','LOGON','LOGOFF','LOGOFF BY CLEANUP','EXPLAIN','PL/SQL EXECUTE','SYSTEM AUDIT','SESSION REC'"
	# To explore all the current available events in your DB run:
	# SQL> select distinct action_name from dba_audit_trail;
	SQLLINESIZE=200 # The LINE SIZE for SQLPLUS outputs.
	OSLINESIZE=300 # The LINE SIZE for OS Commands outputs. [Default is 167]
	SENDER="AUDITRECORDS" # Change the Email sender name. e.g. EXCLUDE_DBUSERS="AUDITRECORDS"
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will not run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM and APX instances:
	EXL_DB="\-MGMTDB|ASM|APX"
	# #######################################
	# Verify Variables:
	# #######################################
	export HTMLENABLE
	export MINUTES
	export RECORDSNUM
	export REPORT_FAILED_LOGINS
	export REPORT_AUDIT_RECORDS
	export EXCLUDE_DBUSERS
	export EXCLUDE_OSUSERS
	export EXCLUDE_ACTIONS
	export SQLLINESIZE
	export OSLINESIZE
	export EXL_DB
	case ${EXCLUDE_DBUSERS} in
	"") export HASHDBUSERNAME="--";;
	*) export HASHDBUSERNAME="";;
	esac
	case ${EXCLUDE_OSUSERS} in
	"") export HASHOSUSERNAME="--";;
	*) export HASHOSUSERNAME="";;
	esac
	case ${EXCLUDE_ACTIONS} in
	"") export HASHACTIONNAME="--";;
	*) export HASHACTIONNAME="";;
	esac
	export SRV_NAME="`uname -n`"
	# Check if MAIL_LIST parameter is not set notify the user and exit:
	case ${EMAIL} in "youremail@yourcompany.com")
	echo
	echo "******************************************************************"
	echo "Buddy! You forgot to edit line# 50 in ${SCRIPT_NAME}.sh script."
	echo "Please replace youremail@yourcompany.com with your E-mail address."
	echo "******************************************************************"
	echo
	echo "Script Terminated !"
	echo
	exit;;
	esac
	# Check if there is another session of the script is running: [Avoid performance impact]
	RUNCOUNTT=`ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}|wc -l`
	if [ ${RUNCOUNTT} -gt 2 ]
	then
	echo -e "\033[32;5m${SCRIPT_NAME}.sh script is currently running by another session.\033[0m"
	echo ""
	echo "Please make sure the following sessions are completed before running dbalarm script: [ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}]"
	ps -ef|grep -v grep|grep -v vi|grep ${SCRIPT_NAME}.sh
	echo "Script Terminated !"
	echo
	exit
	fi
	export MAIL_LIST="${EMAIL}"
	#export MAIL_LIST="-r ${SRV_NAME} ${EMAIL}"
	echo
	echo "[${SCRIPT_NAME} Script Started ...]"
	echo
	# Verify log location:
	LOGDIR=/tmp
	if [ ! -w "${LOGDIR}" ]; then
	LOGDIR=~
	fi
	# ###########################
	# Check the Linux OS version:
	# ###########################
	export PATH=${PATH}:/usr/local/bin
	FILE_NAME=/etc/redhat-release
	export FILE_NAME
	if [ -f ${FILE_NAME} ]
	then
	LNXVER=`cat /etc/redhat-release | grep -o '[0-9]'|head -1`
	export LNXVER
	fi
	# Run the script on each DB:
	for ORACLE_SID 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
	export ORACLE_SID
	# ###################
	# Getting ORACLE_HOME:
	# ###################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep -i "^${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
	# 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
	fi
	# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`dbhome "${ORACLE_SID}"`
	export ORACLE_HOME
	fi
	# ATTEMPT4: 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
	fi
	# ATTEMPT5: 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
	fi
	# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	if [ -x /usr/bin/locate ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	fi
	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
	printf "`echo "Reporting AUDIT records on Database ["` `echo -e "\033[33;5m${ORACLE_SID}\033[0m"` `echo "]"`\n"
	echo ""
	# ###################
	# WARNINGS SECTION:
	# ###################
	# Display a WARNING message if AUDITING is not enabled:
	AUDCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	SELECT COUNT(*) FROM V\$PARAMETER WHERE NAME='audit_trail' AND VALUE='NONE';
	exit;
	EOF
	)
	AUDCOUNT=`echo ${AUDCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${AUDCOUNT} -ge 1 ]
	then
	echo ""
	printf "`echo -e "\033[33;5mINFO!\033[0m"` `echo " AUDITING IS NOT ENABLED ON DATABASE [${ORACLE_SID}] ..."`\n"
	echo ""
	fi
	# Display a WARNING message if NTIMESTAMP# column in sys.aud$ is not indexed:
	INDEXCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	select count(*) from dba_ind_columns where table_owner='SYS' and table_name='AUD\$' and column_name='NTIMESTAMP#';
	exit;
	EOF
	)
	INDEXCOUNT=`echo ${INDEXCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${INDEXCOUNT} -le 0 ]
	then
	echo ""
	printf "`echo -e "\033[33;5mWARNING:\033[0m"` `echo " NTIMESTAMP# Column in sys.aud$ table is"` `echo -e "\033[33;5mNOT INDEXED\033[0m"`\n"
	echo ""
	echo "This script may cause a performance degradation when it run!"
	echo "In order to avoid execution slowness, create an index on aud$ (NTIMESTAMP#) column by executing this CREATE INDEX statement:"
	echo ""
	echo "CREATE INDEX sys.idx_ntimestamp# ON sys.aud\$(ntimestamp#) ONLINE;"
	echo "EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD\$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);"
	echo ""
	echo ""
	export NOINDEXWARNING="PROMPT"
	export NOINDEXWARNING1="PROMPT RECOMMENDATION: sys.aud$ table should be INDEXED to speed up this script using these statements:"
	export NOINDEXWARNING2="PROMPT CREATE INDEX sys.idx_ntimestamp# ON sys.aud\$(ntimestamp#) ONLINE;;"
	export NOINDEXWARNING3="PROMPT EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS', tabname => 'AUD$', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);;"
	sleep 2
	fi
	# ###################
	# Check failed logins:
	# ###################
	case ${REPORT_FAILED_LOGINS} in
	y|Y|yes|YES|Yes|ON|On|on)
	FAILEDLOGINCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	select count(*) from aud\$
	where ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	and action# between 100 and 102
	and returncode = 1017
	${HASHDBUSERNAME} and lower (USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (SPARE1) not in (${EXCLUDE_OSUSERS})
	/
	/*
	select count(*) from DBA_AUDIT_SESSION
	where returncode = 1017
	and timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	/
	*/
	exit;
	EOF
	)
	FAILEDLOGINCOUNT=`echo ${FAILEDLOGINCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${FAILEDLOGINCOUNT} -ge ${RECORDSNUM} ]
	then
	echo "FAILED LOGIN ATTEMPTS DETECTED. SENDING AN EMAIL ALERT ..."
	FAILEDLOGINLOG=/tmp/failed_login_report_${ORACLE_SID}.log
	touch ${FAILEDLOGINLOG}
	# HTML Preparation:
	# #################
	case ${HTMLENABLE} in
	y|Y|yes|YES|Yes|ON|On|on)
	if [ -x /usr/sbin/sendmail ]
	then
	export SENDMAIL="/usr/sbin/sendmail -t"
	export MAILEXEC="echo #"
	export HASHHTML=""
	export HASHHTMLOS=""
	export ENDHASHHTMLOS=""
	export HASHNONHTML="--"
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${FAILEDLOGINLOG}
	)
	export SENDMAILARGS
	else
	export SENDMAIL="echo #"
	export MAILEXEC="mail -s"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	fi
	;;
	*)
	export SENDMAIL="echo #"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	export MAILEXEC="mail -s"
	;;
	esac
	FAILEDLOGINOUTPUT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 termout off echo off feedback off linesize ${SQLLINESIZE}
	EXEC DBMS_SESSION.set_identifier('${SCRIPT_NAME}');
	-- Enable HTML color format:
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='1' bordercolor='#E67E22'" ENTMAP OFF
	SPOOL ${FAILEDLOGINLOG}
	prompt
	${HASHHTML} SET PAGES 0
	${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
	${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
	${HASHHTML} PROMPT FAILED LOGIN ATTEMPTS: [Last ${MINUTES} Minutes]
	${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
	${HASHHTML} set pages 1000
	${HASHNONHTML} prompt ****************************************
	${HASHNONHTML} Prompt FAILED LOGIN ATTEMPTS [Last ${MINUTES} Minutes]
	${HASHNONHTML} prompt ****************************************
	set feedback off linesize ${SQLLINESIZE} pages 1000 timing on
	col TERMINAL FOR A30
	col ACTION_NAME FOR A20
	col TIMESTAMP FOR A21
	col OS_USERNAME FOR A20
	col DB_USERNAME FOR A20
	col DATE FOR A22
	col USERHOST FOR A30
	select to_char (ntimestamp#,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,USERID DB_USERNAME, spare1 OS_USERNAME, USERHOST, TERMINAL from aud\$
	Where ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	and action# between 100 and 102
	and returncode = 1017
	${HASHDBUSERNAME} and lower (USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (spare1) not in (${EXCLUDE_OSUSERS})
	order by ntimestamp#
	/
	PROMPT
	${NOINDEXWARNING}
	${NOINDEXWARNING1}
	${NOINDEXWARNING2}
	${NOINDEXWARNING3}
	/*
	select to_char (EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,OS_USERNAME,DB_USERNAME,TERMINAL,USERHOST,ACTION_NAME
	from DBA_AUDIT_SESSION
	where returncode = 1017
	and timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	order by EXTENDED_TIMESTAMP
	/
	*/
	SPOOL OFF
	exit;
	EOF
	)
	export MSGSUBJECT="ALERT: FAILED LOGIN ATTEMPT DETECTED ON [${ORACLE_SID}] ON [${SRV_NAME}]"
	echo ${MSGSUBJECT}
	SENDMAILARGS=$(
	echo "From: ${SENDER};"
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${FAILEDLOGINLOG}
	)
	${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${FAILEDLOGINLOG}
	echo ${SENDMAILARGS} | tr \; '\n' |awk 'length == 1 || NR == 1 {print $0} length && NR > 1 { print substr($0,2) }'| ${SENDMAIL}
	fi
	echo "FAILED LOGIN CHECK COMPLETED."
	echo
	;;
	esac
	# ####################
	# Check AUDIT RECORDS:
	# ####################
	case ${REPORT_AUDIT_RECORDS} in
	y|Y|yes|YES|Yes|ON|On|on)
	AUDITRECORDSCOUNTRAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
	set pages 0 feedback off;
	prompt
	-- Avoided using DBA_AUDIT_TRAIL view to not get the index on AUD\$(NTIMESTAMP#) ignored in the execution plan!
	select count(*) from aud\$ a, audit_actions act
	where a.action# = act.action (+)
	and a.ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	${HASHDBUSERNAME} and lower (a.USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (a.spare1) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (act.NAME) not in (${EXCLUDE_ACTIONS})
	/
	/*
	select count(*) from DBA_AUDIT_TRAIL
	where timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (ACTION_NAME) not in (${EXCLUDE_ACTIONS})
	/
	*/
	exit;
	EOF
	)
	AUDITRECORDSCOUNT=`echo ${AUDITRECORDSCOUNTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
	if [ ${AUDITRECORDSCOUNT} -ge ${RECORDSNUM} ]
	then
	echo "AUDIT RECORDS DETECTED. SENDING AN EMAIL ALERT ..."
	AUDITRECORDSLOG=/tmp/audit_records_report_${ORACLE_SID}.log
	touch ${AUDITRECORDSLOG}
	# HTML Preparation:
	# #################
	case ${HTMLENABLE} in
	y|Y|yes|YES|Yes|ON|On|on)
	if [ -x /usr/sbin/sendmail ]
	then
	export SENDMAIL="/usr/sbin/sendmail -t"
	export MAILEXEC="echo #"
	export HASHHTML=""
	export HASHHTMLOS=""
	export ENDHASHHTMLOS=""
	export HASHNONHTML="--"
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${AUDITRECORDSLOG}
	)
	export SENDMAILARGS
	else
	export SENDMAIL="echo #"
	export MAILEXEC="mail -s"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	fi
	;;
	*)
	export SENDMAIL="echo #"
	export HASHHTML="--"
	export HASHHTMLOS="echo #"
	export ENDHASHHTMLOS=""
	export HASHNONHTML=""
	export MAILEXEC="mail -s"
	;;
	esac
	AUDITRECORDSOUTPUT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 termout off echo off feedback off linesize ${SQLLINESIZE}
	col name for A40
	EXEC DBMS_SESSION.set_identifier('${SCRIPT_NAME}');
	-- Enable HTML color format:
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='1' bordercolor='#E67E22'" ENTMAP OFF
	SPOOL ${AUDITRECORDSLOG}
	prompt
	${HASHHTML} SET PAGES 0
	${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
	${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
	${HASHHTML} PROMPT Audit Records [Last ${MINUTES} Minutes]
	${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
	${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { background: #AF601A; } td { background: #E67E22; padding: 0px; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
	${HASHHTML} set pages 1000
	${HASHNONHTML} prompt **********************************
	${HASHNONHTML} Prompt Audit Records [Last ${MINUTES} Minutes]
	${HASHNONHTML} prompt **********************************
	set feedback off linesize ${SQLLINESIZE} pages 1000 timing on
	col OS_USERNAME FOR A15
	col DB_USERNAME FOR A15
	col DATE FOR A22
	col OWNER FOR A15
	col OBJ_NAME FOR A25
	col USERHOST FOR A21
	col ACTION_NAME FOR A20
	col ACTION_OWNER_OBJECT FOR A80
	col SQL_TEXT FOR A100
	-- Avoided using DBA_AUDIT_TRAIL view to not get the index on AUD\$(NTIMESTAMP#) ignored in the execution plan!
	select to_char(a.NTIMESTAMP#,'DD-Mon-YYYY HH24:MI:SS')"DATE",a.spare1 OS_USERNAME, a.USERID DB_USERNAME, a.USERHOST, act.NAME||' '||a.OBJ\$CREATOR||' . '||a.OBJ\$NAME ACTION_OWNER_OBJECT, a.RETURNCODE, a.SQLTEXT
	from aud\$ a, audit_actions act
	where a.action# = act.action (+)
	and a.ntimestamp# >= new_time(sysdate - ${MINUTES}/1440,'gmt','edt')
	${HASHDBUSERNAME} and lower (a.USERID) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (a.spare1) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (act.NAME) not in (${EXCLUDE_ACTIONS})
	order by a.ntimestamp#
	/
	PROMPT
	${NOINDEXWARNING}
	${NOINDEXWARNING1}
	${NOINDEXWARNING2}
	${NOINDEXWARNING3}
	/*
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",OS_USERNAME,USERNAME DB_USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT,SQL_TEXT
	from dba_audit_trail
	where timestamp >= (sysdate - ${MINUTES}/1440)
	${HASHDBUSERNAME} and lower (USERNAME) not in (${EXCLUDE_DBUSERS})
	${HASHOSUSERNAME} and lower (OS_USERNAME) not in (${EXCLUDE_OSUSERS})
	${HASHACTIONNAME} and upper (ACTION_NAME) not in (${EXCLUDE_ACTIONS})
	order by EXTENDED_TIMESTAMP
	/
	*/
	SPOOL OFF
	exit;
	EOF
	)
	export MSGSUBJECT="ALERT: AUDIT RECORDS DETECTED ON [${ORACLE_SID}] ON [${SRV_NAME}]"
	echo ${MSGSUBJECT}
	SENDMAILARGS=$(
	echo "To: ${EMAIL};"
	echo "Subject: ${MSGSUBJECT} ;"
	echo "Content-Type: text/html;"
	echo "MIME-Version: 1.0;"
	cat ${AUDITRECORDSLOG}
	)
	${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${AUDITRECORDSLOG}
	echo ${SENDMAILARGS} | tr \; '\n' |awk 'length == 1 || NR == 1 {print $0} length && NR > 1 { print substr($0,2) }'| ${SENDMAIL}
	fi
	echo "AUDIT RECORDS CHECK COMPLETED."
	echo
	;;
	esac
	done
	# #############
	# END OF SCRIPT
	# #############
	# REPORT BUGS to: mahmmoudadel@hotmail.com
	# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM: http://dba-tips.blogspot.com/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".

view raw monitor_audit_records.sh hosted with ❤ by GitHub