Unix Scripts

ksh SCRIPTS FOR DBA

Note: Save the script file with .sh extension - the default extension while saving is .TXT

 
Script Name

check_alert.sh

Purpose Check the alert log file for any oracle error messages. Check if the listener is up and running. The alert log and listener are verified for all databases on the box. If any errors are found, an email is sent to the DBA.
Parameters None
Command Line $ ./check_alert.sh
View Script
 
Script Name

check_db.sh

Purpose To tnsping all Oracle databases on the network and page DBA if a database is not responding. This could be a cronjob run in specified intervals. The database names are obtained from tnsnames.ora file. The entries should be uniform. This script may not work as it is... you may have to make modifications based on your tnsnames.ora file entry formatting. This script is interested only in database names ending with 'OR'.
Parameters None
Command Line $ ./check_db.sh
View Script
 
Script Name

check_server.sh

Purpose To ping other servers on the network and page DBA if a server is not responding. This could be a cronjob run in specified intervals.
Parameters None
Command Line $ ./check_server.sh
View Script
 
Script Name

compare_sid_db.sh

Purpose To compare and verify if all instances mentioned in /etc/oratab is up and running and vice versa.
Parameters None
Command Line $ ./compare_sid_db.sh
View Script
 
Script Name

dbalert.sh

Purpose Apart from the functions of checkalert.sh script, this script logs into each oracle database and looks for potential space problems and invalid objects, triggers, etc. Calls a sql script dbalert.sql. Notifies the DBA of potential problems via email.
Parameters None
Command Line $ ./dbalert.sh
View Shell Script       SQL Script
 
Script Name

find_hogger.sh

Purpose Script to identify the top 20 longest running oracle sessions and the user id, SID, Serial#, logon time, etc. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.
Parameters None
Command Line $ ./find_hogger.sh
View Script      Sample Output
 
Script Name

find_ohome.sh

Purpose Useful if you have multiple versions of oracle on the same box. This script returns the oracle home value accepting the SID name as parameter. This script is used in other scripts on Biju's DBA Page.
Parameters ORACLE_SID Value
Command Line $ ./find_ohome.sh TESTDB
View Script
 
Script Name

sid.sh

Purpose Useful if you have multiple instances of oracle on the same box with different oracle homes. This script helps you to choose the SID name you would like to work and assigns the correct oracle home and includes that in your unix path.
Parameters None
Command Line $ . ./sid.sh (make sure you specify "." before the file name - works only on ksh or sh)
View Script
 
Script Name

tscoalesce.sh

Purpose This script may be used as a cronjob to coalesce the tablespaces periodically. This is especially useful if all your tablespace pctincrease value is set to 0. Uses /etc/oratab to read all instance names, and operating system authentication to log into the database.
Parameters None
Command Line $ ./tscoalesce.sh
View Script
 

Note: On Sun platforms the /etc/oratab file may be under /var/opt/oracle/oratab. Change the scripts as appropriate.

DOWNLOAD ALL UNIX SHELL SCRIPTS

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.