Enable tracing for an Oracle Session

# #################################################
# Script to Enable tracing for an Oracle Session.
# #################################################

# ###########
# 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” #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 UDUMP Location:
# #########################
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
)
UDUMP=`echo ${VAL_DUMP} | perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`
export UDUMP

# #################################
# 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 ${UDUMP}/${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 ${UDUMP}/${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
# #############

Checks ASM Disk groups, FRA & TABLESPACES Size on the database

# ###################################################################
# Checking ASM Diskgroups, FRA and Tablespaces Size
# ###################################################################
SCRIPT_NAME=”tablespaces”

# ###########
# Description:
# ###########
echo
echo “=========================================================================”
echo “This script Checks ASM Diskgroups, FRA & TABLESPACES Size on the database …”
echo “=========================================================================”
echo
sleep 1

# ###############
# SCRIPT SETTINGS:
# ###############
# Allow the script to use the legacy tablespace size calculation in case some tablespaces are NOT presented in dba_tablespace_usage_metrics:
# Although enabling this setting will make sure all tablespaces will be listed, the legacy calculation method is NOT ACCURATE!
SUPPORT_LEGACY=N
export SUPPORT_LEGACY

# #######################################
# 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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# 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 $DB_ID
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}|grep -v “\-MGMTDB”|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

# Neutralize login.sql file:
# Existance of login.sql file under Oracle user Linux home directory eliminates many functions during the execution of this script from crontab:

if [ -f ${USR_ORA_HOME}/login.sql ]
then
mv ${USR_ORA_HOME}/login.sql ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}
fi

# ###################
# Getting DB Version:
# ###################

VAL311=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select version from v\$instance;
exit;
EOF
)
DB_VER=`echo $VAL311|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# #####################
# Getting DB Block Size:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name=’db_block_size’;
exit;
EOF
)
blksize=`echo $VAL312|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# #####################
# Getting DB ROLE:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select DATABASE_ROLE from v\$database;
exit;
EOF
)
DB_ROLE=`echo $VAL312|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

case ${DB_ROLE} in
PRIMARY) DB_ROLE_ID=0;;
*) DB_ROLE_ID=1;;
esac

# #####################
# Checking ASM Disks:
# #####################
VAL314=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select count(*) from v\$asm_diskgroup;
exit;
EOF
)
ASM_GROUP_COUNT=`echo $VAL314|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# If ASM DISKS Are Exist, View their Size Details:
if [ ${ASM_GROUP_COUNT} -gt 0 ]
then
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF

set pages 100 lines 180 feedback off
col name for a35
PROMPT —————–

prompt ASM DISK GROUPS:
PROMPT —————–

select name,total_mb,free_mb,ROUND((1-(free_mb / total_mb))*100, 2) “%FULL” from v\$asm_diskgroup;
exit;
EOF
fi

# ######################################
# Check Flash Recovery Area Utilization:
# ######################################
VAL318=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name=’db_recovery_file_dest’;
exit;
EOF
)
FRA_LOC=`echo ${VAL318}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# If FRA is configured, check the its utilization:
if [ ! -z ${FRA_LOC} ]
then

${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
set pages 100 lines 180 feedback off
col name for a30
col TOTAL_MB for 99999999999999999
col FREE_MB for 99999999999999999
PROMPT
PROMPT —————–

PROMPT FRA Utilization:
PROMPT —————–

SELECT NAME,SPACE_LIMIT/1024/1024 TOTAL_MB,(SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE)/1024/1024 AS FREE_MB,
ROUND((SPACE_USED – SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS “%FULL”
FROM V\$RECOVERY_FILE_DEST;

PROMPT
PROMPT —————-

PROMPT FRA COMPONENTS:
PROMPT —————-

select * from v\$flash_recovery_area_usage;
EOF

fi

# #########################
# Tablespaces Size Check:
# #########################
case ${SUPPORT_LEGACY} in
Y|y|Yes|YES)
# Check if all tablespaces are presented in dba_tablespace_usage_metrics:
DBA_TABLESPACES_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select count(*) from dba_tablespaces;
exit;
EOF
)
DBA_TABLESPACES_COUNT=`echo ${DBA_TABLESPACES_COUNT_RAW}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

DBA_METRIC_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select count(*) from dba_tablespace_usage_metrics;
exit;
EOF
)
DBA_METRIC_COUNT=`echo ${DBA_METRIC_COUNT_RAW}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`

# If There is missing tablespaces inside dba_tablespace_usage_metrics then use the legacy way in calculating tablespaces size:
# Note: This problem happens for newly created tablespaces and for tablespaces having all their datafiles in AUTOEXTEND OFF mode.
if [ ${DBA_TABLESPACES_COUNT} -gt ${DBA_METRIC_COUNT} ]
then
export DB_VER=9
fi
esac

if [ ${DB_VER} -gt 10 ] && [ ${DB_ROLE_ID} -eq 0 ]
then
# If The Database Version is 11g Onwards and it’s NOT in a STANDBY role:
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF

set pages 100 lines 180 feedback off
col tablespace_name for A25
col Total_MB for 999999999999
col Used_MB for 999999999999
col ‘%Used’ for 999.99
comp sum of Total_MB on report
comp sum of Used_MB on report
bre on report
PROMPT
PROMPT ——————

prompt Tablespaces Size:
PROMPT ——————

select tablespace_name,
(tablespace_size*${blksize})/(1024*1024) Total_MB,
(used_space*${blksize})/(1024*1024) Used_MB,
used_percent “%Used”
from dba_tablespace_usage_metrics;
prompt
exit;
EOF

else

# If The Database Version is 10g Backwards:
# Check if AUTOEXTEND OFF (MAXSIZE=0) is set for any of the datafiles divide by ALLOCATED size else divide by MAXSIZE:
VAL33=$(${ORACLE_HOME}/bin/sqlplus -S ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_DATA_FILES WHERE MAXBYTES=0;
EOF
)
VAL44=`echo $VAL33| awk ‘{print $NF}’`
case ${VAL44} in
“0”) CALCPERCENTAGE1=”((sbytes – fbytes)*100 / MAXSIZE) bused ” ;;
*) CALCPERCENTAGE1=”round(((sbytes – fbytes) / sbytes) * 100,2) bused ” ;;
esac

VAL55=$(${ORACLE_HOME}/bin/sqlplus -S ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_TEMP_FILES WHERE MAXBYTES=0;
EOF
)
VAL66=`echo $VAL55| awk ‘{print $NF}’`
case ${VAL66} in
“0”) CALCPERCENTAGE2=”((sbytes – fbytes)*100 / MAXSIZE) bused ” ;;
*) CALCPERCENTAGE2=”round(((sbytes – fbytes) / sbytes) * 100,2) bused ” ;;
esac

${ORACLE_HOME}/bin/sqlplus -S ‘/ as sysdba’ << EOF
set pages 100 lines 180 feedback off
col tablespace for A25
col “MAXSIZE MB” format 999999999
col x for 999999999 heading ‘Allocated MB’
col y for 999999999 heading ‘Free MB’
col z for 999999999 heading ‘Used MB’
col bused for 999.99 heading ‘%Used’
comp sum of x on report
comp sum of y on report
comp sum of z on report
bre on report
select a.tablespace_name tablespace,bb.MAXSIZE/1024/1024 “MAXSIZE MB”,sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes – fbytes)/1024/1024 z,
$CALCPERCENTAGE1
from (select tablespace_name,sum(bytes) sbytes from dba_data_files group by tablespace_name ) a,
(select tablespace_name,sum(bytes) fbytes,count(*) ext from dba_free_space group by tablespace_name) b,
(select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_data_files group by tablespace_name) bb
–where a.tablespace_name in (select tablespace_name from dba_tablespaces)
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = bb.tablespace_name
and round(((sbytes – fbytes) / sbytes) * 100,2) > 0
UNION ALL
select c.tablespace_name tablespace,dd.MAXSIZE/1024/1024 MAXSIZE_GB,sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes – fbytes)/1024/1024 obytes,
$CALCPERCENTAGE2
from (select tablespace_name,sum(bytes) sbytes
from dba_temp_files group by tablespace_name having tablespace_name in (select tablespace_name from dba_tablespaces)) c,
(select tablespace_name,sum(bytes_free) fbytes,count(*) ext from v\$temp_space_header group by tablespace_name) d,
(select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_temp_files group by tablespace_name) dd
–where c.tablespace_name in (select tablespace_name from dba_tablespaces)
where c.tablespace_name = d.tablespace_name (+)
and c.tablespace_name = dd.tablespace_name
order by tablespace;

set feed off
set pages 0
— 100% used tablespaces will not appear in dba_free_space, The following is to eliminate this BUG:
select ‘ALARM: TABLESPACE ‘||tablespace_name||’ IS 100% FULL !’ from dba_data_files minus select ‘ALARM: TABLESPACE ‘||tablespace_name||’ IS 100% FULL !’ from dba_free_space;
prompt
EOF
fi

# De-Neutralize login.sql file:
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ${USR_ORA_HOME}/login.sql
fi

# #############
# END OF SCRIPT
# #############

To Show Top 5 Sessions Consuming CPU

################################################
# Script to Show Top 5 Sessions Consuming CPU.
################################################

#############
# Description:
#############
echo
echo “=====================================================”
echo “This script Shows the Top 5 DB Sessions Consuming CPU …”
echo “=====================================================”
echo
sleep 1

#############################
# Listing Available Databases:
#############################
EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# 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 $DB_ID
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

###############################################
# SQLPLUS: Show Previous/Current SQL Statement:
###############################################

# SQL Script:
${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 a20
col MODULE for a25
col TERMINAL for a15
col “Current SQL” for a140
col “UNIX PID” for 9999999

SELECT * FROM
(SELECT count(*), p.spid “UNIX PID”, s.username, s.module, s.sid, s.serial#, q.sql_id “SQL ID”,q.SQL_TEXT “Current SQL” FROM v\$active_session_history h, v\$session s, v\$sql q, v\$process p WHERE
h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= ‘ON CPU’
AND p.addr = s.paddr
AND q.sql_id=s.PREV_SQL_ID
AND sample_time > sysdate – interval ’15’ minute
GROUP BY p.spid,s.username, s.module, s.sid, s.serial#,q.SQL_TEXT,q.sql_id
ORDER BY count(*) desc) where rownum <= 5;

EOF

###############
# END OF SCRIPT
###############

To get session information

# #################################################
# Script to get session information
# #################################################
SCRIPT_NAME=”session_details”
# ###########
# Description:
# ###########
echo
echo “================================================================”
echo “This script Gets SESSION Information on the current instance …”
echo “================================================================”
echo
sleep 1

# ##########
# VARIABLES:
# ##########

# Define the MAXSIZE for the LOGFILE in KB:
# 100 MB:
MAXSIZE=102400

# Define the LOGFILE PATH:
export LOG_DIR=~/BUNDLE_Logs

if [ ! -d ${LOG_DIR} ]
then
export LOG_DIR=/tmp
fi

LOGFILE=${LOG_DIR}/SESSIONS.log

if [ -f ${LOGFILE} ]
then
LOGSIZE=$(du -k ${LOGFILE} | cut -f 1)

if [ ${LOGSIZE} -ge ${MAXSIZE} ]
then
# Flush the logfile:
cat /dev/null > ${LOGFILE}
fi
fi

# #######################################
# 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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# 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 $DB_ID
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}|grep -v “\-MGMTDB”|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

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

if [ -f ./login.sql ]
then
mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
fi

# ###############################
# SQLPLUS: Getting Session Info:
# ###############################
echo
echo “Enter the USERNAME or SESSION SID: [Blank value means list all sessions on the current instance]”
echo “==================================”
while read ANS
do
case $ANS in
# case the input is non-numeric value:
*[!0-9]*) echo
if [ -z “${ANS}” ]
then

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set feedback off linesize 210 pages 1000
col “USERNAME|UNIX_PID|SID,SERIAL#” for a45
col “MACHINE | MODULE” for a40
col event for a28
col “STATUS|WAITD|ACT_SINC|LOG_T” for a45
col “I|BLKD_BY” for a9

select s.USERNAME||’ | ‘ ||p.spid ||’ | ‘||s.sid||’,’||s.serial# “USERNAME|UNIX_PID|SID,SERIAL#”,s.MACHINE||’ | ‘||s.MODULE “MACHINE | MODULE”
–,w.event,substr(s.status||’|’||w.seconds_in_wait||’sec’,1,30) “STATUS|TIME_WAITED”
,substr(s.status||’|’||round(w.WAIT_TIME_MICRO/1000000)||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,45) “ST|WAITD|ACT_SINC|LOG_T”
,substr(w.event,1,28)”EVENT”
–,s.CLIENT_IDENTIFIER
,s.PREV_SQL_ID “PREV_SQL_ID”,s.sql_id “CURR_SQL_ID”
,s.FINAL_BLOCKING_INSTANCE||’|’||s.FINAL_BLOCKING_SESSION “I|BLKD_BY”
from v\$session s,v\$process p, v\$session_wait w,v\$sql q
where s.USERNAME like upper (‘%$ANS%’)
and p.addr = s.paddr
and s.sid=w.sid
order by s.USERNAME||’ | ‘||s.sid||’,’||s.serial#||’ | ‘||p.spid,MODULE;
EOF
else

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
col “Previous SQL” for a210
col “Current SQL” for a210
set feedback off linesize 210 pages 1000
col “USER|UNXPID|SID,SER|MACH|MOD” for a54
col event for a23
col “STATUS|WAITD|ACT_SINC|LOG_T” for a45
col “I|BLKD_BY” for a9

select s.USERNAME||’|’||p.spid ||’|’||s.sid||’,’||s.serial#||’|’||s.MACHINE||’|’||s.MODULE “USER|UNXPID|SID,SER|MACH|MOD”
,substr(s.status||’|’||round(w.WAIT_TIME_MICRO/1000000)||’|’||s.LAST_CALL_ET||’|’||s.LOGON_TIME,1,45) “ST|WAITD|ACT_SINC|LOG_T”
,substr(w.event,1,23)”EVENT”
,s.PREV_SQL_ID “PREV_SQL_ID”
,s.sql_id “CURR_SQL_ID”
,s.FINAL_BLOCKING_INSTANCE||’|’||s.FINAL_BLOCKING_SESSION “I|BLKD_BY”
–,s.CLIENT_IDENTIFIER
from v\$session s,v\$process p, v\$session_wait w
where s.USERNAME like upper (‘%$ANS%’)
and p.addr = s.paddr
and s.sid=w.sid;
–order by “INS|USER|SID,SER|MACHIN|MODUL”;
Prompt Previous SQL Statement:
prompt ———————–

select s.PREV_SQL_ID,q.SQL_FULLTEXT “Previous SQL”
from v\$session s,v\$process p,v\$sql q, v\$session_wait w
where s.USERNAME like upper (‘%$ANS%’)
and p.addr = s.paddr
and s.sid=w.sid
and q.child_number=0
and q.sql_id=s.PREV_SQL_ID;

prompt
Prompt Current Running SQL Statement:
prompt ——————————

select s.SQL_ID CURR_SQL_ID,q.SQL_FULLTEXT “Current SQL”
from v\$process p,v\$session s ,v\$sql q, v\$session_wait w
where s.USERNAME like upper (‘%$ANS%’)
and p.addr = s.paddr
and s.sid=w.sid
and q.child_number=0
and q.sql_id=s.sql_id;
EOF
fi
echo;
# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi
exit ;;
*) echo
if [ -z “${ANS}” ]
then
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set feedback off
set pages 0
spool ${LOGFILE} APPEND
prompt
select ‘Timestamp: ‘||to_char(sysdate, ‘DD-Mon-YYYY HH24:MI:SS’) from dual;
prompt ===================================
prompt ALL sessions in the Database…
prompt ===================================
prompt

set feedback off linesize 220 pages 1000
col inst for 99
col module for a27
col event for a28
col MACHINE for a27
col “STATUS|WAITD|ACT_SINC|LOG_T” for a45
col “INST|USER|SID,SERIAL#” for a30
col “INS|USER|SID,SER|MACHIN|MODUL” for a72
select
substr(S.USERNAME||’ | ‘||p.spid||’|’||s.sid||’,’||s.serial#||’ | ‘||substr(s.MACHINE,1,27)||’ | ‘||substr(s.MODULE,1,27),1,72)”USER|SPID|SID,SER|MACHIN|MODUL”
–select s.INST_ID||’|’||s.USERNAME||’ | ‘||s.sid||’,’||s.serial# “INST|USER|SID,SERIAL#”
–,substr(s.MODULE,1,27)”MODULE”
–,substr(s.MACHINE,1,27)”MACHINE”
–,substr(s.status||’|’||w.state||’|’||w.WAIT_TIME_MICRO||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,50) “ST|WA_ST|WAITD|ACT_SINC|LOG_T”
,substr(s.status||’|’||round(w.WAIT_TIME_MICRO/1000000)||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,45) “STATUS|WAITD|ACT_SINC|LOG_T”
,substr(w.event,1,28)”EVENT”
–,s.PREV_SQL_ID
,s.SQL_ID CURR_SQL_ID
from v\$session s, v\$session_wait w ,v\$process p
where s.USERNAME is not null
and p.addr = s.paddr
and s.sid=w.sid
order by “USER|SPID|SID,SER|MACHIN|MODUL”,”STATUS|WAITD|ACT_SINC|LOG_T” desc;

set pages 1000
col MACHINE for a70
col MODULE for a70
PROMPT
PROMPT SESSIONS Distribution:
PROMPT ———————-

PROMPT PER MODULE:
select INST_ID,MODULE,count(*) “TOTAL_SESSIONS” from gv\$session group by INST_ID,module order by INST_ID,count(*) desc,MODULE;
PROMPT
PROMPT PER MACHINE:
select INST_ID,MACHINE,count(*) “TOTAL_SESSIONS” from gv\$session group by INST_ID,MACHINE order by INST_ID,count(*) desc,MACHINE;

PROMPT
set pages 0
select ‘ACTIVE SESSIONS: ‘||count(*) from gv\$session where USERNAME is not null and status=’ACTIVE’;
select ‘INACTIVE SESSIONS: ‘||count(*) from gv\$session where USERNAME is not null and status=’INACTIVE’;
select ‘BACKGROUND SESSIONS: ‘||count(*) from gv\$session where USERNAME is null;
PROMPT ——————– ——

select ‘TOTAL SESSIONS: ‘||count(*) from gv\$session;
PROMPT
EOF

else

${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set feedback off linesize 210 pages 1000
col “DBUSER|OSUSER|UNXPID|SID,SER” for a50
col “MACHINE | MODULE” for a40
col event for a28
col “STATUS|WAITD|ACT_SINC|LOG_T” for a45
col “I|BLKD_BY” for a12

select s.USERNAME||’|’||s.OSUSER||’ |’||p.spid ||’ |’||s.sid||’,’||s.serial# “DBUSER|OSUSER|UNXPID|SID,SER”,s.MACHINE||’ | ‘||s.MODULE “MACHINE | MODULE”
–,w.event,substr(s.status||’|’||w.state||’|’||w.seconds_in_wait||’sec’,1,30) “STATUS|WAIT_STATE|TIME_WAITED”
,substr(s.status||’|’||round(w.WAIT_TIME_MICRO/1000000)||’|’||LAST_CALL_ET||’|’||LOGON_TIME,1,45) “STATUS|WAITD|ACT_SINC|LOG_T”
,substr(w.event,1,28)”EVENT”
,s.FINAL_BLOCKING_INSTANCE||’|’||s.FINAL_BLOCKING_SESSION “I|BLKD_BY”
,s.CLIENT_IDENTIFIER
,s.PREV_SQL_ID “PREV_SQL_ID”,s.sql_id “CURR_SQL_ID”
from v\$session s,v\$process p, v\$session_wait w
where s.SID =’$ANS’
and p.addr = s.paddr
and s.sid=w.sid;

prompt
col “Previous SQL” for a140
select q.SQL_ID,q.SQL_FULLTEXT “Previous SQL”
from v\$process p,v\$session s ,v\$sql q
where s.SID =’$ANS’
and p.addr = s.paddr
and q.sql_id=s.PREV_SQL_ID;

prompt
col “Current SQL” for a140
select q.SQL_ID,q.SQL_FULLTEXT “Current SQL”
from v\$process p,v\$session s ,v\$sql q
where s.SID =’$ANS’
and p.addr = s.paddr
and q.sql_id=s.sql_id;
EOF
fi
echo;
# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi
exit ;;

esac
done

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi

# #############
# END OF SCRIPT
# #############

RMAN FULL Backup of a database

# ###############################################
# This script takes a RMAN Backup a database.
# ###############################################

# ###########
# Description:
# ###########
echo
echo “===================================================”
echo “This script Takes a RMAN FULL Backup of a database.”
echo “===================================================”
echo
sleep 1

# ###########################
# CPU count check:
# ###########################

# Count of CPUs:
CPU_NUM=`cat /proc/cpuinfo|grep CPU|wc -l`
export CPU_NUM

# #######################################
# 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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# 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 $DB_ID
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}|grep -v “\-MGMTDB”|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

# ###############################
# RMAN: Script Creation:
# ###############################
# Last RMAN Backup Info:
# #####################
export NLS_DATE_FORMAT=’DD-Mon-YYYY HH24:MI:SS’
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set linesize 170 pages 200
PROMPT LAST 14 DAYS RMAN BACKUP DETAILS:
PROMPT ———————————

set linesize 160
set feedback off
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading “DATA SIZE” for a10
col OUTPUT_BYTES_DISPLAY heading “Backup Size” for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading “Speed/s” for a10
col output_device_type heading “Device_TYPE” for a11
SELECT to_char (start_time,’DD-MON-YY HH24:MI’) START_TIME, to_char(end_time,’DD-MON-YY HH24:MI’) END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -14;

EOF

# Variables:
export NLS_DATE_FORMAT=”DD-MON-YY HH24:MI:SS”

# Building the RMAN BACKUP Script:
echo;echo
echo Please enter the Backup Location: [e.g. /backup/DB]
echo “================================”
while read BKPLOC1
do
/bin/mkdir -p ${BKPLOC1}/RMANBKP_${ORACLE_SID}/`date ‘+%F’`
BKPLOC=${BKPLOC1}/RMANBKP_${ORACLE_SID}/`date ‘+%F’`

if [ ! -d “${BKPLOC}” ]; then
echo “Provided Backup Location is NOT Exist/Writable !”
echo
echo “Please Provide a VALID Backup Location:”
echo “————————————–”
else
break
fi
done
echo
echo “Backup Location is: ${BKPLOC1}”
echo
echo “How many CHANNELS do you want to allocate for this backup? [${CPU_NUM} CPUs Available On This Machine]”
echo “=========================================================”
while read CHANNEL_NUM
do
integ=’^[0-9]+$’
if ! [[ ${CHANNEL_NUM} =~ $integ ]] ; then
echo “Error: Not a valid number !”
echo
echo “Please Enter a VALID NUMBER:”
echo “—————————”
else
break
fi
done
echo
echo “Number Of Channels is: ${CHANNEL_NUM}”
echo
echo “———————————————”
echo “COMPRESSED BACKUP will allocate SMALLER space”
echo “but it’s a bit SLOWER than REGULAR BACKUP.”
echo “———————————————”
echo
echo “Do you want a COMPRESSED BACKUP? [Y|N]: [Y]”
echo “================================”
while read COMPRESSED
do
case $COMPRESSED in
“”|y|Y|yes|YES|Yes) COMPRESSED=” AS COMPRESSED BACKUPSET “; echo “COMPRESSED BACKUP ENABLED.”;break ;;
n|N|no|NO|No) COMPRESSED=””;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

echo
echo “Do you want to ENCRYPT the BACKUP by Password? [Available in Enterprise Edition only] [Y|N]: [N]”
echo “==============================================”
while read ENCR_BY_PASS_ANS
do
case ${ENCR_BY_PASS_ANS} in
y|Y|yes|YES|Yes)
echo
echo “Please Enter the password that will be used to Encrypt the backup:”
echo “—————————————————————–”
read ENCR_PASS
ENCR_BY_PASS=”SET ENCRYPTION ON IDENTIFIED BY ‘${ENCR_PASS}’ ONLY;”
export ENCR_BY_PASS
echo
echo “BACKUP ENCRYPTION ENABLED.”
echo
echo “Later, To RESTORE this backup please use the following command to DECRYPT it, placing it just before the RESTORE Command:”
echo ” e.g.”
echo ” SET DECRYPTION IDENTIFIED BY ‘${ENCR_PASS}’;”
echo ” restore database ….”
echo
break ;;
“”|n|N|no|NO|No) ENCR_BY_PASS=””;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

RMANSCRIPT=${BKPLOC}/RMAN_FULL_${ORACLE_SID}.rman
RMANSCRIPTRUNNER=${BKPLOC}/RMAN_FULL_nohup.sh
RMANLOG=${BKPLOC}/rmanlog.`date ‘+%a’`

echo “${ENCR_BY_PASS}” > ${RMANSCRIPT}
echo “run {” >> ${RMANSCRIPT}
CN=1
while [[ ${CN} -le ${CHANNEL_NUM} ]]
do
echo “allocate channel C${CN} type disk;” >> ${RMANSCRIPT}
((CN = CN + 1))
done
echo “CHANGE ARCHIVELOG ALL CROSSCHECK;” >> ${RMANSCRIPT}
#echo “DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;” >> ${RMANSCRIPT}
echo “BACKUP ${COMPRESSED} INCREMENTAL LEVEL=0 FORMAT ‘${BKPLOC}/%d_%I_%t_%s_%p’ TAG=’FULLBKP'” >> ${RMANSCRIPT}
echo “FILESPERSET 100 DATABASE include current controlfile PLUS ARCHIVELOG;” >> ${RMANSCRIPT}
#echo “BACKUP FORMAT ‘${BKPLOC}/%d_%t_%s_%p.ctl’ TAG=’CONTROL_BKP’ CURRENT CONTROLFILE;” >> ${RMANSCRIPT}
echo “BACKUP ${COMPRESSED} FORMAT ‘${BKPLOC}/CONTROLFILE_%d_%I_%t_%s_%p.bkp’ REUSE TAG=’CONTROL_BKP’ CURRENT CONTROLFILE;” >> ${RMANSCRIPT}
echo “SQL \”ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ”${BKPLOC}/controlfile.trc” REUSE\”;” >> ${RMANSCRIPT}
echo “SQL \”CREATE PFILE=”${BKPLOC}/init${ORACLE_SID}.ora” FROM SPFILE\”;” >> ${RMANSCRIPT}
echo “}” >> ${RMANSCRIPT}
echo “RMAN BACKUP SCRIPT CREATED.”
echo
sleep 1
echo “Backup Location is: ${BKPLOC}”
echo
sleep 1
echo “Starting Up RMAN Backup Job …”
echo
sleep 1
echo “#!/bin/bash” > ${RMANSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/rman target / cmdfile=${RMANSCRIPT} | tee ${RMANLOG} 2>&1 &” >> ${RMANSCRIPTRUNNER}
chmod 740 ${RMANSCRIPTRUNNER}
source ${RMANSCRIPTRUNNER}
echo
echo ” The RMAN backup job is currently running in the background. Disconnecting the current session will NOT interrupt the backup job :-)”
echo ” Now, viewing the backup job log:”
echo
echo “Backup Location is: ${BKPLOC}”
echo “Check the LOGFILE: ${RMANLOG}”
echo

# #############
# END OF SCRIPT
# #############

REBUILD A GIVEN TABLE AND IT’s INDEXES

# ################################################
# SCRIPT TO REBUILD A GIVEN TABLE AND IT’s INDEXES
# ################################################

# ###########
# Description:
# ###########
echo
echo “==============================”
echo “This script gets TABLE Details …”
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” #Excluded INSTANCES [Will not get reported offline].

# ###########################
# List 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`

## 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
PARALLEL_DEGREE=`cat /proc/cpuinfo| grep processor|wc -l`
if [ “${PARALLEL_DEGREE##[0-9]*}” ]
then
PARALLEL_DEGREE=1
fi

## If OS is SUN:
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
PARALLEL_DEGREE=`kstat cpu_info|grep core_id|sort -u|wc -l`
if [ -z “${PARALLEL_DEGREE##[0-9]*}” ]
then
PARALLEL_DEGREE=1
fi
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user’s profile:
if [ -z “${ORACLE_HOME}” ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
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

# ########################################
# SQLPLUS: TABLE REBUILD:
# ########################################
# Checking FORCE LOGGING mode:
# ###########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
select force_logging from v\$database;
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
case ${VAL2} in
YES) echo
echo “^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^”
echo “INFO: THE DATABASE IS IN FORCE LOGGING MODE.”
echo “vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv”
echo;sleep 2;;
*);;
esac

echo “********************************************************************************”
echo “It’s HIGHLY RECOMMENDED to run this script during DOWNTIME WINDOW,”
echo “To AVOID INTERRUPTING long running queries against the table during the rebuild.”
echo “********************************************************************************”

echo
echo “Enter the OWNER of Table:”
echo “========================”
while read OWNER
do
case ${OWNER} in
“”)echo
echo “Enter the OWNER of the Table:”
echo “============================”;;
*)
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘$OWNER’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
case ${VAL22} in
0) echo;echo “ERROR: USER [${OWNER}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !”
echo; echo “Searching For Users Match The Provided String …”; sleep 1
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
select username “Users Match Provided String” from dba_users where username like upper (‘%$OWNER%’);
EOF
echo;echo “Enter A Valid Table Owner:”
echo “=========================”;;
*) break;;
esac
esac
done
echo
echo “Enter the TABLE Name:”
echo “====================”
while read OBJECT_NAME
do
case ${OBJECT_NAME} in
“”)echo
echo “Enter the TABLE NAME:”
echo “====================”;;
*)
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘$OWNER’) AND TABLE_NAME=UPPER(‘$OBJECT_NAME’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
case ${VAL22} in
0) echo;echo “INFO: TABLE [${OBJECT_NAME}] IS NOT EXIST UNDER SCHEMA [$OWNER] !”
echo;echo “Searching for tables match the provided string …”; sleep 1
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
select table_name “Tables Match Provided String” from dba_tables where owner=upper(‘$OWNER’) and table_name like upper (‘%$OBJECT_NAME%’);
EOF
echo;echo “Enter A VALID TABLE NAME:”
echo “========================”;;
*) break;;
esac
esac
done

# INFO AND REBUILD PROCEDURE:
# ##########################
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 100
SPOOL TABLE_${OBJECT_NAME}_INFO_BEFORE_REBUILD.log
PROMPT
PROMPT TABLE INFO:
PROMPT ———–

set linesize 180
col “OWNER.TABLE” for a35
col tablespace_name for a20
col “READONLY” for a8
select t.owner||’.’||t.table_name “OWNER.TABLE”,t.TABLESPACE_NAME,t.PCT_FREE
,t.PCT_USED,d.extents,t.MAX_EXTENTS,t.COMPRESSION,t.READ_ONLY “READONLY”,o.created,t.LAST_ANALYZED,d.bytes/1024/1024 SIZE_MB
from dba_tables t, dba_objects o, dba_segments d
where t.owner= upper(‘$OWNER’)
and t.table_name = upper(‘$OBJECT_NAME’)
and o.owner=t.owner
and o.object_name=t.table_name
and o.owner=d.owner
and t.table_name=d.SEGMENT_NAME;

PROMPT
PROMPT
PROMPT INDEXES BEFORE REBUILD:
PROMPT ———————-

set pages 100
set heading on
COLUMN OWNER FORMAT A25 heading “Index Owner”
COLUMN INDEX_NAME FORMAT A30 heading “Index Name”
COLUMN COLUMN_NAME FORMAT A25 heading “On Column”
COLUMN COLUMN_POSITION FORMAT 9999 heading “Pos”
COLUMN “INDEX” FORMAT A35
COLUMN TABLESPACE_NAME FOR A25
COLUMN INDEX_TYPE FOR A26
SELECT IND.OWNER||’.’||IND.INDEX_NAME “INDEX”,
IND.INDEX_TYPE,
COL.COLUMN_NAME,
COL.COLUMN_POSITION,
IND.TABLESPACE_NAME,
IND.STATUS,
IND.UNIQUENESS,
IND.LAST_ANALYZED,d.bytes/1024/1024 SIZE_MB
FROM SYS.DBA_INDEXES IND,
SYS.DBA_IND_COLUMNS COL,
DBA_SEGMENTS d
WHERE IND.TABLE_NAME = upper(‘$OBJECT_NAME’)
AND IND.TABLE_OWNER = upper(‘$OWNER’)
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = d.OWNER
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME
AND IND.INDEX_NAME = d.SEGMENT_NAME;

SPOOL OFF
SET FEEDBACK OFF
exec dbms_lock.sleep(5);

PROMPT REBUILDING TABLE PROCEDURE WILL START WITHIN 5 Seconds …
PROMPT ——————————————————-

exec dbms_lock.sleep(4);
PROMPT [5]
exec dbms_lock.sleep(1);
PROMPT [4]
exec dbms_lock.sleep(1);
PROMPT [3]
exec dbms_lock.sleep(1);
PROMPT [2]
exec dbms_lock.sleep(1);
PROMPT [1]
exec dbms_lock.sleep(1);
PROMPT
PROMPT [REBUILDING] …
SET FEEDBACK ON

PROMPT
PROMPT SETTING TABLE $OWNER.$OBJECT_NAME IN NOLOGGING MODE …
ALTER TABLE $OWNER.$OBJECT_NAME NOLOGGING;
PROMPT REBUILDING TABLE $OWNER.$OBJECT_NAME …
ALTER TABLE $OWNER.$OBJECT_NAME MOVE PARALLEL $PARALLEL_DEGREE;
PROMPT SETTING TABLE $OWNER.$OBJECT_NAME IN LOGGING MODE …
ALTER TABLE $OWNER.$OBJECT_NAME LOGGING;

SET TERMOUT OFF
set pages 0
SET LINESIZE 157
SET PAGESIZE 5000
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF

SPOOL REBUILD_TABLE_${OBJECT_NAME}_SCRIPT.sql
select ‘SPOOL REBUILD_TABLE_${OBJECT_NAME}_SCRIPT.log’ from dual;
select ‘ALTER INDEX ‘||owner||’.”‘||index_name||'” REBUILD ONLINE PARALLEL $PARALLEL_DEGREE;’ from dba_indexes
where OWNER=upper(‘$OWNER’) and TABLE_NAME=upper(‘$OBJECT_NAME’) and STATUS <> ‘VALID’;
select ‘spool off’ from dual;
SPOOL OFF

SET TERMOUT ON ECHO ON FEEDBACK ON VERIFY ON
PROMPT
PROMPT REBUILDING UNUSABLE INDEXES …
@REBUILD_TABLE_${OBJECT_NAME}_SCRIPT.sql

PROMPT
PROMPT GATHERING STATISTICS ON TABLE [${OWNER}.${OBJECT_NAME}] AND ITS INDEXES …
PROMPT
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => upper(‘$OWNER’),
tabname => upper(‘$OBJECT_NAME’),
cascade => TRUE,
METHOD_OPT => ‘FOR ALL COLUMNS SIZE SKEWONLY’,
DEGREE => DBMS_STATS.AUTO_DEGREE,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/

set pages 100
SET HEADING ON
SPOOL TABLE_${OBJECT_NAME}_INFO_AFTER_REBUILD.log
PROMPT
PROMPT TABLE INFO:
PROMPT ———–

set linesize 180
col “OWNER.TABLE” for a35
col tablespace_name for a20
col “READONLY” for a8
select t.owner||’.’||t.table_name “OWNER.TABLE”,t.TABLESPACE_NAME,t.PCT_FREE
,t.PCT_USED,d.extents,t.MAX_EXTENTS,t.COMPRESSION,t.READ_ONLY “READONLY”,o.created,t.LAST_ANALYZED,d.bytes/1024/1024 SIZE_MB
from dba_tables t, dba_objects o, dba_segments d
where t.owner= upper(‘$OWNER’)
and t.table_name = upper(‘$OBJECT_NAME’)
and o.owner=t.owner
and o.object_name=t.table_name
and o.owner=d.owner
and t.table_name=d.SEGMENT_NAME;

PROMPT
PROMPT
PROMPT INDEXES AFTER REBUILD:
PROMPT ———————-

COLUMN OWNER FORMAT A25 heading “Index Owner”
COLUMN INDEX_NAME FORMAT A30 heading “Index Name”
COLUMN COLUMN_NAME FORMAT A25 heading “On Column”
COLUMN COLUMN_POSITION FORMAT 9999 heading “Pos”
COLUMN “INDEX” FORMAT A35
COLUMN TABLESPACE_NAME FOR A25
COLUMN INDEX_TYPE FOR A26
SELECT IND.OWNER||’.’||IND.INDEX_NAME “INDEX”,
IND.INDEX_TYPE,
COL.COLUMN_NAME,
COL.COLUMN_POSITION,
IND.TABLESPACE_NAME,
IND.STATUS,
IND.UNIQUENESS,
IND.LAST_ANALYZED,d.bytes/1024/1024 SIZE_MB
FROM SYS.DBA_INDEXES IND,
SYS.DBA_IND_COLUMNS COL,
DBA_SEGMENTS d
WHERE IND.TABLE_NAME = upper(‘$OBJECT_NAME’)
AND IND.TABLE_OWNER = upper(‘$OWNER’)
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = d.OWNER
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME
AND IND.INDEX_NAME = d.SEGMENT_NAME;

SPOOL OFF

PROMPT
PROMPT ——————————————

PROMPT TABLE [${OBJECT_NAME}] HAS BEEN REBUILT.
PROMPT ——————————————

PROMPT

EOF

# #############
# END OF SCRIPT
# #############

Deletes Applied Archives older than Specified N hours on STANDBY DATABASE

# ##############################################
# This Script deletes Applied Archives older than Specified N hours on STANDBY DATABASE
# This script will run by default against ALL running STANDBY DATABASES.
# Please read the following instructions on how to use this script:
# – You can set MAIL_LIST variable to your E-mail to receive an email alert if archives
# are not applied.
# e.g. MAIL_LIST=”ram.kumar@dba.com”
# – You can specify the candidate archives for deletion older than N hours by setting
# LAST_N_HOURS variable to the number of hours.
# e.g. Deleting applied archives in older than 24 hours
# LAST_N_HOURS=24
# – You can EXCLUDE any instance from having the script to run against by passing INSTANCE_NAME
# to EXL_DB variable.
# e.g. excluding orcl from archive deletion:
# EXL_DB=”\-MGMTDB|ASM|orcl”
# – You can use FORCE option when deleting the archives from RMAN console: [Y|N]
# e.g. FORCE_DELETION=Y
# – You can decide to CROSSCHECK the archivelogs after the archivelogs deletion: [Y|N]
# e.g. VALIDATE_ARCHIVES=Y
# ################################################

# ##################################
# VARIABLES: [To be ALTERED By User] …………………………………
# ##################################

SCRIPT_NAME=”delete_standby_archives.sh”
SRV_NAME=`uname -n`
MAIL_LIST=”youremail@yourcompany.com”

# #############################################################################
# Define the number of HOURS where ARCHIVES older than N Hours will be deleted: [Default 8 HOURS]
# #############################################################################
LAST_N_HOURS=8
export LAST_N_HOURS

# #############################################################################
# Do you want to CROSSCHECK the ARCHIVE LOGS after the deletion?: [Y|N] [Default YES]
# #############################################################################
VALIDATE_ARCHIVES=Y

# #############################################################################
# Do you want to FORCEFULLY DELETE the ARCHIVE LOGS?: [Y|N] [Default NO]
# #############################################################################
FORCE_DELETION=N

# #######################################
# 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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# #########################
# Setting ORACLE_SID:
# #########################
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}|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_SID is ${ORACLE_SID}”
#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

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

if [ -f ./login.sql ]
then
mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
fi

if [ -f ${USR_ORA_HOME}/login.sql ]
then
mv ${USR_ORA_HOME}/login.sql ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}
fi

# #########################
# Variables:
# #########################
export PATH=$PATH:${ORACLE_HOME}/bin

# #########################
# LOG FILE:
# #########################
export LOG_DIR=`pwd`

if [ ! -d ${LOG_DIR} ]
then
export LOG_DIR=/tmp
fi
LOG_FILE=${LOG_DIR}/DELETE_ARCHIVES.log

# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo $VAL1| perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`
DB_NAME_LOWER=$( echo “$DB_NAME_UPPER” | tr -s ‘[:upper:]’ ‘[:lower:]’ )
export DB_NAME_UPPER
export DB_NAME_LOWER

# DB_NAME is Uppercase or Lowercase?:

if [ -d $ORACLE_HOME/diagnostics/${DB_NAME_LOWER} ]
then
DB_NAME=$DB_NAME_LOWER
else
DB_NAME=$DB_NAME_UPPER
fi

# ###########################
# CHECKING DB ROLE: [STANDBY]
# ###########################
VAL12=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
select COUNT(*) from v\$database where DATABASE_ROLE=’PHYSICAL STANDBY’;
exit;
EOF
)

DB_ROLE=`echo $VAL12| perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’`

# If the database is a standby DB, proceed with the rest of script:

if [ ${DB_ROLE} -gt 0 ]
then
# Delete archives only when they are applied:
VAL31=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
prompt
–select count(*) from v\$archived_log where completion_time between sysdate-2 and sysdate-$LAST_N_HOURS/24 and APPLIED = ‘NO’;
–select count(*) from v\$archived_log where name is not null and completion_time between sysdate and sysdate-$LAST_N_HOURS/24 and FAL=’NO’ and APPLIED = ‘NO’;
select count(*) from v\$archived_log where name is not null and completion_time between sysdate-(${LAST_N_HOURS}+1)/24 and sysdate-({$LAST_N_HOURS})/24 and FAL=’NO’ and APPLIED = ‘NO’;
EOF
)
NO_APPL_ARC=`echo ${VAL31}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`
export NO_APPL_ARC

#echo “NOT_APPLIED_ARCHIVES=$NO_APPL_ARC”

if [ ${NO_APPL_ARC} -gt 0 ]
then
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
spool ${LOG_FILE}
PROMPT
PROMPT ————————————————————————————————

PROMPT SCRIPT TERMINATED! There are archivelogs in the last ${LAST_N_HOURS} Hours are NOT yet APPLIED.
PROMPT MAKE SURE THAT ALL ARCHIVES ARE APPLIED BEFORE DELETING ARCHIVELOGS.
PROMPT ————————————————————————————————

PROMPT THE FOLLOWING ARCHIVES ARE NOT YET APPLIED TO THE STANDBY DB:
PROMPT ————————————————————-

set pages 2000
set linesize 199
col name for a120
select name,to_char(completion_time,’HH24:MI:SS DD-MON-YYYY’) completion_time,applied from v\$archived_log
where name is not null and completion_time <= sysdate-${LAST_N_HOURS}/24 and FAL=’NO’ and APPLIED = ‘NO’
order by completion_time asc;

PROMPT
spool off
EOF

mail -s “ALERT: ARCHIVES IN THE LAST [${LAST_N_HOURS}] HOURS ARE NOT APPLIED ON STANDBY DB [${DB_NAME}] ” ${MAIL_LIST} < ${LOG_FILE}

else

echo “”
echo “^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^”
echo “ALL Archives in the last ${LAST_N_HOURS} Hours were APPLIED successfully.”
echo “^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^”
echo “”
VAL35=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
PROMPT
–select count(*) from v\$archived_log where name is not null and completion_time between sysdate-2 and sysdate-${LAST_N_HOURS}/24 and APPLIED = ‘YES’;
select count(*) from v\$archived_log where name is not null and completion_time <= sysdate-${LAST_N_HOURS}/24 and APPLIED = ‘YES’;
EOF
)
CAND_DEL_ARC=`echo ${VAL35}|perl -lpe’$_ = reverse’ |awk ‘{print $1}’|perl -lpe’$_ = reverse’|cut -f1 -d ‘.’`
export CAND_DEL_ARC
if [ ${CAND_DEL_ARC} -gt 0 ]
then
echo “CHECKING CANDIDATE ARCHIVES FOR DELETION …”
sleep 1
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set pages 0 feedback off;
PROMPT THE FOLLOWING CANDIDATE ARCHIVES WILL BE DELETED:
PROMPT ————————————————-

col name for a120
select name from v\$archived_log where name is not null and FAL=’NO’ and completion_time <= sysdate-${LAST_N_HOURS}/24;
EOF

# CHECK CROSSCHECK OPTION:

case ${VALIDATE_ARCHIVES} in
y|Y|yes|YES|Yes) CROSSCHECK_ARCHIVELOGS=”change archivelog all crosscheck;”; export CROSSCHECK_ARCHIVELOGS;;
*) CROSSCHECK_ARCHIVELOGS=””; export CROSSCHECK_ARCHIVELOGS;;
esac

# CHECK FORCE DELETION OPTION:

case ${FORCE_DELETION} in
y|Y|yes|YES|Yes) FORCE_OPTION=”force”; export FORCE_OPTION;;
*) FORCE_OPTION=””; export FORCE_OPTION;;
esac

# START CANDIDATE ARCHIVES DELETION FROM RMAN CONSOLE:
export NLS_DATE_FORMAT=”DD-MON-YY HH24:MI:SS”
${ORACLE_HOME}/bin/rman target / <<EOF
delete noprompt ${FORCE_OPTION} archivelog all completed before ‘sysdate-${LAST_N_HOURS}/24′;
${CROSSCHECK_ARCHIVELOGS}
EOF
echo “”
echo “ALL ARCHIVES OLDER THAN ${LAST_N_HOURS} HOURS WERE DELETED SUCCESSFULLY.”
echo “vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv”
echo “”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
PROMPT
PROMPT AVAILABLE ARCHIVES STATUS IN THE LAST ${LAST_N_HOURS} HOURS:
PROMPT ———————————————–

set pages 2000 linesize 199
col name for a120
select name,to_char(completion_time,’DD-MON-YYYY HH24:MI:SS’) completion_time,applied from v\$archived_log
where name is not null and completion_time >= sysdate-${LAST_N_HOURS}/24
order by completion_time asc;
EOF
echo “”
echo “NO CANDIDATE ARCHIVES ARE ELIGIBLE FOR DELETION IN THE LAST ${LAST_N_HOURS} HOURS !”
echo “vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv”
echo “”
fi
fi
fi
if [ ${DB_ROLE} -eq 0 ]
then
echo “Database ${DB_NAME} is NOT a STANDBY DB”
echo “This script is designed to run against STANDBY DBs ONLY!”
echo “SCRIPT TERMINATED!”
fi
done

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME} ./login.sql
fi

if [ -f ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ]
then
mv ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME} ${USR_ORA_HOME}/login.sql
fi

# #############
# END OF SCRIPT
# #############

Backup & Gather Statistics On SCHEMA or TABLE.

# ##############################################
# Backup & Gather Statistics On SCHEMA|TABLE.
# To be run by ORACLE user
# ##############################################

# ###########
# Description:
# ###########
echo
echo “=======================================================”
echo “This script Gather & Backup Statistics on SCHEMA|TABLE.”
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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# 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 $DB_ID
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}|grep -v “\-MGMTDB”|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 ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user’s profile if not set:

if [ -z “${ORACLE_BASE}” ]
then
ORACLE_BASE=`grep ‘ORACLE_BASE=\/’ $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_BASE
fi

# #########################
# SQLPLUS Section:
# #########################
# PROMPT FOR VARIABLES:
# ####################

if [ ! -d ${USR_ORA_HOME} ]
then
export USR_ORA_HOME=/tmp
fi

GATHERSTATSSCRIPT=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.sql
GATHERSTATSSCRIPTRUNNER=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.sh
GATHERSTATSSPOOL=${USR_ORA_HOME}/gather_stats_script_DBA_BUNDLE.log
STATS_TABLE=BACKUP_STATS
STATS_OWNER=SYS
STATS_TBS=SYSTEM

VAL33=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT STATUS FROM V\$INSTANCE;
EOF
)
VAL44=`echo $VAL33| awk ‘{print $NF}’`
case ${VAL44} in
“OPEN”) echo ;;
*) echo;echo “ERROR: INSTANCE [${ORACLE_SID}] IS IN STATUS: ${VAL44} !”
echo; echo “PLEASE OPEN THE INSTANCE [${ORACLE_SID}] AND RE-RUN THIS SCRIPT.”;echo; exit ;;
esac

echo “Enter the SCHEMA NAME/TABLE OWNER:”
echo “==================================”
while read SCHEMA_NAME
do
if [ -z ${SCHEMA_NAME} ]
then
echo
echo “Enter the SCHEMA NAME/TABLE OWNER:”
echo “==================================”
else
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper(‘${SCHEMA_NAME}’);
EOF
)
VAL22=`echo $VAL11| awk ‘{print $NF}’`
if [ ${VAL22} -eq 0 ]
then
echo
echo “ERROR: USER [${SCHEMA_NAME}] IS NOT EXIST ON DATABASE [${ORACLE_SID}] !”
echo
echo “Enter the SCHEMA NAME:”
echo “=====================”
else
break
fi
fi
done

echo
echo “Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE [${SCHEMA_NAME}] SCHEMA STATISTICS]”
echo “====================”
while read TABLE_NAME
do
if [ -z ${TABLE_NAME} ]
then
echo
echo “Confirm GATHERING STATISTICS ON WHOLE [${SCHEMA_NAME}] SCHEMA? [Y|N] [Y]”
echo “====================================================”
while read ANS
do
case $ANS in
“”|y|Y|yes|YES|Yes) echo “GATHERING STATISTICS ON SCHEMA [${SCHEMA_NAME}] …”
echo
echo “GATHER HISTOGRAMS ALONG WITH STATISTICS? [Y|N] [N]”
echo “=======================================”
while read ANS1
do
case $ANS1 in
y|Y|yes|YES|Yes) HISTO=”FOR ALL COLUMNS SIZE SKEWONLY”;HISTOMSG=”(+HISTOGRAMS)”; break ;;
“”|n|N|no|NO|No) HISTO=”FOR ALL COLUMNS SIZE 1”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

# Check The Existence of BACKUP STATS TABLE:
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${STATS_OWNER}’) AND TABLE_NAME=upper(‘${STATS_TABLE}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -gt 0 ]
then
echo
echo “STATISTICS BACKUP TABLE [${STATS_OWNER}.${STATS_TABLE}] IS ALREADY EXISTS.”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SET HEADING OFF
SET VERIFY OFF
PROMPT CREATING STATS TABLE [Holds original statistics]…
BEGIN
dbms_stats.create_stat_table (
ownname => upper(‘${STATS_OWNER}’),
tblspace => upper(‘${STATS_TBS}’),
stattab => upper(‘${STATS_TABLE}’));
END;
/
PROMPT
EOF
fi

echo “spool ${GATHERSTATSSPOOL}” > ${GATHERSTATSSCRIPT}
echo “PROMPT BACKING UP CURRENT STATISTICS OF SCHEMA [${SCHEMA_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.EXPORT_SCHEMA_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “statown => upper(‘${STATS_OWNER}’),” >>${GATHERSTATSSCRIPT}
echo “stattab => upper(‘${STATS_TABLE}’));” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT GATHERING STATISTICS ${HISTOMSG} ON SCHEMA [${SCHEMA_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT Feel free to exist this session any time as this script is running in the background :-)” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT To check the progress:” >>${GATHERSTATSSCRIPT}
echo “PROMPT tail -f ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.GATHER_SCHEMA_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “METHOD_OPT => ‘${HISTO}’,” >>${GATHERSTATSSCRIPT}
echo “DEGREE => DBMS_STATS.AUTO_DEGREE,” >>${GATHERSTATSSCRIPT}
echo “estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT (IN CASE THE NEW STATISTICS ARE PERFORMING BAD, RESTORE BACK THE ORIGINAL STATISTICS USING THE FOLLOWING SQL COMMAND):” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => upper(‘${SCHEMA_NAME}’), statown => upper(‘${STATS_OWNER}’), stattab => upper(‘${STATS_TABLE}’));;”>>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT SCRIPT COMPLETED!” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “spool off” >>${GATHERSTATSSCRIPT}
echo “exit” >>${GATHERSTATSSCRIPT}

echo “#!/bin/bash” > ${GATHERSTATSSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/sqlplus \”/ as sysdba\” @${GATHERSTATSSCRIPT} | tee ${GATHERSTATSSPOOL} 2>&1 &” >>${GATHERSTATSSCRIPTRUNNER}
chmod 740 ${GATHERSTATSSCRIPTRUNNER}
echo “”
source ${GATHERSTATSSCRIPTRUNNER}

exit 1 ;;
n|N|no|NO|No) echo; echo “Enter the TABLE NAME:”;echo “====================”;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done
else
# Check The Existence of ENTERED TABLE:
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${SCHEMA_NAME}’) AND TABLE_NAME=upper(‘${TABLE_NAME}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -eq 0 ]
then
echo
echo “ERROR: TABLE [${SCHEMA_NAME}.${TABLE_NAME}] IS NOT EXIST !”
echo;echo “Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE SCHEMA [${SCHEMA_NAME}] STATISTICS]”
echo “====================”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SELECT TABLE_NAME,to_char(LAST_ANALYZED, ‘DD-MON-YYYY HH24:MI:SS’)LAST_STATISTICS_DATE FROM DBA_TABLES WHERE TABLE_NAME=upper(‘${TABLE_NAME}’);
EOF
break
fi
fi
done

echo
echo “GATHER HISTOGRAMS ALONG WITH STATISTICS? [Y|N] [N]”
echo “=======================================”
while read ANS1
do
case $ANS1 in
y|Y|yes|YES|Yes) HISTO=”FOR ALL COLUMNS SIZE SKEWONLY”; HISTOMSG=”(+HISTOGRAMS)”;break ;;
“”|n|N|no|NO|No) HISTO=”FOR ALL COLUMNS SIZE 1”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

echo
echo “GATHER STATISTICS ON ALL TABLE’s INDEXES? [Y|N] [Y]”
echo “=========================================”
while read ANS2
do
case $ANS2 in
“”|y|Y|yes|YES|Yes) CASCD=”TRUE”;CASCMSG=”AND ITS INDEXES”; break ;;
n|N|no|NO|No) CASCD=”FALSE”; break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

# Execution of SQL Statement:
# ##########################

VAL1=$(${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper(‘${STATS_OWNER}’) AND TABLE_NAME=upper(‘${STATS_TABLE}’);
EOF
)
VAL2=`echo $VAL1| awk ‘{print $NF}’`
if [ ${VAL2} -gt 0 ]
then
echo
echo “BACKUP STATS TABLE [${STATS_OWNER}.${STATS_TABLE}] IS ALREADY EXISTS.”
else
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
SET LINESIZE 157
SET PAGESIZE 5000
SET HEADING OFF
SET VERIFY OFF
PROMPT CREATING BACKUP STATS TABLE …
BEGIN
dbms_stats.create_stat_table (
ownname => upper(‘${STATS_OWNER}’),
tblspace => upper(‘${STATS_TBS}’),
stattab => upper(‘${STATS_TABLE}’));
END;
/
PROMPT
EOF
fi

echo “SET LINESIZE 157” > ${GATHERSTATSSCRIPT}
echo “SET PAGESIZE 5000” >>${GATHERSTATSSCRIPT}
echo “SET HEADING OFF” >>${GATHERSTATSSCRIPT}
echo “spool ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “PROMPT BACKING UP CURRENT STATISTICS OF TABLE [${SCHEMA_NAME}.${TABLE_NAME}] …” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.EXPORT_TABLE_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “tabname => upper(‘${TABLE_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “statown => upper(‘${STATS_OWNER}’),” >>${GATHERSTATSSCRIPT}
echo “stattab => upper(‘${STATS_TABLE}’));” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT GATHERING STATISTICS ${HISTOMSG} FOR TABLE [${SCHEMA_NAME}.${TABLE_NAME}] ${CASCMSG} …” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT Feel free to exist this session any time as this script is running in the background :-)” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT To check the progress:” >>${GATHERSTATSSCRIPT}
echo “PROMPT tail -f ${GATHERSTATSSPOOL}” >>${GATHERSTATSSCRIPT}
echo “BEGIN” >>${GATHERSTATSSCRIPT}
echo “DBMS_STATS.GATHER_TABLE_STATS (” >>${GATHERSTATSSCRIPT}
echo “ownname => upper(‘${SCHEMA_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “tabname => upper(‘${TABLE_NAME}’),” >>${GATHERSTATSSCRIPT}
echo “cascade => ${CASCD},” >>${GATHERSTATSSCRIPT}
echo “METHOD_OPT => ‘${HISTO}’,” >>${GATHERSTATSSCRIPT}
echo “DEGREE => DBMS_STATS.AUTO_DEGREE,” >>${GATHERSTATSSCRIPT}
echo “estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);” >>${GATHERSTATSSCRIPT}
echo “END;” >>${GATHERSTATSSCRIPT}
echo “/” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT => IN CASE THE NEW STATISTICS ARE PERFORMING BAD, RESTORE BACK THE ORIGINAL STATISTICS USING THE FOLLOWING SQL COMMAND:” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT EXEC DBMS_STATS.IMPORT_TABLE_STATS (ownname => upper(‘${SCHEMA_NAME}’), tabname => upper(‘${TABLE_NAME}’), statown => upper(‘${STATS_OWNER}’), stattab => upper(‘${STATS_TABLE}’));;” >>${GATHERSTATSSCRIPT}
echo “PROMPT >>>>” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “PROMPT SCRIPT COMPLETED!” >>${GATHERSTATSSCRIPT}
echo “PROMPT” >>${GATHERSTATSSCRIPT}
echo “spool off” >>${GATHERSTATSSCRIPT}
echo “exit” >>${GATHERSTATSSCRIPT}

echo “#!/bin/bash” > ${GATHERSTATSSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/sqlplus \”/ as sysdba\” @${GATHERSTATSSCRIPT} | tee ${GATHERSTATSSPOOL} 2>&1 &” >>${GATHERSTATSSCRIPTRUNNER}
chmod 740 ${GATHERSTATSSCRIPTRUNNER}
echo “”
source ${GATHERSTATSSCRIPTRUNNER}

# #############
# END OF SCRIPT
# #############

To check invalid objects

#################################################
# Script to check invalid objects
#################################################

#############
# Description:
#############
echo
echo “=====================================================”
echo “This script Checks ALL INVALID OBJECTS on a database.”
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” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# 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 $DB_ID
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}|grep -v “\-MGMTDB”|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

####################
# VARIABLES:
####################
LOC1=${USR_ORA_HOME}
cd ${LOC1}

#################################
# SQLPLUS: Check Invalid Objects
#################################
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ <<EOF
set pages 0
SET LINESIZE 190
SET PAGESIZE 5000
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
spool FIX_INVALID_OBJ.sql
select ‘alter package ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%PACKAGE%’ union
select ‘alter type ‘||owner||’.’||object_name||’ compile specification;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%TYPE%’union
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type not in (‘PACKAGE’,’PACKAGE BODY’,’SYNONYM’,’TYPE’,’TYPE BODY’) union
select ‘alter public synonym ‘||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type =’SYNONYM’;
spool off
select ‘Invalid Objects#: ‘||count(distinct (owner||object_name)) a from dba_objects where status <> ‘VALID’;
EOF

# Check if the fix script is exist:
if [ -f ${LOC1}/FIX_INVALID_OBJ.sql ]
then
echo;echo “Do you want to FIX THOSE INVALID Objects? [Y|N] [Y]”
echo “========================================”
while read ANS
do
case $ANS in
“”|y|Y|yes|YES|Yes) echo;echo “START COMPILING INVALID OBJECTS …”;sleep 1
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” <<EOF
set heading off
@FIX_INVALID_OBJ.sql
@FIX_INVALID_OBJ.sql
PROMPT
PROMPT List of remaining INVALID Objects …
PROMPT ———————————

PROMPT
select ‘alter PACKAGE ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%PACKAGE%’ union
select ‘alter TYPE ‘||owner||’.’||object_name||’ compile specification;’ from dba_objects where status <> ‘VALID’ and object_type like ‘%TYPE%’union
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type not in (‘PACKAGE’,’PACKAGE BODY’,’SYNONYM’,’TYPE’,’TYPE BODY’) union
select ‘alter public synonym ‘||object_name||’ compile;’ from dba_objects where status <> ‘VALID’ and object_type =’SYNONYM’;
select ‘Invalid Objects#: ‘||count(distinct (owner||object_name)) a from dba_objects where status <> ‘VALID’;
EOF
break ;;
n|N|no|NO|No) echo;echo “Later To FIX the invalid objects:”
echo “run script: ${LOC1}/FIX_INVALID_OBJ.sql”
echo “OR run: @?/rdbms/admin/utlrp.sql”
echo;exit;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done
fi
# #############
# END OF SCRIPT
# #############