Saturday, February 15, 2014

Oracle Database Administration Scripts | DBA Bundle

Introduction:

In this post, I'll share with you one of the most helpful tools I ever created, it will make database administration day to day activities more easier, faster and safer for you.

I named it DBA Bundle , it's a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link: [V. 3.6  14-Feb-2017]
https://www.dropbox.com/s/ilq2ckntq4vrhgo/DBA_BUNDLE3.tar? dl=0

I've designed all scripts to be able to run on complicated environments, whereas there is one or more Oracle version / Oracle Home installed on the same machine.

All scripts inside the bundle can easily recognize the Oracle environment whether it's a Linux or Unix a long with databases version, Oracle homes. 
All scripts can smartly handle user's wrong inputs as well.

Now let's get started with the top key features in this bundle ...

How to use the bundle:
=================

First, download the bundle tar file and extract it under Oracle owner home directory, e.g. /home/oracle, each script inside this bundle is independent, in other words, the absence of any script will not affect the execution of other scripts.

Second, from the bundle extracted directory, run "aliases_DBA_BUNDLE.sh" script using "." command e.g.  .  aliases_DBA_BUNDLE.sh
It will add an alias for each script to the user's profile to make it easy for you to call any script from OS shell under any working directory using one command "alias" without the need to step under the bundle directory.
Aliases will be displayed to you a long with its description in a tabular format. no need to memorize it, in case you want to list it again just type "bundle" command.

Please note that when you run any interactive script in this bundle it will prompt you to select the database number from the displayed list in case you have more than one up & running database.

For a script like "aliases_DBA_BUNDLE.sh" when you select a database from the list, aliases like "alert, tns, bdump,..." will automatically point to the respective files to the database you have chosen from the list.
e.g. if you have two running instances (orcl & salesdb), and you want to open the alertlog of salesdb, just run "aliases_DBA_BUNDLE.sh"  scripts, enter salesdb number from the displayed list, and then type alert/vialert to view salesdb alertlog files.


If you didn't run "aliases_DBA_BUNDLE.sh" script, for each time you want to call a script, you will need to step under the bundle location and then execute the script from there.

Scripts Description:
Now let me give you a brief description of each script in this bundle:


Scripts With aliases

 

Script Name

Shell Alias

Description

rebuild_table.sh

tablerebuild

Rebuild a table and its related indexes.

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

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.

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

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

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.

session_details.sh

                   

session

List the Details of a user session. (If no input provided, it will list all sessions on the instance).

all_sessions_info.sh

sessions

List All current sessions on all running instances [RAC DB] and sessions distribution in details.

db_locks.sh                       

locks

List Blocking LOCKS details on the database (blocking users, blocking locks on objects, long running operations).

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.

sql_id_details.sh

                   

                   

sqlid

Show the details of a specific SQL STATEMENT by providing its SQL_ID and gives you the option to tune it using SQL TUNING ADVISOR.

Co-author: DBA: Farrukh Salman

parameter_val.sh           

parm

Show the value of a Visible or Hidden initialization Parameter.

db_jobs.sh                        

jobs

List All database Jobs (dba_jobs + dba_scheduler_jobs + Auto Tune Tasks and current running jobs and their wait status) and more details of any job if provided its name/number.  

process_info.sh                              

spid

Show the DB Session details when providing its Unix PID.

oracle_cleanup.sh

cleanup

Backup & Clean up All DBs & Listeners’ Logs.

http://dba-tips.blogspot.ae/2014/02/oracle-logs-cleanup-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.

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  

user_details.sh                               

userdetail

Generate the DDL Creation script for a DB user + important info about its schema and objects.

object_ddl.sh                   

objectddl

Generate DDL SQL Creation script for a database Object.

Role_ddl.sh

roleddl

Generate Full SQL Creation script for a database role.

object_size.sh

objectsize

Calculate any object size and its indexes size.

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

table_info.sh                   

tableinfo

Show all important info about specific table (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 and waiting sessions and their blocking sessions, along with long running operation. (New script in V3.6)

 

The following scripts don't have aliases because either they are not applicable or need to be used carefully:

Scripts Without Aliases

 

Script Name

Description

dbalarm.sh

Monitor CPU, Filesystem/FRA/Tablespaces utilization, blocking locks and ALERTLOGs of ALL Databases and Listeners 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.

(you have to modify this parameter in line number 27 to point to your E-mail Address):

MAIL_LIST="youremail@yourcompany.com"

 

> For sure 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) to report ORA- and TNS- errors when they written to alertlog & listeners logs.

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:

 

Modify line# 80 by replacing youremail@yourcompany.com to 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 every day morning:

0 6 * * * /home/oracle/dbdailychk.sa

For more details:

http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check- script.html

configuration_baseline.sh               

                   

Collect configuration baseline data for OS and all running DATABASES.

For more details:

http://dba-tips.blogspot.ae/2016/12/configuration-baseline- script-for-linux.html

delete_standby_archives.sh               

Deletes Applied Archives on STANDBY DATABASE older than N hours (specified by the user).

For more details:

http://dba-tips.blogspot.ae/2017/01/script-to-delete-applied- archivelogs-on.html

COLD_BACKUP.sh                          

Take a COLD BACKUP of a specific database

 (But the beauty of this script is that It will also creates another script to help you restore the taken cold backup easily)

This script will 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 childrenJ).

 

From time to time I'll keep updating this bundle (adding new scripts, fixing bugs, adding new features), so give this topic a visit at least every 3 months to download the latest version.

As I mentioned in each script, I'M SHARING THIS BUNDLE AND IT'S 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]
http://snk.to/f-cdufbjic   [V. 1.2]
http://snk.to/f-cdpmy98p   [V. 1.3]
http://snk.to/f- cdt3e98p     [V. 1.4]
http://snk.to/f-ctp8rmpx     [V. 1.5]
http://snk.to/f-cdnqo5mu   [V. 1.6]
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 :-)

You can watch this video tutorial on how to use this bundle: (watch this video in full screen mode)



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

33 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