Saturday, May 9, 2015

Oracle Database Health Check Script


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

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

This script was tested on Linux environments.

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

Your suggestions are most welcome.

In case the download link is not working, please find the full code below:

78 comments:

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

    Can u help me fix it ?

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

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

    For further help, please let me know.
    Mahmmoud ADEL

    ReplyDelete
  3. Not work for Solaris based database

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

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

    ReplyDelete
  6. This comment has been removed by the author.

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

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

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

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

    Script Terminated !

    Please suggest where is the issue??

    ReplyDelete
  9. Thanks for passing by.

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

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

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

    ReplyDelete
  10. Hi Adil ,

    does the script supports RAC ?

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

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

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

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

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

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

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

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

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

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

    Replace this block:

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


    With this block:

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


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

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

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

    This view V$DIAG_PROBLEM not exist.

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

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

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

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

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

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

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

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


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

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

    ReplyDelete
  20. also got error on scriptexecution for nfs mounted filesystem:

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

    Regards,
    Ivan

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

    my name is : Samer ALJazzar

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

    thanks and regards,
    Sam

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

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

    ReplyDelete
  24. i have two main OS used

    Solaris 11.3
    Linux Redhat

    Regards,
    Samer

    ReplyDelete
  25. Hi Mahmmud,

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

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

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

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

    Tnx for helping,
    Ivan


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

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

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

    Thanks and Best Regards
    humble oracle dba

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

    Thanks,
    Mahmmoud ADEL

    ReplyDelete
  29. Hi Mahmmoud,

    Thank you for quick answer. I appreciate :).

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

    e.g.

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

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

    last errors:

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

    Script was too long to attach it :(.

    Many Thanks and Best Regards
    humble oracle dba

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

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

    INTEGER_VAR1=${VAR1%.*}

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

    $echo $INTEGER_VAR1
    65

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

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

    ReplyDelete
  32. Hi Adel,

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

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

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

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

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

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

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

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

    ReplyDelete
  34. Hi Mahmmoud :

    Seen very less people like u.

    Please help me resolve the issue in script.

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

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

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

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

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

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

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

    ReplyDelete
  37. Hi Mac,

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

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

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

    ReplyDelete
  38. Hello Adel,

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

    Thanks in advance.

    ReplyDelete
  39. Hello Adel,

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

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

    Thanks,
    Venky

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

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

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

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

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

    ReplyDelete
  45. This comment has been removed by the author.

    ReplyDelete
  46. This comment has been removed by the author.

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

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

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

    ReplyDelete
  49. Hi Mahmmoud,

    I am getting below error while executing the script:

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

    My OS version is Linux el7 64 bit.

    Please help me in this

    Regards,
    Sanket

    ReplyDelete
  50. Hi Sanket,

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

    ReplyDelete
  51. Hi Mahmmoud,

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

    Regards,
    Sanket

    ReplyDelete
  52. Hi Mahmmoud,

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

    Regards,
    Sanket

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


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



    With this line:

    export SQLLINESIZE=200

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

    ReplyDelete
  54. Hi mahmmoud,

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

    Regards,
    Sanket

    ReplyDelete
  55. Thanks for sharing this wonderful blog. Helpful article ! Kindly visit us @ Chocolate gift box

    ReplyDelete
  56. 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

    ReplyDelete
  57. 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

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

    Thanks, Amit

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

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

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

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

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

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

    ReplyDelete
  61. In case you cannot copy the tar file between servers:

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

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

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

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

    Done your tar file is ready.

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

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

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

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

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

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

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

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

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

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

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

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

    ReplyDelete
  67. Hi Adel,

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

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

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

    Regards,
    Sanket D

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

    ReplyDelete
  69. Fantastic! I need you to do two things:

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

    With this line:
    USR_ORA_HOME=~

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

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

    # vi ~/.bash_profile

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

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

    ReplyDelete
  70. Sure let me try this and get back to you

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

    ReplyDelete
  72. This comment has been removed by the author.

    ReplyDelete
  73. Helpful for Oracle database health check and monitoring purpose. Thanks for sharing looking something for PostgreSQL as well.

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

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

    What is the problem?

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

    ReplyDelete
  76. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials , Oracle Project Portfolio Management (PPM) Cloud Tutorial and Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Manufacturing , Oracle APEX , Oracle Financials Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can check more aboutOracle Fusion Supply Chain Management Cloud , Oracle HCM Cloud , Oracle Project Portfolio Management (PPM) Cloud , Oracle Cloud Applications to better understand

    ReplyDelete