Saturday, February 15, 2014

Oracle Database Administration Scripts | DBA Bundle

Welcome my fellow busy DBA, I know you were googling for a DBA script and your good luck landed you here :-).
I know you're super busy and have no time for reading long articles, so let me jump to the bottom line immediately, the DBA Bundle is a group of shell scripts bundled all together in one tar file to help you out with your day to day DBA tasks. I guarantee you if you use it once you will get addicted to it. My main aim here is to get you to love your DBA job.
To DOWNLOAD the latest version use this link:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0

What this Bundle can do? In a nutshell, it will help you out to open the database 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!

How to use the bundle:

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 do 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.

The script will also 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.

You can download older versions from below links : 
https://app.box.com/s/l4cmpxfrfy8t6emqrpgo   [V. 1.1]
https://www.dropbox.com/s/mh0rk14alc69gqj/DBA_BUNDLE1_Sep2014.tar?dl=0   [V. 1.7 Sep2014]
https://www.dropbox.com/s/vrhslrg4l5xhzyb/DBA_BUNDLE1.tar?dl=0      [V. 1.8 Oct2014]
https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2.tar?dl=0  [V. 2.0 08-May-2015]
https://www.dropbox.com/s/wnzvp49cyamqu66/DBA_BUNDLE2_Oct2015.tar?dl=0 [V. 2.2 Oct-2015]
https://www.dropbox.com/s/a1wn1j1squjf1qx/DBA_BUNDLE2_6Feb2016.tar?dl=0 [V2.3 Feb2016]
https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2_25Apr2016.tar?dl=0 [V2.4 Apr2016]
https://www.dropbox.com/s/gxxb7jws8xngurj/DBA_BUNDLE3_10Oct2016.tar?dl=0 [V3.1 Oct2016]
https://www.dropbox.com/s/5wj52xqse9wcu6l/DBA_BUNDLE3_7Dec2016.tar?dl=0 [V3.3 Dec2016]
https://www.dropbox.com/s/c5tvgvvs3c8b749/DBA_BUNDLE3_3Jan2017.tar?dl=0 [V3.4 Jan2017]

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.

79 comments:

mohamed said...

Thanks a lot

Anonymous said...

great job, thanks for sharing!

Rahul N said...

Great work. Thank you!!

Mahmmoud ADEL said...

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

manoj said...

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

Mahmmoud ADEL said...

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.

gnz_bLog said...

Thanks a lot for sharing

Venk@t Oracle DBA's said...

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

Anonymous said...

Thanks Mahmomoud! That is really useful set of scripts!

Jean-Pierre Tomes said...

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

Jean-Pierre Tomes said...

thank you for yours scripts

Jean-Pierre Tomes said...

thanks you for yours scripts
Jean-Pierre

Vishal Pawar said...

Thank you sir for valuable information.

vikram reddy said...

Thanks Mohamed!! Appreciate your efforts.

Anonymous said...

Very kind of you! Thanks

Anonymous said...

this is really awesome....Greatly appreciate...

Özgür Umut said...

Really awesome...
Thanks for sharing Mahmmoud..

David Williams said...

Thanks for Information Oracle Online Training Oracle is an object-relational database management system created and marketed by Oracle Corporation.Oracle Online Training

Anonymous said...

Thank you for sharing a great set of scripts!

Maksudan said...

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

Path Infotech said...

Thanks for sharing the information

Oracle Certification Courses in Noida

Samar said...

Great dear I am thankful to you as I am new to oracle

oracle remote database administration said...

DBA Bundle is awesome tool for oracle database administration. Thanks

Satyajit Mohapatra said...

Super work Mahmoud, much appreciated.

Database Expert said...

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

Anonymous said...

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

Mohamed Adel said...

Thanks..

Anonymous said...

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]$

Mahmmoud ADEL said...

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

Mahmmoud ADEL said...

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

Technology for us said...

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

Mahmmoud ADEL said...

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

Technology for us said...

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

Anonymous said...

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

OraDetector said...

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

Lunameadow said...

I think this is an great blogs. Such a very informative and creative contents. These concept is good for these knowledge.I like it and help me to development very well.Thank you for this brief explanations.

Oracle DBA Training in Chennai

Freddie King said...

mcdonalds gutscheine | startlr | salud limpia

Hieu Nguyen Trung said...

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

OraDetector said...

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

Mahmmoud ADEL said...

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`

Mac Bhardwaj said...

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

Mahmmoud ADEL said...

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'

Mac Bhardwaj said...


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 ~]$

Mahmmoud ADEL said...

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

Okelz said...

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

Mahmmoud ADEL said...

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.

OraDetector said...

Thanks Adil once again,

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

Mahmmoud ADEL said...

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

TEST said...

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

Adi smith said...
This comment has been removed by a blog administrator.
Anonymous said...

Hi Mahmmoud,

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

Adi smith said...
This comment has been removed by a blog administrator.
Unknown said...

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

sallie said...

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

Felipe Viveros said...

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$

Mahmmoud ADEL said...

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

johnsy sai said...

Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information. 
Devops Training courses

Devops Training in Bangalore

Devops Training in pune

Unknown said...

myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance

sudhagar raja said...

Very good brief and this post helped me alot. Say thank you I searching for your facts. Thanks for sharing with us!
java training in tambaram | java training in velachery

java training in omr | oracle training in chennai

Ezhil K S said...

I would really like to read some personal experiences like the way, you've explained through the above article. I'm glad for your achievements and would probably like to see much more in the near future. Thanks for share.
Data Science training in Chennai | Data science training in bangalore
Data science training in pune | Data science online training
Data Science Interview questions and answers | Python training in Kalyan nagar

Unknown said...

myTectra a global learning solutions company helps transform people and organization to gain real, lasting benefits.Join Today.Ready to Unlock your Learning Potential !Read More...

Unknown said...

myTectra offers corporate training services in Bangalore for range of courses on various domain including Information Technology, Digital Marketing and Business courses like Financial Accounting, Human Resource Management, Health and Safety, Soft Skill Development, Quality & Auditing, Food Safety & Hygiene. myTectra is one of the leading corporate training companies in bangalore offers training on more than 500+ courses
corporate training in bangalore
top 10 corporate training companies in india
corporate training
corporate training companies
along these we are going to help the professionals and students to crack their interview with interview questions and answers look a head into sites you might be like....
salesforce interview questions
bootstrap interview questions

Anonymous said...

This is good site and nice point of view.I learnt lots of useful information.

angularjs Training in chennai
angularjs Training in chennai

angularjs-Training in tambaram

angularjs-Training in sholinganallur

angularjs-Training in velachery

pavithra dass said...

thanks for sharing such a nice info.I hope you will share more information like this. please keep on sharing!
German Courses in Chennai
best german classes in chennai
German language training in chennai
Java Training in Chennai
Java course in Chennai
Java Training Institute in Chennai



Safety Professionals said...

I am commenting to let you know what a terrific experience my daughter enjoyed reading through your web page.
iosh course in chennai

sachin.ogeninfo said...


cattle feed bags supplier

aruna ram said...

Great idea! Thank you for your wonderful post and very easily understand to me. Really good work please keeping...
Web Designing Training in Bangalore
Web Development Courses in Bangalore
Web Designing Course in Tnagar
Web Designing Training in Saidapet
Web Designing Course in Omr
Web Designing Training in Omr

Ananya Krishnan said...

Good job in presenting the correct content with the clear explanation. The content looks real with valid information. Good Work

DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

Good to learn about DevOps at this time.


devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai | trending technologies list 2018

ajay prakash said...

Such an excellent and interesting blog, do post like this more with more information, this was very useful, Thank you.
Aviation Academy in Chennai
Aviation Courses in Chennai
best aviation academy in chennai
aviation training in chennai

click track India said...

Thinking of growing as best packers and movers in Mohali? Just click on click track india, and you are ready for the skyrocket sales.

Packers and movers in Chandigarh

Packers and movers in Mohali

Packers and movers in Noida

Packers and movers in Gurgaon

Packers and movers in Delhi NCR

Packers and movers in Bangalore

lekha mathan said...

This is really impressive post, I am inspired with your post, do post more blogs like this, I am waiting for your blogs.
air hostess training in Bangalore
air hostess academy Bangalore
cabin crew training institute in Bangalore
air hostess training center in Bangalore

REKHA PUNIA said...

If you live in Delhi and looking for a good and reliable vashikaran specialist in Delhi to solve all your life problems, then you are at right place. 
love marriage specialist in delhi

vashikaran specialist in delhi

love vashikaran specialist molvi ji

get love back by vashikaran

black magic specialist in Delhi

husband wife problem solution

lather said...

Goyal packers and movers in Panchkula is highly known for their professional and genuine packing and moving services. We are top leading and certified relocation services providers in Chandigarh deals all over India. To get more information, call us.


Packers and movers in Chandigarh
Packers and movers in Panchkula
Packers and movers in Mohali
Packers and movers in Zirakpur
Packers and movers in Patiala
Packers and movers in Ambala
Packers and movers in Ambala cantt
Packers and movers in Pathankot
Packers and movers in Jalandhar
Packers and movers in Ludhiana

Riya Raj said...

The blog is really awesome…. waiting for the new updates…
Angularjs Training institute in Chennai
Angular 2 Training in Chennai
Angularjs Course in Bangalore
Angularjs Training Institute in Bangalore

sathyaramesh said...

Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
Software Training Institutes in Chennai | Software Testing Training Institutes in Chennai
software testing course in coimbatore with placement | best software testing training institute in coimbatore
software training institutes in bangalore | software testing institute in bangalore
software testing madurai | software testing classes in madurai

Robotic Process Automation Tutorial said...

Thank you so much for your information,its very useful and helpful to me.Keep updating and sharing. Thank you.
RPA training in chennai | UiPath training in chennai | rpa course in chennai | Best UiPath Training in chennai

keerthana said...

Such an excellent and interesting blog, do post like this more with more information, this was very useful, Thank you.
Airport management courses in chennai
airline and airport management courses in chennai
airline management courses in chennai
aircraft maintenance course in chennai

jenifer irene said...

Such an excellent and interesting blog, do post like this more with more information, this was very useful, Thank you.
airport ground staff training courses in chennai
airport ground staff training in chennai
ground staff training in chennai

minakshi said...

Are you trying to move in or out of Jind? or near rohtak Find the most famous, reputed and the very best of all Packers and Movers by simply calling or talking to Airavat Movers and Packers

Packers And Movers in Jind

Packers And Movers in Rohtak

Movers And Packers in Rohtak