Saturday, May 9, 2015

Oracle Database Health Check Script


Do you want to receive an email report summarize the health status of your all databases running on the same server?
dbdailychk.sh script will do the job for you, it performs the following health checks each time it runs against all up & running instances on the server and sends you an E-mail report with the results for each database:

 # CHECKING ALL DATABASES ALERTLOGS FOR ERRORS.
# CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS.
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING FLASH RECOVERY AREA UTILIZATION.
# CHECKING ASM DISKGROUPS UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHECKING AUDIT RECORDS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
# CHECKING ACTIVE INCIDENTS.
# CHECKING OUTSTANDING ALERTS.
# CHECKING DATABASE SIZE GROWTH.
# CHECKING OS / HARDWARE STATISTICS.
# CHECKING RESOURCE LIMITS.
# CHECKING RECYCLEBIN.
# CHECKING CURRENT RESTORE POINTS.
# CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE.
# CHECKING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INITIALIZATION PARAMETERS SINCE THE LAST DB STARTUP.
# CHECKING ADVISORS RECOMMENDATIONS:
#          - SQL TUNING ADVISOR
#          - SGA ADVISOR
#          - PGA ADVISOR
#          - BUFFER CACHE ADVISOR
#          - SHARED POOL ADVISOR
#          - SEGMENT ADVISOR
# RMAN BACKUP CHECK.
# REPORT Newly Created Objects [in the last 24h].
# REPORT Long Running Jobs.
# REPORT UNRECOVERABLE DATABASE FILES that don't have a valid backup.

 This script was tested on Linux environments.

 How to use the script?
==================

 Step 1: Download the script from this link:
-------
https://www.dropbox.com/s/w1dpw3iynphm07t/dbdailychk.sh?dl=0

 Step 2: Customize the script to your environment:
-------
Change the E-mail address to your email address in line number 80
MAIL_LIST="youremail@yourcompany.com"

 Note: sendmail service should be well configured on the target machine in order to send emails from.

 Customize the defined thresholds under THRESHOLDS section, as per your preferences:
Modify the numbers in red color:

 # #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:


HTMLENABLE=Y    # Enable HTML Email Format [DB]
FSTHRESHOLD=95    # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95    # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95    # THRESHOLD FOR TABLESPACE %USED [DB]
UNUSEINDXTHRESHOLD=1   # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES  [DB]
INVOBJECTTHRESHOLD=1     # THRESHOLD FOR NUMBER OF INVALID OBJECTS   [DB]
FAILLOGINTHRESHOLD=1      # THRESHOLD FOR NUMBER OF FAILED LOGINS   [DB]
AUDITRECOTHRESHOLD=1     # THRESHOLD FOR NUMBER OF AUDIT RECORDS       [DB]
CORUPTBLKTHRESHOLD=1    # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS[DB]
FAILDJOBSTHRESHOLD=1      # THRESHOLD FOR NUMBER OF FAILED JOBS   [DB]
JOBSRUNSINCENDAY=1          # THRESHOLD FOR JOBS RUNNING LONGER THAN N DAY  [DB]
NEWOBJCONTTHRESHOLD=1  # THRESHOLD FOR NUMBER OF NEWLY CREATED OBJECTS  [DB]
LONG_RUN_QUR_HOURS=1     # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS  [DB]
CLUSTER_CHECK=Y                   # CHECK CLUSTERWARE HEALTH   [OS]
CHKAUDITRECORDS=Y            # CHECK DATABASE AUDIT RECORDS [increases CPU Load]   [DB]
SHOWSQLTUNINGADVISOR=Y  # SHOW SQL TUNING ADVISOR RESULTS IN THE REPORT  [DB]
SHOWMEMORYADVISORS=Y    # SHOW MEMORY ADVISORS RESULTS IN THE REPORT   [DB]
SHOWSEGMENTADVVISOR=Y   # SHOW SEGMENT ADVISOR RESULTS IN THE REPORT   [DB]
SHOWJOBS=Y                          # SHOW DB JOBS DETAILS IN THE REPORT     [DB]
SHOWHASHEDCRED=N        # SHOW DB USERS HASHED VERSION CREDENTIALS IN THE REPORT  [DB]
REPORTUNRECOVERABLE=Y   # REPORT UNRECOVERABLE DATAFILES. [DB]

Step 3: Schedule the script to run in the crontab: [Recommended to run once a day during non-peak hours]
--------
[By Oracle user]
# crontab -e
#Add the following line to schedule the execution of dbdailychk.sh script. let's say every morning:
0 6 * * * /home/oracle/dbdailychk.sh

 Note: /home/oracle/dbdailychk.sh is the full path pointing to dbdailychk.sh script.

 In case you want to run the script from root user crontab instead of oracle's crontab:
[By root user]
# crontab -e
#Add this line to schedule the execution of dbdailychk.sh script every morning:
0 6 * * * su - oracle -c /home/oracle/dbdailychk.sh

 Now, everyday morning you will receive an email summarize the health status of ALL databases running on the that server, also you will receive a separate e-mail if any of defined thresholds been hit.

By default the script will send the report in HTML format if it founds "sendmail" package installed on your system otherwise it will revert to TEXT format, If you don't have "sendmail" installed on your system and you still enthusiastic to receive the E-mail report in HTML format then follow this link:
http://dba-tips.blogspot.com/2017/11/oracle-database-health-check-report-in.html

 In case you are looking for a script for real-time databases & listeners monitoring plus  CPU & filesystem monitoring plus monitoring other components, you may have a look at this link:
http://dba-tips.blogspot.com/2014/02/database-monitoring-script-for-ora-and.html

 Feel free to download the whole DBA bundle which includes this script along with other database administration scripts that can help you in your day to day tasks:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html 

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

 Your suggestions are most welcome.

 In case the download link is not working, please find the full code below:
# ##################################################################################################################################
# DATABASE DAILY HEALTH CHECK MONITORING SCRIPT
VER="[6.2]"
# ===================================================================================================
# CAUTION: THIS SCRIPT MAY CAUSE A SLIGHT OVEARHEAD, DO NOT RUN IT TOO FREQUENT, ONCE A DAY IS IDEAL.
# ===================================================================================================
# ***********
# How To Use:
# ***********
# 1- Set your Email by modifying this parameter below: EMAIL="youremail@yourcompany.com"
# 2- Check the THRESHOLDS section and make sure they meet your policy.
# 3- You can DISABLE/ENABLE some section by altering the parameter that accept Y or N values
# e.g. Disable Memory Check Section in the report:
# Go to THRESHOLDS section and set:
# CHECK_MEMORY=N
# ****************************************************************************************************
# FEATURES:
# ********
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING FLASH RECOVERY AREA UTILIZATION.
# CHECKING ASM DISKGROUPS UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
# CHECKING LONG RUNNING JOBS [For More than 1 Day].
# CHECKING ACTIVE INCIDENTS.
# CHECKING OUTSTANDING ALERTS.
# CHECKING DATABASE SIZE GROWTH.
# CHECKING RMAN BACKUPs.
# REPORT UNRECOVERABLE DB FILES.
# CHECKING OS / HARDWARE STATISTICS.
# CHECKING RESOURCE LIMITS.
# CHECKING RECYCLEBIN.
# CHECKING CURRENT RESTORE POINTS.
# CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE.
# CHEKCING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INTIALIZATION PARAMETERS SINCE THE LAST DB STARTUP.
# CHECKING ADVISORS RECOMMENDATIONS:
# - SQL TUNING ADVISOR
# - SGA ADVISOR
# - PGA ADVISOR
# - BUFFER CACHE ADVISOR
# - SHARED POOL ADVISOR
# - SEGMENT ADVISOR
# CHECKING NEW CREATED OBJECTS.
# CHECKING AUDIT RECORDS.
# CHECKING FAILED LOGIN ATTEMPTS.
#
# # # #
# Author: Mahmmoud ADEL # # # # ###
# # # # # #
#
# Created: 22-12-13 Based on dbalarm.sh script.
# Modifications:18-05-14 Added Filsystem monitoring.
# 19-05-14 Added CPU monitoring.
# 09-12-14 Added Tablespaces monitoring
# Added BLOCKING SESSIONS monitoring
# Added UNUSABLE INDEXES monitoring
# Added INVALID OBJECTS monitoring
# Added FAILED LOGINS monitoring
# Added AUDIT RECORDS monitoring
# Added CORRUPTED BLOCKS monitoring
# [It will NOT run a SCAN. It will look at V$DATABASE_BLOCK_CORRUPTION]
# Added FAILED JOBS monitoring.
# 06-10-15 Replaced mpstat with iostat for CPU Utilization Check
# 02-11-15 Enhanced "FAILED JOBS monitoring" part.
# 13-12-15 Added Advisors Recommendations to the report
# 04-04-16 dba_tablespace_usage_metrics view will be used for 11g onwards versions
# for checking tablespaces size, advised by: Satyajit Mohapatra
# 10-04-16 Add Flash Recovery Area monitoring
# 10-04-16 Add ASM Disk Groups monitoring
# 15-07-16 Add ACTIVE INCIDENTS, RESOURCE LIMITS, RECYCLEBIN, RESTORE POINTS,
# MONITORED INDEXES, REDOLOG SWITCHES, MODIFIED SPFILE PARAMETERS checks.
# 02-01-17 Removed ALERTLOG check for DB & Listener +
# Merged alerts with advisors. [Recommended by: ABD-ELGAWAD]
# 03-01-17 Added checking RAC status feature. [Recommended by: Samer ALJazzar]
# 09-01-17 Added RMAN BACKUP CHECK.
# 04-05-17 Added Reporting of Newly Created Objects in the last 24Hours.
# 12-06-17 Added Long Running Jobs Alert.
# 20-07-17 Neutralize login.sql if found under Oracle user home directory due to bugs.
# 10-10-17 Added reporting Long Running Queries to the report.
# 09-01-18 Workaround for df command bug "`/root/.gvfs': Permission denied"
# 16-05-18 Added SHOWSQLTUNINGADVISOR, SHOWMEMORYADVISORS, SHOWSEGMENTADVVISOR, SHOWJOBS
# and SHOWHASHEDCRED parameters to allow the user to decide whether to show their
# results in the report or not.
# 21-06-18 Added MODOBJCONTTHRESHOLD to control the display of LAST MODIFIED OBJECTS in the report.
# 15-08-18 Added REPORT UNRECOVERABLE DATABASE FILES that don't have a valid backup.
# 10-02-19 Removed the failed jobs alerting from the script.
# 19-02-19 Added HTML email format content.
# 04-04-19 Added Reporting of Top Fragmented Tables.
# 16-06-20 Excluding goldengate modules from Long Running Queries reporting.
# 22-06-20 Modified the check for cluster services to get compatible with 12c+.
# 23-06-20 Listed: Running Instances/listeners, open ports in the report.
# 14-09-20 Added Memory utilization check feature, combining both RAM and SWAP in the calculation.
# 14-09-20 Reorganize script sections to make it easy to edit by users.
# 23-11-20 Enhance df command output in HTML mode
# 04-04-21 Listing Dormant accounts in the report.
# 04-04-21 Auditing data will be included in the report by default. [If AUD$ is big this section will take much time]
# 20-09-21 Show yesterday's hardware (CPU & Memory) stats if crossed the thresholds.
#
#
#
# ##################################################################################################################################
# HERE YOU SET YOUR EMAIL:
EMAIL="youremail@yourcompany.com"
# HERE YOU SET THE SMTP SERVER IF EXIST. UNHASH THE BELOW LINE AND REPLACE "mailrelay.mycompany.com" WITH YOUR COMPANY's SMTP:
#export smtp="mailrelay.mycompany.com:25" # This is an example, your Network Admin knows the SMTP NAME & PORT to use.
# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:
HTMLENABLE=Y # Enable HTML Email Format [DB]
FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
CHECK_MEMORY=Y # REPORT LOW MEMORY USING BELOW TWO DEFINED THRESHOLDS COMBINED [OS]
USEDPHYSICALMEM_PCT=95 # THRESHOLD FOR RAM %UTILIZATION [OS]
USEDSWAPMEM_PCT=75 # THRESHOLD FOR SWAP SPACE %UTILIZATION [OS]
CHECK_CLUSTER=Y # CHECK CLUSTERWARE HEALTH [CL]
CHKAUDITRECORDS=Y # INCLUDE AUDIT RECORDS IN THE REPORT [increases CPU Load] [DB]
SHOWDORMANTACCOUNTS=Y # SHOW INACTIVE ACCOUNTS IN THE REPORT [DB]
DORMANT_INACTIVE_DAYS=45 # SHOW Inactive Accounts with NO login attempts since 45 days.[DB]
SHOWSQLTUNINGADVISOR=Y # INCLUDE SQL TUNING ADVISOR RESULTS IN THE REPORT [DB]
SHOWMEMORYADVISORS=Y # INCLUDE MEMORY ADVISORS RESULTS IN THE REPORT [DB]
SHOWSEGMENTADVVISOR=N # INCLUDE SEGMENT ADVISOR RESULTS IN THE REPORT [DB]
SHOWJOBS=Y # INCLUDE DB JOBS DETAILS IN THE REPORT [DB]
SHOWHASHEDCRED=N # INCLUDE DB USERS HASHED VERSION CREDENTIALS IN THE REPORT [DB]
REPORTUNRECOVERABLE=Y # INCLUDE UNRECOVERABLE DATAFILES IN THE REPORT. [DB]
TBSTHRESHOLD=95 # THRESHOLD FOR TABLESPACE %USED [DB]
FRATHRESHOLD=95 # THRESHOLD FOR FLASH RECOVERY AREA %USED [DB]
ASMTHRESHOLD=95 # THRESHOLD FOR ASM DISK GROUPS [DB]
UNUSEINDXTHRESHOLD=1 # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES [DB]
INVOBJECTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF INVALID OBJECTS [DB]
FAILLOGINTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED LOGINS [DB]
AUDITRECOTHRESHOLD=1 # THRESHOLD FOR NUMBER OF AUDIT RECORDS [DB]
CORUPTBLKTHRESHOLD=1 # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS [DB]
FAILDJOBSTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED JOBS [DB]
JOBSRUNSINCENDAY=1 # THRESHOLD FOR JOBS RUNNING LONGER THAN N DAY [DB]
NEWOBJCONTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF NEWLY CREATED OBJECTS [DB]
MODOBJCONTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF MODIFIED OBJECTS [DB]
LONG_RUN_QUR_HOURS=1 # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS [DB]
SHOW_YESTERDAY_STATS=Y # CHECKER FOR SHOWING HARDWARE RESOURCES THAT CROSSED THRESHOLDS[OS]
# #######################################
# Excluded Modules:
# #######################################
# Here you can exclude the modules you don't want their queries to get reported in the Long Running Queries list:
# goldegate modules are excluded below, to add extra modules, make sure to enclose them between single quote and separating them by a comma:
EXCLUDED_MODULES="'OGG-USE_OCI_THREAD','OGG-OCI_META_THREAD'"
# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:
EXL_DB="\-MGMTDB|ASM|APX" #Excluding INSTANCES [Will get excluded from the report].
# #########################
# Excluded ERRORS:
# #########################
# Here you can exclude the errors that you don't want to be alerted when they appear in the logs:
# Use pipe "|" between each error.
EXL_ALERT_ERR="ORA-2396|TNS-00507|TNS-12502|TNS-12560|TNS-12537|TNS-00505" #Excluded ALERTLOG ERRORS [Will not get reported].
EXL_LSNR_ERR="TNS-00507|TNS-12502|TNS-12560|TNS-12537|TNS-00505" #Excluded LISTENER ERRORS [Will not get reported].
# ################################
# Excluded FILESYSTEM/MOUNT POINTS:
# ################################
# Here you can exclude specific filesystems/mount points from being reported by the script:
# e.g. Excluding: /dev/mapper, /dev/asm mount points:
EXL_FS="\/dev\/mapper\/|\/dev\/asm\/" #Excluded mount points [Will be skipped during the check].
# Workaround df command output bug "`/root/.gvfs': Permission denied"
if [ -f /etc/redhat-release ]
then
export DF='df -hTPx fuse.gvfs-fuse-daemon'
else
export DF='df -hT'
fi
export SRV_NAME="`uname -n`"
case ${EMAIL} in "youremail@yourcompany.com")
echo
echo "##############################################################################################"
echo "You Missed Something! :-)"
echo "In order to receive the HEALTH CHECK report via Email, set your E-mail at line# 110"
echo "by replacing this template [youremail@yourcompany.com] with YOUR E-mail address."
echo "DB HEALTH CHECK report will be saved on disk..."
echo "##############################################################################################"
export SQLLINESIZE=165
echo;;
*)
export SQLLINESIZE=200
export OSLINESIZE=300
;;
esac
SCRIPT_NAME="dbdailychk${VER}"
# In case your company Emails go through a specific SMTP server. Specify it in the below line and UN-HASH it:
#export smtp="mailrelay.mycompany.com:25" #This is an example, you have to check with your Network Admin for the SMTP NAME/PORT to use.
export MAIL_LIST="${EMAIL}"
#export MAIL_LIST="-r ${SRV_NAME} ${EMAIL}"
echo
echo "[dbdailychk Script Started ...]"
# #########################
# Checking The FILESYSTEM:
# #########################
# Report Partitions that reach the threshold of Used Space:
FSLOG=/tmp/filesystem_DBA_BUNDLE.log
echo "[Reported By ${SCRIPT_NAME} Script" > ${FSLOG}
echo "" >> ${FSLOG}
${DF} >> ${FSLOG}
${DF} | grep -v "^Filesystem" |awk '{print substr($0, index($0, $2))}'| egrep -v "${EXL_FS}"|awk '{print $(NF-1)" "$NF}'| while read OUTPUT
do
PRCUSED=`echo ${OUTPUT}|awk '{print $1}'|cut -d'%' -f1`
FILESYS=`echo ${OUTPUT}|awk '{print $2}'`
if [ ${PRCUSED} -ge ${FSTHRESHOLD} ]
then
mail -s "ALARM: Filesystem [${FILESYS}] on Server [${SRV_NAME}] has reached ${PRCUSED}% of USED space" ${MAIL_LIST} < ${FSLOG}
fi
done
rm -f ${FSLOG}
# #########################
# Getting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
if [ $INS_COUNT -eq 0 ]
then
echo "[Reported By ${SCRIPT_NAME} Script]" > /tmp/oracle_processes_DBA_BUNDLE.log
echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
echo "Current running INSTANCES on server [${SRV_NAME}]:" >> /tmp/oracle_processes_DBA_BUNDLE.log
echo "***************************************************" >> /tmp/oracle_processes_DBA_BUNDLE.log
ps -ef|grep -v grep|grep pmon >> /tmp/oracle_processes_DBA_BUNDLE.log
echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
echo "Current running LISTENERS on server [${SRV_NAME}]:" >> /tmp/oracle_processes_DBA_BUNDLE.log
echo "***************************************************" >> /tmp/oracle_processes_DBA_BUNDLE.log
ps -ef|grep -v grep|grep tnslsnr >> /tmp/oracle_processes_DBA_BUNDLE.log
mail -s "ALARM: No Databases Are Running on Server ${SRV_NAME} !!!" ${MAIL_LIST} < /tmp/oracle_processes_DBA_BUNDLE.log
rm -f /tmp/oracle_processes_DBA_BUNDLE.log
exit
fi
# #########################
# 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}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
export PGREP=`which pgrep`
export PWDX=`which pwdx`
if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
then
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
fi
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
fi
# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`dbhome "${ORACLE_SID}"`
export ORACLE_HOME
fi
# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
fi
# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
fi
# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
if [ -x /usr/bin/locate ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
fi
fi
# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
echo "e.g."
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
mail -s "dbdailychk script on Server [${SRV_NAME}] failed to locate ORACLE_HOME for SID [${ORACLE_SID}], Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory" ${MAIL_LIST} < /dev/null
exit
fi
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
# #############################
# Getting hostname in lowercase:
# #############################
HOSTNAMELOWER=$( echo "`hostname --short`"| tr '[A-Z]' '[a-z]' )
export HOSTNAMELOWER
# ########################
# Getting GRID_HOME:
# ########################
CHECK_OCSSD=`ps -ef|grep 'ocssd.bin'|grep -v grep|wc -l`
CHECK_CRSD=`ps -ef|grep 'crsd.bin'|grep -v grep|wc -l`
if [ ${CHECK_OCSSD} -gt 0 ]
then
GRID_HOME=`ps -ef|grep 'ocssd.bin'|grep -v grep|awk '{print $NF}'|sed -e 's/\/bin\/ocssd.bin//g'|grep -v sed|grep -v "//g"|tail -1`
export GRID_HOME
if [ ! -d ${GRID_HOME} ]
then
ASM_INSTANCE_NAME=`ps -ef|grep pmon|grep -v grep|grep asm_pmon_|awk '{print $NF}'|sed -e 's/asm_pmon_//g'|grep -v sed|grep -v "s///g"|tail -1`
GRID_HOME=`dbhome ${ASM_INSTANCE_NAME}`
export GRID_HOME
fi
# ########################
# Getting GRID_BASE:
# ########################
# Locating GRID_BASE:
GRID_BASE=`cat ${GRID_HOME}/crs/install/crsconfig_params|grep ^ORACLE_BASE|tail -1|awk '{print $NF}'|sed -e 's/ORACLE_BASE=//g'`
export GRID_BASE
if [ ! -d ${GRID_BASE} ]
then
GRID_BASE=`cat ${GRID_HOME}/crs/utl/appvipcfg|grep ^ORACLE_BASE|tail -1|awk '{print $NF}'|sed -e 's/ORACLE_BASE=//g'`
export GRID_BASE
fi
if [ ! -d ${GRID_BASE} ]
then
GRID_BASE=`cat ${GRID_HOME}/install/envVars.properties|grep ^ORACLE_BASE|tail -1|awk '{print $NF}'|sed -e 's/ORACLE_BASE=//g'`
export GRID_BASE
fi
fi
# #########################
# Variables:
# #########################
export PATH=${PATH}:${ORACLE_HOME}/bin
export LOG_DIR=${USR_ORA_HOME}/BUNDLE_Logs
mkdir -p ${LOG_DIR}
chown -R ${ORA_USER} ${LOG_DIR}
chmod -R go-rwx ${LOG_DIR}
if [ ! -d ${LOG_DIR} ]
then
mkdir -p /tmp/BUNDLE_Logs
export LOG_DIR=/tmp/BUNDLE_Logs
chown -R ${ORA_USER} ${LOG_DIR}
chmod -R go-rwx ${LOG_DIR}
fi
cat /dev/null > ${LOG_DIR}/dbdailychk.part.log
export LOGFILE=${LOG_DIR}/dbdailychk.part.log
# #########################
# HTML Preparation:
# #########################
case ${HTMLENABLE} in
y|Y|yes|YES|Yes|ON|On|on)
if [ -x /usr/sbin/sendmail ]
then
export SENDMAIL="/usr/sbin/sendmail -t"
export MAILEXEC="echo #"
export HASHHTML=""
export HASHNONHTML="--"
export HASHHTMLOS=""
export HASHNOHTMLOS="echo #"
SENDMAILARGS=$(
echo "To: ${EMAIL};"
echo "Subject: ${MSGSUBJECT} ;"
echo "Content-Type: text/html;"
echo "MIME-Version: 1.0;"
cat ${LOGFILE}
)
export SENDMAILARGS
else
export SENDMAIL="echo #"
export MAILEXEC="mail -s"
export HASHHTML="--"
export HASHNONHTML=""
export HASHHTMLOS="echo #"
export HASHNOHTMLOS=""
fi
;;
*)
export SENDMAIL="echo #"
export HASHHTML="--"
export HASHNONHTML=""
export HASHHTMLOS="echo #"
export HASHNOHTMLOS=""
export MAILEXEC="mail -s"
;;
esac
export LOGFILE=${LOG_DIR}/dbdailychk.part.log
export SRV_NAME="`uname -n`"
# ##########################
# Neutralize login.sql file: [Bug Fix]
# ##########################
# 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
#echo "login.sql file found and will be neutralized."
mv ${USR_ORA_HOME}/login.sql ${USR_ORA_HOME}/login.sql_NeutralizedBy${SCRIPT_NAME}
fi
# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user's profile if it EMPTY:
if [ ! -d "${ORACLE_BASE}" ]
then
ORACLE_BASE=`cat ${ORACLE_HOME}/install/envVars.properties|grep ^ORACLE_BASE|tail -1|awk '{print $NF}'|sed -e 's/ORACLE_BASE=//g'`
export ORACLE_BASE
fi
if [ ! -d "${ORACLE_BASE}" ]
then
ORACLE_BASE=`grep -h 'ORACLE_BASE=\/' ${USR_ORA_HOME}/.bash* ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_BASE
fi
# #########################
# Getting DB_NAME:
# #########################
DB_NAME_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
SELECT name from v\$database;
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo ${DB_NAME_RAW}| 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
export DB_NAME=${DB_NAME_UPPER}
# #########################
# Getting DB_UNQ_NAME:
# #########################
VAL121=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name='db_unique_name';
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_UNQ_NAME=`echo ${VAL121}| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
export DB_UNQ_NAME
# In case DB_UNQ_NAME variable is empty then use DB_NAME instead:
case ${DB_UNQ_NAME} in
'') DB_UNQ_NAME=${DB_NAME}; export DB_UNQ_NAME;;
esac
if [ -d ${ORACLE_BASE}/diag/rdbms/${DB_NAME_UPPER} ]
then
DB_NAME=${DB_NAME_UPPER}
fi
if [ -d ${ORACLE_BASE}/diag/rdbms/${DB_NAME_LOWER} ]
then
DB_NAME=${DB_NAME_LOWER}
fi
if [ -d ${ORACLE_BASE}/diag/rdbms/${DB_UNQ_NAME} ]
then
DB_NAME=${DB_UNQ_NAME}
fi
export DB_NAME
# ###################
# Checking 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:
# #####################
VAL302=$(${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 ${VAL302}|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 lines 1000;
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)
export DB_ROLE_ID=0
;;
*)
export DB_ROLE_ID=1
# Disable the reporting of BLOCKED Sessions if the DB Role is not a PRIMARY:
export BLOCKTHRESHOLD=100000
;;
esac
export SRV_NAME="`uname -n`"
# ############################################
# Checking LONG RUNNING DB JOBS:
# ############################################
VAL410=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
--SELECT count(*) from dba_scheduler_running_jobs where extract(day FROM elapsed_time) > ${JOBSRUNSINCENDAY} and SESSION_ID is not null;
SELECT count(*) from dba_scheduler_running_jobs where extract(day FROM elapsed_time) > ${JOBSRUNSINCENDAY};
exit;
EOF
)
VAL510=`echo ${VAL410} | awk '{print $NF}'`
if [ ${VAL510} -ge 1 ]
then
VAL610=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize ${SQLLINESIZE} pages 1000
-- Enable HTML color format:
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
spool ${LOGFILE}
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Long Running Jobs [${ORACLE_SID}]
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT Long Running Jobs: [${ORACLE_SID}]
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
col INS for 999
col "JOB_NAME|OWNER|SPID|SID" for a55
col ELAPSED_TIME for a17
col CPU_USED for a17
col "WAIT_SEC" for 9999999999
col WAIT_CLASS for a15
col "BLKD_BY" for 9999999
col "WAITED|WCLASS|EVENT" for a45
select j.RUNNING_INSTANCE INS,j.JOB_NAME ||' | '|| j.OWNER||' |'||SLAVE_OS_PROCESS_ID||'|'||j.SESSION_ID"JOB_NAME|OWNER|SPID|SID"
,s.FINAL_BLOCKING_SESSION "BLKD_BY",ELAPSED_TIME,CPU_USED
,substr(s.SECONDS_IN_WAIT||'|'||s.WAIT_CLASS||'|'||s.EVENT,1,45) "WAITED|WCLASS|EVENT",S.SQL_ID
from dba_scheduler_running_jobs j, gv\$session s
where j.RUNNING_INSTANCE=S.INST_ID(+)
and j.SESSION_ID=S.SID(+)
and extract(day FROM elapsed_time) > ${JOBSRUNSINCENDAY}
order by "JOB_NAME|OWNER|SPID|SID",ELAPSED_TIME;
spool off
exit;
EOF
)
#mail -s "WARNING: JOBS running for more than ${JOBSRUNSINCENDAY} day detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" ${MAIL_LIST} < ${LOG_DIR}/long_running_jobs.log
export MSGSUBJECT="WARNING: JOBS running for more than ${JOBSRUNSINCENDAY} day detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]"
#SENDMAILARGS=$(
#echo "To: ${EMAIL};"
#echo "Subject: ${MSGSUBJECT} ;"
#echo "Content-Type: text/html;"
#echo "MIME-Version: 1.0;"
#cat ${LOGFILE}
#)
${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${LOGFILE}
#echo ${SENDMAILARGS} | tr \; '\n' |awk 'length == 1 || NR == 1 {print $0} length && NR > 1 { print substr($0,2) }'| ${SENDMAIL}
(
echo "To: ${EMAIL};"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html;"
echo "Subject: ${MSGSUBJECT}"
cat ${LOGFILE}
) | ${SENDMAIL}
cat /dev/null > ${LOGFILE}
fi
# ############################################
# LOGFILE SETTINGS:
# ############################################
# Logfile path variable:
DB_HEALTHCHK_RPT=${LOG_DIR}/${DB_NAME_UPPER}_HEALTH_CHECK_REPORT.log
OS_HEALTHCHK_RPT=${LOG_DIR}/OS_HEALTH_CHECK_REPORT.log
export DB_HEALTHCHK_RPT
# Flush the logfile:
export LOGDATE=`date +%d-%b-%y`
echo "HEALTH_CHECK_REPORT_FOR_DATABASE_[${DB_NAME_UPPER}]_ON_{${LOGDATE}}" > ${DB_HEALTHCHK_RPT}
cat /dev/null > ${OS_HEALTHCHK_RPT}
# ###########################
# Checking Memory Utilization:
# ###########################
case ${CHECK_MEMORY} in
y|Y|yes|YES|Yes|ON|On|on)
# Check the Linux OS version:
export PATH=${PATH}:/usr/local/bin
FILE_NAME=/etc/redhat-release
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
LNXVER=`cat /etc/redhat-release | grep -o '[0-9]'|head -1`
export LNXVER
# IF LINUX VERSION IS 6 OR LOWER:
if [ ${LNXVER} -le 6 ]
then
MEMTOTAL=`cat /proc/meminfo|grep MemTotal|awk '{print $(NF-1)}'`
MEMUSEDRAW=`free -k|grep 'Mem:'|awk '{print $(3)}'`
MEMCACHED=`cat /proc/meminfo|grep '^Cached:'|awk '{print $(NF-1)}'`
MEMFREE=$(awk "BEGIN {print ${MEMTOTAL} - ${MEMUSEDRAW} + ${MEMCACHED}}")
MEMUSED=$(awk "BEGIN {print ${MEMTOTAL} - ${MEMFREE}}")
MEMUSEDPCTFLOAT=$(awk "BEGIN {print ${MEMUSED} * 100 / ${MEMTOTAL}}")
MEMUSEDPCT=${MEMUSEDPCTFLOAT%.*}
SWAPTOTAL=`cat /proc/meminfo|grep SwapTotal|awk '{print $(NF-1)}'`
SWAPFREE=`cat /proc/meminfo|grep SwapFree|awk '{print $(NF-1)}'`
SWAPUSED=$(awk "BEGIN {print ${SWAPTOTAL} - ${SWAPFREE}}")
SWAPUSEDPCTFLOAT=$(awk "BEGIN {print ${SWAPUSED} * 100 / ${SWAPTOTAL}}")
SWAPUSEDPCT=${SWAPUSEDPCTFLOAT%.*}
fi
# IF LINUX VERSION IS 7 OR HIGHER:
if [ ${LNXVER} -ge 7 ]
then
MEMTOTAL=`cat /proc/meminfo|grep MemTotal|awk '{print $(NF-1)}'`
MEMUSEDRAW=`free -k|grep 'Mem:'|awk '{print $(3)}'`
MEMCACHED=`cat /proc/meminfo|grep '^Cached:'|awk '{print $(NF-1)}'`
MEMFREE=`cat /proc/meminfo|grep MemAvailable|awk '{print $(NF-1)}'`
MEMUSED=$(awk "BEGIN {print ${MEMTOTAL} - ${MEMFREE}}")
MEMUSEDPCTFLOAT=$(awk "BEGIN {print ${MEMUSED} * 100 / ${MEMTOTAL}}")
MEMUSEDPCT=${MEMUSEDPCTFLOAT%.*}
SWAPTOTAL=`cat /proc/meminfo|grep SwapTotal|awk '{print $(NF-1)}'`
SWAPFREE=`cat /proc/meminfo|grep SwapFree|awk '{print $(NF-1)}'`
SWAPUSED=$(awk "BEGIN {print ${SWAPTOTAL} - ${SWAPFREE}}")
SWAPUSEDPCTFLOAT=$(awk "BEGIN {print ${SWAPUSED} * 100 / ${SWAPTOTAL}}")
SWAPUSEDPCT=${SWAPUSEDPCTFLOAT%.*}
fi
# Show MEMORY ALERT if both MEMUSEDPCT and SWAPUSEDPCT are below their defined thresholds:
if [ ${MEMUSEDPCT} -ge ${USEDPHYSICALMEM_PCT} ] && [ ${SWAPUSEDPCT} -ge ${USEDSWAPMEM_PCT} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "MEMORY_WARNING: " >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "Looks_The_Server_Is_Running_Out_of_Memory!." >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
echo "%USED_PHYSICAL_RAM:${MEMUSEDPCT}%" >> ${OS_HEALTHCHK_RPT}
echo "%USED_SWAP_SPACE:${SWAPUSEDPCT}%" >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
echo "Memory_Details:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
free -g >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
mail -s "ALARM: OUT OF MEMORY Scenario is Imminent on Server [${SRV_NAME}]" ${MAIL_LIST} < ${OS_HEALTHCHK_RPT}
fi
fi
;;
esac
# Show yesterday's hardware stats if crossed threshold:
case ${SHOW_YESTERDAY_STATS} in
Y|y|YES|Yes|yes|ON|On|on)
# Yesterday's High CPU Utilization:
# #################################
# If CPU IDLE is below 5% or CPU Utilization is higher than 95%:
HIGH_CPU_CNT=`sar -f /var/log/sa/sa$(date +%d -d yesterday) | sed -r '/^\s*$/d' | awk '{ if ( $NF < 5 ) print $NF,$1,$2 }'|wc -l`
if [ ${HIGH_CPU_CNT} -gt 1 ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "Yesterday's_High_CPU_Periods:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
sar -f /var/log/sa/sa$(date +%d -d yesterday) | sed -r '/^\s*$/d' | grep -v Linux | awk '{ if ( $NF < 5 ) print $NF,$1,$2 }' >> ${OS_HEALTHCHK_RPT}
fi
# Yesterday's High Memory Utilization:
# ####################################
# If Memory utilization cross 95%:
HIGH_MEM_CNT=`sar -r -f /var/log/sa/sa$(date +%d -d yesterday) | grep -Ev 'Average|Linux' | awk '{ if ( $5 > 95 ) print $5,$1,$2 }'|wc -l`
if [ ${HIGH_MEM_CNT} -ge 1 ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "Yesterday's_High_Memory_Periods:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
sar -r -f /var/log/sa/sa$(date +%d -d yesterday) | grep -Ev 'Average|Linux' | awk '{ if ( $5 > 95 ) print $5,$1,$2 }' >> ${OS_HEALTHCHK_RPT}
fi
;;
esac
# Filesystem Utilization:
# #######################
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "Local_Filesystem:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
${DF} >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
sed -i 's/Mounted on/Mounted_on/g' ${OS_HEALTHCHK_RPT}
# ############################################
# Checking RAC/ORACLE_RESTART Services:
# ############################################
case ${CHECK_CLUSTER} in
y|Y|yes|YES|Yes|ON|On|on)
# Check for ocssd clusterware process:
CHECK_OCSSD=`ps -ef|grep 'ocssd.bin'|grep -v grep|wc -l`
CHECK_CRSD=`ps -ef|grep 'crsd.bin'|grep -v grep|wc -l`
if [ ${CHECK_CRSD} -gt 0 ]
then
CLS_STR=crs
export CLS_STR
CLUSTER_TYPE=CLUSTERWARE
export CLUSTER_TYPE
else
CLS_STR=has
export CLS_STR
CLUSTER_TYPE=ORACLE_RESTART
export CLUSTER_TYPE
fi
if [ ${CHECK_CRSD} -gt 0 ]
then
GRID_HOME=`ps -ef|grep 'ocssd.bin'|grep -v grep|awk '{print $NF}'|sed -e 's/\/bin\/ocssd.bin//g'|grep -v sed|grep -v "//g"`
export GRID_HOME
echo "^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "CLUSTERWARE CHECKS:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
FILE_NAME=${GRID_HOME}/bin/ocrcheck
export FILE_NAME
if [ -x ${FILE_NAME} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "OCR DISKS CHECKING:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
${GRID_HOME}/bin/ocrcheck >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
fi
FILE_NAME=${GRID_HOME}/bin/crsctl
export FILE_NAME
if [ -x ${FILE_NAME} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "VOTE DISKS CHECKING:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
${GRID_HOME}/bin/crsctl query css votedisk >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
fi
fi
if [ ${CHECK_OCSSD} -gt 0 ]
then
GRID_HOME=`ps -ef|grep 'ocssd.bin'|grep -v grep|awk '{print $NF}'|sed -e 's/\/bin\/ocssd.bin//g'|grep -v sed|grep -v "//g"`
export GRID_HOME
FILE_NAME=${GRID_HOME}/bin/crsctl
export FILE_NAME
if [ -x ${FILE_NAME} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "${CLUSTER_TYPE}_SERVICES:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
EXCLUDED_SERVICES="ora.proxy_advm|ora.asmgroup|ora.diskmon|ora.ons"
$GRID_HOME/bin/crsctl status res |grep -v "^$"|awk -F "=" 'BEGIN {print " "} {printf("%s",NR%4 ? $2"|" : $2"\n")}'|sed -e 's/ *, /,/g' -e 's/, /,/g'|\
awk -F "|" 'BEGIN { printf "%-50s%-50s\n","Resource_Name","State";printf "%-50s%-50s\n", "-------------------------------------","---------------------"; }{ split ($3,trg,",") split ($4,st,",")} {for (i in trg) {printf "%-50s%-50s\n",$1,st[i]}}'|egrep -v "${EXCLUDED_SERVICES}" >> ${OS_HEALTHCHK_RPT}
fi
FILE_NAME=${ORACLE_HOME}/bin/srvctl
export FILE_NAME
if [ -x ${FILE_NAME} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "DATABASE_SERVICES_STATUS:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
${ORACLE_HOME}/bin/srvctl status service -d ${DB_UNQ_NAME} >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
fi
fi
;;
esac
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "List_of_All_Running_Instances:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
ps -ef|grep pmon|grep -v grep >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "List_of_All_Running_Listeners:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
ps -ef|grep tnslsnr|grep -v grep >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
# https://superuser.com/questions/529830/get-a-list-of-open-ports-in-linux [datashaman]
FILE_NAME=/bin/netstat
export FILE_NAME
if [ -x ${FILE_NAME} ]
then
echo "" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
echo "List_of_All_Open_Ports:" >> ${OS_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${OS_HEALTHCHK_RPT}
netstat -lnt | awk 'NR>2{print $4}' | grep -E '(0.0.0.0:|:::)' | sed 's/.*://' | sort -n | uniq >> ${OS_HEALTHCHK_RPT}
echo "" >> ${OS_HEALTHCHK_RPT}
fi
# Convert OS Checks into HTML format:
#${HASHHTMLOS} awk 'BEGIN { print "<table borader=1>"} {print "<tr>"; for(i=1;i<=NF;i++)print "<td><FONT COLOR=BROWN FACE="Times New Roman" SIZE=${FONTSIZE}>" $i"</FONT></td>"; print "</tr>"} END{print "</table>" }' ${OS_HEALTHCHK_RPT} >> ${DB_HEALTHCHK_RPT}
case ${HASHHTMLOS} in
'echo #')
cat ${OS_HEALTHCHK_RPT} >> ${DB_HEALTHCHK_RPT};;
*)
${HASHHTMLOS} awk 'BEGIN { print "<table borader=1>"} {print "<tr>"; for(i=1;i<=NF;i++)print "<td><FONT COLOR=BROWN FACE="Times New Roman" SIZE=${FONTSIZE}>" $i"</FONT></td>"; print "</tr>"} END{print "</table>" }' ${OS_HEALTHCHK_RPT} >> ${DB_HEALTHCHK_RPT};;
esac
# ############################################
# REPORT UNRECOVERABLE DATABASE FILES: [RMAN]
# ############################################
case ${REPORTUNRECOVERABLE} in
Y|y|YES|Yes|yes|ON|On|on)
VAL37=$(${ORACLE_HOME}/bin/rman target / << EOF
spool log to ${LOG_DIR}/unrecoverable_DBfiles.log;
REPORT UNRECOVERABLE;
spool log off;
exit;
EOF
)
#${HASHHTMLOS} echo "" > ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
#${HASHHTMLOS} echo "" > ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
#${HASHHTMLOS} echo "UNRECOVERABLE_DATABASE_FILES:RMAN" >> ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
#${HASHHTMLOS} echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" >> ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
#${HASHHTMLOS} sed '/Spooling \|Recovery Manager\|RMAN>\|using \|Report \|^$/d' ${LOG_DIR}/unrecoverable_DBfiles.log >> ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
#${HASHHTMLOS} awk 'BEGIN { print "<table borader=1>"} {print "<tr>"; for(i=1;i<=NF;i++)print "<td><FONT COLOR=BROWN FACE="Times New Roman" SIZE=${FONTSIZE}>" $i"</FONT></td>"; print "</tr>"} END{print "</table>" }' ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log >> ${DB_HEALTHCHK_RPT}
;;
esac
# ############################################
# Checking Advisors:
# ############################################
# Checking if the Advisors should be enabled in the report:
case ${SHOWSQLTUNINGADVISOR} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHSTA="";;
*)
export HASHSTA="--";;
esac
case ${SHOWMEMORYADVISORS} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHMA="";;
*)
export HASHMA="--";;
esac
case ${SHOWSEGMENTADVVISOR} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHSA="";;
*)
export HASHSA="--";;
esac
case ${REPORTUNRECOVERABLE} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHU="";;
*)
export HASHU="--";;
esac
case ${SHOWJOBS} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHJ="";;
*)
export HASHJ="--";;
esac
case ${SHOWHASHEDCRED} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHCRD="";;
*)
export HASHCRD="--";;
esac
case ${SHOWDORMANTACCOUNTS} in
Y|y|YES|Yes|yes|ON|On|on)
export HASHDORM="";;
*)
export HASHDORM="--";;
esac
# If the database version is 10g onward collect the advisors recommendations:
if [ ${DB_VER} -gt 9 ]
then
VAL611=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize ${SQLLINESIZE} pages 100
-- Enable HTML color format:
--${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { background: #E67E22; font-size: 80%; } th { font:bold 9pt Arial,Helvetica,sans-serif; align: left; color: #FFFFFF; background: #AF601A; } td { font:9pt; background: #FFFFFF; padding: 0px; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
spool ${DB_HEALTHCHK_RPT} app
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Tablespaces Size
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT Tablespaces Size: [Based on Datafiles MAXSIZE]
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
set pages 1000 linesize ${SQLLINESIZE} tab 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
select tablespace_name,
(tablespace_size*$blksize)/(1024*1024) Total_MB,
(used_space*$blksize)/(1024*1024) Used_MB,
-- used_percent "%Used"
${HASHHTML} case when used_percent > 90 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(used_percent,999.99) || '</span>' else to_char(used_percent,999.99) end as "%Used"
${HASHNONHTML} to_char(used_percent,999.99) "%Used"
from dba_tablespace_usage_metrics;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT ASM STATISTICS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT ASM STATISTICS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^
select name,state,OFFLINE_DISKS,total_mb,free_mb,
${HASHHTML} case when ROUND((1-(free_mb / total_mb))*100, 2) > 90 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(ROUND((1-(free_mb / total_mb))*100, 2),999.99) || '</span>' else to_char(ROUND((1-(free_mb / total_mb))*100, 2),999.99) end as "%FULL"
${HASHNONHTML} to_char(ROUND((1-(free_mb / total_mb))*100, 2),999.99) "%FULL"
from v\$asm_diskgroup;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT FRA STATISTICS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT FRA STATISTICS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT FRA SIZE
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT FRA_SIZE:
${HASHNONHTML} PROMPT ^^^^^^^^^
col name for a35
SELECT NAME,NUMBER_OF_FILES,SPACE_LIMIT/1024/1024/1024 AS TOTAL_SIZE_GB,SPACE_USED/1024/1024/1024 SPACE_USED_GB,
SPACE_RECLAIMABLE/1024/1024/1024 SPACE_RECLAIMABLE_GB,ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "%FULL_AFTER_CLAIM",
${HASHHTML} case when ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1) > 90 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1),999.99) || '</span>' else to_char(ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1),999.99) end as "%FULL_NOW"
${HASHNONHTML} to_char(ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1),999.99) "%FULL_NOW"
FROM V\$RECOVERY_FILE_DEST;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT FRA COMPONENTS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT FRA_COMPONENTS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^
select * from v\$flash_recovery_area_usage;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT DATABASE GROWTH
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT DATABASE GROWTH: [In the Last ~8 days]
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
set serveroutput on
col SNAP_TIME for a45
col "Database Size(GB)" for 99999999999999999
Declare
v_BaselineSize number(20);
v_CurrentSize number(20);
v_TotalGrowth number(20);
v_Space number(20);
cursor usageHist is
select a.snap_id,
SNAP_TIME,
sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
from
(select SNAP_ID,
sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
from DBA_HIST_SEG_STAT
group by SNAP_ID
having sum(SPACE_ALLOCATED_TOTAL) <> 0
order by 1 ) a,
(select distinct SNAP_ID,
to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
from DBA_HIST_SNAPSHOT) b
where a.snap_id=b.snap_id;
Begin
select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
select sum(bytes) into v_CurrentSize from dba_segments;
v_BaselineSize := (v_CurrentSize - v_TotalGrowth) ;
dbms_output.put_line('SNAP_TIME Database Size(GB)');
for row in usageHist loop
v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024*1024);
dbms_output.put_line(row.SNAP_TIME || ' ' || to_char(v_Space) );
end loop;
end;
/
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Active Incidents
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT Active Incidents:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
set linesize ${SQLLINESIZE}
col RECENT_PROBLEMS_1_WEEK_BACK for a45
select PROBLEM_KEY RECENT_PROBLEMS_1_WEEK_BACK,to_char(FIRSTINC_TIME,'DD-MON-YY HH24:mi:ss') FIRST_OCCURENCE,to_char(LASTINC_TIME,'DD-MON-YY HH24:mi:ss')
LAST_OCCURENCE FROM V\$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -10;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT OUTSTANDING ALERTS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT OUTSTANDING ALERTS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^
col CREATION_TIME for a40
col REASON for a80
select REASON,CREATION_TIME,METRIC_VALUE from DBA_OUTSTANDING_ALERTS;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT CORRUPTED BLOCKS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT CORRUPTED BLOCKS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
select * from V\$DATABASE_BLOCK_CORRUPTION;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT BLOCKED SESSIONS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT BLOCKED SESSIONS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
col module for a27
col event for a24
col MACHINE for a27
col "WA_ST|WAITD|ACT_SINC|LOG_T" for a38
col "INST|USER|SID,SERIAL#" for a30
col "INS|USER|SID,SER|MACHIN|MODUL" for a65
col "PREV|CURR SQLID" for a27
col "I|BLKD_BY" for a12
select /*+RULE*/
substr(s.INST_ID||'|'||s.USERNAME||'| '||s.sid||','||s.serial#||' |'||substr(s.MACHINE,1,22)||'|'||substr(s.MODULE,1,18),1,65)"INS|USER|SID,SER|MACHIN|MODUL"
,substr(w.state||'|'||round(w.WAIT_TIME_MICRO/1000000)||'|'||LAST_CALL_ET||'|'||to_char(LOGON_TIME,'ddMon'),1,38) "WA_ST|WAITD|ACT_SINC|LOG_T"
,substr(w.event,1,24) "EVENT"
,s.FINAL_BLOCKING_INSTANCE||'|'||s.FINAL_BLOCKING_SESSION "I|BLKD_BY"
from gv\$session s, gv\$session_wait w
where s.USERNAME is not null
and s.FINAL_BLOCKING_SESSION is not null
and s.sid=w.sid
and s.STATUS='ACTIVE'
order by "I|BLKD_BY" desc,w.event,"INS|USER|SID,SER|MACHIN|MODUL","WA_ST|WAITD|ACT_SINC|LOG_T" desc;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT UN-USABLE INDEXES
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT UN-USABLE INDEXES:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^
PROMPT
col REBUILD_UNUSABLE_INDEXES for a190
${HASHNONHTML} set echo on feedback on pages 1000
${HASHNONHTML} select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' REBUILD_UNUSABLE_INDEXES from dba_indexes where status='UNUSABLE';
${HASHHTML} select OWNER,INDEX_NAME from dba_indexes where status='UNUSABLE';
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT INVALID OBJECTS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT INVALID OBJECTS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT
set pages 0
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';
set pages 1000
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT RMAN BACKUP OPERATIONS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT RMAN BACKUP OPERATIONS: [LAST 24H]
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^
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,
${HASHHTML} case when status not in ('COMPLETED','RUNNING') then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(status) || '</span>' else to_char(status) end as "status",
${HASHNONHTML} status
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -1;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHU}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHU}PROMPT REPORT UNRECOVERABLE DATAFILES
${HASHHTML} ${HASHU}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHU}PROMPT
${HASHNONHTML} ${HASHU}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} ${HASHU}PROMPT REPORT UNRECOVERABLE DATAFILES:
${HASHNONHTML} ${HASHU}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHU}spool off
--${HASHU}host sed '/Spooling \|Recovery Manager\|RMAN>\|^$/d' ${LOG_DIR}/unrecoverable_DBfiles.log >> ${DB_HEALTHCHK_RPT}
${HASHU}host sed '/Spooling \|Recovery Manager\|RMAN>\|using \|Report \|^$/d' ${LOG_DIR}/unrecoverable_DBfiles.log >> ${DB_HEALTHCHK_RPT}
--${HASHU}host ${HASHNOHTMLOS} sed '/Spooling \|Recovery Manager\|RMAN>\|^$/d' ${LOG_DIR}/unrecoverable_DBfiles.log >> ${DB_HEALTHCHK_RPT}
--${HASHU}host ${HASHHTMLOS} sed '/Spooling \|Recovery Manager\|RMAN>\|using \|Report \|^$/d' ${LOG_DIR}/unrecoverable_DBfiles.log > ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log
--${HASHU}host ${HASHHTMLOS} awk 'BEGIN { print "<table borader=1>"} {print "<tr>"; for(i=1;i<=NF;i++)print "<td><FONT COLOR=BROWN FACE="Times New Roman" SIZE=${FONTSIZE}>" $i"</FONT></td>"; print "</tr>"} END{print "</table>" }' ${LOG_DIR}/unrecoverable_DBfiles_FORMATTED.log > ${LOG_DIR}/unrecoverable_DBfiles_HTML.log
--${HASHU}host ${HASHHTMLOS} cat ${LOG_DIR}/unrecoverable_DBfiles_HTML.log >> ${DB_HEALTHCHK_RPT}
${HASHU}spool ${DB_HEALTHCHK_RPT} app
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHJ}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT SCHEDULED JOBS STATUS
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} ${HASHJ}PROMPT <p> <table border='1' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT DBMS_JOBS
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} ${HASHJ}PROMPT SCHEDULED JOBS STATUS:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT DBMS_JOBS:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^
${HASHJ}set linesize ${SQLLINESIZE}
${HASHJ}col LAST_RUN for a25
${HASHJ}col NEXT_RUN for a25
${HASHJ}select job,schema_user,failures,to_char(LAST_DATE,'DD-Mon-YYYY hh24:mi:ss')LAST_RUN,to_char(NEXT_DATE,'DD-Mon-YYYY hh24:mi:ss')NEXT_RUN from dba_jobs;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHJ}PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT DBMS_SCHEDULER
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT DBMS_SCHEDULER:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^
${HASHJ}col OWNER for a15
${HASHJ}col JOB_NAME for a30
${HASHJ}col STATE for a15
${HASHJ}col FAILURE_COUNT for 9999 heading 'Fail'
${HASHJ}col "DURATION(d:hh:mm:ss)" for a22
${HASHJ}col REPEAT_INTERVAL for a70
${HASHJ}col "LAST_RUN || REPEAT_INTERVAL" for a65
${HASHJ}col "DURATION(d:hh:mm:ss)" for a12
${HASHJ}select JOB_NAME,OWNER,ENABLED,STATE,
${HASHJ} ${HASHHTML} case when FAILURE_COUNT > 0 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(FAILURE_COUNT,99999) || '</span>' else to_char(FAILURE_COUNT,99999) end as "FAILURE_COUNT",
${HASHJ} ${HASHNONHTML} FAILURE_COUNT,
${HASHJ}to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')||' || '||REPEAT_INTERVAL "LAST_RUN || REPEAT_INTERVAL",
${HASHJ}extract(day from last_run_duration) ||':'||
${HASHJ}lpad(extract(hour from last_run_duration),2,'0')||':'||
${HASHJ}lpad(extract(minute from last_run_duration),2,'0')||':'||
${HASHJ}lpad(round(extract(second from last_run_duration)),2,'0') "DURATION(d:hh:mm:ss)"
${HASHJ}from dba_scheduler_jobs order by ENABLED,STATE;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHJ}PROMPT <p> <table border='1' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT AUTOTASK INTERNAL MAINTENANCE WINDOWS
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT AUTOTASK INTERNAL MAINTENANCE WINDOWS:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHJ}col WINDOW_NAME for a17
${HASHJ}col NEXT_RUN for a20
${HASHJ}col ACTIVE for a6
${HASHJ}col OPTIMIZER_STATS for a15
${HASHJ}col SEGMENT_ADVISOR for a15
${HASHJ}col SQL_TUNE_ADVISOR for a16
${HASHJ}SELECT WINDOW_NAME,TO_CHAR(WINDOW_NEXT_TIME,'DD-MM-YYYY HH24:MI:SS') NEXT_RUN,AUTOTASK_STATUS STATUS,WINDOW_ACTIVE ACTIVE,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHJ}PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT Current Running Jobs
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT Current Running Jobs:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^^^^^^
${HASHJ}col INS for 999
${HASHJ}col "JOB_NAME|OWNER|SPID|SID" for a55
${HASHJ}col ELAPSED_TIME for a17
${HASHJ}col CPU_USED for a17
${HASHJ}col "WAIT_SEC" for 9999999999
${HASHJ}col WAIT_CLASS for a15
${HASHJ}col "BLKD_BY" for 9999999
${HASHJ}col "WAITED|WCLASS|EVENT" for a45
${HASHJ}select j.RUNNING_INSTANCE INS,j.JOB_NAME ||' | '|| j.OWNER||' |'||SLAVE_OS_PROCESS_ID||'|'||j.SESSION_ID"JOB_NAME|OWNER|SPID|SID"
${HASHJ},s.FINAL_BLOCKING_SESSION "BLKD_BY",ELAPSED_TIME,CPU_USED
${HASHJ},substr(s.SECONDS_IN_WAIT||'|'||s.WAIT_CLASS||'|'||s.EVENT,1,45) "WAITED|WCLASS|EVENT",S.SQL_ID
${HASHJ}from dba_scheduler_running_jobs j, gv\$session s
${HASHJ}where j.RUNNING_INSTANCE=S.INST_ID(+)
${HASHJ}and j.SESSION_ID=S.SID(+)
${HASHJ}order by "JOB_NAME|OWNER|SPID|SID",ELAPSED_TIME;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHJ}PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHJ}PROMPT FAILED DBMS_SCHEDULER JOBS IN THE LAST 24H
${HASHHTML} ${HASHJ}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHJ}PROMPT
${HASHNONHTML} ${HASHJ}PROMPT FAILED DBMS_SCHEDULER JOBS IN THE LAST 24H:
${HASHNONHTML} ${HASHJ}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHJ}col LOG_DATE for a36
${HASHJ}col OWNER for a15
${HASHJ}col JOB_NAME for a35
${HASHJ}col STATUS for a11
${HASHJ}col RUN_DURATION for a20
${HASHJ}col ID for 99
${HASHJ}select INSTANCE_ID ID,JOB_NAME,OWNER,LOG_DATE,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS='FAILED' order by JOB_NAME,LOG_DATE;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Queries Running For More Than [${LONG_RUN_QUR_HOURS}] Hours
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT Queries Running For More Than [${LONG_RUN_QUR_HOURS}] Hour:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col module for a30
col DURATION_HOURS for 99999.9
col STARTED_AT for a13
col "USERNAME| SID,SERIAL#" for a30
${HASHNONHTML} col "SQL_ID | SQL_TEXT" for a120
select username||'| '||sid ||','|| serial# "USERNAME| SID,SERIAL#",substr(MODULE,1,30) "MODULE", to_char(sysdate-last_call_et/24/60/60,'DD-MON HH24:MI') STARTED_AT,
last_call_et/60/60 "DURATION_HOURS"
--||' | '|| (select SQL_FULLTEXT from v\$sql where address=sql_address) "SQL_ID | SQL_TEXT"
,SQL_ID
from v\$session where
username is not null
and module is not null
and module not in (${EXCLUDED_MODULES})
and last_call_et > 60*60*${LONG_RUN_QUR_HOURS}
and status = 'ACTIVE';
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHSTA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHSTA}PROMPT ADVISORS STATUS
${HASHHTML} ${HASHSTA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT ADVISORS STATUS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^
col ADVISOR_NAME for a60
col STATUS for a15
${HASHSTA}SELECT client_name ADVISOR_NAME, status FROM dba_autotask_client ORDER BY client_name;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHSTA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHSTA}PROMPT SQL TUNING ADVISOR
${HASHHTML} ${HASHSTA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHSTA}PROMPT
${HASHNONHTML} ${HASHSTA}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} ${HASHSTA}PROMPT SQL TUNING ADVISOR:
${HASHNONHTML} ${HASHSTA}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHSTA}PROMPT <br> <p> <table border='1' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHSTA}PROMPT Last Execution of SQL TUNING ADVISOR
${HASHHTML} ${HASHSTA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHSTA}PROMPT
${HASHNONHTML} ${HASHSTA}PROMPT Last Execution of SQL TUNING ADVISOR:
${HASHNONHTML} ${HASHSTA}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHSTA}col TASK_NAME for a60
${HASHSTA}set long 2000000000
${HASHSTA}SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI') Last_Execution FROM dba_advisor_executions where TASK_NAME='SYS_AUTO_SQL_TUNING_TASK' and execution_end>sysdate-1;
${HASHSTA}variable Findings_Report CLOB;
${HASHSTA} BEGIN
${HASHSTA} :Findings_Report :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
${HASHSTA} begin_exec => NULL,
${HASHSTA} end_exec => NULL,
${HASHSTA} type => 'TEXT',
${HASHSTA} level => 'TYPICAL',
${HASHSTA} section => 'ALL',
${HASHSTA} object_id => NULL,
${HASHSTA} result_limit => NULL);
${HASHSTA} END;
${HASHSTA} /
${HASHSTA} print :Findings_Report
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHMA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHMA}PROMPT MEMORY ADVISORS
${HASHHTML} ${HASHMA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT ORACLE MEMORY UTILIZATION:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT ORACLE MEMORY UTILIZATION
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
col inst_id heading ins for 999
col COMPONENT for a35
col CURRENT_SIZE_MB for 99999999999
col MAX_SIZE_MB for 99999999999
select INST_ID,COMPONENT,USER_SPECIFIED_SIZE/1024/1024 USER_SPECIFIED_SIZE_MB,CURRENT_SIZE/1024/1024 CURRENT_SIZE_MB,MAX_SIZE/1024/1024 MAX_SIZE_MB from gv\$memory_dynamic_components where COMPONENT not like '%K buffer%' and COMPONENT not in ('ASM Buffer Cache','KEEP buffer cache','RECYCLE buffer cache','unified pga pool','Data Transfer Cache') order by COMPONENT,INST_ID;
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} ${HASHMA}PROMPT MEMORY ADVISORS:
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHMA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHMA}PROMPT SGA ADVISOR
${HASHHTML} ${HASHMA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT SGA ADVISOR:
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^
${HASHMA}col ESTD_DB_TIME for 99999999999999999
${HASHMA}col ESTD_DB_TIME_FACTOR for 9999999999999999999999999999
${HASHMA}select * from V\$SGA_TARGET_ADVICE where SGA_SIZE_FACTOR > .6 and SGA_SIZE_FACTOR < 1.6;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHMA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHMA}PROMPT Buffer Cache ADVISOR
${HASHHTML} ${HASHMA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT Buffer Cache ADVISOR:
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^^^^^^^^^^
${HASHMA}col ESTD_SIZE_MB for 9999999999999
${HASHMA}col ESTD_PHYSICAL_READS for 99999999999999999999
${HASHMA}col ESTD_PHYSICAL_READ_TIME for 99999999999999999999
${HASHMA}select SIZE_FACTOR "%SIZE",SIZE_FOR_ESTIMATE ESTD_SIZE_MB,ESTD_PHYSICAL_READS,ESTD_PHYSICAL_READ_TIME,ESTD_PCT_OF_DB_TIME_FOR_READS
${HASHMA}from V\$DB_CACHE_ADVICE where SIZE_FACTOR >.8 and SIZE_FACTOR<1.3;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHMA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHMA}PROMPT Shared Pool ADVISOR
${HASHHTML} ${HASHMA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT Shared Pool ADVISOR:
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^^^^^^^^^
${HASHMA}col SIZE_MB for 99999999999
${HASHMA}col SIZE_FACTOR for 99999999
${HASHMA}col ESTD_SIZE_MB for 99999999999999999999
${HASHMA}col LIB_CACHE_SAVED_TIME for 99999999999999999999999999
${HASHMA}select SHARED_POOL_SIZE_FOR_ESTIMATE SIZE_MB,SHARED_POOL_SIZE_FACTOR "%SIZE",SHARED_POOL_SIZE_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,ESTD_LC_TIME_SAVED LIB_CACHE_SAVED_TIME,
${HASHMA}ESTD_LC_LOAD_TIME PARSING_TIME from V\$SHARED_POOL_ADVICE
${HASHMA}where SHARED_POOL_SIZE_FACTOR > .9 and SHARED_POOL_SIZE_FACTOR < 1.6;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHMA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHMA}PROMPT PGA ADVISOR
${HASHHTML} ${HASHMA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHMA}PROMPT
${HASHNONHTML} ${HASHMA}PROMPT PGA ADVISOR:
${HASHNONHTML} ${HASHMA}PROMPT ^^^^^^^^^^^^
${HASHMA}col SIZE_FACTOR for 999999999
${HASHMA}col ESTD_SIZE_MB for 99999999999999999999
${HASHMA}col MB_PROCESSED for 99999999999999999999
${HASHMA}col ESTD_TIME for 99999999999999999999
${HASHMA}select PGA_TARGET_FACTOR "%SIZE",PGA_TARGET_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,BYTES_PROCESSED/1024/1024 MB_PROCESSED,
${HASHMA}ESTD_TIME,ESTD_PGA_CACHE_HIT_PERCENTAGE PGA_HIT,ESTD_OVERALLOC_COUNT PGA_SHORTAGE
${HASHMA}from V\$PGA_TARGET_ADVICE where PGA_TARGET_FACTOR > .7 and PGA_TARGET_FACTOR < 1.6;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHSA}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHSA}PROMPT SEGMENT ADVISOR
${HASHHTML} ${HASHSA}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHSA}PROMPT
${HASHNONHTML} ${HASHSA}PROMPT SEGMENT ADVISOR:
${HASHNONHTML} ${HASHSA}PROMPT ^^^^^^^^^^^^^^^^
${HASHSA}select'Task Name : ' || f.task_name || chr(10) ||
${HASHSA}'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
${HASHSA}'Segment Name : ' || o.attr2 || chr(10) ||
${HASHSA}'Segment Type : ' || o.type || chr(10) ||
${HASHSA}'Partition Name : ' || o.attr3 || chr(10) ||
${HASHSA}'Message : ' || f.message || chr(10) ||
${HASHSA}'More Info : ' || f.more_info || chr(10) ||
${HASHSA}'-------------------------------------------' Advice
${HASHSA}FROM dba_advisor_findings f
${HASHSA},dba_advisor_objects o
${HASHSA},dba_advisor_executions e
${HASHSA}WHERE o.task_id = f.task_id
${HASHSA}AND o.object_id = f.object_id
${HASHSA}AND f.task_id = e.task_id
${HASHSA}AND e. execution_start > sysdate - 1
${HASHSA}AND e.advisor_name = 'Segment Advisor'
${HASHSA}ORDER BY f.task_name;
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT TOP FRAGMENTED TABLES:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT TOP FRAGMENTED TABLES
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
col "%RECLAIMABLE_SPACE" for 99
col owner for a30
col "%FRAGMENTED_SPACE" for a17
col LAST_ANALYZED for a13
select * from (select owner,table_name,to_char(LAST_ANALYZED, 'DD-MON-YYYY') LAST_ANALYZED,
round(blocks * ${blksize}/1024/1024) "FULL_SIZE_MB",
round(num_rows * avg_row_len/1024/1024) "ACTUAL_SIZE_MB",
round(blocks * ${blksize}/1024/1024) - round(num_rows * avg_row_len/1024/1024) "FRAGMENTED_SPACE_MB",
round(((round((blocks * ${blksize}/1024/1024)) - round((num_rows * avg_row_len/1024/1024))) / round((blocks * ${blksize}/1024/1024))) * 100)||'%' "%FRAGMENTED_SPACE"
from dba_tables
where blocks>10
-- Exclude SYS objects:
and owner <> 'SYS'
and round(blocks * ${blksize}/1024/1024) > 10
-- Fragmented Space must be > 30%:
and ((round((blocks * ${blksize}/1024/1024)) - round((num_rows * avg_row_len/1024/1024))) / round((blocks * ${blksize}/1024/1024))) * 100 > 30
order by "FRAGMENTED_SPACE_MB" desc) where rownum<11;
PROMPT Hint: The accuracy of the FRAGMENTED TABLES list depends on having a recent STATISTICS.
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT CURRENT OS / HARDWARE STATISTICS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT CURRENT OS / HARDWARE STATISTICS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
select stat_name,value from v\$osstat;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT RESOURCE LIMIT
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT RESOURCE LIMIT:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from gv\$resource_limit order by RESOURCE_NAME;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT RECYCLEBIN OBJECTS#
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT RECYCLEBIN OBJECTS#: [Purging DBA_RECYCLEBIN can boost the performance of X$ tables]
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^
col "RECYCLED_OBJECTS#" for 999999999999999999
col "TOTAL_SIZE_MB" for 99999999999999
set feedback off
select
${HASHHTML} case when count(*) > 1000 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(count(*)) || '</span>' else to_char(count(*)) end as "RECYCLED_OBJECTS#",
${HASHNONHTML} count(*) "RECYCLED_OBJECTS#",
${HASHHTML} case when sum(space)*${blksize}/1024/1024 > 1024 then '<span style="background-color:#E67E22;display:block;overflow:auto">' || to_char(sum(space)*${blksize}/1024/1024) || '</span>' else to_char(sum(space)*${blksize}/1024/1024) end as "TOTAL_SIZE_MB"
${HASHNONHTML} sum(space)*${blksize}/1024/1024 "TOTAL_SIZE_MB"
from dba_recyclebin group by 1;
set feedback on
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT FLASHBACK RESTORE POINTS
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT FLASHBACK RESTORE POINTS:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
col SCN for 999999999999999999
col time for a35
col RESTORE_POINT_TIME for a35
col name for a40
select NAME,SCN,TIME,STORAGE_SIZE/1024/1024/1024 STORAGE_SIZE_GB from v\$restore_point;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT HEALTH MONITOR
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT HEALTH MONITOR:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^
col DESCRIPTION for a80
col repair_script for a80
select name,type,status,description,repair_script from V\$HM_RECOMMENDATION where time_detected > sysdate -1;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Monitored INDEXES
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT Monitored INDEXES:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^
set linesize ${SQLLINESIZE} pages 1000
col Index_NAME for a40
col TABLE_NAME for a40
select io.name Index_NAME, t.name TABLE_NAME,decode(bitand(i.flags, 65536),0,'NO','YES') Monitoring,
decode(bitand(ou.flags, 1),0,'NO','YES') USED,ou.start_monitoring,ou.end_monitoring
from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#;
--PROMPT
--PROMPT To stop monitoring USED indexes use this command:
--prompt select 'ALTER INDEX RA.'||io.name||' NOMONITORING USAGE;' from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
--prompt and decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='YES' order by 1
--prompt /
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT REDO LOG SWITCHES
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT REDO LOG SWITCHES:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^
set linesize ${SQLLINESIZE}
col day for a11
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from v\$log_history where first_time > sysdate-1
GROUP by to_char(first_time,'YYYY-MON-DD') order by 1 asc;
${HASHHTML} PROMPT <br>
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='35%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Modified Parameters Since The Instance Startup
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} PROMPT
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} PROMPT Modified Parameters Since The Instance Startup:
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col name for a45
col VALUE for a80
col DEPRECATED for a10
select NAME,VALUE,ISDEFAULT "DEFAULT",ISDEPRECATED "DEPRECATED" from v\$parameter where ISMODIFIED = 'SYSTEM_MOD' order by 1;
${HASHHTML} PROMPT <br>
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} ${HASHCRD}PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='20%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} ${HASHCRD}PROMPT Cred Backup
${HASHHTML} ${HASHCRD}PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} ${HASHCRD}PROMPT
${HASHNONHTML} ${HASHCRD}PROMPT ^^^^^^^^^^^^
${HASHNONHTML} ${HASHCRD}PROMPT Cred Backup:
${HASHNONHTML} ${HASHCRD}PROMPT ^^^^^^^^^^^^
${HASHCRD}col name for a35
${HASHCRD}col HASH for a35
${HASHCRD}col CREATION_DATE for a20
${HASHCRD}col PASSWORD_LAST_CHANGED for a30
${HASHCRD}col "CREATE_DATE||PASS_LAST_CHANGE" for a60
${HASHNONHTML} ${HASHCRD}select name,PASSWORD HASH,CTIME ||' || '||PTIME "CREATE_DATE||PASS_LAST_CHANGE" from user\$ where PASSWORD is not null order by 1;
${HASHHTML} ${HASHCRD}select name,PASSWORD HASH,CTIME "CREATION_DATE",PTIME "PASSWORD_LAST_CHANGED" from user\$ where PASSWORD is not null order by 1;
spool off
exit;
EOF
)
fi
# #################################################
# Reporting New Created Objects in the last 24Hours:
# #################################################
NEWOBJCONTRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from dba_objects
where created > sysdate-1
and owner <> 'SYS';
exit;
EOF
)
NEWOBJCONT=`echo ${NEWOBJCONTRAW} | awk '{print $NF}'`
if [ ${NEWOBJCONT} -ge ${NEWOBJCONTTHRESHOLD} ]
then
VALNEWOBJCONT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize ${SQLLINESIZE} pages 1000
-- Enable HTML color format:
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
spool ${DB_HEALTHCHK_RPT} app
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT New Created objects [Last 24H]
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} prompt
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt New Created objects [Last 24H] ...
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt
col owner for a30
col object_name for a30
col object_type for a19
col created for a20
select object_type,owner,object_name,to_char(created, 'DD-Mon-YYYY HH24:MI:SS') CREATED from dba_objects
where created > sysdate-1
and owner <> 'SYS'
order by owner,object_type;
spool off
exit;
EOF
)
fi
# ###############################################
# Reporting Modified Objects in the last 24Hours:
# ###############################################
MODOBJCONTRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from dba_objects
where LAST_DDL_TIME > sysdate-1
and owner <> 'SYS';
exit;
EOF
)
MODOBJCONT=`echo ${MODOBJCONTRAW} | awk '{print $NF}'`
if [ ${MODOBJCONT} -ge ${MODOBJCONTTHRESHOLD} ]
then
VALMODOBJCONT=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize ${SQLLINESIZE} pages 1000
-- Enable HTML color format:
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
spool ${DB_HEALTHCHK_RPT} app
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Modified objects in the Last 24H
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} prompt
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt Modified objects in the Last 24H ...
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt
col owner for a30
col object_name for a30
col object_type for a19
col LAST_DDL_TIME for a20
select object_type,owner,object_name,to_char(LAST_DDL_TIME, 'DD-Mon-YYYY HH24:MI:SS') LAST_DDL_TIME from dba_objects
where LAST_DDL_TIME > sysdate-1
and owner <> 'SYS'
order by owner,object_type;
spool off
exit;
EOF
)
fi
# ###############################################
# Checking AUDIT RECORDS ON THE DATABASE:
# ###############################################
# Check if Checking Audit Records is ENABLED:
case ${CHKAUDITRECORDS} in
Y|y|YES|Yes|yes|ON|On|on)
VAL70=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT (SELECT COUNT(*) FROM dba_audit_trail
where ACTION_NAME not like 'LOGO%' and ACTION_NAME not in ('SELECT','SET ROLE') and timestamp > SYSDATE-1)
+
(SELECT COUNT(*) FROM DBA_AUDIT_SESSION WHERE timestamp > SYSDATE-1 and returncode = 1017)
+
(SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp > SYSDATE-1)
+
(SELECT COUNT(*) FROM dba_objects where created > sysdate-1 and owner <> 'SYS') AUD_REC_COUNT FROM dual;
exit;
EOF
)
VAL80=`echo ${VAL70} | awk '{print $NF}'`
if [ ${VAL80} -ge ${AUDITRECOTHRESHOLD} ]
then
VAL90=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize ${SQLLINESIZE} pages 1000
-- Enable HTML color format:
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
spool ${LOG_DIR}/audit_records.log
SET TIMING ON
col OS_USERNAME for a20
col EXTENDED_TIMESTAMP for a36
col OWNER for a25
col OBJ_NAME for a25
col OS_USERNAME for a20
col USERNAME for a25
col USERHOST for a35
col ACTION_NAME for a25
col ACTION_OWNER_OBJECT for a55
col TERMINAL for a30
col ACTION_NAME for a20
col TIMESTAMP for a21
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Failed Login Attempts in the last 24Hours
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} prompt
${HASHNONHTML} prompt
${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt Failed Login Attempts in the last 24Hours ...
${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt
select to_char (EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,OS_USERNAME,USERNAME,TERMINAL,USERHOST,ACTION_NAME
from DBA_AUDIT_SESSION
where timestamp > (sysdate -1)
and returncode = 1017
order by 1;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Audit records in the last 24Hours AUD$
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} prompt
${HASHNONHTML} prompt
${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt Audit records in the last 24Hours AUD$...
${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt
select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
from dba_audit_trail
where
timestamp > SYSDATE-1
and ACTION_NAME not like 'LOGO%'
and ACTION_NAME not in ('SELECT','SET ROLE')
-- and USERNAME not in ('CRS_ADMIN','DBSNMP')
-- and OS_USERNAME not in ('workflow')
-- and OBJ_NAME not like '%TMP_%'
-- and OBJ_NAME not like 'WRKDETA%'
-- and OBJ_NAME not in ('PBCATTBL','SETUP','WRKIB','REMWORK')
order by EXTENDED_TIMESTAMP;
${HASHHTML} SET PAGES 0
${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHHTML} PROMPT Fine Grained Auditing Data
${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHHTML} set pages 1000
${HASHNONHTML} prompt
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt Fine Grained Auditing Data ...
${HASHNONHTML} PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHNONHTML} prompt
col sql_text for a70
col time for a36
col USERHOST for a21
col db_user for a15
select to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') as time,db_user,userhost,sql_text,SQL_BIND
from dba_fga_audit_trail
where
timestamp > SYSDATE-1
-- and policy_name='PAYROLL_TABLE'
order by EXTENDED_TIMESTAMP;
${HASHDORM} ${HASHHTML} SET PAGES 0
${HASHDORM} ${HASHHTML} SET MARKUP HTML OFF SPOOL OFF
${HASHDORM} ${HASHHTML} PROMPT <br> <p> <table border='3' bordercolor='#E67E22' width='30%' align='left' summary='Script output'> <tr> <th scope="col">
${HASHDORM} ${HASHHTML} PROMPT Dormant Accounts: [Accounts that did not login in the last ${DORMANT_INACTIVE_DAYS} days]
${HASHDORM} ${HASHHTML} PROMPT </td> </tr> </table> <p> <br>
${HASHDORM} ${HASHHTML} SET WRAP OFF ECHO OFF FEEDBACK OFF MARKUP HTML ON SPOOL ON HEAD '<title></title> <style type="text/css"> table { font-size: 80%; } th { background: #AF601A; } </style>' TABLE "border='2' bordercolor='#E67E22'" ENTMAP OFF
${HASHDORM} ${HASHHTML} set pages 1000
${HASHDORM} ${HASHNONHTML} prompt
${HASHDORM} ${HASHNONHTML} prompt
${HASHDORM} ${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHDORM} ${HASHNONHTML} prompt Dormant Accounts: [Accounts that did not login in the last ${DORMANT_INACTIVE_DAYS} days] ...
${HASHDORM} ${HASHNONHTML} prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
${HASHDORM} ${HASHNONHTML} prompt
${HASHDORM} select userid,max(ntimestamp#) LAST_LOGIN_DATE from aud$ where ntimestamp# < (sysdate - ${DORMANT_INACTIVE_DAYS}) group by userid order by LAST_LOGIN_DATE;
spool off
exit;
EOF
)
cat ${LOG_DIR}/audit_records.log >> ${DB_HEALTHCHK_RPT}
fi
;;
esac
# Remove odd lines from the report:
sed -i 's/^rows will be truncated//g' ${DB_HEALTHCHK_RPT}
export LOGFILE=${DB_HEALTHCHK_RPT}
export MSGSUBJECT="HEALTH CHECK REPORT: For Database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]"
echo ${MSGSUBJECT}
${MAILEXEC} "${MSGSUBJECT}" ${MAIL_LIST} < ${LOGFILE}
(
echo "To: ${EMAIL};"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html;"
echo "Subject: ${MSGSUBJECT}"
cat ${LOGFILE}
) | ${SENDMAIL}
echo "HEALTH CHECK REPORT FOR DATABASE [${DB_NAME_UPPER}] WAS SAVED TO: ${DB_HEALTHCHK_RPT}"
done
echo ""
# #############################
# De-Neutralize login.sql file: [Bug Fix]
# #############################
# 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
# #############
# REPORT BUGS to: mahmmoudadel@hotmail.com
# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
view raw dbdailychk.sh hosted with ❤ by GitHub
Posted by

117 comments:

  1. se7en0502March 28, 2016 at 7:54 AM

    This is agreat script but i have this error when run script.
    ################
    [oracle@fcsdb DBA_BUNDLE2]$ sh dbdailychk.sh
    df: `/root/.gvfs': Permission denied
    df: `/root/.gvfs': Permission denied
    ##########

    Can u help me fix it ?

    ReplyDelete
  2. Mahmmoud ADELMarch 28, 2016 at 9:32 AM

    Well, the fastest solution for this error is to ignore it :-)
    This error will not affect the filesystem monitoring function inside the script.

    In case you don't like to see this error, you can easily replace "df -h" command inside the script with "df -hx fuse.gvfs-fuse-daemon"

    For further help, please let me know.
    Mahmmoud ADEL

    ReplyDelete
  3. AnonymousApril 1, 2016 at 7:46 AM

    Not work for Solaris based database

    ReplyDelete
  4. AnonymousApril 1, 2016 at 7:48 AM

    bash-3.2$ ./dbdailychk.sh
    chown: /export/home/oratest/BUNDLE_Logs: Not owner
    chown: alert_test_new.log: Not owner
    ./dbdailychk.sh: line 267: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 293: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 305: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 316: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 390: /bin/sqlplus: No such file or directory
    cat: cannot open /export/home/oratest/BUNDLE_Logs/tablespaces_DBA_BUNDLE.log
    ./dbdailychk.sh: line 400: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 423: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 424: /export/home/oratest/BUNDLE_Logs/blocking_sessions.log: No such file or directory
    ./dbdailychk.sh: line 437: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 439: [: -ge: unary operator expected
    ./dbdailychk.sh: line 465: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 467: [: -ge: unary operator expected
    ./dbdailychk.sh: line 505: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 507: [: -ge: unary operator expected
    ./dbdailychk.sh: line 546: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 548: [: -ge: unary operator expected
    ./dbdailychk.sh: line 614: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 616: [: -ge: unary operator expected
    ./dbdailychk.sh: line 643: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 645: [: -ge: unary operator expected
    ./dbdailychk.sh: line 696: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 706: /bin/sqlplus: No such file or directory
    ./dbdailychk.sh: line 715: [: -gt: unary operator expected
    ./dbdailychk.sh: line 1020: /bin/sqlplus: No such file or directory

    ReplyDelete
  5. Mahmmoud ADELApril 1, 2016 at 1:11 PM

    The script was tested on Solaris and it's working fine.
    You have to run the script under the Owner of the Oracle binaries e.g. oracle user.

    ReplyDelete
  6. UnknownApril 6, 2016 at 9:37 AM

    This comment has been removed by the author.

    ReplyDelete
  7. Mahmmoud ADELApril 6, 2016 at 6:33 PM

    Taner,
    Most probably the script cannot get your ORACLE_HOME, because there is no record for it in /etc/oratab file. In this case, I suggest you to explicitly export your ORACLE_HOME in the VARIABLES section, so it will be like this:

    # #########################
    # Variables:
    # #########################
    export ORACLE_HOME=

    ReplyDelete
  8. AnonymousMay 9, 2016 at 9:34 PM

    I am not able to run..My os is HP-UX. I had replaced the email id and am getting below error.

    #######################################################
    Please EDIT line# 51 in dbdailychk.sh script and change
    myself@@gmail.com to your E-mail address.
    #######################################################

    Script Terminated !

    Please suggest where is the issue??

    ReplyDelete
  9. Mahmmoud ADELMay 10, 2016 at 2:29 PM

    Thanks for passing by.

    Although this script is not tested on HP-UX environment, but you shouldn't modify this line:
    case ${MAIL_LIST} in "myself@gmail.com")

    In order to make the script work for you, you have to modify it like this:

    MAIL_LIST="myself@gmail.com"
    case ${MAIL_LIST} in "youremail@yourcompany.com")

    ReplyDelete
  10. OraDetectorOctober 9, 2016 at 11:38 AM

    Hi Adil ,

    does the script supports RAC ?

    ReplyDelete
  11. Mahmmoud ADELOctober 9, 2016 at 3:57 PM

    Thanks for your question.
    Actually instance based checks like: DATABASES/LISTENERS ALERTLOGS, CPU/FILESYSTEM UTILIZATION will not run on all nodes. It will just run against the local node. The rest of checks are running against the database which doesn't matter whether it's a RAC DB or not.

    Honestly speaking, On my production RAC environments, I run the health check script dbdailychk.sh on one node only. Because I'm already scheduling dbalarm script to run every 5 minutes on each node in order to report other issues at the nearest time.

    You can download dbalarm script from this post:
    http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html

    ReplyDelete
  12. OraDetectorOctober 30, 2016 at 8:29 PM

    Thanks Adil , are you gong to update the script to support RAC related Checks ?

    like crsstat , ocrcheck , votedisk , .... etc ,

    ReplyDelete
  13. Mahmmoud ADELJanuary 4, 2017 at 12:20 PM

    Thanks OraDetector for that suggestion.
    I've already added RAC support feature to this script and included your nick name in the feature description.

    Added checking RAC status feature. [Recommended by: OraDetector]

    I wonder if you can send me your name so I can update the description with your good name.

    ReplyDelete
  14. RaviJanuary 11, 2017 at 3:36 PM

    I am trying to run in Oracle Linux and get below error. when i run it directly, it succeeds. can you suggest what could be the issue?
    ++ /mnt/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/sqlplus -S '/ as sysdba'
    + VAL1='
    SELECT name from v$database
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available
    Process ID: 0
    Session ID: 0 Serial number: 0'

    ReplyDelete
  15. Mahmmoud ADELJanuary 17, 2017 at 5:02 PM

    Seems the script didn't manage to pick up the right ORACLE_SID on your environment. Honestly don't know the reason why without having a live look. but as a dirty fix you can export the right ORACLE_SID inside the script by doing the following:

    Replace this block:

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


    With this block:

    # #########################
    # Setting ORACLE_SID:
    # #########################
    for ORACLE_SID in YOUR_GOOD_ORACLE_SID
    do
    export ORACLE_SID


    ... So YOUR_GOOD_ORACLE_SID will be replaced with the right ORACLE_SID
    e.g.
    for ORACLE_SID in ERPDB1

    Also it will give more clear picture if you can provide me with the output of this command:
    ps -ef|grep pmon

    ReplyDelete
  16. OscarMarch 10, 2017 at 11:21 PM

    Sir I had this problem:
    Active Incidents:
    ^^^^^^^^^^^^^^^^^
    LAST_OCCURENCE FROM V$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -10
    *
    ERROR at line 2:
    ORA-00942: table or view does not exist

    This view V$DIAG_PROBLEM not exist.

    ReplyDelete
  17. Mahmmoud ADELMarch 12, 2017 at 6:25 PM

    May I know your database version if you don't mind?

    ReplyDelete
  18. CowtownDBAMarch 24, 2017 at 6:08 PM

    Thank you for providing this health check script! It provides so much useful information.

    ReplyDelete
  19. AnonymousMarch 31, 2017 at 5:08 PM

    How this script actually performs on RAC (two node)? Having this entries in /etc/oratab on node 1(I've added comments for every entry just here, not really in oratab :) ):

    -MGMTDB:/u01/app/12.1.0.2/grid:N - ok, excluded in EXL_DB
    +ASM1:/u01/app/12.1.0.2/grid:N - ok, excluded in EXL_DB
    dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - db_unique_name - added by installer
    dwhcdb1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - instance_name - added manually by dba

    When i run dbalert.sh on that node, i got email with message that "dwhcdb1" is down, but that is false:

    The Following Instances are POSSIBLY Down on [*hostname removed by me] :
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    dwhcdb1

    If above instances are permanently offline, please add their names to 'EXL_DB' parameter at line# 90 or hash their entries in /etc/oratab to let the script ignore them in the next run.

    Current Running Instances:
    ************************
    oracle 44402 1 0 Mar23 ? 00:00:58 asm_pmon_+ASM1
    oracle 155269 1 0 Mar23 ? 00:01:16 ora_pmon_dwhcdb1


    INS_NAME STATUS DB_STATUS LOGINS BLOCKED STARTUP_TIME
    ---------------- ------------ ----------------- ---------- ------- -------------------
    dwhcdb1 OPEN ACTIVE ALLOWED NO 23-MAR-17 08:39:36

    Thank you for sharing this set of dba scripts - great job!
    Ivan

    ReplyDelete
  20. AnonymousMarch 31, 2017 at 5:13 PM

    also got error on scriptexecution for nfs mounted filesystem:

    ./dbalarm.sh: line 125: [: 192.168.xx.xx:/nfs/Backup: integer expression expected

    Regards,
    Ivan

    ReplyDelete
  21. OraDetectorApril 10, 2017 at 10:09 AM

    Thanks Adil for adding my Name to the description of your script

    my name is : Samer ALJazzar

    for some reason the AWK block for CRS check hangs for ever , i think its a syntax issue i'm not able to figure it out

    thanks and regards,
    Sam

    ReplyDelete
  22. Mahmmoud ADELApril 10, 2017 at 11:13 AM

    Hi Ivan,
    Thanks for passing by. As this DB is a RAC one, you will need to do one of the following:
    - Include the DB_NAME in the EXL_DB parameter inside the script e.g.:
    EXL_DB="\-MGMTDB|ASM|dwhcdb"
    - Hash out the oratab entry which refers to the DB_NAME (not the instance name) e.g.:
    #dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N - db_unique_name - added by installer

    ReplyDelete
  23. Mahmmoud ADELApril 10, 2017 at 11:17 AM

    Hi Samer,
    Thanks for your update. I'll update your good name inside the script once I publish the new release.
    May I ask what is the Linux version you are trying to run the script on?

    ReplyDelete
  24. OraDetectorApril 11, 2017 at 7:54 AM

    i have two main OS used

    Solaris 11.3
    Linux Redhat

    Regards,
    Samer

    ReplyDelete
  25. AnonymousApril 18, 2017 at 5:35 PM

    Hi Mahmmud,

    Regarding my rac db - still cannot configure dbalarm script to work properly. If i include DB_NAME (dwhcdb) in EXL_DB list and hash out DB_NAME in oratab, script exits on "Getting ORACLE_SID" block and alert mail is sent:

    subject:
    ALARM: No Databases Are Running on Server: myserver !!!

    and mail body:
    Current running INSTANCES on server [myserver]:
    ***************************************************
    oracle 44402 1 0 Mar23 ? 00:03:05 asm_pmon_+ASM1
    oracle 159940 1 0 Apr12 ? 00:00:56 ora_pmon_dwhcdb1

    Current running LISTENERS on server [myserver]:
    ***************************************************
    oracle 55774 1 0 Mar23 ? 00:01:28 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
    oracle 55813 1 0 Mar23 ? 00:01:24 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN1 -inherit

    Tnx for helping,
    Ivan


    ReplyDelete
  26. OraDetectorMay 8, 2017 at 11:06 PM

    Mahmoud this work will be perfect if you configure the output to be in html format

    ReplyDelete
  27. AnonymousSeptember 5, 2017 at 4:04 PM

    Hi,
    Script is really impressive :) and I am going to use it. I did some simple modifications. However I am still getting an errors like:
    ./dbreport.sh: line 1: :set: command not found
    ./dbreport.sh: line 889: [: 65,3127083: integer expression expected
    ./dbreport.sh: line 889: [: 92,4768884: integer expression expected
    ./dbreport.sh: line 889: [: 1,83010189: integer expression expected
    ./dbreport.sh: line 889: [: 95,3972318: integer expression expected
    ./dbreport.sh: line 889: [: ,1: integer expression expected
    ./dbreport.sh: line 889: [: 96,2540417: integer expression expected
    ./dbreport.sh: line 889: [: 73,8235938: integer expression expected
    ./dbreport.sh: line 889: [: 30,0611055: integer expression expected
    ./dbreport.sh: line 889: [: ,003051759: integer expression expected
    ./dbreport.sh: line 909: [: selected.: integer expression expected

    I tried to use round functions and few other solutions but it seems to be a different problem. Anybody has any idea, what is wrong ?

    Thanks and Best Regards
    humble oracle dba

    ReplyDelete
  28. Mahmmoud ADELSeptember 7, 2017 at 9:08 AM

    I can see from the list of errors that you have errors starting from line number 1, of course the script should be impressive in this way :-))
    Actually I've know idea what kind of updates you have done to the script, and thus I'll not be able to tell what went wrong. If you don't mind, send a copy of your script after the modification and I'll try to have a look into it.

    Thanks,
    Mahmmoud ADEL

    ReplyDelete
  29. AnonymousSeptember 7, 2017 at 12:00 PM

    Hi Mahmmoud,

    Thank you for quick answer. I appreciate :).

    1. Everywhere where is ps -ef|grep pmon i added "| grep caalbtb", to find only this database name.

    e.g.

    for ORACLE_SID in $( ps -ef|grep pmon|grep caalbtb|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

    2. And changed the oratab "/oracle/home/zw/oratab" to my own including only one database name caalbtb

    last errors:

    oracle@caredbs28:caalbdb$ ./dbalarm.sh
    ./dbalarm.sh: line 900: [: 65,3127083: integer expression expected
    ./dbalarm.sh: line 900: [: 92,4768884: integer expression expected
    ./dbalarm.sh: line 900: [: 1,82972042: integer expression expected
    ./dbalarm.sh: line 900: [: 95,3972318: integer expression expected
    ./dbalarm.sh: line 900: [: ,1: integer expression expected
    ./dbalarm.sh: line 900: [: 96,2540417: integer expression expected
    ./dbalarm.sh: line 900: [: 73,8235938: integer expression expected
    ./dbalarm.sh: line 900: [: 30,037208: integer expression expected
    ./dbalarm.sh: line 900: [: ,003051759: integer expression expected
    ./dbalarm.sh: line 920: [: selected.: integer expression expected

    Script was too long to attach it :(.

    Many Thanks and Best Regards
    humble oracle dba

    ReplyDelete
  30. Mahmmoud ADELSeptember 7, 2017 at 1:01 PM

    Well, generally speaking, you will get that error when the variable value which is getting used in "if" or "case" condition is a float value "xx.xx", not an integer "xx".

    e.g. In case, the variable value during the script execution will be 65.3 you will get above error, and thus you have to convert that float 65.3 to an integer number 65 to get rid of ".3" to be able to use that variable inside "if" and "case" conditions. in order to do so you have to use the following:

    INTEGER_VAR1=${VAR1%.*}

    e.g.
    $ VAR1=65.3
    $ INTEGER_VAR1=${VAR1%.*}

    $echo $INTEGER_VAR1
    65

    I hope I have clearly explained it. This is why I hate math :-)

    ReplyDelete
  31. AnonymousNovember 6, 2017 at 4:54 PM

    Hi Mahmmoud, many thanks for your help. It helped :).

    ReplyDelete
  32. AnonymousDecember 20, 2017 at 5:58 PM

    Hi Adel,

    I'm getting the below error and observed that the issue is happening for multiple Instances having different owners.

    HEALTH CHECK REPORT FOR DATABASE [CATDB] WAS SAVED TO: /home/oracle/BUNDLE_Logs/CATDB_HEALTH_CHECK_REPORT.log
    mkdir: `/home/oraem12c/BUNDLE_Logs': Permission denied
    chown: cannot access `/home/oraem12c/BUNDLE_Logs': Permission denied
    chmod: cannot access `/home/oraem12c/BUNDLE_Logs': Permission denied
    chown: changing ownership of `/tmp/BUNDLE_Logs/audit_records.log': Operation not permitted
    chown: changing ownership of `/tmp/BUNDLE_Logs/CATDB_HEALTH_CHECK_REPORT.log': Operation not permitted
    chown: changing ownership of `/tmp/BUNDLE_Logs': Operation not permitted
    grep: /home/oraem12c/.bash*: Permission denied
    grep: /home/oraem12c/.*profile: Permission denied
    HEALTH CHECK REPORT FOR DATABASE [EMCC] WAS SAVED TO: /tmp/BUNDLE_Logs/EMCC_HEALTH_CHECK_REPORT.log

    Need to modify the script in USR_ORA_HOME should have privileges to access all the Instance owners.

    I'm trying to troubleshoot and let me know if you have any fix already applied.

    ReplyDelete
  33. Mahmmoud ADELDecember 22, 2017 at 5:47 PM

    Honestly, I didn't consider this case during the script design (multiple databases running from different Oracle Homes each Oracle Home is owned by different user). But you can workaround this situation by running different versions of this script on each user's crontab, but make sure you exclude other instances owned by other users.

    e.g.
    In order to run the script against database [EMCC] which is owned by [oraem12c] user and to avoid running it against [CATDB] database which owned by user [oracle], change the following line for the script which will run with user [oraem12c]:
    EXL_DB="\-MGMTDB|ASM|CATDB"

    And vice versa when you run the script with user [oracle]:
    EXL_DB="\-MGMTDB|ASM|EMCC"

    So you will have a script for each user excludes other users databases. No permissions got changed, and thus auditors should be happy.

    ReplyDelete
  34. UnknownJanuary 5, 2018 at 9:30 AM

    Hi Mahmmoud :

    Seen very less people like u.

    Please help me resolve the issue in script.

    ./dbdailychk.sh: line 216: bc: command not found
    10557: Permission denied
    ./dbdailychk.sh: line 460: [: too many arguments
    ./dbdailychk.sh: line 468: [: too many arguments
    ./dbdailychk.sh: line 476: [: too many arguments
    ./dbdailychk.sh: line 535: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 544: [: too many arguments
    ./dbdailychk.sh: line 560: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 580: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 593: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 606: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 624: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 626: [: -ge: unary operator expected
    ./dbdailychk.sh: line 671: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 673: [: -ge: unary operator expected
    ./dbdailychk.sh: line 819: [: too many arguments
    ./dbdailychk.sh: line 846: [: -gt: unary operator expected
    ./dbdailychk.sh: line 1298: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 1300: [: -ge: unary operator expected
    ./dbdailychk.sh: line 1342: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
    ./dbdailychk.sh: line 1344: [: -ge: unary operator expected
    HEALTH CHECK REPORT FOR DATABASE [] WAS SAVED TO: /home/oracle/BUNDLE_Logs/_HEALTH_CHECK_REPORT.log

    [oracle@n2vl-td-kyc13 ~]$ send-mail: warning: inet_protocols: disabling IPv6 name/address support: Address family not supported by protocol
    postdrop: warning: inet_protocols: disabling IPv6 name/address support: Address family not supported by protocol
    ^C
    [oracle@n2vl-td-kyc13 ~]$

    ReplyDelete
  35. Mahmmoud ADELJanuary 5, 2018 at 5:35 PM

    Thanks for passing by. Looks you got a bunch of errors!. But I can see the script didn't manage to to set the right ORACLE_HOME during its execution. So I would recommend you to set it manually inside the script just below the variables section.

    e.g.
    # #########################
    # Variables:
    # #########################
    export ORACLE_HOME=/u01/app/oracle/...

    Regarding the other "send-mail" error, you may need to read the solution in this post:
    https://unix.stackexchange.com/questions/64414/ipv6-support-is-disabled-warnings

    ReplyDelete
  36. UnknownJanuary 10, 2018 at 3:59 PM

    Thnak for your reply. I have set oracle home. Now few errors.

    [oracle@n2vl-td-kyc13 DBA_BUNDLE2]$ ./dbdailychk.sh
    ./dbdailychk.sh: line 148: bc: command not found
    ./dbdailychk.sh: line 1350: /bin/lsnrctl: No such file or directory
    tail: cannot open '/trace/-no_crs_notify.log' for reading: No such file or directory
    grep: invalid option -- '_'
    Usage: grep [OPTION]... PATTERN [FILE]...
    Try 'grep --help' for more information.
    ./dbdailychk.sh: line 1350: /bin/lsnrctl: No such file or directory
    tail: cannot open '/trace/-no_crs_notify.log' for reading: No such file or directory
    [oracle@n2vl-td-kyc13 DBA_BUNDLE2]$

    ReplyDelete
  37. Mahmmoud ADELJanuary 11, 2018 at 11:37 AM

    Hi Mac,

    You're using a very old version of dbdailychk script which is under DBA_BUNDLE version 2, the latest release is now 4.1, kindly download it from this post:
    http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

    Every month a new release of DBA Bundle get released with new features and bug fixes.

    In case the script still giving the same error, kindly export your ORACLE_HOME as shown in my last comment, and keep me updated in case you get any errors.

    ReplyDelete
  38. VivekFebruary 12, 2018 at 10:59 AM

    Hello Adel,

    Is it possible to provide Top 10 fragmented tables and inactive session count with threshold limit for the Oracle version 11g in RHEL 6.5 Version?

    Thanks in advance.

    ReplyDelete
  39. Oracle12cApril 10, 2018 at 5:25 PM

    Hello Adel,

    I have not tested your script. But looking at the script shows the effort you put in putting this together. Excellent work. I have one question about handling the ORACLE HOME for 12.2 as they do not have the oratab entries due to the Flex ASM feature.

    I plan to test this on Linux. Will give it a try and let you know if I see any issues.

    Thanks,
    Venky

    ReplyDelete
  40. Mahmmoud ADELApril 11, 2018 at 7:26 PM

    Ideally, this not supposed to make any problem. The script will determine the right ORACLE_HOME using different ways one of them is by checking /etc/oratab, in case, ORACLE_HOME information is not available in oratab the script will look go through other methods to get it.
    But rule number 1, as you already mentioned, the script should be tested on a similar test environment before it gets deployed on production.

    ReplyDelete
  41. UnknownMay 8, 2018 at 9:26 AM

    Searching for Oracle Health Check? Call us 1-800-450-8670 to Oracle Database System
    In the event that you distinguish moderate Oracle inquiries or lazy execution of your Oracle Database at that point handle these issues by reaching to Cognegic's Remote DBA Experts. Here we consistently check your setup setting or execution measurements and give you most ideal way to tackle your Oracle related issues. You can take our best help by dialing this number 1-800-450-8670 and ask your inquiries to Online Oracle DB Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  42. Rahul Malode Oracle DBAMay 27, 2018 at 7:32 AM

    Is it possible to run the script from Central Inventory and connect to each database using TNS and give the results?

    ReplyDelete
  43. Mahmmoud ADELMay 27, 2018 at 12:08 PM

    Well, the short answer it will not work with this script, as it's mainly dependant on OS authentication to connect in a passwordless fashion to the local DBs on the same server.

    ReplyDelete
  44. Rahul Malode Oracle DBAMay 27, 2018 at 7:28 PM

    Thanks for the update also is it possible to get the backcup status(RMAN/exp) report from the local server using this script.

    ReplyDelete
  45. Rahul Malode Oracle DBAMay 27, 2018 at 7:44 PM

    This comment has been removed by the author.

    ReplyDelete
  46. Rahul Malode Oracle DBAMay 27, 2018 at 7:45 PM

    This comment has been removed by the author.

    ReplyDelete
  47. Rahul Malode Oracle DBAMay 28, 2018 at 7:59 AM

    I have executed the script,but its not showing db_name , db_status(up/down), listner_name, listener_status(up /down), OS files system.

    ReplyDelete
  48. Mahmmoud ADELMay 28, 2018 at 12:01 PM

    Regarding RMAN backup status, there is a section in the report shows RMAN BACKUP OPERATIONS in the last 24 hours.

    The script only runs against up & running databases, listener information is not within the scope of this script, you can try another script called dbalarm that can monitor those elements along with their logs:
    http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html

    ReplyDelete
  49. Sanket DJune 18, 2018 at 4:28 PM

    Hi Mahmmoud,

    I am getting below error while executing the script:

    bash: $'\r': command not found
    bash: dbdailychk.sh line 102: syntax error near unexpected token `newline'
    'bash: dbdailychk.sh line 102: ` echo ;;

    My OS version is Linux el7 64 bit.

    Please help me in this

    Regards,
    Sanket

    ReplyDelete
  50. Mahmmoud ADELJune 19, 2018 at 8:52 AM

    Hi Sanket,

    Most probably something went wrong when you copied the script to your machine. Bad character, lost character! Try to re-copy the script again and compare it with the GitHub version shown at the bottom of the post.

    ReplyDelete
  51. Sanket DJune 19, 2018 at 3:06 PM

    Hi Mahmmoud,

    Tried with downloading the script 4-5 times but still getting the same error.
    Can you please help?

    Regards,
    Sanket

    ReplyDelete
  52. Sanket DJune 19, 2018 at 3:07 PM

    Hi Mahmmoud,

    Tried with downloading the script 4-5 times but still getting the same error.
    Can you please help?

    Regards,
    Sanket

    ReplyDelete
  53. Mahmmoud ADELJune 20, 2018 at 1:58 PM

    Hi Sanket,
    Looks the script is failing in the first "case" block, let's troubleshoot it by replacing the following "case" block:


    case ${MAIL_LIST} in "youremail@yourcompany.com")
    echo
    echo "##############################################################################################"
    echo "You Missed Something :-)"
    echo "In order to receive the HEALTH CHECK report via Email, you have to ADD your E-mail at line# 90"
    echo "by replacing this template [youremail@yourcompany.com] with YOUR E-mail address."
    echo "DB HEALTH CHECK report will be saved on disk..."
    echo "##############################################################################################"
    export SQLLINESIZE=165
    echo;;
    *) export SQLLINESIZE=200
    esac



    With this line:

    export SQLLINESIZE=200

    In case it's still throwing errors and I guess it will do so :-) , I would recommend to have a remote session with you, as I think there is something unique in your environment causing this error. Please email me on mahmmoudadel@hotmail.com

    ReplyDelete
  54. Sanket DJune 24, 2018 at 9:21 AM

    Hi mahmmoud,

    Even after changing the CASE block, error persists. I have sent you an email on your personal id as well, please let me know your preferable time for remote session.

    Regards,
    Sanket

    ReplyDelete
  55. Sanket DOctober 16, 2018 at 4:12 PM

    Excellent Script Adel

    ReplyDelete
  56. AnonymousJanuary 27, 2019 at 7:58 PM

    Sometimes the email report size comes larger than 5mb and get blocked by the exchange, any idea how to control the report size?

    Thanks, Amit

    ReplyDelete
  57. Mahmmoud ADELJanuary 28, 2019 at 10:36 AM

    Then you can disable a few advisors outputs, which make the email size go big.
    I recommend you to start with disabling showing of the following advisor (SQL Tuning Advisor & Segment Advisor) results in the report by setting their flag in THRESHOLD section to N:

    # #########################
    # THRESHOLDS:
    # #########################
    # Modify the THRESHOLDS to the value you prefer:
    ....
    SHOWSQLTUNINGADVISOR=N # SHOW SQL TUNING ADVISOR RESULTS IN THE REPORT [DB]
    SHOWSEGMENTADVVISOR=N # SHOW SEGMENT ADVISOR RESULTS IN THE REPORT [DB]

    Some databases have a huge number of audit records and you may not have a need to look into it on a daily basis so you can disable showing audit records in the report by setting its flag in THRESHOLD section to N:

    CHKAUDITRECORDS=N # CHECK DATABASE AUDIT RECORDS [increases CPU Load] [DB]

    As you can see you can easily control the data appear in the report by playing with the values in the THRESHOLD section, but first, read the control comment carefully before editing.

    ReplyDelete
  58. AnonymousFebruary 1, 2019 at 5:02 PM

    I have a problem with copying the bundle tar file between the servers because the route "including sftp" is blocked. can you suggest a away can over come this situation?

    ReplyDelete
  59. Mahmmoud ADELFebruary 3, 2019 at 4:31 PM

    In case you cannot copy the tar file between servers:

    1- convert the tar file to binary text file using this command:
    od -An -vtx1 DBA_BUNDLE5.tar > DBA_BUNDLE5.txt

    2- create an empty file called DBA_BUNDLE5.txt on the target server.

    3- Copy the content of DBA_BUNDLE5.txt from the source server and paste it to the target server DBA_BUNDLE5.txt, you may end up with copying 60k rows or more, so try to make the session buffer big enough to let you copy all the contents to the clipboard in one go, it's an option called "lines of scrollback" in Putty set it to 70000 or more.

    4- Convert the text binary file on the destination server to a tar file:
    LC_ALL=C tr -cd 0-9a-fA-F < DBA_BUNDLE5.txt | xxd -r -p > DBA_BUNDLE5.tar

    Done your tar file is ready.

    ReplyDelete
  60. VageehaApril 25, 2019 at 6:25 PM

    Hi Adel, Thanks for this script. I'm modifying script to pass my username and password as I don't have sysdba priviege yet...but can you tell me how long will the execution of this script will take in Non-Prod environment.Here I have cluster setup but not RAC and I'm ignoring other instances as I'm just trying to run it on only one instance.
    Thanks in advance :)

    ReplyDelete
  61. Mahmmoud ADELApril 26, 2019 at 10:20 AM

    Hi Vageeha,
    The script ideally takes less than a minute to run. You can ignore the instances you don't want the script to run against by feeding them to EXL_DB parameter at line# 150. i.e. in case you want the script to ignore "SALES" and "hr" instances you can modify the parameter like this:
    EXL_DB="\-MGMTDB|ASM|SALES|hr"

    ReplyDelete
  62. VageehaMay 16, 2019 at 10:50 AM

    Hi Adel,
    Thanks for your reply previously.My script is 99% done for my usage but i am trying to reduce my space between two topics for example,in your output space is high above queries running for more than an hour i hope you understand this and guide me how can i reduce or increase it as i wish.
    Thanks again :)

    ReplyDelete
  63. VageehaMay 16, 2019 at 10:55 AM

    Hi Adel,
    Sorry i missed this point in my previous post... incase if we don't have output for any check can we say it with no rows selected or something instead of leaving it blank?
    Thank you!

    ReplyDelete
  64. Mahmmoud ADELMay 16, 2019 at 1:19 PM

    Hi Vageetha,
    In order to adjust the time of long running queries to be reported you have to change the following line# 1327:
    last_call_et/60/60 "DURATION_HOURS"

    let's say you want to report the queries that run for more than 30 min "instead of 60min) you adjust that line like this:
    last_call_et/30/60 "DURATION_HOURS"

    and so on ...
    But if you really interested in getting long running queries to be reported in a real time you have to use this script instead:
    https://dba-tips.blogspot.com/2018/04/report-long-running-queries-long-active.html

    where you can easily adjust the time in the threshold parameter section:
    EXEC_TIME_IN_MINUTES=60 # Report Sessions running longer than N minutes [Default is 60 minutes].

    Don't forget to provide your Email to this parameter:
    EMAIL="youremail@yourcompany.com"

    and schedule it in the crontab to run let's say every 5 minutes */5

    Why I'm not recommending you to use use DB Health Check script for this purpose, because it supposed to run only one time a day due to its overhead on the DB.

    Regarding the Blank output in the report, this means the feature is not being used in your database. i.e. if FRA section showing No Rows Selected this means you are not using Flash Recovery Area in your DB. same thing for the Advisors and other blank output if found.

    ReplyDelete
  65. Sanket DFebruary 10, 2020 at 4:49 PM

    Hi Adel,

    How can we run this script if we are not able to sudo to ora or oracle user?

    Due to contractual obligation i would not be able to run it with ora,oracle, grid or root user as i can sudo to them. I have my os id created where i have dba privileges and generally do all the stuff using that only.

    Could you please help me in running your wonderful script for my environment?

    Regards,
    Sanket D

    ReplyDelete
  66. Mahmmoud ADELFebruary 10, 2020 at 6:05 PM

    Before I suggest anything, Is your user id in DBA group? Can you log in to the database as sysdba:
    sqlplus "/ as sysdba"

    ReplyDelete
  67. Sanket DFebruary 11, 2020 at 11:20 AM

    Yes Adel, I have that...

    ReplyDelete
  68. Mahmmoud ADELFebruary 11, 2020 at 6:43 PM

    Fantastic! I need you to do two things:

    1- Open the script, under "Getting ORACLE_HOME" section, Replace this line:
    USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`

    With this line:
    USR_ORA_HOME=~

    2- Under your Linux User ID set ORACLE_HOME variable to the right path of ORACLE_HOME on that server:

    i.e. "suppose that your ORACLE_HOME location is: /u01/app/oracle/product/11.2.0/db_1"

    # vi ~/.bash_profile

    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

    In case it's still failing, please paste the error message as well.

    ReplyDelete
  69. Sanket DFebruary 11, 2020 at 7:23 PM

    Sure let me try this and get back to you

    ReplyDelete
  70. Sanket DFebruary 11, 2020 at 7:26 PM

    One more thing ... i have multiple oracle homes for multiple db's e.g. 11g has different and 12c has different in that case, do i have to export it one by one? or how it can be handled?

    ReplyDelete
  71. SantoshFebruary 28, 2020 at 10:39 AM

    This comment has been removed by the author.

    ReplyDelete
  72. AnonymousMay 28, 2020 at 10:49 AM

    The script hang in this stage when running it for the first time:

    [Grid Infrastructure Setup Detected] Locating Grid Infrastructure ALERTLOG ...
    Checking GRID INFRASTRUCTURE ALERTLOG ...

    What is the problem?

    ReplyDelete
  73. Mahmmoud ADELMay 28, 2020 at 9:07 PM

    This is applicable to dbalarm script, I've already replied on the dbalarm post:
    "cancel the current execution by pressing Ctrl+c
    And execute the script again, you shouldn't face this issue again."

    ReplyDelete
  74. KumarNovember 9, 2020 at 6:15 PM

    Wow!!!! WoW!!!! can't believe, The script is worked as mentioned. And really saved our 1 hr time for spending everyday manual healthcheck.

    Really appreciate your effort on this.

    ReplyDelete
  75. stephuricJanuary 3, 2021 at 5:19 PM

    hi Mahmmoud, happy new year, you re doing a good job i must confess. this script will be wonderful and will also save me alot of time. i have tested it on test environment. but i was having some challenges: i have sent you an e-mail already but let me past my out here:


    ^^^^^^^^^^^^^^^^^
    Local_Filesystem:
    ^^^^^^^^^^^^^^^^^



    ^^^^^^^^^^^^^^^^^^^^^^^
    ORACLE_RESTART_SERVICES:
    ^^^^^^^^^^^^^^^^^^^^^^^
    Resource_Name State
    ------------------------------------- ---------------------
    ora.DATA.dg ONLINE on devdbr19
    ora.FRA.dg ONLINE on devdbr19
    ora.LISTENER.lsnr INTERMEDIATE on devdbr19
    ora.RECO1.dg ONLINE on devdbr19
    ora.RECO2.dg ONLINE on devdbr19
    ora.asm ONLINE on devdbr19
    ora.cssd ONLINE on devdbr19
    ora.evmd ONLINE on devdbr19
    ora.r19uatdb.db ONLINE on devdbr19
    ora.t24live.db INTERMEDIATE on devdbr19

    ^^^^^^^^^^^^^^^^^^^^^^^^
    DATABASE_SERVICES_STATUS:
    ^^^^^^^^^^^^^^^^^^^^^^^^
    PRCD-1120 : The resource for database identifier could not be found.
    PRCR-1001 : Resource ora.identifier.db does not exist


    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    List_of_All_Running_Instances:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    oraprod 39452964 1 0 Dec 27 - 0:26 ora_pmon_T24LIVE
    gridprod 19661670 1 0 Nov 29 - 0:26 asm_pmon_+ASM
    oraprod 38404990 1 0 Dec 20 - 1:45 ora_pmon_R19UATDB


    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    List_of_All_Running_Listeners:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    gridprod 44368404 1 0 Oct 05 - 19:17 /u01/app/grid/product/19.3.0/grid/bin/tnslsnr LISTENER -inherit


    ^^^^^^^^^^^^^^^^^^^^^^^
    List_of_All_Open_Ports:
    ^^^^^^^^^^^^^^^^^^^^^^^


    Tablespaces Size

    SP2-0158: unknown SET option "text/css">"
    SP2-0552: Bind variable "AUTO" not declared.
    ASM STATISTICS

    SP2-0158: unknown SET option "text/css">"
    SP2-0552: Bind variable "AUTO" not declared.
    FRA STATISTICS

    SP2-0158: unknown SET option "text/css">"
    FRA SIZE

    SP2-0158: unknown SET option "text/css">"
    SP2-0552: Bind variable "AUTO" not declared.
    FRA COMPONENTS

    SP2-0158: unknown SET option "text/css">"
    select * from v$flash_recovery_area_usage * ERROR at line 1: ORA-01507: database not mounted
    DATABASE GROWTH

    SP2-0158: unknown SET option "text/css">"
    SP2-0158: unknown SET option "text/css">"
    from DBA_HIST_SNAPSHOT) b * ERROR at line 19: ORA-06550: line 19, column 18: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 7, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 22, column 63: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 22, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 23, column 47: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 23, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 25, column 27: PLS-00201: identifier 'SNAP_TIME Database Size(GB)' must be declared ORA-06550: line 25, column 5: PL/SQL: Statement ignored ORA-06550: line 27, column 42: PLS-00364: loop index variable 'ROW' use is invalid ORA-06550: line 27, column 13: PL/SQL: Statement ignored ORA-06550: line 28, column 30: PLS-00364: loop index variable 'ROW' use is invalid ORA-06550: line 28, column 9: PL/SQL: Statement ignored
    Active Incidents

    ReplyDelete
  76. stephuricJanuary 3, 2021 at 5:41 PM

    note: its on AIX, it will work i think so, but may be something needs to be twik

    ReplyDelete
    Replies
    1. Mahmmoud ADELJanuary 3, 2021 at 5:47 PM

      No, actually this script is not compatible with AIX. Sorry!

      Delete
  77. Mahmmoud ADELJanuary 3, 2021 at 5:46 PM

    Hi stephuric,
    Thanks for passing by, actually this script is not compatible with container type database, as it's login directly using / as sysdba

    ReplyDelete
  78. stephuricJanuary 3, 2021 at 6:28 PM

    thanks for your response here. initially, when I ran the script, the OS report came out fine but the Database checks all came out with error:
    SP2-0158: unknown SET option “text/css”>”
    and
    SP2-0552: Bind variable “AUTO” not declared

    ReplyDelete
  79. stephuricJanuary 3, 2021 at 6:30 PM

    Then, I removed from the script, then some of the database check metrics came out but some did not with only error SP2-0552: Bind variable “AUTO” not declared. What do I need to do to mitigate against this error, as it seems it is working on AIX, you have done a great work already

    ReplyDelete
    Replies
    1. stephuricJanuary 3, 2021 at 6:31 PM

      i removed:---------style type="text/css"

      Delete
    2. Mahmmoud ADELJanuary 3, 2021 at 6:53 PM

      Would you mind running the following code piece by oracle user from the shell, and let me know the output:

      ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
      prompt
      select platform_name,CDB from v\$database;
      exit;
      EOF


      Delete
    3. stephuricJanuary 3, 2021 at 7:05 PM

      This comment has been removed by the author.

      Delete
    4. stephuricJanuary 3, 2021 at 7:06 PM

      This comment has been removed by the author.

      Delete
    5. stephuricJanuary 3, 2021 at 7:11 PM


      PLATFORM_NAME
      --------------------------------------------------------------------------------
      CDB
      ---
      AIX-Based Systems (64-bit)
      NO

      Delete
  80. stephuricJanuary 3, 2021 at 7:13 PM

    i have to follow it like this:

    bash-5.0$ cd $ORACLE_HOME/bin
    bash-5.0$ sqlplus -S "/ as sysdba" < prompt
    > select platform_name,CDB from v\$database;
    > exit
    > EOF


    PLATFORM_NAME
    --------------------------------------------------------------------------------
    CDB
    ---
    AIX-Based Systems (64-bit)
    NO

    ReplyDelete
  81. Mahmmoud ADELJanuary 3, 2021 at 7:33 PM

    Would you mind executing the script from the shell and send me all the output (not the report) to my email to investigate it further: mahmmoudadel@hotmail.com

    ReplyDelete
  82. stephuricJanuary 3, 2021 at 8:52 PM

    i have forwarded it to your mail as requested. am also available if you will be chanced to have a session with me

    ReplyDelete
  83. stephuricJanuary 4, 2021 at 11:57 AM

    good day mahmmoud, trust you re doing well today? thanks for your support and assistant here yesterday, still waiting to hear from you.

    ReplyDelete
  84. FernandoJanuary 5, 2021 at 12:28 PM

    Hello, with what user should we run this script?

    I get a lot of

    175684: Permission denied

    ReplyDelete
    Replies
    1. Mahmmoud ADELJanuary 5, 2021 at 4:02 PM

      You should run it with Oracle user, but you can ignore that error if the script is generating the report successfully. I'll sort this out in the next release.

      Delete
    2. FernandoJanuary 11, 2021 at 4:28 PM

      It does not seem to work for me...

      Delete
    3. Mahmmoud ADELJanuary 11, 2021 at 5:50 PM

      Could you please share the errors you are getting?

      Delete
    4. FernandoJanuary 12, 2021 at 10:45 AM

      Only the error I told you above.

      I don't get any email from the shell.

      I have many DBs on the server and I just get 2 HEALTH_CHECK_REPORT.log for 2 DBs and then I get a 0_HEALTH_CHECK_REPORT.log for all the other DBs.

      Delete
    5. Mahmmoud ADELJanuary 12, 2021 at 8:26 PM

      Are you getting this issue on that server only, or other servers are having the same issues with this script?

      Delete
    6. FernandoJanuary 13, 2021 at 12:57 PM

      Just tried it in another server, and same problem, not a single report was generated, only 0_HEALTH_CHECK_REPORT.log

      This is exadata by the way...

      Delete
    7. FernandoJanuary 13, 2021 at 1:24 PM

      Sorry, some files were generated, but most of the DBs have a 0_HEALTH_CHECK_REPORT.log generated and no emails sent.

      Delete
    8. Mahmmoud ADELJanuary 13, 2021 at 2:48 PM

      Is the Email service is configured on this server. i.e. can you send yourself an Email from the shell?
      Also, what is the DB version and whether it's CDB or non-CDB?

      Delete
    9. FernandoJanuary 13, 2021 at 3:02 PM

      Yes, mail works on the servers although we normally use mailx.

      These are versions 12c and 19c. Some have CDBs and others don't have.

      Delete
    10. Mahmmoud ADELJanuary 13, 2021 at 4:04 PM

      You may need to export the smtp in the script as well if you have one:
      e.g.
      export smtp="smtp.mycompany.com"

      By the way, the script will not work against CDBs, because it's using "/ as sysdba" to connect.

      Delete
    11. FernandoJanuary 13, 2021 at 4:38 PM

      I never need to do that with other shells.

      Oh too bad then...

      Delete
  85. DaveJanuary 6, 2021 at 8:26 PM

    I'm getting this error:
    /oracle/11.2.0.3/dbhome_1/bin/sqlplus: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

    ReplyDelete
    Replies
    1. Mahmmoud ADELJanuary 7, 2021 at 11:10 AM

      Please add this line in the script exactly before this section:

      # #############################
      # Getting hostname in lowercase:
      # #############################

      export LD_LIBRARY_PATH=${ORACLE_HOME}/lib

      Delete
  86. Syed Waheed (DBA)November 4, 2021 at 2:15 PM

    Dear Adel,

    Is there a way to get the output in single HTML file locally on the server. We are not allowed to configure mail on the server.

    - Syed

    ReplyDelete
    Replies
    1. Mahmmoud ADELNovember 4, 2021 at 7:32 PM

      Hi Syed,

      Thanks for passing by. When you don't provide any email id inside the script it will write the report by default on disk and once it completes, it will prompt the complete path of the report:

      ##############################################################################################
      You Missed Something! :-)
      In order to receive the HEALTH CHECK report via Email, set your E-mail at line# 110
      by replacing this template [youremail@yourcompany.com] with YOUR E-mail address.
      DB HEALTH CHECK report will be saved on disk...
      ##############################################################################################

      Delete
  87. healthcheckuppackageDecember 8, 2021 at 5:05 PM

    Hello Very Nice your Blog and Keep Update for more information about.>Health Checkup Packages In Delhi

    ReplyDelete
  88. MB MaxMarch 23, 2022 at 7:15 PM

    Hi Mahmmoud ADEL, Thanks for this. I got an error below. what do you think might be the issue

    [dbdailychk Script Started ...]
    ./dbdailychk.sh: line 2259: syntax error near unexpected token `done'
    ./dbdailychk.sh: line 2259: `        done'

    ReplyDelete
    Replies
    1. Mahmmoud ADELMarch 24, 2022 at 7:37 PM

      Hi MB,

      Thanks for passing by, the script works fine on Linux environments, which OS or Linux flavor and version did you execute the script against?
      Also, did you change anything inside the scripts other than EMAIL section?

      Delete
  89. Healthchecks360August 11, 2022 at 9:25 AM

    I feel that is one of the such a vital information for me And iam glad reading your article. Pretty neatly donehealth check up packages full body check up dubai

    ReplyDelete
  90. AnonymousAugust 23, 2023 at 7:46 AM

    hi Adel.. It's a great script. even after 6-7 years, people are able to make the most of it. Just wanted to ask, if any DBA wants to do the health assessment of any DB. Is there a way, you could create a script for that? Or any suggestions or advice would help.

    ReplyDelete
  91. E8EB9Yousef3E45DDecember 30, 2023 at 3:01 AM

    3735A
    düzce görüntülü sohbet kadınlarla
    maraş rastgele görüntülü sohbet ücretsiz
    ısparta Görüntülü Sohbet Uygulama
    bilecik nanytoo sohbet
    bilecik sesli sohbet odası
    nevşehir nanytoo sohbet
    Rize En İyi Sesli Sohbet Uygulamaları
    çankırı görüntülü canlı sohbet
    random görüntülü sohbet

    ReplyDelete
  92. 4ECA5Philip035E7January 20, 2024 at 7:01 AM

    E092D
    Binance Nasıl Oynanır
    Telcoin Coin Hangi Borsada
    Kripto Para Madenciliği Nedir
    Shinja Coin Hangi Borsada
    Coin Üretme Siteleri
    Hamster Coin Hangi Borsada
    Lunc Coin Hangi Borsada
    Binance Kaldıraçlı İşlem Nasıl Yapılır
    Binance Referans Kodu

    ReplyDelete
  93. 127EDArmandoD2A45February 11, 2024 at 1:42 AM

    7B920
    zkswap
    uniswap
    phantom wallet
    eigenlayer
    bscpad
    pancakeswap
    avalaunch
    trezor suite
    DefiLlama

    ReplyDelete
  94. AnonymousNovember 5, 2024 at 10:38 AM

    <a href="https://elasraa.com/%d8%b4%d8%b1%d9%83%d8%a9-%d8%aa%d9%86%d8%b8%d9%8a%d9%81-%d8%a7%d9%81%d8%b1%d8%a7%d9%

    ReplyDelete
  95. AnonymousNovember 29, 2024 at 7:40 PM

    <a href="https://almthaly-dammam.com/%d8%b4%d8%b1%d9%83%d8%a9-%d9%85%d9%83%d8%a7%d9%81%d8%ad%d8%a9-%d8%ad%d

    ReplyDelete

Subscribe to: Post Comments (Atom)