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:

     https://gist.github.com/haballan/aa52fbb1f8e497db989f19ef046c7939

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.