Oracle Database Administration Scripts | DBA Bundle
Introduction:
In this post, I'll share with you one of the most helpful tools I ever created, to make the day to day database administration tasks more faster, safer and easier for the DBA.
This tool I call it DBA Bundle , it's a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link: [V. 2.4 25-Apr-2016] https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2.tar?dl=0
I've designed the scripts to be able to run on complicated environments, where there is one or more Oracle version installed, or there is more than one ORACLE_HOME on the same machine.
All scripts in this bundle can easily understand the environment whatever it's Linux or UNIX, most scripts can work fine on different Oracle versions and different oracle homes and different oracle users owners on the same server, all scripts have been designed to smartly handle wrong user inputs.
Now let's get started with the main functions 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, please note that each script inside this bundle is independent, in other words, the absence of any script will not affect the execution of other scripts.
Second, Run a script called "aliases_DBA_BUNDLE.sh", it will add an alias for each script inside the bundle to the user's profile to make it easy for you to call any script using one command "alias" without the need to step under the bundle location. It also creates aliases that help you easily access the database configuration files and logs e.g.
alert command alias will tail -f the ALERTLOG of the selected database|instance.In this post, I'll share with you one of the most helpful tools I ever created, to make the day to day database administration tasks more faster, safer and easier for the DBA.
This tool I call it DBA Bundle , it's a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link: [V. 2.4 25-Apr-2016] https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2.tar?dl=0
I've designed the scripts to be able to run on complicated environments, where there is one or more Oracle version installed, or there is more than one ORACLE_HOME on the same machine.
All scripts in this bundle can easily understand the environment whatever it's Linux or UNIX, most scripts can work fine on different Oracle versions and different oracle homes and different oracle users owners on the same server, all scripts have been designed to smartly handle wrong user inputs.
Now let's get started with the main functions 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, please note that each script inside this bundle is independent, in other words, the absence of any script will not affect the execution of other scripts.
Second, Run a script called "aliases_DBA_BUNDLE.sh", it will add an alias for each script inside the bundle to the user's profile to make it easy for you to call any script using one command "alias" without the need to step under the bundle location. It also creates aliases that help you easily access the database configuration files and logs e.g.
vialert command alias will vi the ALERTLOG of the selected database|instance.
tns command alias will open the tnsnames.ora file using vi editor
lis command alias will open the listener.ora file using vi editor
There is still more of nice and easy shortcuts will be listed along with the description after you execute "aliases_DBA_BUNDLE.sh"script .
In order to get this post abbreviated, I'll let you discover the rest of aliases yourself !
To call script "aliases_DBA_BUNDLE.sh" use "." command: e.g.
# . aliases_DBA_BUNDLE.sh
What if I've more than one database on the server, how can I use "aliases_DBA_BUNDLE.sh" to point to the logfiles for the database I want?
Simple, let's say you've two databases running on the same server (SALESDB & ORCL), if you want to use the shortcuts (alert,tns,bdump,..) for SALESDB just while running the script select SALESDB, later if you want to use the shortcuts (aliases) for ORCL DB re-execute"aliases_DBA_BUNDLE.sh" again and select ORCL this time, and so on.
Note: Executing "aliases_DBA_BUNDLE.sh" more than once will not harm.
If you didn't manage to run "aliases_DBA_BUNDLE.sh" script , each time you want to call a script you have to step under the bundle location and then execute the script you want.
Now let me give you a brief description of each script in the bundle:
Script Name | Description
-------------------|--------------------------------------------
rebuild_table.sh| Rebuild a table and its related indexes (New in V. 1.5)
-------------------|--------------------------------------------
export_data.sh | Export Full DB|SCHEMA|TABLE data
(gives your the option of using exp or expdp utility for the export).
(gives your the option of using exp or expdp utility for the export).
-------------------|--------------------------------------------
RMAN_full.sh | Take an online RMAN full backup for the database
(gives you the option of compressed backup).
(gives you the option of compressed backup).
-------------------|--------------------------------------------
tablespaces.sh | List All TABLESPACES and it's size on a database.
-------------------|---------------------------------------------datafiles.sh | List All DATAFILES and it's size on a database.
-------------------|---------------------------------------------
audit_records.sh | 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
-------------------|---------------------------------------------
zAngA_zAngA.sh | Retrieve the AUDIT data for a DB user in a specific date
or number of days back.
(this is the one linked to the alias if used "aliases_DBA_BUNDLE.sh" script).
http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records-script.html
-------------------|--------------------------------------------
gather_stats.sh | Gather STATISTICS on a SCHEMA or TABLE using DBMS_STATS
-------------------|--------------------------------------------
invalid_objects.sh | List All Invalid Objects on the DB + it's compile statements.
-------------------|---------------------------------------------
biggest_50_objects.sh | List the Biggest 50 Objects on a database.
-------------------|---------------------------------------------
session_details.sh| List the Details of a user session.
(if no input, will list all sessions on the instance).
-------------------|---------------------------------------------
all_sessions_info.sh| List All current sessions on all running instances [RAC DB].
-------------------|---------------------------------------------
db_locks.sh | List the Blocking LOCKS on a database.
-------------------|---------------------------------------------
unlock_user.sh | Unlock a specific DB User Account + the option of reset the user's password.
-------------------|---------------------------------------------
sql_id_details.sh | Show the details of a specific SQL STATEMENT by providing it's SQL_ID.
-------------------|---------------------------------------------
parameter_val.sh | Show the value of a Visible or Hidden initialization Parameter.
-------------------|---------------------------------------------
db_jobs.sh | List All database Jobs (dba_jobs + dba_scheduler_jobs).
-------------------|---------------------------------------------
process_info.sh| Show the DB Session details when providing it's Unix PID.
-------------------|---------------------------------------------
oracle_cleanup.sh| Backup & Clean up All DB & it's Listener LOGs.
-------------------|---------------------------------------------
last_logon_report.sh| Show the last login date of ALL users in the database.
-------------------|---------------------------------------------
failed_logins.sh | Show the failed login attempts in the last provided n of days.
-------------------|---------------------------------------------
start_tracing.sh | Start TRACING an Oracle Session activities in a logfile.
http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
-------------------|---------------------------------------------
stop_tracing.sh | Stop TRACING an already traced Oracle Session
+ provide the logfile and it's TKPROFED log.
http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
-------------------|---------------------------------------------
user_details.sh | Generate the Full SQL Creation script for a DB user + important info.
-------------------|---------------------------------------------
object_ddl.sh | Generate Full SQL Creation script for a database Object.
-------------------|--------------------------------------------
Archives_Delete.sh| Delete all Archive logs older than (provided) number of days.
-------------------|--------------------------------------------
analyze_objects.sh| Analyze All tables under a specific SCHEMA (using ANALYZE command).
-------------------|--------------------------------------------
table_info.sh | Show all important info about specific table (size, indexes, non indexed FK, constraints,...).
*******************
The following scripts doesn't have aliases because it's not applicable or need to be used carefully:
--------------------------------------------------------------------------------------------------------
dbalarm.sh | Monitor CPU utilization + Filesystem usage + 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 an email to the DBA
(you have to modify this parameter in line number 16 to point to your E-mail Address):
MAIL_LIST="youremail@yourcompany.com"
> Of course the sendmail service should be configured on the server.
*The best usage of this script, is to 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.
http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html
-------------------------|----------------------------------------------
dbdailychk.sh | Perform health check tasks on all running databases on the server such as:
- Checking all databases alert logs for errors.
- Checking all listeners alert logs for errors.
- Checking CPU utilization.
- Checking filesystem utilization.
- Checking tablespaces utilization on all databases.
- Checking blocking sessions on all databases.
- Checking unusable indexes on all databases.
- Checking invalid objects on all databases.
- Checking failed login attempts on all databases.
- Checking audit records on all databases.
- Checking corrupted blocks on all databases.
- Checking failed jobs on all databases.
Modify line# 50 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 detailed information, please visit:
http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check-script.html
-------------------------|----------------------------------------------
SHUTDOWN_All.sh | SHUTDOWN ALL running Databases & Listeners on The server.
-------------------------|----------------------------------------------
COLD_BACKUP.sh | Take a COLD BACKUP of a specific database
(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 startup the database.
http://dba-tips.blogspot.ae/2014/02/cold-backup-script.html
--------------------------------------------------------------
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]
Your suggestions, bug reporting and comments are most welcome :-)
