Common shell scripts used to monitor Oracle databases

 

Preface

This article describes DBA's daily responsibilities in monitoring Oracle databases and how to use shell scripts to perform repeated monitoring. This article first reviews some common DBA Unix Commands and explains how to regularly execute DBA scripts through Unix Cron. The article also introduces eight important scripts to monitor the Oracle database:

Check instance availability

Check listener availability

Check the error message in the alert Log File

Old log files are cleared before the location where the log files are stored is full

Analyze table and index for better performance

Check the space usage

Find invalid objects

Monitor users and transactions

Basic Unix knowledge required by DBA

Basic UNIX Commands

The following are some common Unix commands:

Ps -- display process grep -- search for some text mode mailx in the file -- read or send mail cat -- connect to the file or display them cut -- select the displayed column awk -- mode matching language df -- display remaining disk Space

Here are some examples of how DBAs use these commands:

Display available instances on the server:

$ Ps-ef | grep smon oracle 21832 1 0 Feb 24? Ora_smon_oradb1 oracle 898 1 0 Feb 15? 10:48:57 ora_smon_oradb2 dliu 25199 19038 0 05:43:54 pts/6 grep smon oracle 27798 1 0? Ora_smon_oradb3 oracle 28781 1 0 Mar 03? 0: 01 ora_smon_oradb4,

Display available listeners on the server:

$ Ps-ef | grep listener | grep-v grep, because sometimes the listener is capitalized, the result is not displayed.) oracle 23879 1 0 Feb 24? 33: 36/8 .1.7/bin/tnslsnr listener_db1-inherit oracle 27939 1 0 05:44:02? /8.1.7/bin/tnslsnr listener_db2-inherit oracle 23536 1 0 Feb 12? /8.1.7/bin/tnslsnr listener_db3-inherit oracle 28891 1 0 Mar 03? 0: 01/8.1.7/bin/tnslsnr listener_db4-inherit

View the file system usage of the Oracle archive directory

$ Df-k | grep oraarch/dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7%/u09/oraarch

Count the number of rows in the alter. log file:

$ Cat alert. log | wc-l 2984

List all Oracle error messages in the alert. log file:

$ Grep ORA-alert. log ORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [1881], [25860496], [25857716], []

CRONTAB Basics

A crontab file contains six fields:

Minute 0-59

Hour 0-23

Day 1-31 of the month

Month 1-12

Day of the week 0-6, with 0 = Sunday

Unix commands or Shell scripts

To edit a crontab file, enter Crontab-e.

To view a crontab file, enter:

Crontab-l 0 4 ** 5/dba/admin/analyze_table.ksh 30 3 ** 3, 6/dba/admin/hotbackup. ksh/dev/null 2> & 1

In the preceding example, the first line shows that the script for an analysis table runs at am every week. The second line shows a script for hot backup running at a.m. on every Wednesday and Saturday.

 

Common Shell scripts for monitoring databases

The following eight shell scripts cover 90% of DBA's daily monitoring work. You may need to modify UNIX environment variables.

Check Availability of Oracle instances

The oratab file lists all databases on the server.

$ Cat/var/opt/oracle/oratab ############################## ###############################/var/opt/oracle/oratab ######################################## ###################### oradb1: /u01/app/oracle/product/8.1.7: Y oradb2:/u01/app/oracle/product/8.1.7: Y oradb3:/u01/app/oracle/product/8.1.7: N oradb4:/u01/app/oracle/product/8.1.7: Y

The following script checks all the Databases listed in the oratab file and finds the database status (start or close)

######################################## ######################## Ckinstance. ksh ####################################### ############################# ORATAB =/var/opt/oracle/oratab echo 'date' echo Oracle Database (s) status 'hostname': db = 'egrep-I: Y |: N $ ORATAB | cut-d: -f1 | grep-v # | grep-v * 'pslist = 'ps-ef | grep pmon 'for I in $ db; do echo $ pslist | grep ora_pmon _ $ I>/dev/null 2> $1 if ($? ); Then echo Oracle Instance-$ I: Down else echo Oracle Instance-$ I: Up fi done

Run the following command to confirm that the script can be executed:

$ Chmod 744 ckinstance. ksh $ ls-l ckinstance. ksh-rwxr -- r -- 1 oracle dba 657 Mar 5 ckinstance. ksh *

The following is an instance Availability Report:

$ Ckinstance. ksh Mon Mar 4 10:44:12 PST 2002 Oracle Database (s) Status for DBHOST server: Oracle Instance-oradb1: Up Oracle Instance-oradb2: Up Oracle Instance-oradb3: Down Oracle Instance-oradb4: up

Check Availability of Oracle listeners

The following script checks the Oracle listener. If the listener stops, the script restarts the listener:

######################################## ################################# Cklsnr. sh ####################################### ###################################! /Bin/ksh DBALIST = primary.dba@company.com, another.dba@company.com; export DBALIST cd/var/opt/oracle rm-f lsnr. exist ps-ef | grep mylsnr | grep-v grep> lsnr. exist if [-s lsnr. exist] then echo else echo Alert | mailx-s Listener 'mylsnr 'on 'hostname' is down $ DBALIST TNS_ADMIN =/var/opt/oracle; export TNS_ADMIN ORACLE_SID = db1; export ORACLE_SID ORAENV_ASK = NO; export ORAENV_ASK PATH = $ PATH:/bin:/usr/local/bin; export PATH. oraenv LD_LIBRARY_PATH =$ {ORACLE_HOME}/lib; export LD_LIBRARY_PATH lsnrctl start mylsnr fi

Checking Alert logs (ORA-XXXXX)

Some environment variables used by each script can be placed in a profile:

######################################## ################################# Oracle. profile ####################################### ################################## EDITOR = vi; export EDITOR ORACLE_BASE =/u01/app/oracle; export ORACLE_BASE ORACLE_HOME = $ ORACLE_BASE/product/8.1.7; export ORACLE_HOME LD_LIBRARY_PATH = $ ORACLE_HOME/lib; export LD_LIBRARY_PATH TNS_ADMIN =/var/opt/oracle; export TNS_ADMIN NLS_LANG = american; export NLS_LANG NLS_DATE_FORMAT = 'mon dd yyyy HH24: MI: ss '; export NLS_DATE_FORMAT ORATAB =/var/opt/oracle/oratab; export oratab path = $ PATH: $ ORACLE_HOME/bin:/usr/ccs/bin: /usr/bin:/usr/sbin:/usr/openwin/bin:/opt/bin :.; export path dbalist = primary.dba@company.com, another.dba@company.com; export DBALIST

The following script first calls oracle. profile to set all environment variables. If any Oracle error is found, the script also sends a warning email to the DBA.

######################################## ############################ Ckalertlog. sh ####################################### ################################! /Bin/ksh .. /etc/oracle. profile for SID in 'cat $ ORACLE_HOME/sidlist 'do cd $ ORACLE_BASE/admin/$ SID/bdump if [-f alert _ $ {SID }. log] then mv alert _ $ {SID }. log alert_work.log touch alert _ $ {SID }. log cat alert_work.log> alert _ $ {SID }. hist grep ORA-alert_work.log> alert. err fi if ['cat alert. err | wc-l '-gt 0] then mailx-s $ {SID} oracle alert errors $ DBALIST <alert. err fi rm-f alert. err rm-f alert_work.log done

Clear old archive files

The following script clears the old archive file when the log file reaches 90% capacity:

$ Df-k | grep arch Filesystem kbytes used avail capacity Mounted on/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43%/u08/archive ######## ######################################## ######################### clean_arch.ksh ############## ######################################## ####################! /Bin/ksh df-k | grep arch> dfk. result archive_filesystem = 'awk-F' {print $6} 'dfk. result 'archive_capacity = 'awk-F' {print $5} 'dfk. result 'If [[$ videos> 90%] then echo Filesystem $ {archive_filesystem} is $ {archive_capacity} filled # try one of the following option depend on your need find $ archive_filesystem-type f-mtime + 2-exec rm-r {}; tar rman fi

Analysis table and index (for better performance)

The following shows how to transfer parameters to a script:

######################################## ############################ Analyze_table.sh ######### ######################################## ######################! /Bin/ksh # input parameter: 1: password #2: SID if ($ # <1) then echo "Please enter oracle user password as the first parameter! "Exit 0 fi if ($ # <2) then echo" Please enter instance name as the second parameter! "Exit 0 fi

To input parameters to execute the script, enter:

$ Analyze_table.sh manager oradb1

The first part of the script generates an analyze. SQL file, which contains the statements used in the analysis table. The second part of the script analyzes all the tables:

######################################## ############################# Analyze_table.sh ######## ######################################## ####################### sqlplus-s <! Oracle/$1 @ $2 set heading off set feed off set pagesize 200 set linesize 100 spool analyze_table. SQL select ANALYZE TABLE | owner |. | segment_name | estimate statistics sample 10 PERCENT; from dba_segments where segment_type = TABLE and owner not in (SYS, SYSTEM); spool off exit! Sqlplus-s <! Oracle/$1 @ $2 @./analyze_table. SQL exit!

The following is an example of analyze. SQL:

$ Cat analyze. SQL ANALYZE TABLE HIRWIN. JANUSAGE_SUMMARY estimate statistics sample 10 PERCENT; analyze table hirwin. JANUSER_PROFILE estimate statistics sample 10 PERCENT; analyze table appssys. HIST_SYSTEM_ACTIVITY estimate statistics sample 10 PERCENT; analyze table htomeh. QUEST_IM_VERSION estimate statistics sample 10 PERCENT; analyze table jstenzel. HIST_SYS_ACT_0615 estimate statistics sample 10 PERCENT; analyze table jstenzel. HISTORY_SYSTEM_0614 estimate statistics sample 10 PERCENT; analyze table jstenzel. CALC_SUMMARY3 estimate statistics sample 10 PERCENT; analyze table imon. QUEST_IM_LOCK_TREE estimate statistics sample 10 PERCENT; analyze table appssys. HIST_USAGE_SUMMARY estimate statistics sample 10 PERCENT; analyze table patrol. P $ lockconflicttx estimate statistics sample 10 PERCENT;

Check table space usage

The following script checks the use of tablespaces. If only 10% of the tablespace is left, it will send a warning email.

######################################## ############################# Ck_tbsp.sh ######## ######################################## ########################! /Bin/ksh sqlplus-s <! Oracle/$1 @ $2 set feed off set linesize 100 set pagesize 200 spool tablespace. alert select f. TABLESPACE_NAME, TO_CHAR (T. TOTAL_SPACE-F. FREE_SPACE), 999,999) "USED (MB)", TO_CHAR (F. FREE_SPACE, 999,999) "FREE (MB)", TO_CHAR (T. TOTAL_SPACE, 999,999) "TOTAL (MB)", TO_CHAR (ROUND (F. FREE_SPACE/T. TOTAL_SPACE) * 100), 999) | % PER_FREE FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS * (select value/102) 4 from v \ $ parameter where name = db_block_size)/1024) FREE_SPACE FROM DBA_FREE_SPACE group by TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576 )) TOTAL_SPACE FROM DBA_DATA_FILES group by TABLESPACE_NAME) t where f. TABLESPACE_NAME = T. TABLESPACE_NAME AND (ROUND (F. FREE_SPACE/T. TOTAL_SPACE) * 100) <10; spool off exit! If ['cat tablespace. alert | wc-l'-gt 0] then cat tablespace. alert-l tablespace. alert> tablespace. tmp mailx-s "tablespace alert for $ {2}" $ DBALIST <tablespace. tmp fi

Example of warning email output:

TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE ----------------------- ------------- hour ---------------------- SYSTEM 2,047 203 2,250 9% STBS01 302 327 8% 241 252

An invalid database object is found.

The following code finds invalid database objects:

######################################## #############################

# Invalid_object_alert.sh ##

######################################## #############################

#! /Bin/ksh./etc/oracle. profile sqlplus-s <! Oracle/$1 @ $2 set feed off set heading off column object_name format a30 spool invalid_object.alert select owner, OBJECT_NAME, OBJECT_TYPE,

Status from DBA_OBJECTS where status =

Invalid order by owner, OBJECT_TYPE, OBJECT_NAME; spool off exit! If ['cat align | wc-l'-gt 0] then mailx-s "invalid objects for $ {2}" $ DBALIST <invalid_object.alert fi $ cat invalid_object.alert OWNER OBJECT_NAME OBJECT_TYPE STATUS comment --------------------------------------------

INVALID Response package body invalid when X _ $ invalid view invalid imon IW_MON PACKAGE BODY INVALID IMON INVALID VIEW invalidlbailey procedure invalid patrol p $ invalid view invalid sys specified package body invalid sys specified procedure invalid sys aq $ _ specified type invalid sys specified view invalid sys HS_CLASS_DD VIEW

Monitor users and transactions (deadlocks, etc)

The following script sends a warning email when a deadlock occurs:

######################################## ############################ Deadlock_alert.sh ########## ######################################## ##################

##! /Bin/ksh ../etc/oracle. profile sqlplus-s <! Oracle/$1 @ $2 set feed off set heading off spool deadlock. alert select sid, DECODE (BLOCK, 0, NO, YES) BLOCKER, DECODE (REQUEST, 0, NO, YES) waiter from v $ lock where request> 0 or block> 0 order by block DESC; spool off exit! If ['cat deadlock. alert | wc-l'-gt 0] then mailx-s "deadlock alert for $ {2}" $ DBALIST <deadlock. alert fi

 

Conclusion

0, 20, 40 7-17 ** 1-5/dba/scripts/ckinstance. sh>/dev/null 2> & 1, 40 7-17 ** 1-5/dba/scripts/cklsnr. sh>/dev/null 2> & 1, 40 7-17 ** 1-5/dba/scripts/ckalertlog. sh>/dev/null 2> & 1 30 *** 0-6/dba/scripts/clean_arch.sh>/dev/null 2> & 1*5 ** 1, 3/dba/ scripts/analyze_table.sh>/dev/null 2> & 1*5 ** 0-6/dba/scripts/ck_tbsp.sh>/dev/null 2> & 1*5 ** 0- 6/dba/scripts/invalid_object_alert.sh>/dev/null 2> & 1 0, 20, 40 7-17 ** 1-5/dba/scripts/deadlock_alert.sh>/dev/null 2> & 1

 

From the programmer's mental journey