Saturday, February 15, 2014

Oracle Database Administration Scripts | DBA Bundle

If you were googling for a DBA scripts/tools to help you out with managing the mighty Oracle DB then you have landed on the right blog :-)
I know you're super busy and have no time for reading long articles, so let me jump to the bottom line, the DBA Bundle is a group of shell scripts bundled all together in one tar file, once deployed on the system it allows you to execute different tasks using one single Linux command alias. Its main goal is to simplify your day-to-day common/complex DBA tasks.

DOWNLOAD the latest version from this link:
https://www.dropbox.com/s/le5pxctq0w6v49u/DBA_BUNDLE7.tar?dl=0
 
After opening the link, click on the Down Arrow on the right side:



In case the Dropbox website is blocked in your area, or you cannot copy files directly to the server you want to operate the bundle from due to security restrictions; then do this small trick:

1- On the Server, create a new empty file with name DBA_BUNDLE7.txt:
     # touch
DBA_BUNDLE7.txt
2- Open below link and Copy its content (Ctrl+a then Ctrl+c) and paste it in the DBA_BUNDLE7.txt file:
3- Execute this command to convert the text file to a tar file:
     # LC_ALL=C tr -cd 0-9a-fA-F < DBA_BUNDLE7.txt | xxd -r -p > DBA_BUNDLE7.tar

Then continue to follow below instructions ...
 

What this Bundle can do?
In a nutshell, it's a multi-tool can easily get deployed on any Oracle server. It will help you out to open the database/ASM instance alertlog, Clusterware log, listener config file, display the Clusterware services status, display the tablespaces/ASM diskgroups utilization, display the active sessions, display the blocking locks, display the invalid objects, display audit records, gather database statistics, perform data export, take an RMAN/Cold backup and more and more of such activities using ONE single command from OS shell for each activity without the need to google for the commands you want to use!

How it works: First, download the bundle tar file and extract it under Oracle owner home directory, e.g. /home/oracle  
Second, go to the bundle extracted location and execute "aliases_DBA_BUNDLE.sh" script, using "." command
e.g.
.  aliases_DBA_BUNDLE.sh
This script will get you up and running with the bundle by doing two things:
1- Set the default database environment, so whenever you run any of common aliases like "alert, tns, bdump,..." it will automatically point to the respective files for the default database.
e.g. if you have two running instances (orcl & salesdb), and you want to open the alertlog for salesdb, just run ". aliases_DBA_BUNDLE.sh"  script, enter salesdb number from the displayed list and then type alert/vialert to view salesdb alertlog file.
2- Add the bundle aliases inside the user's profile to make it easy for you to call any script from any working directory using one command alias without the need to step under the bundle directory.
3-At the end it will display all the aliases along with their description in a tabular format. So, you don't have to memorize the aliases, later you can execute "bundle" command to swap between default databases or to view the aliases' description.

For interactive scripts, whenever you execute any of them you will be prompted for the database number to enter in case you have more than one up & running database on the same machine.

Scripts Description:
Now let me give you a brief description for each script in the bundle:


Generic Aliases Associated with the Default Selected Database
Shell Alias Description
bundle Set a database as a default database (all generic aliases will be associated to this database)
alert Open the Database Alertlog with tail -f
vialert Open the Database Alertlog with vi editor
raclog Open the Clusterware/Oracle Restart Alertlog
sql Open sqlplus ‘/ as sysdba’
p List all Running Database Instances (PMON Processes)
lsn List Running Listeners
lis Open listener.ora file with vi editor
tns Open tnsnames.ora file with vi editor
pfile Open the default instance PFILE with vi editor
spfile Open the default instance SPFILE with view editor
oh Go to $ORACLE_HOME directory
dbs Go to $ORACLE_HOME/dbs
aud Go to $ORACLE_HOME/rdbms/audit
bdump Go to BACKGROUND_DUMP_DEST
network Go to $ORACLE_HOME/network/admin
removebundle Remove all the bundle Aliases from the System. (When you run it, let me know the reason)
Scripts With aliases

Script Name Shell Alias Description
table_info.sh  tableinfo Show specific table’s important info (size, indexes, non indexed FK, constraints,...).
oradebug.sh   oradebug Generate Hang Analysis report using oradebug tool in case of instance hang. (New script in V3.6)
active_sessions.sh  active Show the current active sessions and their blocking sessions, along with long running operations + Current running jobs + long running queries. (New script in V3.6)
session_details.sh  session List the Details of a specific user session. (If no input provided, it will list all sessions on the instance).
all_sessions_info.sh sessions List All connected sessions on all running instances [RAC DB] along with their distribution in details.
process_info.sh  spid Show the DB Session details when providing its Unix PID.
sql_id_details.sh sqlid Show the details of a specific SQL STATEMENT by providing its SQL_ID and gives you the option of tuning it using SQL TUNING ADVISOR.
Co-author: Farrukh Salman [Karkoor]
asmdisks.sh asmdisks Show ASM Diskgroups and their size, ASM disks, ASM disks mount points
On OS.
tablespaces.sh  tbs List All TABLESPACES, ASM Disk Groups and FRA (if was configured) allocated size and free space details.
datafiles.sh  datafiles List All DATAFILES and their size.
export_data.sh   exportdata Export Full DB|SCHEMA|TABLE data. (Gives you the option of using exp or expdp utility for the export).
RMAN_full.sh      rmanfull Takes an online RMAN full backup for the database (gives you the option of number of channels/compressed/Encrypted backup type).
Archives_Delete.sh archivedel Delete all Archive logs older than (provided) number of days.
analyze_objects.sh Analyze Analyze All tables under a specific SCHEMA (using ANALYZE legacy command).
gather_stats.sh  gather Gather STATISTICS on a SCHEMA or TABLE using DBMS_STATS. http://dba-tips.blogspot.ae/2014/09/script-to-ease-gathering- statistics-on.html
rebuild_table.sh tablerebuild Rebuild a table and its related indexes.
db_locks.sh                        locks List Blocking LOCKS details on the database (blocking users, blocking locks on objects, long running operations).
db_jobs.sh jobs List All database Jobs (dba_jobs + dba_scheduler_jobs + Auto Tune Tasks and current running jobs and their wait status) + job DDL & its history if provided its name/number.  
invalid_objects.sh invalid List All invalid Objects on the DB + their compile statements.
biggest_100_objects.sh objects List the Biggest 100 Objects on the database.
object_size.sh objectsize Calculate any object size + its indexes size.
lock_user.sh lockuser Lock a specific DB User Account and expire the password.
unlock_user.sh unlockuser Unlock a specific DB User Account + the option of reset the account password.
audit_records.sh   audit Retrieve AUDIT data for a DB user in a specific date or number of days back. http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records- script.html
last_logon_report.sh lastlogin Show the last login date of ALL users in the database.
failed_logins.sh failedlogin Show the failed login attempts in the last provided n number of days.
parameter_val.sh  parm Show the value of a Visible/Hidden initialization Parameter.
user_details.sh    userdetail Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects.
user_ddl.sh userddl Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects.
object_ddl.sh  objectddl Generate DDL script for a database Object + its granted permissions on it.
role_ddl.sh roleddl Generate DDL script for a database role.
start_tracing.sh     starttrace Start TRACING an Oracle session activities in a trace file.  http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html  
stop_tracing.sh  stoptrace Stop TRACING an already traced Oracle session & provide the session’s trace file and its TKPROFED log version.  http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html  
oracle_cleanup.sh cleanup Backup & Clean up All DBs & Listeners’ Logs. http://dba-tips.blogspot.ae/2014/02/oracle-logs-cleanup-script.html
Scripts Without aliases
Script Name Description
dbalarm.sh Monitors ALERTLOGs of ALL Databases and Listeners log running on the server and instantly report ORA- errors and TNS- errors that appears in these logs to the DBA E-mail Address by sending a detailed email to the DBA along with monitoring CPU, Filesystem/FRA/Tablespaces utilization, blocking locks.
(you have to modify this parameter in line number 27 to point to your E-mail Address):
MAIL_LIST="youremail@yourcompany.com"
Note: sendmail service should be configured on the server.
*The best way to use this script is by schedule it to run in the crontab every 5 minutes (or less).
For more details:
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script- for-ora-and.html
dbdailychk.sh Perform the following health checks on all running databases on the server:
# 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:

Replace youremail@yourcompany.com template with your e-mail address.
You can also customize the defined thresholds as per your preferences under THRESHOLD section inside the script.
Last step, Schedule the script to run in the crontab e.g. one time early morning:
0 6 * * * /home/oracle/dbdailychk.sa
For more details:
http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check- script.html
delete_standby_archives.sh Deletes the applied Archives on STANDBY DATABASES
older than N hours (specified by the user). To be customized and scheduled from the crontab.
For more details:
http://dba-tips.blogspot.ae/2017/01/script-to-delete-applied- archivelogs-on.html
COLD_BACKUP.sh Takes a COLD BACKUP of a specific database
(But the beauty of this script once it take the cold backup it will generate another script to help you to restore the taken cold backup easily)
This script will perform the following activities: shutdown the database, take a cold backup, create a restore script (in case you want to restore this cold backup later) then it will automatically startup the database.
For more Details:
http://dba-tips.blogspot.ae/2014/02/cold-backup-script.html
SHUTDOWN_All.sh SHUTDOWN ALL running Databases & Listeners on The server. Keep away from children :-)
schedule_rman_full_bkp.sh Takes an RMAN Full backup for a specific database. Can be scheduled in the crontab.
You MUST adjust the variables/channels/maintenance section to match your environment.
schedule_rman_image_copy_bkp.sh Takes an RMAN Image/Copy for a specific database. Can be scheduled in the crontab.
You MUST adjust the variables/channels/maintenance sections to match your env.

Why consider RMAN image backups in your backup strategy? the answer is in this link:
http://dba-tips.blogspot.ae/2011/11/switch-database-to-rman-copy-backup-and.html
delete_applied_archives_on_standby.sh Deletes the applied archivelogs on a standby DB.
For More Details:
http://dba-tips.blogspot.com/2017/01/script-to-delete-applied-archivelogs-on.html
configuration_baseline.sh Collects all kind of configuration baseline data for OS and all running DATABASES to help you track and control the changes on your environment.
For more details:
http://dba-tips.blogspot.com/2016/12/configuration-baseline-script-for-linux.html
backup_ctrl_spf_AWR.sh Backup Controlfile as (Trace/RMAN BKP), Backup SPFILE and Generate AWR for full day This script can be scheduled in the crontab to run once a day. Script options/variables must be modified to match your environment. New Starting from V4.1
kill_long_running_queries.sh Kill all queries running longer than 2.5 Hours(can be customized inside the script) by specific modules (to be specified inside the script)
This script can be scheduled in the crontab. Script options/variables MUST be modified to get the killing criteria match your requirements. New Starting from V4.1
check_standby_lag.sh If you have a standby DB then you can use this script on Primary DB site to report to you any LAG happens between the primary and the standby DB.
The variables section at the top of the script must be populated by you to match your environment or the script will not be able to run.
This script can be scheduled in the crontab to run every 5 minutes. New Starting from V4.1
This link will show you how to use this script:
http://dba-tips.blogspot.ae/2017/11/shell-script-to-check-lag-sync-status.html
From time to time I'll keep updating this bundle with new scripts, fixing bugs and adding new features, so give this topic a visit at least once every 3 months to download the latest version.

As I mentioned inside each script, I'M SHARING THIS BUNDLE AND ITS SCRIPTS IN THE HOPE THAT IT WILL BE USEFUL FOR YOU, BUT WITHOUT ANY WARRANTY. ALL SCRIPTS IN THIS BUNDLE ARE PROVIDED "AS IS".

No one is perfect... that's why pencils have erasers.

Your suggestions, bug reporting, and comments are most welcome :-)

Lastly, A special thank you to the beautiful mind Abd El-Gawad Othman, without his support, suggestions, and encouragement I wouldn't be confident enough to share this bundle with you.

139 comments:

  1. great job, thanks for sharing!

    ReplyDelete
  2. Great work. Thank you!!

    ReplyDelete
  3. Mohamed, Anonymous gentleman, Rahul
    Thanks to you, I hope this bundle be beneficial for you.

    ReplyDelete
  4. Superb Mohamed, its one thing to spend your time on developing this beauty..but to share it for free, its something else.. :) Thanks a ton..

    ReplyDelete
  5. Manoj,
    Thank you for your nice compliment, long time back since I started my career in Oracle world, I've got tons of invaluable information from others web sites and blogs. now it's time to reciprocate the favor to the Oracle community by sharing my humble knowledge hoping that help others.

    ReplyDelete
  6. Thanks a lot Mahmmoud Adel it's very useful for daily activity for DBA.

    ReplyDelete
  7. Thanks Mahmomoud! That is really useful set of scripts!

    ReplyDelete
  8. thank you, i am going read all yours scripts
    Jean-Pierre

    ReplyDelete
  9. thanks you for yours scripts
    Jean-Pierre

    ReplyDelete
  10. Thank you sir for valuable information.

    ReplyDelete
  11. Thanks Mohamed!! Appreciate your efforts.

    ReplyDelete
  12. Very kind of you! Thanks

    ReplyDelete
  13. this is really awesome....Greatly appreciate...

    ReplyDelete
  14. Really awesome...
    Thanks for sharing Mahmmoud..

    ReplyDelete
  15. Thank you for sharing a great set of scripts!

    ReplyDelete
  16. great job.........
    thanks for sharing your knowledge

    ReplyDelete
  17. Great dear I am thankful to you as I am new to oracle

    ReplyDelete
  18. DBA Bundle is awesome tool for oracle database administration. Thanks

    ReplyDelete
  19. Super work Mahmoud, much appreciated.

    ReplyDelete
  20. DBA Metrix Solutions provides database administrator (DBA) that have responsibility of implementing, maintaining, tuning, repairing a database among other production support related functions. Our remote DBA involved in implementing security to protect the data. DBA metrix helps you to achieve all business goals with database consulting and support.
    DBA Metrix Solutions

    ReplyDelete
  21. Such bundle should be the FIRST thing ORACLE CORP HAD TO DO after SQLPLUS.
    GREAT! Thank you!

    ReplyDelete
  22. Hello,

    [oracle@ol6-112-rac1 DBA_BUNDLE3]$ . aliases_DBA_BUNDLE.sh
    -bash: /bin/sqlplus: No such file or directory
    -bash: /bin/sqlplus: No such file or directory

    Setting Up Alieses...

    Please help to check above error!
    My server OS plaform is Oracle Linux.

    [oracle@ol6-112-rac1 DBA_BUNDLE3]$ echo $ORACLE_HOME

    [oracle@ol6-112-rac1 DBA_BUNDLE3]$ echo $ORACLE_SID
    RAC1
    [oracle@ol6-112-rac1 DBA_BUNDLE3]$

    ReplyDelete
  23. Hi,

    This error indicates that your ORACLE_HOME variable is not set in your environment:
    You have one of two options:

    Option 1: export ORACLE_HOME variable in oracle user .bash_profile:
    e.g. add this line to /home/oracle/.bash_profile
    export ORACLE_HOME=

    Option 2: Add an extra line in your /etc/oratab e.g.:
    RAC1::N

    ReplyDelete
  24. Sorry some words got trimmed in my last comment. Blogger has treated them as HTML tags !

    Option 1: export ORACLE_HOME variable in oracle user .bash_profile:
    e.g. add this line to /home/oracle/.bash_profile
    export ORACLE_HOME=YOUR_ORACLE_HOME_FULL_PATH

    Option 2: Add an extra line in your /etc/oratab e.g.:
    RAC1:YOUR_ORACLE_HOME_FULL_PATH:N

    ReplyDelete
  25. Hello,

    I have added it but still the same.

    [oracle@ol6-112-rac1 DBA_BUNDLE3]$ . aliases_DBA_BUNDLE.sh
    -bash: /bin/sqlplus: No such file or directory
    -bash: /bin/sqlplus: No such file or directory

    Setting Up Alieses...

    ===========
    [oracle@ol6-112-rac1 ~]$ cat .bash_profile
    # .bash_profile

    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi

    # User specific environment and startup programs

    PATH=$PATH:$HOME/bin

    export PATH

    # Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=$TMP; export TMPDIR

    ORACLE_HOSTNAME=ol6-112-rac1.localdomain; export ORACLE_HOSTNAME
    ORACLE_UNQNAME=RAC; export ORACLE_UNQNAME
    ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
    GRID_HOME=/u01/app/11.2.0.3/grid; export GRID_HOME
    DB_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export DB_HOME
    ORACLE_HOME=$DB_HOME; export ORACLE_HOME
    ORACLE_SID=RAC1; export ORACLE_SID
    ORACLE_TERM=xterm; export ORACLE_TERM
    BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
    PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

    alias grid_env='. /home/oracle/grid_env'
    alias db_env='. /home/oracle/db_env'

    # The Following Entry For DBA_BUNDLE Aliases
    . /home/oracle/.DBA_BUNDLE_profile
    [oracle@ol6-112-rac1 ~]$
    ==============

    [oracle@ol6-112-rac1 ~]$ cat /etc/oratab
    #Backup file is /u01/app/oracle/product/11.2.0.3/db_1/srvm/admin/oratab.bak.ol6-112-rac1 line added by Agent
    #



    # This file is used by ORACLE utilities. It is created by root.sh
    # and updated by either Database Configuration Assistant while creating
    # a database or ASM Configuration Assistant while creating ASM instance.

    # A colon, ':', is used as the field terminator. A new line terminates
    # the entry. Lines beginning with a pound sign, '#', are comments.
    #
    # Entries are of the form:
    # $ORACLE_SID:$ORACLE_HOME::
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively. The third filed indicates
    # to the dbstart utility that the database should , "Y", or should not,
    # "N", be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    +ASM1:/u01/app/11.2.0.3/grid:N # line added by Agent
    RAC:/u01/app/oracle/product/11.2.0.3/db_1:N # line added by Agent
    [oracle@ol6-112-rac1 ~]$


    ==============

    Regards,
    Pitou

    ReplyDelete
  26. Hi Pitou,

    Thanks for your comment. Printing the values of your .bash_profile showed me the full picture.
    Actually you've triggered a small bug in the script where ORACLE_HOME should be defined without including any implicit variables. e.g.:
    The script will work if ORACLE_HOME was defined in .bash_profile like this:
    ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1

    But first, I recommend you to re-download the bundle tar file from the same link as I triggered another bug in your environment as well and have already fixed it.

    Also /etc/oratab should have an entry for your instance name "RAC1" but you only have an entry for database name "RAC".
    So adding an extra entry for your instance inside /etc/oratab will fix this issue permanently:
    RAC1:/u01/app/oracle/product/11.2.0.3/db_1:N # line added for DBA_BUNDLE deployment

    The last resort, is to explicitly define your ORACLE_HOME inside aliases_DBA_BUNDLE.sh script. Which I don't recommend in case you have more than one instance running from different ORACLE HOMEs. you can do that by adding this line:
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
    just BEFORE this block of code:
    # #########################
    # Getting DB_NAME:
    # #########################

    ReplyDelete
    Replies
    1. Hi,
      It works now after add bellow line to /etc/oratab.

      RAC1:/u01/app/oracle/product/11.2.0.3/db_1:N

      Thanks.
      Pitou

      Delete
  27. Thanks Very much Mahmomoud for this beautiful and helpful program. I was wondering if you have something similar for Oracle Apps DBA activities.
    Regards.

    ReplyDelete
  28. Thanks Mahmoud For your efforts to put such amazing bundle scripts together , one more request to go and I believe your script would be complete which is sqlplus output in html format

    thanks
    Sam

    ReplyDelete
  29. Thanks alot. This topic helps many newbies like me become stronger. Thank you!

    ReplyDelete
  30. hi ADEL,

    i got the following error while running the script ( Check_standby_lag.sh ) on Solaris

    cat: cannot open /etc/redhat-release: No such file or directory
    grep: illegal option -- o
    Usage: grep [-c|-l|-q] -bhinsvw pattern file . . .

    Primary DB Sequence is: 16257
    Standby DB Sequence is: 16257
    Number of Lagged Archives Between is: 0

    ReplyDelete
  31. Good to see you back Sameer!
    You can just ignore that error, it's a minor one as it's checking /etc/redhat-release which is not available on Solaris! it won't affect the script functionality. if the error is disturbing you can just hash this line:
    LNXVER=`cat /etc/redhat-release | grep -o '[0-9]'|head -1`

    ReplyDelete
  32. Hi Mahmmoud,

    Please help in this erros

    ./dbdailychk.sh: line 219: bc: command not found
    10557: Permission denied
    ./dbdailychk.sh: line 469: [: too many arguments
    ./dbdailychk.sh: line 477: [: too many arguments
    ./dbdailychk.sh: line 485: [: too many arguments
    ./dbdailychk.sh: line 542: 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 551: [: too many arguments
    ./dbdailychk.sh: line 567: 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 587: 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 600: 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 613: 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 631: 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 633: [: -ge: unary operator expected
    ./dbdailychk.sh: line 676: 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 678: [: -ge: unary operator expected
    ./dbdailychk.sh: line 824: [: too many arguments
    ./dbdailychk.sh: line 851: [: -gt: unary operator expected
    ./dbdailychk.sh: line 1374: 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 1376: [: -ge: unary operator expected
    ./dbdailychk.sh: line 1421: 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 1423: [: -ge: unary operator expected
    HEALTH CHECK REPORT FOR DATABASE [] WAS SAVED TO: /home/oracle/BUNDLE_Logs/_HEALTH_CHECK_REPORT.log

    ReplyDelete
  33. Hi Mac,

    Can you send me the output of the following commands please:

    cat /etc/oratab

    env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'

    grep -h 'ORACLE_HOME=\/' /home/oracle/.bash_profile /home/oracle/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1

    locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'

    ReplyDelete

  34. Hi Dear,

    Please find output:



    [oracle@n2vl-td-kyc13 ~]$ cat /etc/oratab


    #Backup file is /grid_home/app/12.2.0/gridhome/srvm/admin/oratab.bak.n2vl-td-kyc13 line added by Agent
    #



    # This file is used by ORACLE utilities. It is created by root.sh
    # and updated by either Database Configuration Assistant while creating
    # a database or ASM Configuration Assistant while creating ASM instance.

    # A colon, ':', is used as the field terminator. A new line terminates
    # the entry. Lines beginning with a pound sign, '#', are comments.
    #
    # Entries are of the form:
    # $ORACLE_SID:$ORACLE_HOME::
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively. The third field indicates
    # to the dbstart utility that the database should , "Y", or should not,
    # "N", be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    -MGMTDB:/grid_home/app/12.2.0/gridhome:N
    +ASM1:/grid_home/app/12.2.0/gridhome:N # line added by Agent
    test:/oracle_home/app/12.2.0/orahome:N
    ***************************************************************************************************
    [oracle@n2vl-td-kyc13 ~]$ env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'



    ORACLE_PATH=.:/oracle_home/app/dba_scripts/sql:/oracle_home/app/12.2.0/orahome/rdbms/admin
    LD_LIBRARY_PATH=/oracle_home/app/12.2.0/orahome/lib:/lib:/usr/lib
    ORACLE_BASE=/oracle_home/app
    PATH=/oracle_home/app/12.2.0/orahome/bin:/usr/sbin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/bin
    CLASSPATH=/oracle_home/app/12.2.0/orahome/jlib:/oracle_home/app/12.2.0/orahome/rdbms/jlib
    /oracle_home/app/12.2.0/orahome
    [oracle@n2vl-td-kyc13 ~]$
    ******************************************************************************************************************

    [oracle@n2vl-td-kyc13 ~]$ grep -h 'ORACLE_HOME=\/' /home/oracle/.bash_profile /home/oracle/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1


    /oracle_home/app/12.2.0/orahome
    [oracle@n2vl-td-kyc13 ~]$
    **************************************************************************************************************************

    [oracle@n2vl-td-kyc13 ~]$ locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'

    -bash: locate: command not found
    [oracle@n2vl-td-kyc13 ~]$

    ReplyDelete
  35. Thanks Mac. The problem is that you have the word "oracle_home" in your ORACLE_HOME path which confuses the script!

    Now let's tackle this problem by adding the following line:
    export ORACLE_HOME=/oracle_home/app/12.2.0/orahome

    exactly before these blocks in the script:


    # Check Long Running Transactions if CPUDIGMORE=Y:

    # ##########################
    # Neutralize login.sql file: [Bug Fix]
    # ##########################


    So, it should look like this after editing the script:

    export ORACLE_HOME=/oracle_home/app/12.2.0/orahome
    # Check Long Running Transactions if CPUDIGMORE=Y:


    export ORACLE_HOME=/oracle_home/app/12.2.0/orahome
    # ##########################
    # Neutralize login.sql file: [Bug Fix]
    # ##########################

    ReplyDelete
  36. the package is great, i would suggest that you add this post, as an html or pdf file, inside the tar file, this way the file will self-contain the description of what it contains.
    Amazing job, thanks for sharing

    ReplyDelete
  37. Thank you Okelz for your kind comment.
    In order to get a brief description for each script/alias inside the bundle just step under the bundle directory e.g. /home/oracle/DBA_BUNDLE4 then invoke aliases_DBA_BUNDLE.sh script with "." command e.g. . aliases_DBA_BUNDLE.sh it will ask you to select your default database then will print a brief description for each script/alias in a tabular format, you can also get the same by just executing "bundle" command alias from anywhere.

    ReplyDelete
  38. Thanks Adil once again,

    i appreciate if u can add a new changes/updates section on the main blog post

    ReplyDelete
  39. Hi Sameer,
    Adding the new features to the main post will make lengthy, but I made a new post summarizes the new scripts/features in V4.1.

    Here you go:
    http://dba-tips.blogspot.ae/2018/02/dba-bundle-v41-released.html

    ReplyDelete
  40. Hi Mahmmoud

    download version 4.1 and I get the following error:

    [oracle@localhost DBA_BUNDLE4]$ . aliases_DBA_BUNDLE.sh
    : no se encontró la orden...
    bash: aliases_DBA_BUNDLE.sh: línea 77: error sintáctico cerca del elemento inesperado `elif'
    'ash: aliases_DBA_BUNDLE.sh: línea 77: `elif [ $INS_COUNT -gt 1 ]
    [oracle@localhost DBA_BUNDLE4]$


    Can you tell me what happens

    Thank you very much

    ReplyDelete
  41. Hi Mahmmoud,

    Thank you very much for the scripts. can you please share if there is script to monitor archive log threshold.

    ReplyDelete
  42. Hi Mohammed Adel,
    export database script is very helpful for us.but import database script is missing can you share the same

    ReplyDelete
  43. Hi Mohammed -
    Is there a way to schedule the cleanup process to run as a cron job?

    ReplyDelete
  44. Hi Mohammed,

    outstanding work sharing the script. i am having a strange issue running
    . aliases_DBA_BUNDLE.sh:

    oracle@host:~/DBA_BUNDLE4$. aliases_DBA_BUNDLE.sh

    Please Use "." command to run this script.
    e.g.
    . ~/DBA_BUNDLE4/aliases_DBA_BUNDLE.sh

    oracle@host:~/DBA_BUNDLE4$

    ReplyDelete
  45. Apologize for the late reply on some comments due to the attack of spam comments which I've just cleaned up.

    Comment by Anonymous on July 10 2018: can you please share if there is script to monitor archive log threshold.
    Answer: Yes, as far you've configured db_recovery_file_dest and pointing the archivelogs to db_recovery_file_dest then the dbalarm.sh script will manage to pick it up. you need to adjust the following parameter inside dbalarm.sh script in case you're not happy with 95% threshold:
    FRATHRESHOLD=95 # THRESHOLD FOR FRA %USED [DB]

    Comment by Unknown on July 25 2018: export database script is very helpful for us.but import database script is missing can you share the same
    Answer: Actually I'm not comfortable with sharing critical scripts which can modify/restore data, if such scripts is used wrongly the impact will be severe.

    Comment by Sallie on August 14, 2018: Is there a way to schedule the cleanup process to run as a cron job?
    Answer: well, the script need minor changes to run from cron, like explicitly mention the ORACLE_SID and the backup location of logs "LOC1", along with removing the lines that prompt the user for the same.

    Comment by Felipe Viveros on August 22 2018: having a strange issue running . aliases_DBA_BUNDLE.sh
    Answer: Honestly, it's weird for me as well :-) Can you tru running it again in below fashion with full path:
    . ~/DBA_BUNDLE4/aliases_DBA_BUNDLE.sh

    ReplyDelete
  46. 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
  47. In case you cannot copy the tar file between the 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
  48. Hi Adil,

    I got the following error
    dbdailychk_html.sh[1614]: /export/home/oracle/sendEmail*/sendEmail: not found [No such file or directory]

    Please help to fix it

    Regards,
    Sam

    ReplyDelete
  49. Hi Sam,

    looks you didn't download/extract the sendmail binaries under user's home directory;

    1- Download sendEmail executables which will allow the script to send the report as an attachment:
    http://caspian.dotconf.net/menu/Software/SendEmail/

    2- Extract sendEmail package files under the home directory of the Oracle software Owner:
    # cd /export/home/oracle
    # tar xvf sendEmail-v1.56.tar

    Next week I'll update the Health check report script, so in case it didn't find sendmail binaries installed it will revert automatically from HTML to TEXT format.

    ReplyDelete
  50. I've tested your bundle, it's very handy but unfortunately I cannot use on production servers because System admin team doesn't give us DBAs access on database server machines due to security reasons.

    ReplyDelete
    Replies
    1. Security Reasons! funny! Security standards should be applied smartly not blindly.

      OK, Imagine you are a house owner having a kitchen and inside this kitchen only one appliance which is the fridge, then you grant your housekeeper access to the fridge to eat any food she wants, but she is not allowed to enter the kitchen, but according to this access she can eat the whole food inside the fridge at anytime. Now, we can conclude some facts from this example:
      - The kitchen (The Server) equals nothing without the fridge (The Database).
      - The fridge (The Database) equals nothing without the food inside (The Data).
      - The Housekeeper (The DBA) can eat the whole food (The Data) at anytime which makes the kitchen (The Server) useless because there is no food inside.

      The one million dollar question here, what a hell the gain the house owner has made out of banning the housekeeper from entering the kitchen?

      Pardon me, I've a radical believe here, no matter how tough the security standards your company is following any savvy DBA can reach to your precious item in your IT infrastructure which is "data" and can do whatever he wants. The database server is nothing without the database inside.

      The rule of thumb here; If you don't trust your DBA, don't hire him at the first place.
      Case closed.

      Delete
  51. Mahmmoud ADEL thank you very much

    ReplyDelete
  52. Hi ,
    can I use the rebuild_table.sh script with PDB?

    ReplyDelete
  53. Actually, I didn't test the bundle on PDBs, but most probably it will not work, as it first checks the running instances expecting that each instance represents one DB, unlike the PDB architecture!

    ReplyDelete
  54. Great dear, I am thankful to you.

    ReplyDelete
  55. Great post. Thanks for sharing such detailed information.
    Oracle course

    ReplyDelete
  56. cant seem to open the bundle script link, can you kindly share

    ReplyDelete
    Replies
    1. After opening the link, click the "down arrow button ⤓" just right to the Share button at the top.

      Delete
  57. Hello Mahmmoud ADEL,

    First all, thanks for this great job done!
    As you have mentioned that the DBABUNDLE will not work in the PDB database. I have tried it, can't find pdbs instances.

    Once again thanks for this excellent blog.

    ReplyDelete
  58. Hey Adel, Great I have been using this since before v4.1... However is there a way to shut and start the oracle RAC databases and listener services.

    Eg,
    Choose service
    1. Database
    2. LISTENER
    Option choosed:1

    Choose Action:
    1. Stop db
    2. Start db
    3. Status

    Option: 1

    Choose Database (pick from list)
    1. Db1
    2.Db2
    Option 1

    So it should execute
    Srvctl Stop database -d Db1

    Is there a way to write a script in similar way or is there anything already in place.

    ReplyDelete
  59. Hi ArunKumar,

    Thanks for your comment.

    Actually there is a script in the bundle with name "SHUTDOWN_All.sh" to shut down all DBs & listeners on the server in one shot, but it's very old and not compatible with RAC environment. I may update it soon to provide DB & listener selection and be compatible with RAC environment. I let you know when it's ready.

    ReplyDelete
    Replies
    1. Adel,

      Did you get a chance to look something on this, it would be of a great help if this is completed for the whole community.

      Delete
    2. Adel,

      Waiting for this RAC and selection based database shutdown startup along with listener.

      Delete
  60. Hi Adel,

    why the script depends on the location and where to extract it ? can't be mobile ?

    Regards,
    Sam

    ReplyDelete
  61. Hi Sam,

    Actually I remember I constrained the bundle to get extracted under the home directory of Oracle user for a purpose, but I forgot that purpose :-)

    Anyways, your point is a valid one. I've removed that constraint so the user can extract the bundle anywhere; this will come in the next release which will be published in the next few days ... Stay tuned!

    ReplyDelete
  62. Thank you! Great set of Oracle Utilities and scripts. I can see where you spent a lot of time and effort on these.

    ReplyDelete
  63. Great job Mahmmoud. The most important of these scripts dbalarm.sh doesn't work for me in the bundle. It keeps telling me to "Please replace youremail@yourcompany.com with your E-mail address.", even though I changed it. But when download this script straight from your sight, it works well.
    Thanks again

    ReplyDelete
  64. Thanks for your comments. Some scripts which mainly designed for monitoring will not work unless you provide your Email in order to get notified. For dbalarm script, just open the script with vi editor and go down to this line:
    EMAIL="youremail@yourcompany.com"

    Then replace it with your Email. e.g. here I'm replacing it with my personal Email:

    EMAIL="mahmmoudadel@hotmail.com"

    ReplyDelete
  65. can this script be deployed in an aix server?

    ReplyDelete
  66. i got this error when i execute it on one of test server on AIX.

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


    [dbdailychk Script Started ...]
    df: Not a recognized flag: h
    Usage: df [-P] | [-IMitvc] [-gkm] [-s] [-T {local|remote|vfstype}] [-F {hdr1 hdr2 hdr3}] [filesystem ...] [file ...]
    df: Not a recognized flag: h
    Usage: df [-P] | [-IMitvc] [-gkm] [-s] [-T {local|remote|vfstype}] [-F {hdr1 hdr2 hdr3}] [filesystem ...] [file ...]
    ./dailycheck.sh[287]: pgrep: not found.
    ./dailycheck.sh[289]: pwdx: not found.
    hostname: Not a recognized flag: -
    df: Not a recognized flag: h
    Usage: df [-P] | [-IMitvc] [-gkm] [-s] [-T {local|remote|vfstype}] [-F {hdr1 hdr2 hdr3}] [filesystem ...] [file ...]
    netstat: Not a recognized flag: l
    usage: netstat [-Aaon] [-f address_family] [-@ [wparname]]
    [-D]
    [-cCgimMnPrsuvZ] [-f address_family] [ [-p proto] | [-@ [wparname]] ]
    [-n] [-I interface] [interval]
    ./dailycheck.sh[1905]: identifier: 0403-009 The specified number is not valid for this command.
    ./dailycheck.sh[1956]: identifier: 0403-009 The specified number is not valid for this command.
    HEALTH CHECK REPORT: For Database [T24LIVE] on Server [devdbr19]
    # HEALTH CHECK REPORT: For Database [T24LIVE] on Server [devdbr19] stephenogunshola@xxxxxxxxx.com
    HEALTH CHECK REPORT FOR DATABASE [T24LIVE] WAS SAVED TO: /home/oraprod/BUNDLE_Logs/T24LIVE_HEALTH_CHECK_REPORT.log
    ./dailycheck.sh[287]: pgrep: not found.
    ./dailycheck.sh[289]: pwdx: not found.
    hostname: Not a recognized flag: -
    df: Not a recognized flag: h
    Usage: df [-P] | [-IMitvc] [-gkm] [-s] [-T {local|remote|vfstype}] [-F {hdr1 hdr2 hdr3}] [filesystem ...] [file ...]
    netstat: Not a recognized flag: l
    usage: netstat [-Aaon] [-f address_family] [-@ [wparname]]
    [-D]
    [-cCgimMnPrsuvZ] [-f address_family] [ [-p proto] | [-@ [wparname]] ]
    [-n] [-I interface] [interval]

    ReplyDelete
  67. Hi,
    Unfortunately, this bundle is not compatible with AIX, but it works fine on Linux and Solaris.

    ReplyDelete
    Replies
    1. thank you for your candid response here, do you have any handy daily check script that is compatible on AIX?

      Delete
  68. Hi Mahmmoud ADEL,
    When i execute the alias script, my putty session is closing. Is there anything i need to take care?

    Like this i'm executing.

    ~/DBA_BUNDLE6 $ . aliases_DBA_BUNDLE.sh

    I have already set ORACLE_HOME in my .bash_profile

    oracle@PROD01 ~/DBA_BUNDLE6 $ cat ~/.bash_profile
    # .bash_profile

    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
    . ~/.bashrc
    fi

    # User specific environment and startup programs

    PATH=$PATH:$HOME/.local/bin:$HOME/bin

    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
    export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/bin:/sbin:/usr/bin:/usr/sbin
    PATH=$PATH:$HOME/bin

    ReplyDelete
    Replies
    1. Hi,
      More information i'm adding. The session is closing when execute the alias command.

      [oracle@REPT01 ~]$ . /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh

      Please Use "." command to run this script.
      e.g.
      . /home/oracle/aliases_DBA_BUNDLE.sh

      Connection to 10.128.238.49 closed. ==> See here <==
      [oracle@TEST01 ~]$

      Delete
    2. Hi Nagaraju,

      Is this a Linux machine?
      Also, would you mind getting me the output of this command:

      env|grep ORACLE_HOME

      Delete
    3. Hi,
      Yes. this is LINUX only.

      oracle@PROD01 ~ $ uname -a
      Linux CDRVGDPROD01 3.10.0-514.10.2.el7.x86_64 #1 SMP Mon Feb 20 02:37:52 EST 2017 x86_64 x86_64 x86_64 GNU/Linux
      oracle@PROD01 ~ $

      oracle@PROD01 ~ $ env| grep ORACLE_HOME
      ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

      Delete
    4. OK. Can you remove all "exit" and "exit1" keywords from the script and run it again?

      Please send me the execution output.

      Delete
    5. Sure. Let me run and update you the output

      Thank you

      Delete
    6. Hi,

      After commenting all exit/exit 1 commands, putty is not closed. But the script is still failing to execute. The output is

      [oracle@REPT01 DBA_BUNDLE6]$ . aliases_DBA_BUNDLE.sh

      Please Use "." command to run this script.
      e.g.
      . /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh

      [oracle@REPT01 DBA_BUNDLE6]$ . /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh

      Please Use "." command to run this script.
      e.g.
      . /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh

      [oracle@REPT01 DBA_BUNDLE6]$

      Delete
    7. Would you mind to get me the output of this command please:

      set -o | grep history

      Delete
    8. Hi,
      Please find the output..

      [oracle@REPT01 ~]$ set -o | grep history
      history on

      [oracle@REPT01 ~]$ history|tail -5
      4092 2021-01-06 18:28:08 . /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh
      4093 2021-01-06 18:36:57 ./aliases_DBA_BUNDLE.sh
      4094 2021-01-06 18:38:25 ls -lrt
      4095 2021-01-06 21:42:56 set -o | grep history
      4096 2021-01-06 21:43:07 history|tail -5

      Delete
    9. Hi Nagaraju,

      so far I cannot see anything wrong in your environment, so kindly remove the following section from the scripts:

      if [ -z "${USEDCOMM}" ]
      then
      echo ""
      echo "Please Use \".\" command to run this script."
      echo "e.g."
      echo ". ${BUNDLE_LOC}/aliases_DBA_BUNDLE.sh"
      echo ""
      exit 1
      return
      fi

      Delete
    10. Sure. Let me remove and re-execute the script

      . aliases_DBA_BUNDLE.sh

      Delete
    11. Hi,
      Now i got below errors..

      oracle@PROD1 ~/DBA_BUNDLE6 $ . aliases_DBA_BUNDLE.sh
      Setting ORACLE_HOME ...
      8168: Permission denied
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      Setting ORACLE_BASE ...
      cat: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      Getting DB NAME ...
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      Locating DB Instance ALERTLOG ...
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      Setting GRID_HOME ...
      Setting GRID_BASE ...
      [ASM Instance Found] Locating ASM Instance ALERTLOG ...
      -bash: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      awk: cmd. line:1: (FILENAME=- FNR=1) fatal: attempt to access field -1
      [GRID INFRASTRUCTURE Setup Detected] Locating GRID INFRASTRUCTURE ALERTLOG ...
      Lastly, Setting up the Environment Variables and Command Aliases ...

      *******************
      LIST OF ALL ALIASES:
      *******************

      Delete
    12. The script cannot find the ORACLE_HOME, would you mind running the following commands, replacing xxxxx with the right ORACLE_SID:

      export ORACLE_SID=xxxxx

      whoami

      ps -ef|grep -v grep|grep pmon


      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':'`
      echo "First Attempt ORACLE_HOME is: $ORACLE_HOME"
      ls -l ${ORACLE_HOME}/bin/sqlplus

      ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
      echo "Second Attempt ORACLE_HOME is: $ORACLE_HOME"
      ls -l ${ORACLE_HOME}/bin/sqlplus

      ORACLE_HOME=`dbhome "${ORACLE_SID}"`
      echo "Third Attempt ORACLE_HOME is: $ORACLE_HOME"
      ls -l ${ORACLE_HOME}/bin/sqlplus

      ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
      echo "Forth Attempt ORACLE_HOME is: $ORACLE_HOME"
      ls -l ${ORACLE_HOME}/bin/sqlplus

      ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
      echo "Fifth Attempt ORACLE_HOME is: $ORACLE_HOME"
      ls -l ${ORACLE_HOME}/bin/sqlplus

      Delete
  69. HI,
    Please find the output..

    oracle@PROD01 ~ $ export ORACLE_SID=OIDPRD1

    oracle@PROD01 ~ $ whoami
    oracle

    oracle@PROD01 ~ $ ps -ef|grep -v grep|grep pmon
    oracle 8168 1 0 2020 ? 00:01:30 ora_pmon_OIDPRD1
    grid 22170 1 0 2020 ? 00:20:25 asm_pmon_+ASM1
    grid 23867 1 0 2020 ? 00:14:49 mdb_pmon_-MGMTDB

    oracle@PROD01 ~ $ ORATAB=/etc/oratab

    oracle@PROD01 ~ $ ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`

    oracle@PROD01 ~ $ echo "First Attempt ORACLE_HOME is: $ORACLE_HOME"
    First Attempt ORACLE_HOME is: /u01/app/oracle/product/12.1.0/dbhome_1
    /u01/app/oracle/product/12.1.0/dbhome_1

    oracle@PROD01 ~ $ ls -l ${ORACLE_HOME}/bin/sqlplus
    -rwxr-x--x. 1 oracle oinstall 10525 Jun 9 2016 /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus

    ReplyDelete
    Replies
    1. Would you mind to add the following line:

      export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

      before this section in the script:

      # ########################
      # Getting ORACLE_BASE:
      # ########################

      Delete
    2. HI,
      Even after adding also failing to execute..

      I added below
      =============
      mv ./login.sql ./login.sql_NeutralizedBy${SCRIPT_NAME}
      fi

      export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 ==> I ADDED HERE <==

      # ########################
      # Getting ORACLE_BASE:
      # ########################

      After that, i executed below.

      oracle@PROD01 ~/DBA_BUNDLE6 $ . aliases_DBA_BUNDLE.sh
      Setting ORACLE_HOME ...
      8168: Permission denied
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      -bash: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      Setting ORACLE_BASE ...
      Getting DB NAME ...
      Locating DB Instance ALERTLOG ...
      Setting GRID_HOME ...
      Setting GRID_BASE ...
      [ASM Instance Found] Locating ASM Instance ALERTLOG ...
      [GRID INFRASTRUCTURE Setup Detected] Locating GRID INFRASTRUCTURE ALERTLOG ...
      Lastly, Setting up the Environment Variables and Command Aliases ...
      -bash: alias: /u01/app/oracle/product/12.1.0/dbhome_1: not found
      -bash: alias: /u01/app/oracle/product/12.1.0/dbhome_1: not found
      -bash: alias: /u01/app/oracle/product/12.1.0/dbhome_1: not found
      -bash: alias: /u01/app/oracle/product/12.1.0/dbhome_1: not found
      -bash: alias: /u01/app/oracle/product/12.1.0/dbhome_1: not found
      -bash: /home/oracle/.DBA_BUNDLE_profile: line 47: unexpected EOF while looking for matching `"'
      -bash: /home/oracle/.DBA_BUNDLE_profile: line 148: syntax error: unexpected end of file

      *******************
      LIST OF ALL ALIASES:
      *******************
      ------------------------------------------------------------------------
      When i fire the alias command - active

      [oracle@PROD01 09:22:18 DBA_BUNDLE6]$ active
      8168: Permission denied
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 155: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 162: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 169: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 176: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 186: [: /u01/app/oracle/product/12.1.0/dbhome_1: binary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 218: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 323: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 326: [: -gt: unary operator expected
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 379: /u01/app/oracle/product/12.1.0/dbhome_1: Is a directory
      /home/oracle/DBA_BUNDLE6/active_sessions.sh: line 383: [: -gt: unary operator expected

      Delete
    3. cat /home/oracle/.DBA_BUNDLE_profile

      Delete
    4. oracle@PROD01 ~/DBA_BUNDLE6 $ cat /home/oracle/.DBA_BUNDLE_profile
      export ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1
      export LD_LIBRARY_PATH="/u01/app/oracle/product/12.1.0/dbhome_1
      export TNS_ADMIN="/u01/app/oracle/product/12.1.0/dbhome_1
      alias oh='cd /u01/app/oracle/product/12.1.0/dbhome_1
      alias dbs='cd /u01/app/oracle/product/12.1.0/dbhome_1
      alias rman='/u01/app/oracle/product/12.1.0/dbhome_1
      alias lis='view /u01/app/oracle/product/12.1.0/dbhome_1
      alias tns='view /u01/app/oracle/product/12.1.0/dbhome_1
      alias sqlnet='view /u01/app/oracle/product/12.1.0/dbhome_1
      alias pfile='view /u01/app/oracle/product/12.1.0/dbhome_1
      alias aud='cd /u01/app/oracle/product/12.1.0/dbhome_1
      alias network='cd /u01/app/oracle/product/12.1.0/dbhome_1
      alias spfile='view /u01/app/oracle/product/12.1.0/dbhome_1
      export ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1
      export LD_LIBRARY_PATH="/u01/app/oracle/product/12.1.0/dbhome_1

      Delete
    5. export ORACLE_SID="OIDPRD1" #DBA_BUNDLE6
      export ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1" #DBA_BUNDLE6
      export LD_LIBRARY_PATH="/u01/app/oracle/product/12.1.0/dbhome_1/lib" #DBA_BUNDLE6
      export TNS_ADMIN="/u01/app/oracle/product/12.1.0/dbhome_1/network/admin" #DBA_BUNDLE6
      export PATH="/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/sbin:/usr/sbin:${ORACLE_HOME}/bin:${ORACLE_HOME}/OPatch" #DBA_BUNDLE6
      EDITOR=vi #DBA_BUNDLE6
      export EDITOR #DBA_BUNDLE6
      alias l='ls' #DBA_BUNDLE6
      alias d='date' #DBA_BUNDLE6 >> Display the date.
      alias df='df -hP' #DBA_BUNDLE6
      alias top='top -c' #DBA_BUNDLE6
      alias ll='ls -rtlh' #DBA_BUNDLE6
      alias lla='ls -rtlha' #DBA_BUNDLE6
      alias cron='crontab -e' #DBA_BUNDLE6 >> Open the crontab for editing.
      alias crol='crontab -l' #DBA_BUNDLE6 >> Display the crontab.
      alias profile='. /home/oracle/.DBA_BUNDLE_profile' #DBA_BUNDLE6 >> Call the user's profile to reload Environment Variables.
      alias viprofile='view /home/oracle/.DBA_BUNDLE_profile' #DBA_BUNDLE6 >> View the user's profile.
      alias catprofile='cat /home/oracle/.DBA_BUNDLE_profile' #DBA_BUNDLE6 >> Display the user's profile.
      alias alert='tail -100f /u01/app/oracle/diag/rdbms/oidprd/OIDPRD1/trace/alert_OIDPRD1.log' #DBA_BUNDLE6 >> Tail the default instance ALERTLOG file.
      alias dbalert='tail -100f /u01/app/oracle/diag/rdbms/oidprd/OIDPRD1/trace/alert_OIDPRD1.log' #DBA_BUNDLE6 >> Tail the default instance ALERTLOG file.
      alias vialert='view /u01/app/oracle/diag/rdbms/oidprd/OIDPRD1/trace/alert_OIDPRD1.log' #DBA_BUNDLE6 >> View the default instance ALERTLOG file.
      alias asmalert='tail -100f /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log' #DBA_BUNDLE6 >> Tail the ASM instance ALERTLOG file.

      Delete
    6. Would you mind sending me the following files from the server to my Email: mahmmoudadel@hotmail.com
      /home/oracle/.DBA_BUNDLE_profile
      /home/oracle/DBA_BUNDLE6/aliases_DBA_BUNDLE.sh

      I need to investigate why the lines get trimmed after the ORACLE_HOME path!

      Delete
    7. Sure. Let me email you the files..

      Delete
    8. HI,
      FYI, I've emailed you both the files..

      Delete
    9. I've sent you a new script, please use it and let me know if you get any problems.

      Delete
  70. Hi Pepe,
    Looks I've deleted your comment by mistake while I was getting rid of spam comments!

    I'm posting your comment again:

    Hi ! thanks a lot for your website
    When I run dbalarm.sh script that comes inside "DBA_BUNDLE6", it seems to be hung "Checking GRID INFRASTRUCTURE ALERTLOG ..." It never finish..

    Checking Offline Databases ...
    Checking Listener Log ...
    [ASM Instance Found] Locating ASM Instance ALERTLOG ...
    Checking ASM ALERTLOG ...
    ALERT: ASM Instance on Server [OracleRestart12] reporting errors: ORA-15027: active use of diskgroup "DATA" precludes its dismount
    [Grid Infrastructure Setup Detected] Locating Grid Infrastructure ALERTLOG ...
    Checking GRID INFRASTRUCTURE ALERTLOG ...

    ReplyDelete
    Replies
    1. Pepe,
      Have you tried to cancel the execution and run the dbalarm script again? Is it still go in the hung state?
      Would you mind to provide the output for this command after cancelling the execution:

      # which asmalert

      Delete
  71. Hey Adel, Great I have been using this since before v4.1... However is there a way to shut and start the oracle RAC databases and listener services.

    Eg,
    Choose service
    1. Database
    2. LISTENER
    Option choosed:1

    Choose Action:
    1. Stop db
    2. Start db
    3. Status

    Option: 1

    Choose Database (pick from list)
    1. Db1
    2.Db2
    Option 1

    So it should execute
    Srvctl Stop database -d Db1

    Is there a way to write a script in similar way or is there anything already in place.

    ReplyDelete
    Replies
    1. Hi Arunkumar,

      Please accept my apology for making you write your suggestion over and over, I didn't reply your previous comments immediately. My bad!

      Actually, I've put your suggestion in my plan for new scripts, but was very busy and didn't find time to work on it. I'll do my best to create it once I get free time, and sure, I'll let you know.

      Please remain assured that your suggestions are always welcome.

      Delete
  72. Thank you so much.
    Baraka allah fik akhi.

    ReplyDelete
  73. Mahmoud you are doing an awesome work, I feel very happy to have worked with you, be blessed ever.

    Cheers!
    Farrukh

    ReplyDelete
  74. Hi Mahmoud,

    please share how to use the alias scripts with examples and screenshots.

    ReplyDelete
    Replies
    1. Hi Seenu,
      Thanks for passing by, using the alias script is very simple, all you want to do is; you upload the bundle tar file on the server, extract it, and step inside the new extracted directory, then run this command as oracle user:
      . aliases_DBA_BUNDLE.sh

      Yes, don't forget the dot! Then it will install all the bundle scripts as aliases in the oracle user profile, below shows an excerpt of the output that will appear to you, it will list each alias and its usage: If you read the complete output carefully, you will master the use of this bundle:

      ===============================================================
      |Alias |Usage |
      |===============|===============================================|
      |bundle |Setup Aliases/Env Vars for a specific instance |
      |---------------|-----------------------------------------------|
      |removebundle |Remove bundle Aliases/Variables from the System|
      |---------------|-----------------------------------------------|
      |alert |tail -f the Database Instance Alertlog |
      |---------------|-----------------------------------------------|
      |asmalert |tail -f the ASM instance Alertlog |
      |---------------|-----------------------------------------------|
      |vialert |Open the Database Alertlog with vi editor |
      |---------------|-----------------------------------------------|
      |raclog |Open the Clusterware Alertlog [If found] |
      |---------------|-----------------------------------------------|
      |crss |Show Clusterware Services Status [If found] |
      |---------------|-----------------------------------------------|
      |lis |Open listener.ora file under ORACLE_HOME |
      |---------------|-----------------------------------------------|
      |tns |Open tnsnames.ora file under ORACLE_HOME |
      |---------------|-----------------------------------------------|
      |sqlnet |Open sqlnet.ora file under ORACLE_HOME |
      |---------------|-----------------------------------------------|
      |pfile |Open the PFILE under ORACLE_HOME/dbs |
      |---------------|-----------------------------------------------|
      |spfile |Open the SPFILE under ORACLE_HOME/dbs |
      |---------------|-----------------------------------------------|
      |oh |Go to ORACLE_HOME Dir |
      |---------------|-----------------------------------------------|
      |gh |Go to GRID_HOME Dir |
      |---------------|-----------------------------------------------|
      |dbs |Go to ORACLE_HOME/dbs |
      |---------------|-----------------------------------------------|
      |aud |Go to ORACLE_HOME/rdbms/audit |
      |---------------|-----------------------------------------------|
      |bdump |Go to BACKGROUND_DUMP_DEST |
      |---------------|-----------------------------------------------|
      |network |Go to ORACLE_HOME/network/admin |
      |---------------|-----------------------------------------------|
      |p |List the Current Running Instances |
      |---------------|-----------------------------------------------|
      |lsn |List the Current Running Listeners |
      |---------------|-----------------------------------------------|
      |dfs / datafiles|List All DATAFILES on a database |
      |---------------|-----------------------------------------------|
      |tbs/tablespaces|List All TABLESPACES on a database |
      |---------------|-----------------------------------------------|
      |invalid |List All Invalid Objects + Compile statements |
      |---------------|-----------------------------------------------|
      ...
      ...

      Delete
    2. Also, you can watch this boring video, play it in 2x speed:

      https://www.youtube.com/watch?v=Zw4liZDvgLk

      Delete
  75. Very useful information... thanks a lot..

    ReplyDelete
  76. Does it work with CDB databases?

    ReplyDelete
    Replies
    1. Unfortunately, it doesn't support CDB architecture.

      Delete
    2. Does it support CDBs and PDBs now?

      Delete
  77. Infycle Technologies, the top software training institute and placement center in Chennai offers the Digital Marketing course in Chennai for freshers, students, and tech professionals at the best offers. In addition to the Oracle training, other in-demand courses such as DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete

  78. Thanks for Sharing..Keep Update…

    Web Hosting is the process of buying space for a website on the World Wide Web.
    Web Hosting Explained For Beginners
    Web Hosting

    ReplyDelete
  79. Thank you for a great set of aliases and utilities!

    ReplyDelete
  80. For the email on the dbdaily some email are never receive only gmail :(

    ReplyDelete
    Replies
    1. Most probably the email size is big enough to exceed the limited quota on the exchange server, you may need to reduce the size of the output by turning off some features under THRESHOLD section inside the script (set the values to N):

      ......
      # #########################
      # THRESHOLDS:
      # #########################
      ........
      CHKAUDITRECORDS=N
      SHOWDORMANTACCOUNTS=N
      ...
      SHOWSQLTUNINGADVISOR=N
      SHOWMEMORYADVISORS=N
      SHOWSEGMENTADVVISOR=N

      Delete
  81. PAKKAHOUSE IS AN INDIAN REALESTATE AND ECOMMERCE WEBSITE, THIS SITE ALSO CATERS FOR MANY
    BROKERAGE STOCK SITES AND ALSO EDU AND GOVERNMENT ADVERTISEMENT,
    FOR MORE INFORMATION AND UPDATE ABOUT NEWS PLEASE CLICK AND VISIT THE SITE FOR DAILY UPDATES계룡출장마사지
    보령출장마사지
    서산출장마사지
    아산출장마사지
    천안출장마사지
    예산출장마사지
    청양출장마사지
    당진출장마사지

    ReplyDelete
  82. Thank you for your script.

    ReplyDelete
  83. If OS is HP-UX then ./oraenv is not going to work

    ReplyDelete
    Replies
    1. Thanks for your comment.

      Actually the bundle is well tested on Linux OS, many of the scripts can work on Sun OS as well, but for HP-UX the vast majority of the scripts are not compatible with, and hence you I strongly recommend you to test them on a test environment to make sure they will work properly on Prod.

      By the way, I'm not using oraenv script in any of the scripts, but the script is setting the environment variables in a way similar to oraenv.

      Delete
  84. Thanks a lot Mahmmoud. I have been searching health script for more than 1month. You blog saved my job.

    ReplyDelete