Do you want to receive an email report summarize the health status of your all databases running on the same server?
dbdailychk.sh script will do the job for you, it performs the following health checks each time it runs against all up & running instances on the server and sends you an E-mail report with the results for each database:
# CHECKING ALL DATABASES ALERTLOGS FOR ERRORS.
# CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS.
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING FLASH RECOVERY AREA UTILIZATION.
# CHECKING ASM DISKGROUPS UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHECKING AUDIT RECORDS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
# CHECKING ACTIVE INCIDENTS.
# CHECKING OUTSTANDING ALERTS.
# CHECKING DATABASE SIZE GROWTH.
# CHECKING OS / HARDWARE STATISTICS.
# CHECKING RESOURCE LIMITS.
# CHECKING RECYCLEBIN.
# CHECKING CURRENT RESTORE POINTS.
# CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE.
# CHECKING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INITIALIZATION PARAMETERS SINCE THE LAST DB STARTUP.
# CHECKING ADVISORS RECOMMENDATIONS:
# – SQL TUNING ADVISOR
# – SGA ADVISOR
# – PGA ADVISOR
# – BUFFER CACHE ADVISOR
# – SHARED POOL ADVISOR
# – SEGMENT ADVISOR
# RMAN BACKUP CHECK.
# REPORT Newly Created Objects [in the last 24h].
# REPORT Long Running Jobs.
# REPORT UNRECOVERABLE DATABASE FILES that don’t have a valid backup.
This script was tested on Linux environments.
How to use the script?
==================
Step 1: Download the script from this link:
——-
https://www.dropbox.com/s/w1dpw3iynphm07t/dbdailychk.sh?dl=0
Step 2: Customize the script to your environment:
——-
Change the E-mail address to your email address in line number 80
MAIL_LIST=”youremail@yourcompany.com“
Note: sendmail service should be well configured on the target machine in order to send emails from.
Customize the defined thresholds under THRESHOLDS section, as per your preferences:
Modify the numbers in red color:
# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:
HTMLENABLE=Y # Enable HTML Email Format [DB] FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95 # THRESHOLD FOR TABLESPACE %USED [DB]
UNUSEINDXTHRESHOLD=1 # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES [DB]
INVOBJECTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF INVALID OBJECTS [DB]
FAILLOGINTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED LOGINS [DB]
AUDITRECOTHRESHOLD=1 # THRESHOLD FOR NUMBER OF AUDIT RECORDS [DB]
CORUPTBLKTHRESHOLD=1 # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS[DB]
FAILDJOBSTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED JOBS [DB]
JOBSRUNSINCENDAY=1 # THRESHOLD FOR JOBS RUNNING LONGER THAN N DAY [DB]
NEWOBJCONTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF NEWLY CREATED OBJECTS [DB]
LONG_RUN_QUR_HOURS=1 # THRESHOLD FOR QUERIES RUNNING LONGER THAN N HOURS [DB]
CLUSTER_CHECK=Y # CHECK CLUSTERWARE HEALTH [OS]
CHKAUDITRECORDS=Y # CHECK DATABASE AUDIT RECORDS [increases CPU Load] [DB]
SHOWSQLTUNINGADVISOR=Y # SHOW SQL TUNING ADVISOR RESULTS IN THE REPORT [DB]
SHOWMEMORYADVISORS=Y # SHOW MEMORY ADVISORS RESULTS IN THE REPORT [DB]
SHOWSEGMENTADVVISOR=Y # SHOW SEGMENT ADVISOR RESULTS IN THE REPORT [DB]
SHOWJOBS=Y # SHOW DB JOBS DETAILS IN THE REPORT [DB]
SHOWHASHEDCRED=N # SHOW DB USERS HASHED VERSION CREDENTIALS IN THE REPORT [DB]
Step 3: Schedule the script to run in the crontab: [Recommended to run once a day during non-peak hours]
——–
[By Oracle user]
# crontab -e
#Add the following line to schedule the execution of dbdailychk.sh script. let’s say every morning:
0 6 * * * /home/oracle/dbdailychk.sh
Note: /home/oracle/dbdailychk.sh is the full path pointing to dbdailychk.sh script.
In case you want to run the script from root user crontab instead of oracle’s crontab:
[By root user]
# crontab -e
#Add this line to schedule the execution of dbdailychk.sh script every morning:
0 6 * * * su – oracle -c /home/oracle/dbdailychk.sh
Now, everyday morning you will receive an email summarize the health status of ALL databases running on the that server, also you will receive a separate e-mail if any of defined thresholds been hit.
By default the script will send the report in HTML format if it founds “sendmail” package installed on your system otherwise it will revert to TEXT format, If you don’t have “sendmail” installed on your system and you still enthusiastic to receive the E-mail report in HTML format then follow this link:
http://dba-tips.blogspot.com/2017/11/oracle-database-health-check-report-in.html
In case you are looking for a script for real-time databases & listeners monitoring plus CPU & filesystem monitoring plus monitoring other components, you may have a look at this link:
http://dba-tips.blogspot.com/2014/02/database-monitoring-script-for-ora-and.html
Feel free to download the whole DBA bundle which includes this script along with other database administration scripts that can help you in your day to day tasks:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL BUT WITHOUT ANY WARRANTY. IT IS PROVIDED “AS IS”.
Your suggestions are most welcome.
In case the download link is not working, please find the full code below:
Import Shell Script For Importing Data On Oracle
This is agreat script but i have this error when run script.
################
[oracle@fcsdb DBA_BUNDLE2]$ sh dbdailychk.sh
df: `/root/.gvfs': Permission denied
df: `/root/.gvfs': Permission denied
##########
Can u help me fix it ?
Well, the fastest solution for this error is to ignore it 🙂
This error will not affect the filesystem monitoring function inside the script.
In case you don't like to see this error, you can easily replace "df -h" command inside the script with "df -hx fuse.gvfs-fuse-daemon"
For further help, please let me know.
Mahmmoud ADEL
Not work for Solaris based database
bash-3.2$ ./dbdailychk.sh
chown: /export/home/oratest/BUNDLE_Logs: Not owner
chown: alert_test_new.log: Not owner
./dbdailychk.sh: line 267: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 293: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 305: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 316: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 390: /bin/sqlplus: No such file or directory
cat: cannot open /export/home/oratest/BUNDLE_Logs/tablespaces_DBA_BUNDLE.log
./dbdailychk.sh: line 400: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 423: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 424: /export/home/oratest/BUNDLE_Logs/blocking_sessions.log: No such file or directory
./dbdailychk.sh: line 437: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 439: [: -ge: unary operator expected
./dbdailychk.sh: line 465: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 467: [: -ge: unary operator expected
./dbdailychk.sh: line 505: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 507: [: -ge: unary operator expected
./dbdailychk.sh: line 546: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 548: [: -ge: unary operator expected
./dbdailychk.sh: line 614: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 616: [: -ge: unary operator expected
./dbdailychk.sh: line 643: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 645: [: -ge: unary operator expected
./dbdailychk.sh: line 696: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 706: /bin/sqlplus: No such file or directory
./dbdailychk.sh: line 715: [: -gt: unary operator expected
./dbdailychk.sh: line 1020: /bin/sqlplus: No such file or directory
The script was tested on Solaris and it's working fine.
You have to run the script under the Owner of the Oracle binaries e.g. oracle user.
This comment has been removed by the author.
Taner,
Most probably the script cannot get your ORACLE_HOME, because there is no record for it in /etc/oratab file. In this case, I suggest you to explicitly export your ORACLE_HOME in the VARIABLES section, so it will be like this:
# #########################
# Variables:
# #########################
export ORACLE_HOME=
I am not able to run..My os is HP-UX. I had replaced the email id and am getting below error.
#######################################################
Please EDIT line# 51 in dbdailychk.sh script and change
myself@@gmail.com to your E-mail address.
#######################################################
Script Terminated !
Please suggest where is the issue??
Thanks for passing by.
Although this script is not tested on HP-UX environment, but you shouldn't modify this line:
case ${MAIL_LIST} in "myself@gmail.com")
In order to make the script work for you, you have to modify it like this:
MAIL_LIST="myself@gmail.com"
case ${MAIL_LIST} in "youremail@yourcompany.com")
Hi Adil ,
does the script supports RAC ?
Thanks for your question.
Actually instance based checks like: DATABASES/LISTENERS ALERTLOGS, CPU/FILESYSTEM UTILIZATION will not run on all nodes. It will just run against the local node. The rest of checks are running against the database which doesn't matter whether it's a RAC DB or not.
Honestly speaking, On my production RAC environments, I run the health check script dbdailychk.sh on one node only. Because I'm already scheduling dbalarm script to run every 5 minutes on each node in order to report other issues at the nearest time.
You can download dbalarm script from this post:
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html
Thanks Adil , are you gong to update the script to support RAC related Checks ?
like crsstat , ocrcheck , votedisk , …. etc ,
Thanks OraDetector for that suggestion.
I've already added RAC support feature to this script and included your nick name in the feature description.
Added checking RAC status feature. [Recommended by: OraDetector]
I wonder if you can send me your name so I can update the description with your good name.
I am trying to run in Oracle Linux and get below error. when i run it directly, it succeeds. can you suggest what could be the issue?
++ /mnt/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/sqlplus -S '/ as sysdba'
+ VAL1='
SELECT name from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0'
Seems the script didn't manage to pick up the right ORACLE_SID on your environment. Honestly don't know the reason why without having a live look. but as a dirty fix you can export the right ORACLE_SID inside the script by doing the following:
Replace this block:
# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
do
export ORACLE_SID
With this block:
# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in YOUR_GOOD_ORACLE_SID
do
export ORACLE_SID
… So YOUR_GOOD_ORACLE_SID will be replaced with the right ORACLE_SID
e.g.
for ORACLE_SID in ERPDB1
Also it will give more clear picture if you can provide me with the output of this command:
ps -ef|grep pmon
Sir I had this problem:
Active Incidents:
^^^^^^^^^^^^^^^^^
LAST_OCCURENCE FROM V$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -10
*
ERROR at line 2:
ORA-00942: table or view does not exist
This view V$DIAG_PROBLEM not exist.
May I know your database version if you don't mind?
Thank you for providing this health check script! It provides so much useful information.
How this script actually performs on RAC (two node)? Having this entries in /etc/oratab on node 1(I've added comments for every entry just here, not really in oratab 🙂 ):
-MGMTDB:/u01/app/12.1.0.2/grid:N – ok, excluded in EXL_DB
+ASM1:/u01/app/12.1.0.2/grid:N – ok, excluded in EXL_DB
dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N – db_unique_name – added by installer
dwhcdb1:/u01/app/oracle/product/12.1.0.2/dbhome_1:N – instance_name – added manually by dba
When i run dbalert.sh on that node, i got email with message that "dwhcdb1" is down, but that is false:
The Following Instances are POSSIBLY Down on [*hostname removed by me] :
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
dwhcdb1
If above instances are permanently offline, please add their names to 'EXL_DB' parameter at line# 90 or hash their entries in /etc/oratab to let the script ignore them in the next run.
Current Running Instances:
************************
oracle 44402 1 0 Mar23 ? 00:00:58 asm_pmon_+ASM1
oracle 155269 1 0 Mar23 ? 00:01:16 ora_pmon_dwhcdb1
INS_NAME STATUS DB_STATUS LOGINS BLOCKED STARTUP_TIME
—————- ———— —————– ———- ——- ——————-
dwhcdb1 OPEN ACTIVE ALLOWED NO 23-MAR-17 08:39:36
Thank you for sharing this set of dba scripts – great job!
Ivan
also got error on scriptexecution for nfs mounted filesystem:
./dbalarm.sh: line 125: [: 192.168.xx.xx:/nfs/Backup: integer expression expected
Regards,
Ivan
Thanks Adil for adding my Name to the description of your script
my name is : Samer ALJazzar
for some reason the AWK block for CRS check hangs for ever , i think its a syntax issue i'm not able to figure it out
thanks and regards,
Sam
Hi Ivan,
Thanks for passing by. As this DB is a RAC one, you will need to do one of the following:
– Include the DB_NAME in the EXL_DB parameter inside the script e.g.:
EXL_DB="-MGMTDB|ASM|dwhcdb"
– Hash out the oratab entry which refers to the DB_NAME (not the instance name) e.g.:
#dwhcdb:/u01/app/oracle/product/12.1.0.2/dbhome_1:N – db_unique_name – added by installer
Hi Samer,
Thanks for your update. I'll update your good name inside the script once I publish the new release.
May I ask what is the Linux version you are trying to run the script on?
i have two main OS used
Solaris 11.3
Linux Redhat
Regards,
Samer
Hi Mahmmud,
Regarding my rac db – still cannot configure dbalarm script to work properly. If i include DB_NAME (dwhcdb) in EXL_DB list and hash out DB_NAME in oratab, script exits on "Getting ORACLE_SID" block and alert mail is sent:
subject:
ALARM: No Databases Are Running on Server: myserver !!!
and mail body:
Current running INSTANCES on server [myserver]:
***************************************************
oracle 44402 1 0 Mar23 ? 00:03:05 asm_pmon_+ASM1
oracle 159940 1 0 Apr12 ? 00:00:56 ora_pmon_dwhcdb1
Current running LISTENERS on server [myserver]:
***************************************************
oracle 55774 1 0 Mar23 ? 00:01:28 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 55813 1 0 Mar23 ? 00:01:24 /u01/app/12.1.0.2/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
Tnx for helping,
Ivan
Mahmoud this work will be perfect if you configure the output to be in html format
Hi,
Script is really impressive 🙂 and I am going to use it. I did some simple modifications. However I am still getting an errors like:
./dbreport.sh: line 1: :set: command not found
./dbreport.sh: line 889: [: 65,3127083: integer expression expected
./dbreport.sh: line 889: [: 92,4768884: integer expression expected
./dbreport.sh: line 889: [: 1,83010189: integer expression expected
./dbreport.sh: line 889: [: 95,3972318: integer expression expected
./dbreport.sh: line 889: [: ,1: integer expression expected
./dbreport.sh: line 889: [: 96,2540417: integer expression expected
./dbreport.sh: line 889: [: 73,8235938: integer expression expected
./dbreport.sh: line 889: [: 30,0611055: integer expression expected
./dbreport.sh: line 889: [: ,003051759: integer expression expected
./dbreport.sh: line 909: [: selected.: integer expression expected
I tried to use round functions and few other solutions but it seems to be a different problem. Anybody has any idea, what is wrong ?
Thanks and Best Regards
humble oracle dba
I can see from the list of errors that you have errors starting from line number 1, of course the script should be impressive in this way :-))
Actually I've know idea what kind of updates you have done to the script, and thus I'll not be able to tell what went wrong. If you don't mind, send a copy of your script after the modification and I'll try to have a look into it.
Thanks,
Mahmmoud ADEL
Hi Mahmmoud,
Thank you for quick answer. I appreciate :).
1. Everywhere where is ps -ef|grep pmon i added "| grep caalbtb", to find only this database name.
e.g.
for ORACLE_SID in $( ps -ef|grep pmon|grep caalbtb|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
2. And changed the oratab "/oracle/home/zw/oratab" to my own including only one database name caalbtb
last errors:
oracle@caredbs28:caalbdb$ ./dbalarm.sh
./dbalarm.sh: line 900: [: 65,3127083: integer expression expected
./dbalarm.sh: line 900: [: 92,4768884: integer expression expected
./dbalarm.sh: line 900: [: 1,82972042: integer expression expected
./dbalarm.sh: line 900: [: 95,3972318: integer expression expected
./dbalarm.sh: line 900: [: ,1: integer expression expected
./dbalarm.sh: line 900: [: 96,2540417: integer expression expected
./dbalarm.sh: line 900: [: 73,8235938: integer expression expected
./dbalarm.sh: line 900: [: 30,037208: integer expression expected
./dbalarm.sh: line 900: [: ,003051759: integer expression expected
./dbalarm.sh: line 920: [: selected.: integer expression expected
Script was too long to attach it :(.
Many Thanks and Best Regards
humble oracle dba
Well, generally speaking, you will get that error when the variable value which is getting used in "if" or "case" condition is a float value "xx.xx", not an integer "xx".
e.g. In case, the variable value during the script execution will be 65.3 you will get above error, and thus you have to convert that float 65.3 to an integer number 65 to get rid of ".3" to be able to use that variable inside "if" and "case" conditions. in order to do so you have to use the following:
INTEGER_VAR1=${VAR1%.*}
e.g.
$ VAR1=65.3
$ INTEGER_VAR1=${VAR1%.*}
$echo $INTEGER_VAR1
65
I hope I have clearly explained it. This is why I hate math 🙂
Hi Mahmmoud, many thanks for your help. It helped :).
Hi Adel,
I'm getting the below error and observed that the issue is happening for multiple Instances having different owners.
HEALTH CHECK REPORT FOR DATABASE [CATDB] WAS SAVED TO: /home/oracle/BUNDLE_Logs/CATDB_HEALTH_CHECK_REPORT.log
mkdir: `/home/oraem12c/BUNDLE_Logs': Permission denied
chown: cannot access `/home/oraem12c/BUNDLE_Logs': Permission denied
chmod: cannot access `/home/oraem12c/BUNDLE_Logs': Permission denied
chown: changing ownership of `/tmp/BUNDLE_Logs/audit_records.log': Operation not permitted
chown: changing ownership of `/tmp/BUNDLE_Logs/CATDB_HEALTH_CHECK_REPORT.log': Operation not permitted
chown: changing ownership of `/tmp/BUNDLE_Logs': Operation not permitted
grep: /home/oraem12c/.bash*: Permission denied
grep: /home/oraem12c/.*profile: Permission denied
HEALTH CHECK REPORT FOR DATABASE [EMCC] WAS SAVED TO: /tmp/BUNDLE_Logs/EMCC_HEALTH_CHECK_REPORT.log
Need to modify the script in USR_ORA_HOME should have privileges to access all the Instance owners.
I'm trying to troubleshoot and let me know if you have any fix already applied.
Honestly, I didn't consider this case during the script design (multiple databases running from different Oracle Homes each Oracle Home is owned by different user). But you can workaround this situation by running different versions of this script on each user's crontab, but make sure you exclude other instances owned by other users.
e.g.
In order to run the script against database [EMCC] which is owned by [oraem12c] user and to avoid running it against [CATDB] database which owned by user [oracle], change the following line for the script which will run with user [oraem12c]:
EXL_DB="-MGMTDB|ASM|CATDB"
And vice versa when you run the script with user [oracle]:
EXL_DB="-MGMTDB|ASM|EMCC"
So you will have a script for each user excludes other users databases. No permissions got changed, and thus auditors should be happy.
Hi Mahmmoud :
Seen very less people like u.
Please help me resolve the issue in script.
./dbdailychk.sh: line 216: bc: command not found
10557: Permission denied
./dbdailychk.sh: line 460: [: too many arguments
./dbdailychk.sh: line 468: [: too many arguments
./dbdailychk.sh: line 476: [: too many arguments
./dbdailychk.sh: line 535: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 544: [: too many arguments
./dbdailychk.sh: line 560: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 580: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 593: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 606: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 624: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 626: [: -ge: unary operator expected
./dbdailychk.sh: line 671: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 673: [: -ge: unary operator expected
./dbdailychk.sh: line 819: [: too many arguments
./dbdailychk.sh: line 846: [: -gt: unary operator expected
./dbdailychk.sh: line 1298: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 1300: [: -ge: unary operator expected
./dbdailychk.sh: line 1342: ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin: No such file or directory
./dbdailychk.sh: line 1344: [: -ge: unary operator expected
HEALTH CHECK REPORT FOR DATABASE [] WAS SAVED TO: /home/oracle/BUNDLE_Logs/_HEALTH_CHECK_REPORT.log
[oracle@n2vl-td-kyc13 ~]$ send-mail: warning: inet_protocols: disabling IPv6 name/address support: Address family not supported by protocol
postdrop: warning: inet_protocols: disabling IPv6 name/address support: Address family not supported by protocol
^C
[oracle@n2vl-td-kyc13 ~]$
Thanks for passing by. Looks you got a bunch of errors!. But I can see the script didn't manage to to set the right ORACLE_HOME during its execution. So I would recommend you to set it manually inside the script just below the variables section.
e.g.
# #########################
# Variables:
# #########################
export ORACLE_HOME=/u01/app/oracle/…
Regarding the other "send-mail" error, you may need to read the solution in this post:
https://unix.stackexchange.com/questions/64414/ipv6-support-is-disabled-warnings
Thnak for your reply. I have set oracle home. Now few errors.
[oracle@n2vl-td-kyc13 DBA_BUNDLE2]$ ./dbdailychk.sh
./dbdailychk.sh: line 148: bc: command not found
./dbdailychk.sh: line 1350: /bin/lsnrctl: No such file or directory
tail: cannot open '/trace/-no_crs_notify.log' for reading: No such file or directory
grep: invalid option — '_'
Usage: grep [OPTION]… PATTERN [FILE]…
Try 'grep –help' for more information.
./dbdailychk.sh: line 1350: /bin/lsnrctl: No such file or directory
tail: cannot open '/trace/-no_crs_notify.log' for reading: No such file or directory
[oracle@n2vl-td-kyc13 DBA_BUNDLE2]$
Hi Mac,
You're using a very old version of dbdailychk script which is under DBA_BUNDLE version 2, the latest release is now 4.1, kindly download it from this post:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html
Every month a new release of DBA Bundle get released with new features and bug fixes.
In case the script still giving the same error, kindly export your ORACLE_HOME as shown in my last comment, and keep me updated in case you get any errors.
Hello Adel,
Is it possible to provide Top 10 fragmented tables and inactive session count with threshold limit for the Oracle version 11g in RHEL 6.5 Version?
Thanks in advance.
Hello Adel,
I have not tested your script. But looking at the script shows the effort you put in putting this together. Excellent work. I have one question about handling the ORACLE HOME for 12.2 as they do not have the oratab entries due to the Flex ASM feature.
I plan to test this on Linux. Will give it a try and let you know if I see any issues.
Thanks,
Venky
Ideally, this not supposed to make any problem. The script will determine the right ORACLE_HOME using different ways one of them is by checking /etc/oratab, in case, ORACLE_HOME information is not available in oratab the script will look go through other methods to get it.
But rule number 1, as you already mentioned, the script should be tested on a similar test environment before it gets deployed on production.
Searching for Oracle Health Check? Call us 1-800-450-8670 to Oracle Database System
In the event that you distinguish moderate Oracle inquiries or lazy execution of your Oracle Database at that point handle these issues by reaching to Cognegic's Remote DBA Experts. Here we consistently check your setup setting or execution measurements and give you most ideal way to tackle your Oracle related issues. You can take our best help by dialing this number 1-800-450-8670 and ask your inquiries to Online Oracle DB Support.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Is it possible to run the script from Central Inventory and connect to each database using TNS and give the results?
Well, the short answer it will not work with this script, as it's mainly dependant on OS authentication to connect in a passwordless fashion to the local DBs on the same server.
Thanks for the update also is it possible to get the backcup status(RMAN/exp) report from the local server using this script.
This comment has been removed by the author.
I have executed the script,but its not showing db_name , db_status(up/down), listner_name, listener_status(up /down), OS files system.
This comment has been removed by the author.
Regarding RMAN backup status, there is a section in the report shows RMAN BACKUP OPERATIONS in the last 24 hours.
The script only runs against up & running databases, listener information is not within the scope of this script, you can try another script called dbalarm that can monitor those elements along with their logs:
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html
Hi Mahmmoud,
I am getting below error while executing the script:
bash: $'r': command not found
bash: dbdailychk.sh line 102: syntax error near unexpected token `newline'
'bash: dbdailychk.sh line 102: ` echo ;;
My OS version is Linux el7 64 bit.
Please help me in this
Regards,
Sanket
Hi Sanket,
Most probably something went wrong when you copied the script to your machine. Bad character, lost character! Try to re-copy the script again and compare it with the GitHub version shown at the bottom of the post.
Hi Mahmmoud,
Tried with downloading the script 4-5 times but still getting the same error.
Can you please help?
Regards,
Sanket
Hi Mahmmoud,
Tried with downloading the script 4-5 times but still getting the same error.
Can you please help?
Regards,
Sanket
Hi Sanket,
Looks the script is failing in the first "case" block, let's troubleshoot it by replacing the following "case" block:
case ${MAIL_LIST} in "youremail@yourcompany.com")
echo
echo "##############################################################################################"
echo "You Missed Something :-)"
echo "In order to receive the HEALTH CHECK report via Email, you have to ADD your E-mail at line# 90"
echo "by replacing this template [youremail@yourcompany.com] with YOUR E-mail address."
echo "DB HEALTH CHECK report will be saved on disk…"
echo "##############################################################################################"
export SQLLINESIZE=165
echo;;
*) export SQLLINESIZE=200
esac
With this line:
export SQLLINESIZE=200
In case it's still throwing errors and I guess it will do so 🙂 , I would recommend to have a remote session with you, as I think there is something unique in your environment causing this error. Please email me on mahmmoudadel@hotmail.com
Hi mahmmoud,
Even after changing the CASE block, error persists. I have sent you an email on your personal id as well, please let me know your preferable time for remote session.
Regards,
Sanket
Thanks for sharing this wonderful blog. Helpful article ! Kindly visit us @ Chocolate gift box
Excellent Script Adel
Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live. I have bookmarked more article from this website. Such a nice blog you are providing ! Kindly Visit Us @ Best Travels in Madurai | Tours and Travels in Madurai | Madurai Travels
Thanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays.Well written article Thank You for Sharing with Us pmp training centers in chennai| pmp training in velachery | project management courses in chennai |pmp training in chennai | pmp training institute in chennai | pmp training in chennai
Sometimes the email report size comes larger than 5mb and get blocked by the exchange, any idea how to control the report size?
Thanks, Amit
Then you can disable a few advisors outputs, which make the email size go big.
I recommend you to start with disabling showing of the following advisor (SQL Tuning Advisor & Segment Advisor) results in the report by setting their flag in THRESHOLD section to N:
# #########################
# THRESHOLDS:
# #########################
# Modify the THRESHOLDS to the value you prefer:
….
SHOWSQLTUNINGADVISOR=N # SHOW SQL TUNING ADVISOR RESULTS IN THE REPORT [DB]
SHOWSEGMENTADVVISOR=N # SHOW SEGMENT ADVISOR RESULTS IN THE REPORT [DB]
Some databases have a huge number of audit records and you may not have a need to look into it on a daily basis so you can disable showing audit records in the report by setting its flag in THRESHOLD section to N:
CHKAUDITRECORDS=N # CHECK DATABASE AUDIT RECORDS [increases CPU Load] [DB]
As you can see you can easily control the data appear in the report by playing with the values in the THRESHOLD section, but first, read the control comment carefully before editing.
I have a problem with copying the bundle tar file between the servers because the route "including sftp" is blocked. can you suggest a away can over come this situation?
In case you cannot copy the tar file between servers:
1- convert the tar file to binary text file using this command:
od -An -vtx1 DBA_BUNDLE5.tar > DBA_BUNDLE5.txt
2- create an empty file called DBA_BUNDLE5.txt on the target server.
3- Copy the content of DBA_BUNDLE5.txt from the source server and paste it to the target server DBA_BUNDLE5.txt, you may end up with copying 60k rows or more, so try to make the session buffer big enough to let you copy all the contents to the clipboard in one go, it's an option called "lines of scrollback" in Putty set it to 70000 or more.
4- Convert the text binary file on the destination server to a tar file:
LC_ALL=C tr -cd 0-9a-fA-F < DBA_BUNDLE5.txt | xxd -r -p > DBA_BUNDLE5.tar
Done your tar file is ready.
Hi Adel, Thanks for this script. I'm modifying script to pass my username and password as I don't have sysdba priviege yet…but can you tell me how long will the execution of this script will take in Non-Prod environment.Here I have cluster setup but not RAC and I'm ignoring other instances as I'm just trying to run it on only one instance.
Thanks in advance 🙂
Hi Vageeha,
The script ideally takes less than a minute to run. You can ignore the instances you don't want the script to run against by feeding them to EXL_DB parameter at line# 150. i.e. in case you want the script to ignore "SALES" and "hr" instances you can modify the parameter like this:
EXL_DB="-MGMTDB|ASM|SALES|hr"
Hi Adel,
Thanks for your reply previously.My script is 99% done for my usage but i am trying to reduce my space between two topics for example,in your output space is high above queries running for more than an hour i hope you understand this and guide me how can i reduce or increase it as i wish.
Thanks again 🙂
Hi Adel,
Sorry i missed this point in my previous post… incase if we don't have output for any check can we say it with no rows selected or something instead of leaving it blank?
Thank you!
Hi Vageetha,
In order to adjust the time of long running queries to be reported you have to change the following line# 1327:
last_call_et/60/60 "DURATION_HOURS"
let's say you want to report the queries that run for more than 30 min "instead of 60min) you adjust that line like this:
last_call_et/30/60 "DURATION_HOURS"
and so on …
But if you really interested in getting long running queries to be reported in a real time you have to use this script instead:
https://dba-tips.blogspot.com/2018/04/report-long-running-queries-long-active.html
where you can easily adjust the time in the threshold parameter section:
EXEC_TIME_IN_MINUTES=60 # Report Sessions running longer than N minutes [Default is 60 minutes].
Don't forget to provide your Email to this parameter:
EMAIL="youremail@yourcompany.com"
and schedule it in the crontab to run let's say every 5 minutes */5
Why I'm not recommending you to use use DB Health Check script for this purpose, because it supposed to run only one time a day due to its overhead on the DB.
Regarding the Blank output in the report, this means the feature is not being used in your database. i.e. if FRA section showing No Rows Selected this means you are not using Flash Recovery Area in your DB. same thing for the Advisors and other blank output if found.
Hi Adel,
How can we run this script if we are not able to sudo to ora or oracle user?
Due to contractual obligation i would not be able to run it with ora,oracle, grid or root user as i can sudo to them. I have my os id created where i have dba privileges and generally do all the stuff using that only.
Could you please help me in running your wonderful script for my environment?
Regards,
Sanket D
Before I suggest anything, Is your user id in DBA group? Can you log in to the database as sysdba:
sqlplus "/ as sysdba"
Yes Adel, I have that…
Fantastic! I need you to do two things:
1- Open the script, under "Getting ORACLE_HOME" section, Replace this line:
USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
With this line:
USR_ORA_HOME=~
2- Under your Linux User ID set ORACLE_HOME variable to the right path of ORACLE_HOME on that server:
i.e. "suppose that your ORACLE_HOME location is: /u01/app/oracle/product/11.2.0/db_1"
# vi ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
In case it's still failing, please paste the error message as well.
Sure let me try this and get back to you
One more thing … i have multiple oracle homes for multiple db's e.g. 11g has different and 12c has different in that case, do i have to export it one by one? or how it can be handled?
This comment has been removed by the author.
Helpful for Oracle database health check and monitoring purpose. Thanks for sharing looking something for PostgreSQL as well.
The script hang in this stage when running it for the first time:
[Grid Infrastructure Setup Detected] Locating Grid Infrastructure ALERTLOG …
Checking GRID INFRASTRUCTURE ALERTLOG …
What is the problem?
This is applicable to dbalarm script, I've already replied on the dbalarm post:
"cancel the current execution by pressing Ctrl+c
And execute the script again, you shouldn't face this issue again."
Wow!!!! WoW!!!! can't believe, The script is worked as mentioned. And really saved our 1 hr time for spending everyday manual healthcheck.
Really appreciate your effort on this.
hi Mahmmoud, happy new year, you re doing a good job i must confess. this script will be wonderful and will also save me alot of time. i have tested it on test environment. but i was having some challenges: i have sent you an e-mail already but let me past my out here:
^^^^^^^^^^^^^^^^^
Local_Filesystem:
^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^
ORACLE_RESTART_SERVICES:
^^^^^^^^^^^^^^^^^^^^^^^
Resource_Name State
————————————- ———————
ora.DATA.dg ONLINE on devdbr19
ora.FRA.dg ONLINE on devdbr19
ora.LISTENER.lsnr INTERMEDIATE on devdbr19
ora.RECO1.dg ONLINE on devdbr19
ora.RECO2.dg ONLINE on devdbr19
ora.asm ONLINE on devdbr19
ora.cssd ONLINE on devdbr19
ora.evmd ONLINE on devdbr19
ora.r19uatdb.db ONLINE on devdbr19
ora.t24live.db INTERMEDIATE on devdbr19
^^^^^^^^^^^^^^^^^^^^^^^^
DATABASE_SERVICES_STATUS:
^^^^^^^^^^^^^^^^^^^^^^^^
PRCD-1120 : The resource for database identifier could not be found.
PRCR-1001 : Resource ora.identifier.db does not exist
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
List_of_All_Running_Instances:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
oraprod 39452964 1 0 Dec 27 – 0:26 ora_pmon_T24LIVE
gridprod 19661670 1 0 Nov 29 – 0:26 asm_pmon_+ASM
oraprod 38404990 1 0 Dec 20 – 1:45 ora_pmon_R19UATDB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
List_of_All_Running_Listeners:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
gridprod 44368404 1 0 Oct 05 – 19:17 /u01/app/grid/product/19.3.0/grid/bin/tnslsnr LISTENER -inherit
^^^^^^^^^^^^^^^^^^^^^^^
List_of_All_Open_Ports:
^^^^^^^^^^^^^^^^^^^^^^^
Tablespaces Size
SP2-0158: unknown SET option "text/css">"
SP2-0552: Bind variable "AUTO" not declared.
ASM STATISTICS
SP2-0158: unknown SET option "text/css">"
SP2-0552: Bind variable "AUTO" not declared.
FRA STATISTICS
SP2-0158: unknown SET option "text/css">"
FRA SIZE
SP2-0158: unknown SET option "text/css">"
SP2-0552: Bind variable "AUTO" not declared.
FRA COMPONENTS
SP2-0158: unknown SET option "text/css">"
select * from v$flash_recovery_area_usage * ERROR at line 1: ORA-01507: database not mounted
DATABASE GROWTH
SP2-0158: unknown SET option "text/css">"
SP2-0158: unknown SET option "text/css">"
from DBA_HIST_SNAPSHOT) b * ERROR at line 19: ORA-06550: line 19, column 18: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 7, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 22, column 63: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 22, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 23, column 47: PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only ORA-06550: line 23, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 25, column 27: PLS-00201: identifier 'SNAP_TIME Database Size(GB)' must be declared ORA-06550: line 25, column 5: PL/SQL: Statement ignored ORA-06550: line 27, column 42: PLS-00364: loop index variable 'ROW' use is invalid ORA-06550: line 27, column 13: PL/SQL: Statement ignored ORA-06550: line 28, column 30: PLS-00364: loop index variable 'ROW' use is invalid ORA-06550: line 28, column 9: PL/SQL: Statement ignored
Active Incidents
This comment has been removed by the author.
note: its on AIX, it will work i think so, but may be something needs to be twik
Hi stephuric,
Thanks for passing by, actually this script is not compatible with container type database, as it's login directly using / as sysdba
No, actually this script is not compatible with AIX. Sorry!
thanks for your response here. initially, when I ran the script, the OS report came out fine but the Database checks all came out with error:
SP2-0158: unknown SET option “text/css”>”
and
SP2-0552: Bind variable “AUTO” not declared
Then, I removed from the script, then some of the database check metrics came out but some did not with only error SP2-0552: Bind variable “AUTO” not declared. What do I need to do to mitigate against this error, as it seems it is working on AIX, you have done a great work already
i removed:———style type="text/css"
its a single instance database and not a PDB
Would you mind running the following code piece by oracle user from the shell, and let me know the output:
${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
prompt
select platform_name,CDB from v$database;
exit;
EOF
This comment has been removed by the author.
This comment has been removed by the author.
PLATFORM_NAME
——————————————————————————–
CDB
—
AIX-Based Systems (64-bit)
NO
i have to follow it like this:
bash-5.0$ cd $ORACLE_HOME/bin
bash-5.0$ sqlplus -S "/ as sysdba" < prompt
> select platform_name,CDB from v$database;
> exit
> EOF
PLATFORM_NAME
——————————————————————————–
CDB
—
AIX-Based Systems (64-bit)
NO
Would you mind executing the script from the shell and send me all the output (not the report) to my email to investigate it further: mahmmoudadel@hotmail.com
i have forwarded it to your mail as requested. am also available if you will be chanced to have a session with me
good day mahmmoud, trust you re doing well today? thanks for your support and assistant here yesterday, still waiting to hear from you.