HomenewsScriptsOracle Database Administration Scripts | DBA Bundle

Oracle Database Administration Scripts | DBA Bundle

  • January 1, 2018
  • Posted by: admin
  • Category: Scripts
19 Comments

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. 4.0  07-Jan-2018]

https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.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:

 

 

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

Created by  Database expert Mahmmoud ADEL



19 Comments

Leave a Reply