Scripts details to monitor database Check list
##############################################################################
oracle@02:~> crontab -l
########### Rman Backup Script #############
05 04 * * * /u04/MasterDB/scripts/rman_DB_FULL.sh 2>&1 >>/u04/MasterDB/rmanbackup_PRODB/RMAN_PRODB_FULL.log
########### Rman Backup Status on Mail #############
35 06 * * * /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
#### OS Disk Alert #################
#0,15,30,45 * * * * /u04/MasterDB/scripts/disk_threshod.sh
#05 05 * * * /u04/MasterDB/scripts/mountpoint_check.sh
#0,15,30,45 * * * * /u04/MasterDB/scripts/DR_check/diskalert_threshold.sh
#### Sar Output ######################
26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
#### DR Check ########################
0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
#### Tablespace Alert ###############
0,15,30,45 * * * * /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh > /u04/MasterDB/scripts/daily_health_check/ts_alert.log 2>&1
#### Daily Database Health Check ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/daily_healthcheck.sh
30 05 * * 1 /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
#### Database Health Check ###################
#30 05 * * * /home/oracle/dbhealth.sh
#### Archive I/O ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#### Alert log monitor #############
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/alert_monitor.sh > /u04/MasterDB/scripts/alert_log/alert_monitor.log1 2>&1
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/check_alert_log.sh > /u04/MasterDB/scripts/alert_log/alert_monitor11.log1 2>&1
##################### Stats Gathers Schema ###################################
0 4 * * 0 /u04/MasterDB/scripts/SMRPRODB_statsgather.sh
################################ SMR DATABASE STARTUP DOWN CHECK #######################
5,10,15,20,25,30,35,40,45,50,55,00 * * * * /u04/MasterDB/scripts/smr_prod_check_instance.sh > /u04/MasterDB/scripts/smr_prod_check_alert_monitor.log 2>&1
oracle@test02:~>
#### Expdp Backup ####################
25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com
oracle@02:~> crontab -l
########### Rman Backup Script #############
05 04 * * * /u04/MasterDB/scripts/rman_DB_FULL.sh 2>&1 >>/u04/MasterDB/rmanbackup_PRODB/RMAN_PRODB_FULL.log
########### Rman Backup Status on Mail #############
35 06 * * * /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
#### OS Disk Alert #################
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/mount_point.sh
#### Expdp Backup ####################
25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com
#### Sar Output ######################
26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
#### DR Check ########################
0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
0,15,30,45 * * * * /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh > /u04/MasterDB/scripts/daily_health_check/ts_alert.log 2>&1
0,15,30,45 * * * * /u04/MasterDB/scripts/check_tablespace.sh > /u04/MasterDB/scripts/daily_health_check/ts_alert1.log 2>&1
#### Health Check ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/daily_healthcheck.sh
30 05 * * 1 /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
#30 05 * * * /home/oracle/dbhealth.sh
#### Archive I/O ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#### Alert log monitor #############
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/alert_monitor.sh > /u04/MasterDB/scripts/alert_log/alert_monitor.log1 2>&1
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/check_alert_log.sh > /u04/MasterDB/scripts/alert_log/alert_monitor11.log1 2>&1
##################### Mount Point#########################
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/mount_point.sh
##################### Stats Gathers Schema ###################################
0 4 * * 0 /u04/MasterDB/scripts/PRODB_statsgather.sh
################################ DATABASE STARTUP DOWN CHECK #######################
5,10,15,20,25,30,35,40,45,50,55,00 * * * * /u04/MasterDB/scripts/prod_check_instance.sh > /u04/MasterDB/scripts/prod_check_alert_monitor.log 2>&1
#######################################################################
################################## RMAN BACKUP #####################################
oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.sh
ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1
DD=`date +%d%m%y`
export ORACLE_HOME
ORACLE_SID=PRODB
export ORACLE_SID
Rman_Log=/u04/MasterDB/scripts/"$ORACLE_SID"_bkp${DD}.log
touch $Rman_Log
/u02/app/oracle/product/12.1.0/dbhome_1/bin/rman target test/test1234 @/u04/MasterDB/scripts/rman_PRODB_FULL.rcv >> /u03/rmanbackup_PRODB/rman_RODB_FULL.log
oracle@test02:~>
oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.rcv
run
{
allocate channel c1 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c2 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c3 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c4 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
crosscheck backup;
crosscheck archivelog all;
report obsolete;
delete noprompt obsolete;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup full database format '/u03/rmanbackup_PRODB/%d_%U.bckp' plus archivelog;
copy current controlfile to '/u03/rmanbackup_PRODB/control01_%d_%T_%U.bak';
backup current controlfile for standby format '/u03/rmanbackup_RODB/standbycontrol_%d_%T_%U.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
oracle@test02:~>
############################ Rman Backup Status ################################################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.log
printf "to:varun.yadav@abc.com\n" > $DR_log
#printf "cc:abc@abccom,core@abc.com\n" >> $DR_log
printf "subject: SMRPRODB Database RMAN Backup STATUS AND DETAILS " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT RMAN backup and Size Details
PROMPT ==================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -3;
select ctime "Date"
, decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
, bsize "Size MB"
from (select trunc(bp.completion_time) ctime
, backup_type
, round(sum(bp.bytes/1024/1024),2) bsize
from v\$backup_set bs, v\$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
#### Disk Alert #################
oracle@test02:~> cat /u04/MasterDB/scripts/disk_threshod.sh
# Shell script to monitor or watch the disk space
# It will send an email to $ADMIN, if the (free avilable) percentage
# of space is >= 90%
# -------------------------------------------------------------------------
# Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# ----------------------------------------------------------------------
# Linux shell script to watch disk space (should work on other UNIX oses )
# SEE URL: http://www.cyberciti.biz/tips/shell-script-to-watch-the-disk-space.html
# set admin email so that you can get email
ADMIN="varun.yadav@abc.com"
# set alert level 90% is default
ALERT=90
df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;
do
#echo $output
usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $2 }' )
if [ $usep -ge $ALERT ]; then
i echo "Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |
mail -s " Alert: Almost out of disk space" $usep $ADMIN
fi
done
oracle@test02:~>
########################### EXPDP ###########################
oracle@test02:~> cat /u04/MasterDB/scripts/expdp_smrprodb_full.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
expdp abc/abc@PRODB dumpfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).dmp directory=expdp logfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_PRODB parallel=2
#Moving log files
mv /u04/MasterDB/expdp/expdp*.log /u04/MasterDB/expdp/expdp_fulldb_backup_logs
#Granting permissions for Dump files
chmod -R 775 /u04/MasterDB/expdp/
#Compressing files
gzip /u04/MasterDB/expdp/*.*
#Removing 2 days old dump files
#find /u04/MasterDB/expdp/ -mtime +2 -exec rm {} \;
oracle@test02:~>
#### Sar Output ######################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
DD=`date +%d%m%y`
cd /u04/MasterDB/scripts/daily_health_check/sar_output
touch /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log
sar 3600 24 > /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log
cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log >> /u04/MasterDB/scripts/daily_health_check/sar_output/sar.tmp
mailx -s " Hourly OS CPU Utilization SAR Details " abc@abc.com< sar.tmp
rm -rf sar.tmp
exit
oracle@test02:~>
#### DR Check ########################
oracle@test02:~> cat /u04/MasterDB/scripts/DR_check/dr_check.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/DR_check/DR_Status.log
printf "to:abc@abc.com\n" > $DR_log
#printf "cc:abc@abc.com\n" >> $DR_log
printf "subject: DR HOURLY STATUS REPORT " >> $DR_log
printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~> /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log1=/u04/MasterDB/scripts/daily_health_check/tb_check.log
printf "to:abc@abc.com\n" > $DR_log1
#printf "cc:core_oradba@mind-infotech.com\n" >> $DR_log1
printf "subject: PRODB Tablespace HOURLY STATUS REPORT\n" >> $DR_log1
#printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log1
sqlplus -s "/as sysdba" <<EOF >> $DR_log1
et echo off
set feed off
set verify off
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
COLUMN TABLESPACE FORMAT A25
select name DBNAME from v\$database;
prompt =======================================================================
prompt TABLESPACE UTILIZATION
prompt =======================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace
/
prompt ========================================================================
prompt Tablespace More Than 85% used
prompt ========================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
set feedback on
set verify on
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/PRODB/ArchiveLog
/
EOF
/usr/sbin/sendmail -t < $DR_log1
oracle@test02:~>
######### Tablespace Threshold Alert ###################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh
#!/bin/sh
export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=PRODB
cd /u04/MasterDB/scripts/daily_health_check
logfile=/u04/MasterDB/scripts/daily_health_check/Tablespace_alert.log
cnt1=`ps -ef|grep pmon|grep $ORACLE_SID|wc -l`
if [ $cnt1 -eq 1 ];
then
sqlplus -s "/as sysdba" > /dev/null << EOF
spool $logfile
@/u04/MasterDB/scripts/daily_health_check//tablespace_alert.sql
spool off
exit
EOF
# If there are more then these two lines in the output file, mail it.
count=`cat $logfile|wc -l`
#echo $count
if [ $count -ge 4 ];
then
mailx -s "ALERT: 90 % Threshold Alert FOR PROD Database "coredba@abc.com <$logfile
fi
fi
oracle@test02:~>
#### ######Database Health Check ###################
oracle@test02:~> cat /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/weekly_health_check/SMRWeeklyreport.log
printf "to:abc@abc.com\n" > $DR_log
printf "subject: Weekly SMRPRODB Database Healthcheck Details " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
#column date_column new_value today_var
/
select to_char(sysdate,'yyyy-Mon-dd_hh-miAM')date_column from dual
/
spool /u04/MasterDB/scripts/daily_health_check/SMRPROD_&today_var.txt
PROMPT =============================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT =============================================================
PROMPT CURRENT DATE and TIME
PROMPT ======================
Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;
PROMPT
PROMPT DATABASE NAME
PROMPT =================
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
PROMPT
PROMPT Users logged information
PROMPT ===============================
select status,count(*) from v\$session
--where username not in ('SYS','SYSTEM')
group by status;
PROMPT
PROMPT Previous day archive logs generation
PROMPT ======================================
select count(*) from v\$archived_log
where to_char(COMPLETION_TIME,'DD-Mon-YY')=to_char(sysdate-1,'DD-Mon-YY');
PROMPT
PROMPT latest Archive No.
PROMPT ======================================
archive log list
PROMPT
PROMPT CONTROL FILES
PROMPT ==================
select * from v\$controlfile;
PROMPT
PROMPT LOG FILE GROUPS
PROMPT ==================
set lines 200
col status for a7
col type for a7
col member for a60
col IS_RECOVERY_DEST_FILE for a3
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v\$logfile;
PROMPT
PROMPT
PROMPT LOG FILE MEMBERS
PROMPT ==================
select * from v\$log;
PROMPT
PROMPT SIZE OF DATABASE Daily
PROMPT ==================
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a17
COL "Used in %" FORMAT a17
COL "Free in %" FORMAT a17
COL "Database Name" FORMAT a17
COL "Free Space" FORMAT a17
COL "Growth DAY" FORMAT a17
COL "Growth WEEK" FORMAT a17
COL "Growth DAY in %" FORMAT a17
COL "Growth WEEK in %" FORMAT a17
SELECT
(select min(creation_time) from v\$datafile) "Created",
(select name from v\$database) "DB Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "F
ree in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3)
|| '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*
7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V\$DATAFILE UNION ALL SELECT BYTES FROM V\$TEMPFILE UNION ALL SELECT BYTES FROM V\$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
PROMPT
PROMPT SIZE OF DATABASE
PROMPT ==================
select 'Data files',sum(bytes)/1024/1024/1024 from dba_data_files
union
select 'Temp Files',sum(bytes)/1024/1024/1024 from dba_temp_files
union
select 'Log files',sum(bytes*members)/1024/1024/1024 from v\$log
union
select 'segments',sum(bytes)/1024/1024/1024 from dba_segments
union
select 'free space',sum(bytes)/1024/1024/1024 from dba_free_space;
PROMPT
PROMPT TABLESPACES AND DATAFILES
PROMPT ============================
col TABLESPACE_NAME for a20
col file_id for a7
col TABLESPACE_NAME for a30
col file_name for a60
col STATUS for a9
select TABLESPACE_NAME, to_char(file_id, '999') "File_id", FILE_NAME, BYTES/1024/1024 "Size in MB" , STATUS
from dba_data_files
order by TABLESPACE_NAME, file_name;
PROMPT
PROMPT DATAFILES Details in AutoExtend Mode
PROMPT ====================================
set lines 200
col FILE_NAME for a60
col tablespace_name for a30
col AUTOEXTENSIBLE for a3
col bytes a10
select file_name,tablespace_name,AUTOEXTENSIBLE,bytes/1024/1024/1024 "SIZE IN GB " from dba_data_files
where AUTOEXTENSIBLE='YES' order by tablespace_name;
PROMPT
PROMPT UTILIZATION OF TABLESPACES
PROMPT ============================
set lines 200
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace;
PROMPT
PROMPT TABLESPACES utl >85
PROMPT ============================
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
PROMPT OBJECTS WHOSE STATUS ARE INVALID
PROMPT ===================================
Rem Check object status
select OBJECT_NAME, owner, object_type, STATUS from all_objects where
object_type in
('FUNCTION','INDEX', 'LIBRARY','PACKAGE','PACKAGE BODY',
'PROCEDURE', 'SEQUENCE','SYNONYM','TABLE','TRIGGER',
'TYPE','UNDEFINED','VIEW')
and status = 'INVALID'
and OWNER not in ('SYS','SYSTEM')
and status is not null ;
PROMPT FILES THAT NEEDS RECOVERY
PROMPT =============================
Rem Check wether there is any data file that needs media recovery
select * from v\$recover_file;
PROMPT
PROMPT
PROMPT
PROMPT Database users detail
PROMPT =====================
set lines 200
col username for a25
col account_status for a10
col default_tablespace for a20
select username,account_status,default_tablespace from dba_users;
PROMPT
PROMPT
PROMPT Last Modified Tables Details
PROMPT =====================
set lines 500;
col TABLE_OWNER for a30
col TABLE_NAME for a40
col TIMESTAMP for a70
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES,
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2017
order by 6;
PROMPT
PROMPT
PROMPT Total Objects
PROMPT =========================
set lines 140
col owner for a30
select owner as "SCHEMA",count(*) from dba_objects
group by owner;
PROMPT
PROMPT Archive Log generation History for Last one month
PROMPT ==================================================
set lines 200
SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v\$log_history where to_date(first_time) > sysdate -30 GROUP by to_char(first_time,'YYYY-MON-DD'),
to_date(first_time) order by to_date(first_time)
;
PROMPT
PROMPT
PROMPT dba_jobs assinged
PROMPT ==================================================
set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/
PROMPT
PROMPT
PROMPT List all materialized view logs
PROMPT ==================================================
select log_owner,log_table from dba_mview_logs
/
PROMPT
PROMPT
PROMPT Show all materialized and resfresh times
PROMPT ==================================================
set lines 100 pages 999
col last_refresh format a20
select owner,mview_name,to_char(last_refresh_date,'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/
PROMPT
PROMPT
PROMPT Show materialized view tables and masters
PROMPT ==================================================
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
PROMPT
PROMPT
PROMPT This is useful for spotting failures
PROMPT ==================================================
set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/
prompt ========================================================================
prompt Rman Backup details
prompt ========================================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -3;
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt DR SYNC STATUS
prompt ========================================================================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;
/
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
############# Database health check deeply######
oracle@test02:~> cat /home/oracle/dbhealth.sh
# ###################################################################################################
# DATABASE DAILY HEALTH CHECK MONITORING SCRIPT
VER="[3.9]"
# ===============================================================================
# CAUTION:
# THIS SCRIPT MAY CAUSE A SLIGHT PERFORMANCE IMPACT WHEN IT RUN,
# I RECOMMEND TO NOT RUN THIS SCRIPT SO FREQUENT.
# E.G. YOU MAY CONSIDER TO SCHEDULE IT TO RUN ONE TIME BETWEEN 12:00AM to 5:00AM.
# ===============================================================================
#
# 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 FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHEKCING 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.
# 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
#
# # # #
# 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.
#
#
#
#
#
#
#
#
#
#
#
# ###################################################################################################
SCRIPT_NAME="dbdailychk${VER}"
SRV_NAME=`uname -n`
MAIL_LIST="abc@abc.com"
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 "##############################################################################################"
echo;;
esac
# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:
FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
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]
LONG_RUN_QUR_HOURS=1 # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS [DB]
# CHECK DATABASE AUDIT RECORDS: [Expected to increase CPU Load during script execution when enabled]
CHKAUDITRECORDS=Y
# CHECK CLUSTERWARE HEALTH:
CLUSTER_CHECK=Y
# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances dbalarm 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" #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 dbalarm:
# e.g. Excluding: /dev/mapper, /dev/asm mount points:
EXL_FS="\/dev\/mapper\/|\/dev\/asm\/" #Excluded mount points [Will be skipped during the check].
# #########################
# 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 -h >> ${FSLOG}
df -h | 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}
# #############################
# Checking The CPU Utilization:
# #############################
# Report CPU Utilization if reach >= CPUTHRESHOLD:
OS_TYPE=`uname -s`
CPUUTLLOG=/tmp/CPULOG_DBA_BUNDLE.log
# Getting CPU utilization in last 5 seconds:
case `uname` in
Linux ) CPU_REPORT_SECTIONS=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1 | grep ';' -o | wc -l`
CPU_COUNT=`cat /proc/cpuinfo|grep processor|wc -l`
if [ ${CPU_REPORT_SECTIONS} -ge 6 ]; then
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 7`
else
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 6`
fi
;;
AIX ) CPU_IDLE=`iostat -t $INTERVAL_SEC $NUM_REPORT | sed -e 's/,/./g'|tr -s ' ' ';' | tail -1 | cut -d ";" -f 6`
CPU_COUNT=`lsdev -C|grep Process|wc -l`
;;
SunOS ) CPU_IDLE=`iostat -c $INTERVAL_SEC $NUM_REPORT | tail -1 | awk '{ print $4 }'`
CPU_COUNT=`psrinfo -v|grep "Status of processor"|wc -l`
;;
HP-UX) SAR="/usr/bin/sar"
CPU_COUNT=`lsdev -C|grep Process|wc -l`
if [ ! -x $SAR ]; then
echo "sar command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
else
CPU_IDLE=`/usr/bin/sar 1 5 | grep Average | awk '{ print $5 }'`
fi
;;
*) echo "uname command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
;;
esac
# Getting Utilized CPU (100-%IDLE):
CPU_UTL_FLOAT=`echo "scale=2; 100-($CPU_IDLE)"|bc`
# Convert the average from float number to integer:
CPU_UTL=${CPU_UTL_FLOAT%.*}
if [ -z ${CPU_UTL} ]
then
CPU_UTL=1
fi
# Compare the current CPU utilization with the Threshold:
CPULOG=/tmp/top_processes_DBA_BUNDLE.log
if [ ${CPU_UTL} -ge ${CPUTHRESHOLD} ]
then
export COLUMNS=300 #Increase the COLUMNS width to display the full output [Default is 167]
echo "CPU STATS:" > ${CPULOG}
echo "=========" >> ${CPULOG}
mpstat 1 5 >> ${CPULOG}
echo "" >> ${CPULOG}
echo "VMSTAT Output:" >> ${CPULOG}
echo "=============" >> ${CPULOG}
echo "[If the runqueue number in the (r) column exceeds the number of CPUs [${CPU_COUNT}] this indicates a CPU bottleneck on the system]." >> ${CPULOG}
echo "" >> ${CPULOG}
vmstat 2 5 >> ${CPULOG}
echo "" >> ${CPULOG}
echo "Top 10 Processes:" >> ${CPULOG}
echo "================" >> ${CPULOG}
echo "" >> ${CPULOG}
top -c -b -n 1|head -17 >> ${CPULOG}
unset COLUMNS #Set COLUMNS width back to the default value
#ps -eo pcpu,pid,user,args | sort -k 1 -r | head -11 >> ${CPULOG}
# Check ACTIVE SESSIONS on DB side:
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|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
fi
# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
fi
# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
fi
# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
fi
# Check Long Running Transactions if CPUDIGMORE=Y:
case ${CPUDIGMORE} in
y|Y|yes|YES|Yes)
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 200
SPOOL ${CPULOG} APPEND
prompt
prompt ----------------------------------------------------------------
Prompt ACTIVE SESSIONS ON DATABASE $ORACLE_SID:
prompt ----------------------------------------------------------------
set feedback off linesize 200 pages 1000
col "OS_PID" for a8
col module for a30
col event for a27
col "USER|SID,SER# |MOD|MACHINE" for a60
col WAIT_STATE for a25
col "STATUS|WAIT_STATE|TIME_WAITED" for a31
col "CURR_SQLID" for a35
col "SQLID | FULL_SQL_TEXT" for a75
select p.spid "OS_PID",s.USERNAME||'|'||s.sid||','||s.serial#||' | '||substr(s.MODULE,1,27)||'|'||substr(s.MACHINE,1,20) "USER|SID,SER# |MOD|MACHINE",
substr(s.status||'|'||w.state||'|'||w.seconds_in_wait||'|'||LAST_CALL_ET||'|'||LOGON_TIME,1,50) "ST|WA_ST|WAITD|ACTIVE|LOGIN",
substr(s.status||'|'||w.state||'|'||w.seconds_in_wait||'sec',1,30) "STATUS|WAIT_STATE|TIME_WAITED",
--substr(w.event,1,30)"EVENT",s.SQL_ID ||' | '|| Q.SQL_FULLTEXT "SQLID | FULL_SQL_TEXT"
substr(w.event,1,30)"EVENT",s.SQL_ID
from v\$session s,v\$process p, v\$session_wait w, v\$SQL Q
where s.username is not null
and s.status='ACTIVE'
and p.addr = s.paddr
and s.sid=w.sid
and s.SQL_ID=Q.SQL_ID
order by s.USERNAME||' | '||s.sid||','||s.serial#,s.MODULE;
prompt
prompt ----------------------------------------------------------------
Prompt Long Running Operations On DATABASE $ORACLE_SID:
prompt ----------------------------------------------------------------
col "USER | SID,SERIAL#" for a40
col MESSAGE for a80
col "%COMPLETE" for 999.99
col "SID|SERIAL#" for a12
set linesize 200
select USERNAME||' | '||SID||','||SERIAL# "USER | SID,SERIAL#",SQL_ID,START_TIME,SOFAR/TOTALWORK*100 "%COMPLETE",
trunc(ELAPSED_SECONDS/60) MIN_ELAPSED, trunc(TIME_REMAINING/60) MIN_REMAINING,substr(MESSAGE,1,80)MESSAGE
from v\$session_longops where SOFAR/TOTALWORK*100 <>'100'
order by MIN_REMAINING;
SPOOL OFF
EOF
;;
esac
done
mail -s "ALERT: CPU Utilization on Server [${SRV_NAME}] has reached [${CPU_UTL}%]" ${MAIL_LIST} < ${CPULOG}
fi
rm -f ${CPUUTLLOG}
rm -f ${CPULOG}
# #########################
# Getting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -Ev ${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}|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
fi
# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
fi
# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
fi
# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
fi
# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
echo "e.g."
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
mail -s "dbalarm script on Server [${SRV_NAME}] failed to find ORACLE_HOME, Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory" ${MAIL_LIST} < /dev/null
exit
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
# ##########################
# 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
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 [ -z "${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`
fi
# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo ${VAL1}| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
DB_NAME_LOWER=$( echo "${DB_NAME_UPPER}" | tr -s '[:upper:]' '[:lower:]' )
export DB_NAME_UPPER
export DB_NAME_LOWER
# DB_NAME is Uppercase or Lowercase?:
if [ -d ${ORACLE_HOME}/diagnostics/${DB_NAME_LOWER} ]
then
DB_NAME=${DB_NAME_LOWER}
else
DB_NAME=${DB_NAME_UPPER}
fi
# #########################
# 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
# ###################
# 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:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name='db_block_size';
exit;
EOF
)
blksize=`echo $VAL312|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# #####################
# Getting DB ROLE:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select DATABASE_ROLE from v\$database;
exit;
EOF
)
DB_ROLE=`echo $VAL312|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
case ${DB_ROLE} in
PRIMARY) DB_ROLE_ID=0;;
*) DB_ROLE_ID=1;;
esac
# ############################################
# Checking 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 200 pages 1000
spool ${LOG_DIR}/long_running_jobs.log
PROMPT Long Running Jobs:
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
rm -f ${LOG_DIR}/long_running_jobs.log
fi
# ############################################
# Checking FAILED JOBS ON THE DATABASE:
# ############################################
VAL40=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
--SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM dba_scheduler_jobs where FAILURE_COUNT <> '0') FAIL_COUNT FROM dual;
SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED') FAIL_COUNT FROM dual;
exit;
EOF
)
VAL50=`echo ${VAL40} | awk '{print $NF}'`
if [ ${VAL50} -ge ${FAILDJOBSTHRESHOLD} ]
then
VAL60=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 200 pages 1000
spool ${LOG_DIR}/failed_jobs.log
PROMPT DBMS_JOBS:
PROMPT ^^^^^^^^^^
col LAST_RUN for a25
col NEXT_RUN for a25
set long 9999999
--select dbms_xmlgen.getxml('select job,schema_user,failures,LAST_DATE LAST_RUN,NEXT_DATE NEXT_RUN from dba_jobs where failures <> 0') xml from dual;
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 where failures <> '0';
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT ^^^^^^^^^^^^^^^
col OWNER for a25
col JOB_NAME for a40
col STATE for a11
col STATUS for a11
col FAILURE_COUNT for 999 heading 'Fail'
col RUNTIME_IN_LAST24H for a25
col RUN_DURATION for a14
--HTML format Outputs:
--Set Markup Html On Entmap On Spool On Preformat Off
-- Get the whole failed runs in the last 24 hours:
select to_char(LOG_DATE,'DD-Mon-YYYY hh24:mi:ss')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED';
--XML Output
--select dbms_xmlgen.getxml('select to_char(LOG_DATE,''DD-Mon-YYYY hh24:mi:ss'')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>''SUCCEEDED''') xml from dual;
spool off
exit;
EOF
)
mail -s "WARNING: FAILED JOBS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" ${MAIL_LIST} < ${LOG_DIR}/failed_jobs.log
rm -f ${LOG_DIR}/failed_jobs.log
fi
# ############################################
# LOGFILE SETTINGS:
# ############################################
# Logfile path variable:
DB_HEALTHCHK_RPT=${LOG_DIR}/${DB_NAME}_HEALTH_CHECK_REPORT.log
export DB_HEALTHCHK_RPT
# Flush the logfile:
echo "^^^^^^^^^^^" > ${DB_HEALTHCHK_RPT}
echo "REPORTED BY: ${SCRIPT_NAME}" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
# ############################################
# Checking RAC/ORACLE_RESTART Services:
# ############################################
case ${CLUSTER_CHECK} in
y|Y|yes|YES|Yes)
# 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 "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "CLUSTERWARE CHECKS:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
FILE_NAME=${GRID_HOME}/bin/ocrcheck
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "OCR DISKS CHECKING:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${GRID_HOME}/bin/ocrcheck >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
fi
FILE_NAME=${GRID_HOME}/bin/crsctl
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "VOTE DISKS CHECKING:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${GRID_HOME}/bin/crsctl query css votedisk >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_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 [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "${CLUSTER_TYPE} SERVICES:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
AWK=/usr/bin/awk
$AWK \
'BEGIN {printf "%-55s %-24s %-18s\n", "HA Resource", "Target", "State";
printf "%-55s %-24s %-18s\n", "-----------", "------", "-----";}' >> ${DB_HEALTHCHK_RPT}
$GRID_HOME/bin/crsctl status resource | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$1'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-55s %-24s %-18s\n", appname, apptarget, appstate; state=0;}' >> ${DB_HEALTHCHK_RPT}
fi
FILE_NAME=${ORACLE_HOME}/bin/srvctl
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "DATABASE SERVICES STATUS:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${ORACLE_HOME}/bin/srvctl status service -d ${DB_UNQ_NAME} >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
fi
fi
;;
esac
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "Local Filesystem:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
df -h >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
# ############################################
# Checking Advisors:
# ############################################
# 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 200 pages 100
spool ${DB_HEALTHCHK_RPT} app
PROMPT
PROMPT ^^^^^^^^^^^^^^
PROMPT Tablespaces Size: [Based on Datafiles MAXSIZE]
PROMPT ^^^^^^^^^^^^^^
set pages 1000 linesize 200 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"
from dba_tablespace_usage_metrics;
PROMPT ^^^^^^^^^^^^^^
PROMPT ASM STATISTICS:
PROMPT ^^^^^^^^^^^^^^
select name,state,OFFLINE_DISKS,total_mb,free_mb,ROUND((1-(free_mb / total_mb))*100, 2) "%FULL" from v\$asm_diskgroup;
PROMPT ^^^^^^^^^^^^^^
PROMPT FRA STATISTICS:
PROMPT ^^^^^^^^^^^^^^
PROMPT
PROMPT FRA_SIZE:
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",
ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1) AS "%FULL_NOW" FROM V\$RECOVERY_FILE_DEST;
PROMPT FRA_COMPONENTS:
PROMPT ^^^^^^^^^^^^^^^^^
select * from v\$flash_recovery_area_usage;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT DATABASE GROWTH: [In the Last ~8 days]
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
set serveroutput on
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;
/
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^
PROMPT Active Incidents:
PROMPT ^^^^^^^^^^^^^^^^^
set linesize 200
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;
PROMPT
PROMPT OUTSTANDING ALERTS:
PROMPT ^^^^^^^^^^^^^^^^^^^
select * from DBA_OUTSTANDING_ALERTS;
PROMPT
PROMPT CORRUPTED BLOCKS:
PROMPT ^^^^^^^^^^^^^^^^^
select * from V\$DATABASE_BLOCK_CORRUPTION;
PROMPT
PROMPT BLOCKING SESSIONS:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200 pages 0 echo on feedback on
col BLOCKING_STATUS for a90
select 'User: '||s1.username || '@' || s1.machine || '(SID=' || s1.sid ||' ) running SQL_ID:'||s1.sql_id||' is blocking
User: '|| s2.username || '@' || s2.machine || '(SID=' || s2.sid || ') running SQL_ID:'||s2.sql_id||' For '||s2.SECONDS_IN_WAIT||' sec
----------------------------------------------------------------
Warn user '||s1.username||' Or use the following statement to kill his session:
----------------------------------------------------------------
ALTER SYSTEM KILL SESSION '''||s1.sid||','||s1.serial#||''' immediate;' AS blocking_status
from gv\$LOCK l1, gv\$SESSION s1, gv\$LOCK l2, gv\$SESSION s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s2.SECONDS_IN_WAIT desc;
PROMPT
PROMPT UN-USABLE INDEXES:
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT
set echo on feedback on pages 1000
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' from dba_indexes where status='UNUSABLE';
PROMPT
PROMPT INVALID OBJECTS:
PROMPT ^^^^^^^^^^^^^^^
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
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT RMAN BACKUP OPERATIONS: [LAST 24H]
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, 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;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^
PROMPT SCHEDULED JOBS STATUS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT DBMS_JOBS:
PROMPT ^^^^^^^^^^
set linesize 200
col LAST_RUN for a25
col NEXT_RUN for a25
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;
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT ^^^^^^^^^^^^^^^^
col OWNER for a15
col STATE for a15
col FAILURE_COUNT for 9999 heading 'Fail'
col "DURATION(d:hh:mm:ss)" for a22
col REPEAT_INTERVAL for a70
col "LAST_RUN || REPEAT_INTERVAL" for a65
col "DURATION(d:hh:mm:ss)" for a12
--col LAST_START_DATE for a40
select JOB_NAME,OWNER,ENABLED,STATE,FAILURE_COUNT,to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')||' || '||REPEAT_INTERVAL "LAST_RUN || REPEAT_INTERVAL",
extract(day from last_run_duration) ||':'||
lpad(extract(hour from last_run_duration),2,'0')||':'||
lpad(extract(minute from last_run_duration),2,'0')||':'||
lpad(round(extract(second from last_run_duration)),2,'0') "DURATION(d:hh:mm:ss)"
from dba_scheduler_jobs order by ENABLED,STATE;
PROMPT
PROMPT Current Running Jobs:
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(+)
order by "JOB_NAME|OWNER|SPID|SID",ELAPSED_TIME;
PROMPT
PROMPT AUTOTASK INTERNAL MAINTENANCE WINDOWS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col WINDOW_NAME for a17
col NEXT_RUN for a20
col ACTIVE for a6
col OPTIMIZER_STATS for a15
col SEGMENT_ADVISOR for a15
col SQL_TUNE_ADVISOR for a16
col HEALTH_MONITOR for a15
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,HEALTH_MONITOR FROM DBA_AUTOTASK_WINDOW_CLIENTS;
PROMPT
PROMPT FAILED DBMS_SCHEDULER JOBS IN THE LAST 24H:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col LOG_DATE for a36
col OWNER for a15
col JOB_NAME for a35
col STATUS for a11
col RUN_DURATION for a20
col ID for 99
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;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT Queries Running For More Than [${LONG_RUN_QUR_HOURS}] Hours:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col module for a30
col DURATION_HOURS for 99999.9
col STARTED_AT for a13
col "USERNAME| SID,SERIAL#" for a30
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 last_call_et > 60*60*${LONG_RUN_QUR_HOURS}
and status = 'ACTIVE';
PROMPT ^^^^^^^^^^^^^^^^
PROMPT ADVISORS STATUS:
PROMPT ^^^^^^^^^^^^^^^^
col CLIENT_NAME for a60
col window_group for a60
col STATUS for a15
SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT SQL TUNING ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT Last Execution of SQL TUNING ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col TASK_NAME for a60
set long 2000000000
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;
variable Findings_Report CLOB;
BEGIN
:Findings_Report :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :Findings_Report
PROMPT
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT MEMORY ADVISORS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT SGA ADVISOR:
PROMPT ^^^^^^^^^^^^
col ESTD_DB_TIME for 99999999999999999
col ESTD_DB_TIME_FACTOR for 9999999999999999999999999999
select * from V\$SGA_TARGET_ADVICE where SGA_SIZE_FACTOR > .6 and SGA_SIZE_FACTOR < 1.6;
PROMPT
PROMPT Buffer Cache ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^
col ESTD_SIZE_MB for 9999999999999
col ESTD_PHYSICAL_READS for 99999999999999999999
col ESTD_PHYSICAL_READ_TIME for 99999999999999999999
select SIZE_FACTOR "%SIZE",SIZE_FOR_ESTIMATE ESTD_SIZE_MB,ESTD_PHYSICAL_READS,ESTD_PHYSICAL_READ_TIME,ESTD_PCT_OF_DB_TIME_FOR_READS
from V\$DB_CACHE_ADVICE where SIZE_FACTOR >.8 and SIZE_FACTOR<1.3;
PROMPT
PROMPT Shared Pool ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^
col SIZE_MB for 99999999999
col SIZE_FACTOR for 99999999
col ESTD_SIZE_MB for 99999999999999999999
col LIB_CACHE_SAVED_TIME for 99999999999999999999999999
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,
ESTD_LC_LOAD_TIME PARSING_TIME from V\$SHARED_POOL_ADVICE
where SHARED_POOL_SIZE_FACTOR > .9 and SHARED_POOL_SIZE_FACTOR < 1.6;
PROMPT
PROMPT PGA ADVISOR:
PROMPT ^^^^^^^^^^^^
col SIZE_FACTOR for 999999999
col ESTD_SIZE_MB for 99999999999999999999
col MB_PROCESSED for 99999999999999999999
col ESTD_TIME for 99999999999999999999
select PGA_TARGET_FACTOR "%SIZE",PGA_TARGET_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,BYTES_PROCESSED/1024/1024 MB_PROCESSED,
ESTD_TIME,ESTD_PGA_CACHE_HIT_PERCENTAGE PGA_HIT,ESTD_OVERALLOC_COUNT PGA_SHORTAGE
from V\$PGA_TARGET_ADVICE where PGA_TARGET_FACTOR > .7 and PGA_TARGET_FACTOR < 1.6;
PROMPT
PROMPT SEGMENT ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^
select'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'-------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT CURRENT OS / HARDWARE STATISTICS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
select stat_name,value from v\$osstat;
PROMPT
PROMPT ^^^^^^^^^^^^^^^
PROMPT RESOURCE LIMIT:
PROMPT ^^^^^^^^^^^^^^^
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from gv\$resource_limit order by RESOURCE_NAME;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^
PROMPT RECYCLEBIN OBJECTS#:
PROMPT ^^^^^^^^^^^^^^^^^^^^
set feedback off
select count(*) "RECYCLED_OBJECTS#",sum(space)*$blksize/1024/1024 "TOTAL_SIZE_MB" from dba_recyclebin group by 1;
set feedback on
PROMPT
PROMPT [Note: Consider Purging DBA_RECYCLEBIN for better performance]
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT FLASHBACK RESTORE POINTS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
select * from V\$RESTORE_POINT;
PROMPT
PROMPT ^^^^^^^^^^^^^^^
PROMPT HEALTH MONITOR:
PROMPT ^^^^^^^^^^^^^^^
select name,type,status,description,repair_script from V\$HM_RECOMMENDATION where time_detected > sysdate -1;
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT Monitored INDEXES:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200 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 /
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT REDO LOG SWITCHES:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200
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;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT Modified Parameters Since Instance Startup:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col name for a45
col VALUE for a120
col DEPRECATED for a10
select NAME,VALUE,ISDEFAULT "DEFAULT",ISDEPRECATED "DEPRECATED" from v\$parameter where ISMODIFIED = 'SYSTEM_MOD' order by 1;
PROMPT
PROMPT ^^^^^^^^^^^^
PROMPT Cred Backup:
PROMPT ^^^^^^^^^^^^
col name for a35
col "CREATE_DATE||PASS_LAST_CHANGE" for a60
select name,PASSWORD HASH,CTIME ||' || '||PTIME "CREATE_DATE||PASS_LAST_CHANGE" 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 200 pages 1000
spool ${DB_HEALTHCHK_RPT} app
prompt
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt New Created objects [Last 24H] ...
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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
# ###############################################
# Checking AUDIT RECORDS ON THE DATABASE:
# ###############################################
# Check if Checking Audit Records is ENABLED:
case ${CHKAUDITRECORDS} in
Y|y|YES|Yes|yes)
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 200 pages 1000
spool ${LOG_DIR}/audit_records.log
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
prompt
prompt
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Failed Login Attempts in the last 24Hours ...
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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 returncode = 1017
and timestamp > (sysdate -1)
order by 1;
prompt
prompt
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Audit records in the last 24Hours AUD$...
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt
select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
from dba_audit_trail
where
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')
and timestamp > SYSDATE-1 order by EXTENDED_TIMESTAMP;
prompt
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Fine Grained Auditing Data ...
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
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;
spool off
exit;
EOF
)
cat ${LOG_DIR}/audit_records.log >> ${DB_HEALTHCHK_RPT}
fi
;;
esac
mail -s "HEALTH CHECK REPORT: For Database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" ${MAIL_LIST} < ${DB_HEALTHCHK_RPT}
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".
oracle@test02:~>
############## Daily Archive IO details ####################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/daily_health_check/archive_IO/PROD_ARCHIVE_IO.log
printf "to:abc@abc.com\n" > $DR_log
#printf "cc:abc@abc.com\n" >> $DR_log
printf "subject: Daily Archive IO Report " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
select name,open_mode,database_role from v\$database;
set timi on
select 'Started Time = '||to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;
COLUMN LUN FORMAT A70
COLUMN total_io FORMAT 999999999999
set pages 1000
SELECT SUBSTR(i.name,1,7) LUN,sum(x.phyrds+x.phywrts) "Total_IO" FROM v\$filestat x,SYS.ts$ ts,v\$datafile i,SYS.file$ f WHERE i
.file# = f.file# AND ts.ts# = f.ts# AND x.file# = f.file# group by SUBSTR(i.name,1,7);
select 'End Time = '||to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;
PROMPT
PROMPT
PROMPT Previous day archive logs generation
PROMPT ==================================================
select count(1),sum((BLOCKS*BLOCK_SIZE)/1024/1024/1024) "Total_archive_size_in_GB" from v\$archived_log where first_time >= trunc(sysdate) -1 and first_time <trunc(sysdate);
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
###################### Alert Monitor #####################
oracle@test02:~> cat /u04/MasterDB/scripts/alert_log/alert_monitor.sh
. /home/oracle/.bash_profile
export SID=SMRPRODB
#export DIR=/u04/MasterDB/scripts/alert_log
export DIR=/u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace
touch $DIR/alert_${SMRPRODB}.hist
touch $DIR/alert.err
cd =/u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace
tail -1000 alert_SMRPRODB.log >> $DIR/alert_SMRPRODB.hist
grep ORA- $DIR/alert_SMRPRODB.hist > $DIR/alert.err
diff $DIR/alert.err $DIR/alert_old.err >$DIR/alert_error.err
if [ `cat $DIR/alert_error.err|wc -l` -gt 0 ]
then
# mailx -s "${SID} SMRPRODB ALERT : ORA- ERRORS" varun.yadav@abc.com,core@abc.com< $DIR/alert.err
mailx -s "${SID} ALERT : ORA- ERRORS" varun.yadav@abc.com,core@abc.com< $DIR/alert.err
fi
mv $DIR/alert.err $DIR/alert_old.err
rm $DIR/alert_error.err
rm $DIR/alert_${SID}.hist
oracle@test02:~>
############ Alert log check ##########
oracle@test02:~> cat /u04/MasterDB/scripts/alert_log/check_alert_log.sh
#####################################################################
## check_alert_log.sh ##
##
## Author :
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u02/app
export ORACLE_HOME=/u02/app/oracle/diag/rdbms/prodb/PRODB/trace
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=PRODB
##
######## Other variables #################
##
DBA=varun.yadav@abc.com,core_oradba@abc.com
datevar=$(date)
##
#######
#######
#######
####### End of configuration
#######
#######
##
cd /u02/app/oracle/diag/rdbms/prodb/PRODB/trace
if [ -f alert_${ORACLE_SID}.log ]
then
tail -500 alert_${ORACLE_SID}.log > /u04/MasterDB/scripts/alert_log/alert_work.log
grep ORA- /u04/MasterDB/scripts/alert_log/alert_work.log >> /u04/MasterDB/scripts/alert_log/alert.err
grep Shut /u04/MasterDB/scripts/alert_log/alert_work.log >> /u04/MasterDB/scripts/alert_log/alert.err
fi
export error << `cat /u04/MasterDB/scripts/alert_log/alert.err`
if[ `cat /u04/MasterDB/scripts/alert_log/alert.err|wc -l` -gt 0 ]
then
mailx -s “${ORACLE_SID} ORACLE ALERT ERRORS $datevar” $DBA < /u04/MasterDB/scripts/alert_log/alert_work.log
fi
fi
#rm -f /u04/MasterDB/scripts/alert_log/alert.err
#rm -f /u04/MasterDB/scripts/alert_log/alert_work.log
exit
oracle@test02:~>
##################### Stats Gathers Schema ###################################
oracle@test02:~> cat /u04/MasterDB/scripts/SMRPRODB_statsgather.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
stats_log=/u04/MasterDB/scripts/dmenvstats.log
printf "to:varun.yadav@abc.com\n" > $stats_log
printf "cc:abc@abc.com\n" >> $stats_log
printf "subject:Production Infodba Schema stats gather details " >> $stats_log
sqlplus -s "/as sysdba" <<EOF >> $stats_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT Infodba Schema stats gather details
PROMPT ==================================================
exec DBMS_Stats.Gather_Schema_Stats('INFODBA', estimate_percent => 10, degree => 3,cascade => true);
EOF
/usr/sbin/sendmail -t < $stats_log
oracle@test02:~>
oracle@test02:~> cat /u04/MasterDB/scripts/PRODB_statsgather.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
stats_log=/u04/MasterDB/scripts/dmenvstats.log
printf "to:varun.yadav@abc.com\n" > $stats_log
printf "cc:abc@abc.com\n" >> $stats_log
printf "subject:Production Infodba Schema stats gather details " >> $stats_log
sqlplus -s "/as sysdba" <<EOF >> $stats_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT Infodba Schema stats gather details
PROMPT ==================================================
exec DBMS_Stats.Gather_Schema_Stats('INFODBA', estimate_percent => 10, degree => 3,cascade => true);
EOF
/usr/sbin/sendmail -t < $stats_log
oracle@test02:~> cat /u04/MasterDB/scripts/smr_prod_check_instance.sh
#!/bin/sh
SERVICE=ora_pmon_PRODB
#replace ‘orcl’ with your sid
DBALIST="core@abc.com";export DBALIST
if ps -ef | grep -v grep | grep $SERVICE > /dev/null
then
#echo "$SERVICE service running, everything is fine" | mailx -s "database prodb DR on `hostname` is up" $DBALIST
#echo "Alert" | mailx -s "database prodb DR on `hostname` is UP" $DBALIST
else
echo "$SERVICE is not running contact DBA " $DBALIST | mailx -s "database prodb Production database 64 on `hostname` is down" $DBALIST
#echo "Alert" | mailx -s "database prodb DR on `hostname` is down" $DBALIST
fi
oracle@test02:~>
- Rman Backup
- Disk Alert
- Mount Point
- Mount Point Threshold
- Expdp Backup
- Sar Output
- DR Check
- Tablespace Alert
- Tablespace Alert Threshhold
- Health Check Daily
- DB Health
- Archive I/O
- Oracle Alert log monitor
- Stats Gathers Schema
- DATABASE STARTUP DOWN CHECK
##############################################################################
- Crontab list details scheduled in linux OS
oracle@02:~> crontab -l
########### Rman Backup Script #############
05 04 * * * /u04/MasterDB/scripts/rman_DB_FULL.sh 2>&1 >>/u04/MasterDB/rmanbackup_PRODB/RMAN_PRODB_FULL.log
########### Rman Backup Status on Mail #############
35 06 * * * /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
#### OS Disk Alert #################
#0,15,30,45 * * * * /u04/MasterDB/scripts/disk_threshod.sh
#05 05 * * * /u04/MasterDB/scripts/mountpoint_check.sh
#0,15,30,45 * * * * /u04/MasterDB/scripts/DR_check/diskalert_threshold.sh
#### Sar Output ######################
26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
#### DR Check ########################
0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
#### Tablespace Alert ###############
0,15,30,45 * * * * /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh > /u04/MasterDB/scripts/daily_health_check/ts_alert.log 2>&1
#### Daily Database Health Check ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/daily_healthcheck.sh
30 05 * * 1 /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
#### Database Health Check ###################
#30 05 * * * /home/oracle/dbhealth.sh
#### Archive I/O ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#### Alert log monitor #############
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/alert_monitor.sh > /u04/MasterDB/scripts/alert_log/alert_monitor.log1 2>&1
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/check_alert_log.sh > /u04/MasterDB/scripts/alert_log/alert_monitor11.log1 2>&1
##################### Stats Gathers Schema ###################################
0 4 * * 0 /u04/MasterDB/scripts/SMRPRODB_statsgather.sh
################################ SMR DATABASE STARTUP DOWN CHECK #######################
5,10,15,20,25,30,35,40,45,50,55,00 * * * * /u04/MasterDB/scripts/smr_prod_check_instance.sh > /u04/MasterDB/scripts/smr_prod_check_alert_monitor.log 2>&1
oracle@test02:~>
#### Expdp Backup ####################
25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com
oracle@02:~> crontab -l
########### Rman Backup Script #############
05 04 * * * /u04/MasterDB/scripts/rman_DB_FULL.sh 2>&1 >>/u04/MasterDB/rmanbackup_PRODB/RMAN_PRODB_FULL.log
########### Rman Backup Status on Mail #############
35 06 * * * /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
#### OS Disk Alert #################
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/mount_point.sh
#### Expdp Backup ####################
25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com
#### Sar Output ######################
26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
#### DR Check ########################
0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
0,15,30,45 * * * * /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh > /u04/MasterDB/scripts/daily_health_check/ts_alert.log 2>&1
0,15,30,45 * * * * /u04/MasterDB/scripts/check_tablespace.sh > /u04/MasterDB/scripts/daily_health_check/ts_alert1.log 2>&1
#### Health Check ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/daily_healthcheck.sh
30 05 * * 1 /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
#30 05 * * * /home/oracle/dbhealth.sh
#### Archive I/O ###################
30 04 * * * /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#### Alert log monitor #############
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/alert_monitor.sh > /u04/MasterDB/scripts/alert_log/alert_monitor.log1 2>&1
10,20,30,40,50,00 * * * * /u04/MasterDB/scripts/alert_log/check_alert_log.sh > /u04/MasterDB/scripts/alert_log/alert_monitor11.log1 2>&1
##################### Mount Point#########################
0 */12 * * * /u04/MasterDB/scripts/daily_health_check/mount_point.sh
##################### Stats Gathers Schema ###################################
0 4 * * 0 /u04/MasterDB/scripts/PRODB_statsgather.sh
################################ DATABASE STARTUP DOWN CHECK #######################
5,10,15,20,25,30,35,40,45,50,55,00 * * * * /u04/MasterDB/scripts/prod_check_instance.sh > /u04/MasterDB/scripts/prod_check_alert_monitor.log 2>&1
#######################################################################
- Rman Backup
- Disk Alert
- Mount Point
- Mount Point Threshold
- Expdp Backup
- Sar Output
- DR Check
- Tablespace Alert
- Tablespace Alert Threshhold
- Health Check Daily
- DB Health
- Archive I/O
- Oracle Alert log monitor
- Stats Gathers Schema
- DATABASE STARTUP DOWN CHECK
################################## RMAN BACKUP #####################################
oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.sh
ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1
DD=`date +%d%m%y`
export ORACLE_HOME
ORACLE_SID=PRODB
export ORACLE_SID
Rman_Log=/u04/MasterDB/scripts/"$ORACLE_SID"_bkp${DD}.log
touch $Rman_Log
/u02/app/oracle/product/12.1.0/dbhome_1/bin/rman target test/test1234 @/u04/MasterDB/scripts/rman_PRODB_FULL.rcv >> /u03/rmanbackup_PRODB/rman_RODB_FULL.log
oracle@test02:~>
oracle@test02:~> cat /u04/MasterDB/scripts/rman_PRODB_FULL.rcv
run
{
allocate channel c1 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c2 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c3 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
allocate channel c4 device type disk format '/u03/rmanbackup_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';
crosscheck backup;
crosscheck archivelog all;
report obsolete;
delete noprompt obsolete;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup full database format '/u03/rmanbackup_PRODB/%d_%U.bckp' plus archivelog;
copy current controlfile to '/u03/rmanbackup_PRODB/control01_%d_%T_%U.bak';
backup current controlfile for standby format '/u03/rmanbackup_RODB/standbycontrol_%d_%T_%U.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
oracle@test02:~>
############################ Rman Backup Status ################################################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/daily_health_check/rmanbackupstatus.log
printf "to:varun.yadav@abc.com\n" > $DR_log
#printf "cc:abc@abccom,core@abc.com\n" >> $DR_log
printf "subject: SMRPRODB Database RMAN Backup STATUS AND DETAILS " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT RMAN backup and Size Details
PROMPT ==================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -3;
select ctime "Date"
, decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
, bsize "Size MB"
from (select trunc(bp.completion_time) ctime
, backup_type
, round(sum(bp.bytes/1024/1024),2) bsize
from v\$backup_set bs, v\$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
#### Disk Alert #################
oracle@test02:~> cat /u04/MasterDB/scripts/disk_threshod.sh
# Shell script to monitor or watch the disk space
# It will send an email to $ADMIN, if the (free avilable) percentage
# of space is >= 90%
# -------------------------------------------------------------------------
# Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# ----------------------------------------------------------------------
# Linux shell script to watch disk space (should work on other UNIX oses )
# SEE URL: http://www.cyberciti.biz/tips/shell-script-to-watch-the-disk-space.html
# set admin email so that you can get email
ADMIN="varun.yadav@abc.com"
# set alert level 90% is default
ALERT=90
df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;
do
#echo $output
usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $2 }' )
if [ $usep -ge $ALERT ]; then
i echo "Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |
mail -s " Alert: Almost out of disk space" $usep $ADMIN
fi
done
oracle@test02:~>
########################### EXPDP ###########################
oracle@test02:~> cat /u04/MasterDB/scripts/expdp_smrprodb_full.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
expdp abc/abc@PRODB dumpfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).dmp directory=expdp logfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_PRODB parallel=2
#Moving log files
mv /u04/MasterDB/expdp/expdp*.log /u04/MasterDB/expdp/expdp_fulldb_backup_logs
#Granting permissions for Dump files
chmod -R 775 /u04/MasterDB/expdp/
#Compressing files
gzip /u04/MasterDB/expdp/*.*
#Removing 2 days old dump files
#find /u04/MasterDB/expdp/ -mtime +2 -exec rm {} \;
oracle@test02:~>
#### Sar Output ######################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh
DD=`date +%d%m%y`
cd /u04/MasterDB/scripts/daily_health_check/sar_output
touch /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log
sar 3600 24 > /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log
cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log >> /u04/MasterDB/scripts/daily_health_check/sar_output/sar.tmp
mailx -s " Hourly OS CPU Utilization SAR Details " abc@abc.com< sar.tmp
rm -rf sar.tmp
exit
oracle@test02:~>
#### DR Check ########################
oracle@test02:~> cat /u04/MasterDB/scripts/DR_check/dr_check.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/DR_check/DR_Status.log
printf "to:abc@abc.com\n" > $DR_log
#printf "cc:abc@abc.com\n" >> $DR_log
printf "subject: DR HOURLY STATUS REPORT " >> $DR_log
printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~> /u04/MasterDB/scripts/DR_check/dr_check.sh
#### Tablespace Alert ###############
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/tablespace_details.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log1=/u04/MasterDB/scripts/daily_health_check/tb_check.log
printf "to:abc@abc.com\n" > $DR_log1
#printf "cc:core_oradba@mind-infotech.com\n" >> $DR_log1
printf "subject: PRODB Tablespace HOURLY STATUS REPORT\n" >> $DR_log1
#printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log1
sqlplus -s "/as sysdba" <<EOF >> $DR_log1
et echo off
set feed off
set verify off
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
COLUMN TABLESPACE FORMAT A25
select name DBNAME from v\$database;
prompt =======================================================================
prompt TABLESPACE UTILIZATION
prompt =======================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace
/
prompt ========================================================================
prompt Tablespace More Than 85% used
prompt ========================================================================
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
set feedback on
set verify on
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/PRODB/ArchiveLog
/
EOF
/usr/sbin/sendmail -t < $DR_log1
oracle@test02:~>
######### Tablespace Threshold Alert ###################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/tablespace_threshold.ksh
#!/bin/sh
export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=PRODB
cd /u04/MasterDB/scripts/daily_health_check
logfile=/u04/MasterDB/scripts/daily_health_check/Tablespace_alert.log
cnt1=`ps -ef|grep pmon|grep $ORACLE_SID|wc -l`
if [ $cnt1 -eq 1 ];
then
sqlplus -s "/as sysdba" > /dev/null << EOF
spool $logfile
@/u04/MasterDB/scripts/daily_health_check//tablespace_alert.sql
spool off
exit
EOF
# If there are more then these two lines in the output file, mail it.
count=`cat $logfile|wc -l`
#echo $count
if [ $count -ge 4 ];
then
mailx -s "ALERT: 90 % Threshold Alert FOR PROD Database "coredba@abc.com <$logfile
fi
fi
oracle@test02:~>
#### ######Database Health Check ###################
oracle@test02:~> cat /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/weekly_health_check/SMRWeeklyreport.log
printf "to:abc@abc.com\n" > $DR_log
printf "subject: Weekly SMRPRODB Database Healthcheck Details " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
#column date_column new_value today_var
/
select to_char(sysdate,'yyyy-Mon-dd_hh-miAM')date_column from dual
/
spool /u04/MasterDB/scripts/daily_health_check/SMRPROD_&today_var.txt
PROMPT =============================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT =============================================================
PROMPT CURRENT DATE and TIME
PROMPT ======================
Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;
PROMPT
PROMPT DATABASE NAME
PROMPT =================
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
PROMPT
PROMPT Users logged information
PROMPT ===============================
select status,count(*) from v\$session
--where username not in ('SYS','SYSTEM')
group by status;
PROMPT
PROMPT Previous day archive logs generation
PROMPT ======================================
select count(*) from v\$archived_log
where to_char(COMPLETION_TIME,'DD-Mon-YY')=to_char(sysdate-1,'DD-Mon-YY');
PROMPT
PROMPT latest Archive No.
PROMPT ======================================
archive log list
PROMPT
PROMPT CONTROL FILES
PROMPT ==================
select * from v\$controlfile;
PROMPT
PROMPT LOG FILE GROUPS
PROMPT ==================
set lines 200
col status for a7
col type for a7
col member for a60
col IS_RECOVERY_DEST_FILE for a3
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v\$logfile;
PROMPT
PROMPT
PROMPT LOG FILE MEMBERS
PROMPT ==================
select * from v\$log;
PROMPT
PROMPT SIZE OF DATABASE Daily
PROMPT ==================
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a17
COL "Used in %" FORMAT a17
COL "Free in %" FORMAT a17
COL "Database Name" FORMAT a17
COL "Free Space" FORMAT a17
COL "Growth DAY" FORMAT a17
COL "Growth WEEK" FORMAT a17
COL "Growth DAY in %" FORMAT a17
COL "Growth WEEK in %" FORMAT a17
SELECT
(select min(creation_time) from v\$datafile) "Created",
(select name from v\$database) "DB Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "F
ree in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3)
|| '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*
7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V\$DATAFILE UNION ALL SELECT BYTES FROM V\$TEMPFILE UNION ALL SELECT BYTES FROM V\$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
PROMPT
PROMPT SIZE OF DATABASE
PROMPT ==================
select 'Data files',sum(bytes)/1024/1024/1024 from dba_data_files
union
select 'Temp Files',sum(bytes)/1024/1024/1024 from dba_temp_files
union
select 'Log files',sum(bytes*members)/1024/1024/1024 from v\$log
union
select 'segments',sum(bytes)/1024/1024/1024 from dba_segments
union
select 'free space',sum(bytes)/1024/1024/1024 from dba_free_space;
PROMPT
PROMPT TABLESPACES AND DATAFILES
PROMPT ============================
col TABLESPACE_NAME for a20
col file_id for a7
col TABLESPACE_NAME for a30
col file_name for a60
col STATUS for a9
select TABLESPACE_NAME, to_char(file_id, '999') "File_id", FILE_NAME, BYTES/1024/1024 "Size in MB" , STATUS
from dba_data_files
order by TABLESPACE_NAME, file_name;
PROMPT
PROMPT DATAFILES Details in AutoExtend Mode
PROMPT ====================================
set lines 200
col FILE_NAME for a60
col tablespace_name for a30
col AUTOEXTENSIBLE for a3
col bytes a10
select file_name,tablespace_name,AUTOEXTENSIBLE,bytes/1024/1024/1024 "SIZE IN GB " from dba_data_files
where AUTOEXTENSIBLE='YES' order by tablespace_name;
PROMPT
PROMPT UTILIZATION OF TABLESPACES
PROMPT ============================
set lines 200
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace;
PROMPT
PROMPT TABLESPACES utl >85
PROMPT ============================
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
PROMPT OBJECTS WHOSE STATUS ARE INVALID
PROMPT ===================================
Rem Check object status
select OBJECT_NAME, owner, object_type, STATUS from all_objects where
object_type in
('FUNCTION','INDEX', 'LIBRARY','PACKAGE','PACKAGE BODY',
'PROCEDURE', 'SEQUENCE','SYNONYM','TABLE','TRIGGER',
'TYPE','UNDEFINED','VIEW')
and status = 'INVALID'
and OWNER not in ('SYS','SYSTEM')
and status is not null ;
PROMPT FILES THAT NEEDS RECOVERY
PROMPT =============================
Rem Check wether there is any data file that needs media recovery
select * from v\$recover_file;
PROMPT
PROMPT
PROMPT
PROMPT Database users detail
PROMPT =====================
set lines 200
col username for a25
col account_status for a10
col default_tablespace for a20
select username,account_status,default_tablespace from dba_users;
PROMPT
PROMPT
PROMPT Last Modified Tables Details
PROMPT =====================
set lines 500;
col TABLE_OWNER for a30
col TABLE_NAME for a40
col TIMESTAMP for a70
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES,
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2017
order by 6;
PROMPT
PROMPT
PROMPT Total Objects
PROMPT =========================
set lines 140
col owner for a30
select owner as "SCHEMA",count(*) from dba_objects
group by owner;
PROMPT
PROMPT Archive Log generation History for Last one month
PROMPT ==================================================
set lines 200
SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v\$log_history where to_date(first_time) > sysdate -30 GROUP by to_char(first_time,'YYYY-MON-DD'),
to_date(first_time) order by to_date(first_time)
;
PROMPT
PROMPT
PROMPT dba_jobs assinged
PROMPT ==================================================
set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/
PROMPT
PROMPT
PROMPT List all materialized view logs
PROMPT ==================================================
select log_owner,log_table from dba_mview_logs
/
PROMPT
PROMPT
PROMPT Show all materialized and resfresh times
PROMPT ==================================================
set lines 100 pages 999
col last_refresh format a20
select owner,mview_name,to_char(last_refresh_date,'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/
PROMPT
PROMPT
PROMPT Show materialized view tables and masters
PROMPT ==================================================
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
PROMPT
PROMPT
PROMPT This is useful for spotting failures
PROMPT ==================================================
set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/
prompt ========================================================================
prompt Rman Backup details
prompt ========================================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -3;
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt DR SYNC STATUS
prompt ========================================================================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;
/
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
############# Database health check deeply######
oracle@test02:~> cat /home/oracle/dbhealth.sh
# ###################################################################################################
# DATABASE DAILY HEALTH CHECK MONITORING SCRIPT
VER="[3.9]"
# ===============================================================================
# CAUTION:
# THIS SCRIPT MAY CAUSE A SLIGHT PERFORMANCE IMPACT WHEN IT RUN,
# I RECOMMEND TO NOT RUN THIS SCRIPT SO FREQUENT.
# E.G. YOU MAY CONSIDER TO SCHEDULE IT TO RUN ONE TIME BETWEEN 12:00AM to 5:00AM.
# ===============================================================================
#
# 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 FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHEKCING 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.
# 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
#
# # # #
# 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.
#
#
#
#
#
#
#
#
#
#
#
# ###################################################################################################
SCRIPT_NAME="dbdailychk${VER}"
SRV_NAME=`uname -n`
MAIL_LIST="abc@abc.com"
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 "##############################################################################################"
echo;;
esac
# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:
FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
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]
LONG_RUN_QUR_HOURS=1 # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS [DB]
# CHECK DATABASE AUDIT RECORDS: [Expected to increase CPU Load during script execution when enabled]
CHKAUDITRECORDS=Y
# CHECK CLUSTERWARE HEALTH:
CLUSTER_CHECK=Y
# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances dbalarm 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" #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 dbalarm:
# e.g. Excluding: /dev/mapper, /dev/asm mount points:
EXL_FS="\/dev\/mapper\/|\/dev\/asm\/" #Excluded mount points [Will be skipped during the check].
# #########################
# 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 -h >> ${FSLOG}
df -h | 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}
# #############################
# Checking The CPU Utilization:
# #############################
# Report CPU Utilization if reach >= CPUTHRESHOLD:
OS_TYPE=`uname -s`
CPUUTLLOG=/tmp/CPULOG_DBA_BUNDLE.log
# Getting CPU utilization in last 5 seconds:
case `uname` in
Linux ) CPU_REPORT_SECTIONS=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1 | grep ';' -o | wc -l`
CPU_COUNT=`cat /proc/cpuinfo|grep processor|wc -l`
if [ ${CPU_REPORT_SECTIONS} -ge 6 ]; then
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 7`
else
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 6`
fi
;;
AIX ) CPU_IDLE=`iostat -t $INTERVAL_SEC $NUM_REPORT | sed -e 's/,/./g'|tr -s ' ' ';' | tail -1 | cut -d ";" -f 6`
CPU_COUNT=`lsdev -C|grep Process|wc -l`
;;
SunOS ) CPU_IDLE=`iostat -c $INTERVAL_SEC $NUM_REPORT | tail -1 | awk '{ print $4 }'`
CPU_COUNT=`psrinfo -v|grep "Status of processor"|wc -l`
;;
HP-UX) SAR="/usr/bin/sar"
CPU_COUNT=`lsdev -C|grep Process|wc -l`
if [ ! -x $SAR ]; then
echo "sar command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
else
CPU_IDLE=`/usr/bin/sar 1 5 | grep Average | awk '{ print $5 }'`
fi
;;
*) echo "uname command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
;;
esac
# Getting Utilized CPU (100-%IDLE):
CPU_UTL_FLOAT=`echo "scale=2; 100-($CPU_IDLE)"|bc`
# Convert the average from float number to integer:
CPU_UTL=${CPU_UTL_FLOAT%.*}
if [ -z ${CPU_UTL} ]
then
CPU_UTL=1
fi
# Compare the current CPU utilization with the Threshold:
CPULOG=/tmp/top_processes_DBA_BUNDLE.log
if [ ${CPU_UTL} -ge ${CPUTHRESHOLD} ]
then
export COLUMNS=300 #Increase the COLUMNS width to display the full output [Default is 167]
echo "CPU STATS:" > ${CPULOG}
echo "=========" >> ${CPULOG}
mpstat 1 5 >> ${CPULOG}
echo "" >> ${CPULOG}
echo "VMSTAT Output:" >> ${CPULOG}
echo "=============" >> ${CPULOG}
echo "[If the runqueue number in the (r) column exceeds the number of CPUs [${CPU_COUNT}] this indicates a CPU bottleneck on the system]." >> ${CPULOG}
echo "" >> ${CPULOG}
vmstat 2 5 >> ${CPULOG}
echo "" >> ${CPULOG}
echo "Top 10 Processes:" >> ${CPULOG}
echo "================" >> ${CPULOG}
echo "" >> ${CPULOG}
top -c -b -n 1|head -17 >> ${CPULOG}
unset COLUMNS #Set COLUMNS width back to the default value
#ps -eo pcpu,pid,user,args | sort -k 1 -r | head -11 >> ${CPULOG}
# Check ACTIVE SESSIONS on DB side:
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|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
fi
# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
fi
# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
fi
# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
fi
# Check Long Running Transactions if CPUDIGMORE=Y:
case ${CPUDIGMORE} in
y|Y|yes|YES|Yes)
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 200
SPOOL ${CPULOG} APPEND
prompt
prompt ----------------------------------------------------------------
Prompt ACTIVE SESSIONS ON DATABASE $ORACLE_SID:
prompt ----------------------------------------------------------------
set feedback off linesize 200 pages 1000
col "OS_PID" for a8
col module for a30
col event for a27
col "USER|SID,SER# |MOD|MACHINE" for a60
col WAIT_STATE for a25
col "STATUS|WAIT_STATE|TIME_WAITED" for a31
col "CURR_SQLID" for a35
col "SQLID | FULL_SQL_TEXT" for a75
select p.spid "OS_PID",s.USERNAME||'|'||s.sid||','||s.serial#||' | '||substr(s.MODULE,1,27)||'|'||substr(s.MACHINE,1,20) "USER|SID,SER# |MOD|MACHINE",
substr(s.status||'|'||w.state||'|'||w.seconds_in_wait||'|'||LAST_CALL_ET||'|'||LOGON_TIME,1,50) "ST|WA_ST|WAITD|ACTIVE|LOGIN",
substr(s.status||'|'||w.state||'|'||w.seconds_in_wait||'sec',1,30) "STATUS|WAIT_STATE|TIME_WAITED",
--substr(w.event,1,30)"EVENT",s.SQL_ID ||' | '|| Q.SQL_FULLTEXT "SQLID | FULL_SQL_TEXT"
substr(w.event,1,30)"EVENT",s.SQL_ID
from v\$session s,v\$process p, v\$session_wait w, v\$SQL Q
where s.username is not null
and s.status='ACTIVE'
and p.addr = s.paddr
and s.sid=w.sid
and s.SQL_ID=Q.SQL_ID
order by s.USERNAME||' | '||s.sid||','||s.serial#,s.MODULE;
prompt
prompt ----------------------------------------------------------------
Prompt Long Running Operations On DATABASE $ORACLE_SID:
prompt ----------------------------------------------------------------
col "USER | SID,SERIAL#" for a40
col MESSAGE for a80
col "%COMPLETE" for 999.99
col "SID|SERIAL#" for a12
set linesize 200
select USERNAME||' | '||SID||','||SERIAL# "USER | SID,SERIAL#",SQL_ID,START_TIME,SOFAR/TOTALWORK*100 "%COMPLETE",
trunc(ELAPSED_SECONDS/60) MIN_ELAPSED, trunc(TIME_REMAINING/60) MIN_REMAINING,substr(MESSAGE,1,80)MESSAGE
from v\$session_longops where SOFAR/TOTALWORK*100 <>'100'
order by MIN_REMAINING;
SPOOL OFF
EOF
;;
esac
done
mail -s "ALERT: CPU Utilization on Server [${SRV_NAME}] has reached [${CPU_UTL}%]" ${MAIL_LIST} < ${CPULOG}
fi
rm -f ${CPUUTLLOG}
rm -f ${CPULOG}
# #########################
# Getting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -Ev ${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}|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
fi
# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
fi
# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
fi
# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
fi
# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
echo "e.g."
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
mail -s "dbalarm script on Server [${SRV_NAME}] failed to find ORACLE_HOME, Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory" ${MAIL_LIST} < /dev/null
exit
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
# ##########################
# 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
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 [ -z "${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`
fi
# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo ${VAL1}| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
DB_NAME_LOWER=$( echo "${DB_NAME_UPPER}" | tr -s '[:upper:]' '[:lower:]' )
export DB_NAME_UPPER
export DB_NAME_LOWER
# DB_NAME is Uppercase or Lowercase?:
if [ -d ${ORACLE_HOME}/diagnostics/${DB_NAME_LOWER} ]
then
DB_NAME=${DB_NAME_LOWER}
else
DB_NAME=${DB_NAME_UPPER}
fi
# #########################
# 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
# ###################
# 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:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name='db_block_size';
exit;
EOF
)
blksize=`echo $VAL312|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# #####################
# Getting DB ROLE:
# #####################
VAL312=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select DATABASE_ROLE from v\$database;
exit;
EOF
)
DB_ROLE=`echo $VAL312|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
case ${DB_ROLE} in
PRIMARY) DB_ROLE_ID=0;;
*) DB_ROLE_ID=1;;
esac
# ############################################
# Checking 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 200 pages 1000
spool ${LOG_DIR}/long_running_jobs.log
PROMPT Long Running Jobs:
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
rm -f ${LOG_DIR}/long_running_jobs.log
fi
# ############################################
# Checking FAILED JOBS ON THE DATABASE:
# ############################################
VAL40=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
--SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM dba_scheduler_jobs where FAILURE_COUNT <> '0') FAIL_COUNT FROM dual;
SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED') FAIL_COUNT FROM dual;
exit;
EOF
)
VAL50=`echo ${VAL40} | awk '{print $NF}'`
if [ ${VAL50} -ge ${FAILDJOBSTHRESHOLD} ]
then
VAL60=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 200 pages 1000
spool ${LOG_DIR}/failed_jobs.log
PROMPT DBMS_JOBS:
PROMPT ^^^^^^^^^^
col LAST_RUN for a25
col NEXT_RUN for a25
set long 9999999
--select dbms_xmlgen.getxml('select job,schema_user,failures,LAST_DATE LAST_RUN,NEXT_DATE NEXT_RUN from dba_jobs where failures <> 0') xml from dual;
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 where failures <> '0';
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT ^^^^^^^^^^^^^^^
col OWNER for a25
col JOB_NAME for a40
col STATE for a11
col STATUS for a11
col FAILURE_COUNT for 999 heading 'Fail'
col RUNTIME_IN_LAST24H for a25
col RUN_DURATION for a14
--HTML format Outputs:
--Set Markup Html On Entmap On Spool On Preformat Off
-- Get the whole failed runs in the last 24 hours:
select to_char(LOG_DATE,'DD-Mon-YYYY hh24:mi:ss')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED';
--XML Output
--select dbms_xmlgen.getxml('select to_char(LOG_DATE,''DD-Mon-YYYY hh24:mi:ss'')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>''SUCCEEDED''') xml from dual;
spool off
exit;
EOF
)
mail -s "WARNING: FAILED JOBS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" ${MAIL_LIST} < ${LOG_DIR}/failed_jobs.log
rm -f ${LOG_DIR}/failed_jobs.log
fi
# ############################################
# LOGFILE SETTINGS:
# ############################################
# Logfile path variable:
DB_HEALTHCHK_RPT=${LOG_DIR}/${DB_NAME}_HEALTH_CHECK_REPORT.log
export DB_HEALTHCHK_RPT
# Flush the logfile:
echo "^^^^^^^^^^^" > ${DB_HEALTHCHK_RPT}
echo "REPORTED BY: ${SCRIPT_NAME}" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
# ############################################
# Checking RAC/ORACLE_RESTART Services:
# ############################################
case ${CLUSTER_CHECK} in
y|Y|yes|YES|Yes)
# 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 "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "CLUSTERWARE CHECKS:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
FILE_NAME=${GRID_HOME}/bin/ocrcheck
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "OCR DISKS CHECKING:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${GRID_HOME}/bin/ocrcheck >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
fi
FILE_NAME=${GRID_HOME}/bin/crsctl
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "VOTE DISKS CHECKING:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${GRID_HOME}/bin/crsctl query css votedisk >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_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 [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "${CLUSTER_TYPE} SERVICES:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
AWK=/usr/bin/awk
$AWK \
'BEGIN {printf "%-55s %-24s %-18s\n", "HA Resource", "Target", "State";
printf "%-55s %-24s %-18s\n", "-----------", "------", "-----";}' >> ${DB_HEALTHCHK_RPT}
$GRID_HOME/bin/crsctl status resource | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$1'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-55s %-24s %-18s\n", appname, apptarget, appstate; state=0;}' >> ${DB_HEALTHCHK_RPT}
fi
FILE_NAME=${ORACLE_HOME}/bin/srvctl
export FILE_NAME
if [ -f ${FILE_NAME} ]
then
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "DATABASE SERVICES STATUS:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
${ORACLE_HOME}/bin/srvctl status service -d ${DB_UNQ_NAME} >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
fi
fi
;;
esac
echo "" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
echo "Local Filesystem:" >> ${DB_HEALTHCHK_RPT}
echo "^^^^^^^^^^^^^^" >> ${DB_HEALTHCHK_RPT}
df -h >> ${DB_HEALTHCHK_RPT}
echo "" >> ${DB_HEALTHCHK_RPT}
# ############################################
# Checking Advisors:
# ############################################
# 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 200 pages 100
spool ${DB_HEALTHCHK_RPT} app
PROMPT
PROMPT ^^^^^^^^^^^^^^
PROMPT Tablespaces Size: [Based on Datafiles MAXSIZE]
PROMPT ^^^^^^^^^^^^^^
set pages 1000 linesize 200 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"
from dba_tablespace_usage_metrics;
PROMPT ^^^^^^^^^^^^^^
PROMPT ASM STATISTICS:
PROMPT ^^^^^^^^^^^^^^
select name,state,OFFLINE_DISKS,total_mb,free_mb,ROUND((1-(free_mb / total_mb))*100, 2) "%FULL" from v\$asm_diskgroup;
PROMPT ^^^^^^^^^^^^^^
PROMPT FRA STATISTICS:
PROMPT ^^^^^^^^^^^^^^
PROMPT
PROMPT FRA_SIZE:
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",
ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1) AS "%FULL_NOW" FROM V\$RECOVERY_FILE_DEST;
PROMPT FRA_COMPONENTS:
PROMPT ^^^^^^^^^^^^^^^^^
select * from v\$flash_recovery_area_usage;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT DATABASE GROWTH: [In the Last ~8 days]
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
set serveroutput on
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;
/
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^
PROMPT Active Incidents:
PROMPT ^^^^^^^^^^^^^^^^^
set linesize 200
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;
PROMPT
PROMPT OUTSTANDING ALERTS:
PROMPT ^^^^^^^^^^^^^^^^^^^
select * from DBA_OUTSTANDING_ALERTS;
PROMPT
PROMPT CORRUPTED BLOCKS:
PROMPT ^^^^^^^^^^^^^^^^^
select * from V\$DATABASE_BLOCK_CORRUPTION;
PROMPT
PROMPT BLOCKING SESSIONS:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200 pages 0 echo on feedback on
col BLOCKING_STATUS for a90
select 'User: '||s1.username || '@' || s1.machine || '(SID=' || s1.sid ||' ) running SQL_ID:'||s1.sql_id||' is blocking
User: '|| s2.username || '@' || s2.machine || '(SID=' || s2.sid || ') running SQL_ID:'||s2.sql_id||' For '||s2.SECONDS_IN_WAIT||' sec
----------------------------------------------------------------
Warn user '||s1.username||' Or use the following statement to kill his session:
----------------------------------------------------------------
ALTER SYSTEM KILL SESSION '''||s1.sid||','||s1.serial#||''' immediate;' AS blocking_status
from gv\$LOCK l1, gv\$SESSION s1, gv\$LOCK l2, gv\$SESSION s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s2.SECONDS_IN_WAIT desc;
PROMPT
PROMPT UN-USABLE INDEXES:
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT
set echo on feedback on pages 1000
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' from dba_indexes where status='UNUSABLE';
PROMPT
PROMPT INVALID OBJECTS:
PROMPT ^^^^^^^^^^^^^^^
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
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT RMAN BACKUP OPERATIONS: [LAST 24H]
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, 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;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^
PROMPT SCHEDULED JOBS STATUS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT DBMS_JOBS:
PROMPT ^^^^^^^^^^
set linesize 200
col LAST_RUN for a25
col NEXT_RUN for a25
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;
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT ^^^^^^^^^^^^^^^^
col OWNER for a15
col STATE for a15
col FAILURE_COUNT for 9999 heading 'Fail'
col "DURATION(d:hh:mm:ss)" for a22
col REPEAT_INTERVAL for a70
col "LAST_RUN || REPEAT_INTERVAL" for a65
col "DURATION(d:hh:mm:ss)" for a12
--col LAST_START_DATE for a40
select JOB_NAME,OWNER,ENABLED,STATE,FAILURE_COUNT,to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')||' || '||REPEAT_INTERVAL "LAST_RUN || REPEAT_INTERVAL",
extract(day from last_run_duration) ||':'||
lpad(extract(hour from last_run_duration),2,'0')||':'||
lpad(extract(minute from last_run_duration),2,'0')||':'||
lpad(round(extract(second from last_run_duration)),2,'0') "DURATION(d:hh:mm:ss)"
from dba_scheduler_jobs order by ENABLED,STATE;
PROMPT
PROMPT Current Running Jobs:
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(+)
order by "JOB_NAME|OWNER|SPID|SID",ELAPSED_TIME;
PROMPT
PROMPT AUTOTASK INTERNAL MAINTENANCE WINDOWS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col WINDOW_NAME for a17
col NEXT_RUN for a20
col ACTIVE for a6
col OPTIMIZER_STATS for a15
col SEGMENT_ADVISOR for a15
col SQL_TUNE_ADVISOR for a16
col HEALTH_MONITOR for a15
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,HEALTH_MONITOR FROM DBA_AUTOTASK_WINDOW_CLIENTS;
PROMPT
PROMPT FAILED DBMS_SCHEDULER JOBS IN THE LAST 24H:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col LOG_DATE for a36
col OWNER for a15
col JOB_NAME for a35
col STATUS for a11
col RUN_DURATION for a20
col ID for 99
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;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT Queries Running For More Than [${LONG_RUN_QUR_HOURS}] Hours:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col module for a30
col DURATION_HOURS for 99999.9
col STARTED_AT for a13
col "USERNAME| SID,SERIAL#" for a30
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 last_call_et > 60*60*${LONG_RUN_QUR_HOURS}
and status = 'ACTIVE';
PROMPT ^^^^^^^^^^^^^^^^
PROMPT ADVISORS STATUS:
PROMPT ^^^^^^^^^^^^^^^^
col CLIENT_NAME for a60
col window_group for a60
col STATUS for a15
SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT SQL TUNING ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT Last Execution of SQL TUNING ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col TASK_NAME for a60
set long 2000000000
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;
variable Findings_Report CLOB;
BEGIN
:Findings_Report :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :Findings_Report
PROMPT
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT MEMORY ADVISORS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT
PROMPT SGA ADVISOR:
PROMPT ^^^^^^^^^^^^
col ESTD_DB_TIME for 99999999999999999
col ESTD_DB_TIME_FACTOR for 9999999999999999999999999999
select * from V\$SGA_TARGET_ADVICE where SGA_SIZE_FACTOR > .6 and SGA_SIZE_FACTOR < 1.6;
PROMPT
PROMPT Buffer Cache ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^
col ESTD_SIZE_MB for 9999999999999
col ESTD_PHYSICAL_READS for 99999999999999999999
col ESTD_PHYSICAL_READ_TIME for 99999999999999999999
select SIZE_FACTOR "%SIZE",SIZE_FOR_ESTIMATE ESTD_SIZE_MB,ESTD_PHYSICAL_READS,ESTD_PHYSICAL_READ_TIME,ESTD_PCT_OF_DB_TIME_FOR_READS
from V\$DB_CACHE_ADVICE where SIZE_FACTOR >.8 and SIZE_FACTOR<1.3;
PROMPT
PROMPT Shared Pool ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^^^^^^
col SIZE_MB for 99999999999
col SIZE_FACTOR for 99999999
col ESTD_SIZE_MB for 99999999999999999999
col LIB_CACHE_SAVED_TIME for 99999999999999999999999999
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,
ESTD_LC_LOAD_TIME PARSING_TIME from V\$SHARED_POOL_ADVICE
where SHARED_POOL_SIZE_FACTOR > .9 and SHARED_POOL_SIZE_FACTOR < 1.6;
PROMPT
PROMPT PGA ADVISOR:
PROMPT ^^^^^^^^^^^^
col SIZE_FACTOR for 999999999
col ESTD_SIZE_MB for 99999999999999999999
col MB_PROCESSED for 99999999999999999999
col ESTD_TIME for 99999999999999999999
select PGA_TARGET_FACTOR "%SIZE",PGA_TARGET_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,BYTES_PROCESSED/1024/1024 MB_PROCESSED,
ESTD_TIME,ESTD_PGA_CACHE_HIT_PERCENTAGE PGA_HIT,ESTD_OVERALLOC_COUNT PGA_SHORTAGE
from V\$PGA_TARGET_ADVICE where PGA_TARGET_FACTOR > .7 and PGA_TARGET_FACTOR < 1.6;
PROMPT
PROMPT SEGMENT ADVISOR:
PROMPT ^^^^^^^^^^^^^^^^
select'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'-------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT CURRENT OS / HARDWARE STATISTICS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
select stat_name,value from v\$osstat;
PROMPT
PROMPT ^^^^^^^^^^^^^^^
PROMPT RESOURCE LIMIT:
PROMPT ^^^^^^^^^^^^^^^
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from gv\$resource_limit order by RESOURCE_NAME;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^
PROMPT RECYCLEBIN OBJECTS#:
PROMPT ^^^^^^^^^^^^^^^^^^^^
set feedback off
select count(*) "RECYCLED_OBJECTS#",sum(space)*$blksize/1024/1024 "TOTAL_SIZE_MB" from dba_recyclebin group by 1;
set feedback on
PROMPT
PROMPT [Note: Consider Purging DBA_RECYCLEBIN for better performance]
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT FLASHBACK RESTORE POINTS:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^
select * from V\$RESTORE_POINT;
PROMPT
PROMPT ^^^^^^^^^^^^^^^
PROMPT HEALTH MONITOR:
PROMPT ^^^^^^^^^^^^^^^
select name,type,status,description,repair_script from V\$HM_RECOMMENDATION where time_detected > sysdate -1;
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT Monitored INDEXES:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200 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 /
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^
PROMPT REDO LOG SWITCHES:
PROMPT ^^^^^^^^^^^^^^^^^^
set linesize 200
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;
PROMPT
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PROMPT Modified Parameters Since Instance Startup:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
col name for a45
col VALUE for a120
col DEPRECATED for a10
select NAME,VALUE,ISDEFAULT "DEFAULT",ISDEPRECATED "DEPRECATED" from v\$parameter where ISMODIFIED = 'SYSTEM_MOD' order by 1;
PROMPT
PROMPT ^^^^^^^^^^^^
PROMPT Cred Backup:
PROMPT ^^^^^^^^^^^^
col name for a35
col "CREATE_DATE||PASS_LAST_CHANGE" for a60
select name,PASSWORD HASH,CTIME ||' || '||PTIME "CREATE_DATE||PASS_LAST_CHANGE" 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 200 pages 1000
spool ${DB_HEALTHCHK_RPT} app
prompt
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt New Created objects [Last 24H] ...
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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
# ###############################################
# Checking AUDIT RECORDS ON THE DATABASE:
# ###############################################
# Check if Checking Audit Records is ENABLED:
case ${CHKAUDITRECORDS} in
Y|y|YES|Yes|yes)
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 200 pages 1000
spool ${LOG_DIR}/audit_records.log
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
prompt
prompt
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Failed Login Attempts in the last 24Hours ...
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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 returncode = 1017
and timestamp > (sysdate -1)
order by 1;
prompt
prompt
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Audit records in the last 24Hours AUD$...
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt
select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
from dba_audit_trail
where
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')
and timestamp > SYSDATE-1 order by EXTENDED_TIMESTAMP;
prompt
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt Fine Grained Auditing Data ...
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^
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;
spool off
exit;
EOF
)
cat ${LOG_DIR}/audit_records.log >> ${DB_HEALTHCHK_RPT}
fi
;;
esac
mail -s "HEALTH CHECK REPORT: For Database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" ${MAIL_LIST} < ${DB_HEALTHCHK_RPT}
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".
oracle@test02:~>
############## Daily Archive IO details ####################
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/archive_IO/archive_IO.sh
#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/daily_health_check/archive_IO/PROD_ARCHIVE_IO.log
printf "to:abc@abc.com\n" > $DR_log
#printf "cc:abc@abc.com\n" >> $DR_log
printf "subject: Daily Archive IO Report " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set lines 200
select name,open_mode,database_role from v\$database;
set timi on
select 'Started Time = '||to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;
COLUMN LUN FORMAT A70
COLUMN total_io FORMAT 999999999999
set pages 1000
SELECT SUBSTR(i.name,1,7) LUN,sum(x.phyrds+x.phywrts) "Total_IO" FROM v\$filestat x,SYS.ts$ ts,v\$datafile i,SYS.file$ f WHERE i
.file# = f.file# AND ts.ts# = f.ts# AND x.file# = f.file# group by SUBSTR(i.name,1,7);
select 'End Time = '||to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;
PROMPT
PROMPT
PROMPT Previous day archive logs generation
PROMPT ==================================================
select count(1),sum((BLOCKS*BLOCK_SIZE)/1024/1024/1024) "Total_archive_size_in_GB" from v\$archived_log where first_time >= trunc(sysdate) -1 and first_time <trunc(sysdate);
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
###################### Alert Monitor #####################
oracle@test02:~> cat /u04/MasterDB/scripts/alert_log/alert_monitor.sh
. /home/oracle/.bash_profile
export SID=SMRPRODB
#export DIR=/u04/MasterDB/scripts/alert_log
export DIR=/u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace
touch $DIR/alert_${SMRPRODB}.hist
touch $DIR/alert.err
cd =/u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace
tail -1000 alert_SMRPRODB.log >> $DIR/alert_SMRPRODB.hist
grep ORA- $DIR/alert_SMRPRODB.hist > $DIR/alert.err
diff $DIR/alert.err $DIR/alert_old.err >$DIR/alert_error.err
if [ `cat $DIR/alert_error.err|wc -l` -gt 0 ]
then
# mailx -s "${SID} SMRPRODB ALERT : ORA- ERRORS" varun.yadav@abc.com,core@abc.com< $DIR/alert.err
mailx -s "${SID} ALERT : ORA- ERRORS" varun.yadav@abc.com,core@abc.com< $DIR/alert.err
fi
mv $DIR/alert.err $DIR/alert_old.err
rm $DIR/alert_error.err
rm $DIR/alert_${SID}.hist
oracle@test02:~>
############ Alert log check ##########
oracle@test02:~> cat /u04/MasterDB/scripts/alert_log/check_alert_log.sh
#####################################################################
## check_alert_log.sh ##
##
## Author :
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u02/app
export ORACLE_HOME=/u02/app/oracle/diag/rdbms/prodb/PRODB/trace
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=PRODB
##
######## Other variables #################
##
DBA=varun.yadav@abc.com,core_oradba@abc.com
datevar=$(date)
##
#######
#######
#######
####### End of configuration
#######
#######
##
cd /u02/app/oracle/diag/rdbms/prodb/PRODB/trace
if [ -f alert_${ORACLE_SID}.log ]
then
tail -500 alert_${ORACLE_SID}.log > /u04/MasterDB/scripts/alert_log/alert_work.log
grep ORA- /u04/MasterDB/scripts/alert_log/alert_work.log >> /u04/MasterDB/scripts/alert_log/alert.err
grep Shut /u04/MasterDB/scripts/alert_log/alert_work.log >> /u04/MasterDB/scripts/alert_log/alert.err
fi
export error << `cat /u04/MasterDB/scripts/alert_log/alert.err`
if[ `cat /u04/MasterDB/scripts/alert_log/alert.err|wc -l` -gt 0 ]
then
mailx -s “${ORACLE_SID} ORACLE ALERT ERRORS $datevar” $DBA < /u04/MasterDB/scripts/alert_log/alert_work.log
fi
fi
#rm -f /u04/MasterDB/scripts/alert_log/alert.err
#rm -f /u04/MasterDB/scripts/alert_log/alert_work.log
exit
oracle@test02:~>
##################### Stats Gathers Schema ###################################
oracle@test02:~> cat /u04/MasterDB/scripts/SMRPRODB_statsgather.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
stats_log=/u04/MasterDB/scripts/dmenvstats.log
printf "to:varun.yadav@abc.com\n" > $stats_log
printf "cc:abc@abc.com\n" >> $stats_log
printf "subject:Production Infodba Schema stats gather details " >> $stats_log
sqlplus -s "/as sysdba" <<EOF >> $stats_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT Infodba Schema stats gather details
PROMPT ==================================================
exec DBMS_Stats.Gather_Schema_Stats('INFODBA', estimate_percent => 10, degree => 3,cascade => true);
EOF
/usr/sbin/sendmail -t < $stats_log
oracle@test02:~>
oracle@test02:~> cat /u04/MasterDB/scripts/PRODB_statsgather.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin
stats_log=/u04/MasterDB/scripts/dmenvstats.log
printf "to:varun.yadav@abc.com\n" > $stats_log
printf "cc:abc@abc.com\n" >> $stats_log
printf "subject:Production Infodba Schema stats gather details " >> $stats_log
sqlplus -s "/as sysdba" <<EOF >> $stats_log
set lines 200
PROMPT DATABASE NAME
PROMPT =================
Select name,open_mode,database_role,db_unique_name from v\$database;
PROMPT Infodba Schema stats gather details
PROMPT ==================================================
exec DBMS_Stats.Gather_Schema_Stats('INFODBA', estimate_percent => 10, degree => 3,cascade => true);
EOF
/usr/sbin/sendmail -t < $stats_log
oracle@test02:~> cat /u04/MasterDB/scripts/smr_prod_check_instance.sh
#!/bin/sh
SERVICE=ora_pmon_PRODB
#replace ‘orcl’ with your sid
DBALIST="core@abc.com";export DBALIST
if ps -ef | grep -v grep | grep $SERVICE > /dev/null
then
#echo "$SERVICE service running, everything is fine" | mailx -s "database prodb DR on `hostname` is up" $DBALIST
#echo "Alert" | mailx -s "database prodb DR on `hostname` is UP" $DBALIST
else
echo "$SERVICE is not running contact DBA " $DBALIST | mailx -s "database prodb Production database 64 on `hostname` is down" $DBALIST
#echo "Alert" | mailx -s "database prodb DR on `hostname` is down" $DBALIST
fi
oracle@test02:~>
