Common shell scripts for monitoring Oracle Databases-Go

8 DBA most commonly used shell scripts for monitoring Oracle Databases--Go to

Category: Linux

A 8 Important scripts to monitor the Oracle database:

1. Check the availability of the instance

2. Check the availability of the listener

3. Check the alert log file for error messages

4. Empty the old log file before the log file is full

5. Analyze table and index for better performance

6. Checking the use of table spaces

7. Find the Invalid object

8. Monitoring Users and Transactions

Two Basic UNIX knowledge required by DBAs

Basic UNIX commands, here are some common UNIX commands:

ps--Display Process

grep--search for a text pattern in a file

mailx--read or send mail

cat--connect files or display them

cut--Select columns to display

awk--Pattern Matching language

df--show the remaining disk space

Here are some examples of how DBAs can use these commands:

1. Display the available instances on the server: $ ps-ef|        grep Smon Oracle 22086 1 0 02:32:24? 0:04 ora_smon_pprd10 Oracle 5215 28972 0 08:10:19 pts/4 0:00 grep smon

2. Display the available listeners on the server: $ ps-ef grep listener grep-v grep (the grep command should be prefixed with the-I parameter, that is, Grep-i listener, which is the function of ignoring case, because sometimes listener is uppercase,        will not see the results) $ ps-ef|grep-i Listener Oracle 9655 1 0 Mar 12?        0:01/data/app/oracle/9.2.0/bin/tnslsnr Listener-inherit Oracle 22610 1 0 02:45:02? 0:02/data/app/oracle/10.2.0/bin/tnslsnr listener-inherit Oracle 5268 28972 0 08:13:02 pts/4 0:00 grep-i LISTENER

3. View file system usage for Oracle Archive directory $ df-k | GREP/DATA/DEV/MD/DSK/D50 104977675 88610542 15317357 86%/data

4. Count the number of lines in the Alter.log file: $ cat Alert_pprd10.log | Wc-l 13124 $ more Alert_pprd10.log | Wc-l 13124

5. 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], []

6. crontab Basic One Crontab file contains six fields: 0-59 hours in 0-23 months of the first day 1-3 January 1-12 weeks 0-6, with 0 = Sunday

7. Unix command or shell script 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 above example, the first line shows a script for the analysis table that runs at 5 4:00am per week. The second line shows a script that performs a hot backup in each of the Wednesday and Saturday 3:00a.m.

Three Common shell scripts for the monitoring database the 8 shell scripts provided below cover 90% of the DBA's daily monitoring work, and you may also need to modify the UNIX environment variables.

1. Check the availability of Oracle instances Oratab file lists all databases on the server $ cat/var/opt/oracle/oratab #

# This file was used by ORACLE utilities. It is created by root.sh # and updated by the database Configuration Assistant when creating # a database.

# a colon, ': ', used as the field terminator.  A New line terminates # The entry.  Lines beginning with A Pound sign, ' # ', is comments. # # Entries is of the form. #   $ORACLE _sid: $ORACLE _home:<n| Y>: # # The first and second fields is the system identifier and home # Directory of the database respectively.  The third filed indicates # to the Dbstart utility so the database should, "Y", or should not, # "N", being brought up at System boot time. # # Multiple entries with the same $ORACLE _sid is not allowed. # # # # # # *:/data/app/oracle/9.2.0:n trng:/data/app/oracle/9.2.0:y *:/data/app/oracle/9.2.0:n PPRD:/data/app/oracle/ 10.2.0:y pprd10:/data/app/oracle/10.2.0:n

The following script examines all the databases listed in the Oratab file and finds out the status of the database (start or close) ################################################################## # # # # Ckinstance.ksh # ################################################################### ORATAB=/var/opt/oracle/ Oratab echo "' Date '" echo "Oracle Database (s) Status ' hostname ': \ n" 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

Use 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 22:59 ckinstance. Ksh

The following is a report of instance availability: $ sh ckinstance.ksh Wed may 12:51:20 PDT, Oracle Database (s) Status gambels:oracle INSTANCE-PPRD: Up Oracle Instance-pprd10:up

2. Check the availability of Oracle listeners there is a similar script that examines the Oracle listener. If the listener is stopped, the script will restart the listener: ##################################################################### # # cklsnr.sh # # ####### ############################################################## #!/bin/ksh tns_admin=/var/opt/oracle; Export Tns_admin oracle_sid= PPRD10; Export Oracle_sid oraenv_ask=no; Export Oraenv_ask path= $PATH:/bin:/usr/local/bin; Export PATH. Oraenv dbalist= "www.linuxidc.com,www.linuxidc.net"; export dbalist cd/var/opt/oracle rm-f lsnr.exist ps-ef | grep PPRD10 | grep-v grep > Lsnr.exist If [-S lsnr.exist] then echo else echo "Alert" | Mailx-s "Listener ' PPRD10 ' on ' hostname ' are down" $DBALIST lsnrctl start PPRD10 fi

3. Check alert log (ora-xxxxx) #################################################################### # # ckalertlog.sh # # # # # # # ################################################################# #!/bin/ksh

Editor=vi; Export EDITOR oracle_sid=pprd10; Export Oracle_sid oracle_base=/data/app/oracle; Export Oracle_base oracle_home= $ORACLE _base/10.2.0; 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: $ORACLE _home/bin:/usr/ Ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; Export PATH dbalist= "www.linuxidc.com,www.linuxidc.net"; export dbalist

CD $ORACLE _base/admin/pprd10/bdump If [-f Alert_pprd10.log] then mv Alert_pprd10.log Alert_work.log Touch ALERT_PPRD10.L OG cat alert_work.log >> alert_pprd10.hist grep ora-alert_work.log > Alert.err fi if [' Cat Alert.err | wc-l '- GT 0] Then mailx-s "PPRD10 ORACLE ALERT ERRORS" $DBALIST < Alert.err fi rm-f alert.err rm-f alert_work.log

4. Clear the old archive file the following script will empty the old archive 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

</script.

####################################################################### # # Clean_arch.ksh # ##################### ################################################## #!/bin/ksh Df-k | grep arch > Dfk.result archive_filesystem= ' awk-f "" ' {print $6} ' dfk.result ' archive_capacity= ' awk-f ' "' {print $ } ' Dfk.result '

If [$archive _capacity > 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

5. Analyze tables and indexes (for better performance) I'll show you the following if you transfer parameters into 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!" EX It 0 fi if ($#<2) then echo "Please enter instance name as the second parameter!" Exit 0 fi to pass in the parameters to execute the script, enter: $ analyze The first part of the _table.sh Manager oradb1 script produces a analyze.sql file that contains the statements used to parse the table. The second part of the script analyzes all tables: ################################################################# # # analyze_table.sh # # ########### ###################################################### sqlplus-s '/As Sysdba ' <<eof set heading off set feed off s ET pagesize set linesize spool analyze_table.sql select ' Analyze table ' | | Owner | | '. ' | | Segment_name | | ' ESTIMATE STATISTICS SAMPLE PERCENT; ' from dba_segments where segment_type = ' TABLE ' and owner not in (' SYS ', ' SystEM '); Spool off exit EOF Sqlplus-s '/as Sysdba ' <<eof @./analyze_table.sql exit EOF

The following is an example of Analyze.sql: $ cat analyze.sql analyze TABLE hirwin. Janusage_summary ESTIMATE STATISTICS SAMPLE PERCENT; ANALYZE TABLE Hirwin. Januser_profile ESTIMATE STATISTICS SAMPLE PERCENT; ANALYZE TABLE Appssys. Hist_system_activity ESTIMATE STATISTICS SAMPLE PERCENT; ANALYZE TABLE Htomeh. Quest_im_version ESTIMATE STATISTICS SAMPLE PERCENT; ANALYZE TABLE Jstenzel. hist_sys_act_0615 ESTIMATE STATISTICS SAMPLE PERCENT;

6. Check tablespace usage using the following script to detect the use of tablespaces. If the tablespace has only 10% left, it will send a warning email. ##################################################################### # # ck_tbsp.sh # ########################### ########################################## #!/bin/ksh

Editor=vi; export EDITOR oracle_sid=pprd10; export oracle_sid oracle_base=/data/app/oracle; export oracle_base Oracle_home= $ORACLE _base/10.2.0; 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: $ORACLE _home/bin:/usr/ Ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; Export PATH dbalist= "www.linuxidc.com,www.linuxidc.net"; export dbalist

Sqlplus-s '/As Sysdba ' <<eof set feed off set linesize set pagesize $ column "Used (MB)" Format A10 column " Free (MBytes) "Format A10 column" Total (MB) "Format A10 column Per_free format A10 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_C HAR (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/1024 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)) Tota L_space from Dba_data_files GROUP by Tablespace_name) T WHERE f.tablespace_name = T.tablespace_name and (ROUND (f.free_s Pace/t.total_space) (*100)) < 80; Spool off exit EOF if [' Cat tablespace.alert|wc-l '-GT 0] then cat tablespace.alert > Tablespace.tmp MAILX-S "tablespace  alert  for  PPRD10" $DBALIST < tablespace.tmp fi

An example of warning email output is as follows: tablespace_name                 Used (MB)   Free (MB)   Total (MB) per_free                              ----------------------------------------------------------------------                             system                               519         401        920     44 %                                milldata                              559        441      1,000     %                                sysaux                                331        609         940    %                                 millreports                          146        254         400    %   

7. Find out invalid database objects The following finds invalid database objects: ##################################################################### # #invalid_ Object_alert.sh #####################################################################

</script.

#!/bin/ksh Editor=vi; Export EDITOR oracle_sid=pprd10; Export Oracle_sid oracle_base=/data/app/oracle; Export Oracle_base oracle_home= $ORACLE _base/10.2.0; 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: $ORACLE _home/bin:/usr/ Ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; Export PATH dbalist= "www.linuxidc.com,www.linuxidc.net"; export dbalist

Sqlplus-s '/As Sysdba ' <<eof set feed off set heading off column OWNER format A10 column object_name format A35 Co Lumn object_type format A10 column STATUS format A10 spool Invalid_object.alert SELECT OWNER, object_name, Object_type, ST ATUs from dba_objects WHERE STATUS = ' INVALID ' ORDER by OWNER, Object_type, object_name; Spool off exit EOF if [' Cat Invalid_object.alert | wc-l '-GT 0] then mailx-s "Invalid OBJECTS for PPRD10" $DBALIST < ; Invalid_object.alert fi

$ more Invalid_object.alert

public     all_wm_locked_tables                 synonym    INVALID public     all_wm_versioned_ tables             SYNONYM    INVALID public     dba_wm_versioned_tables              synonym    INVALID public     sdo_cart_text                         synonym    INVALID public     sdo_geometry                          synonym    INVALID public     sdo_regaggr     & nbsp;                    synonym    INVALID public     sdo_regaggrset                       synonym     INVALID public     sdo_region                           synonym    INVALID public     sdo_regionset                        synonym     INVALID public     user_wm_locked_tables                synonym    INVALID public      User_wm_versioned_tables            SYNONYM    INVALID public     wm_compress_batch_sizes              synonym    INVALID

8. Monitoring users and transactions (deadlock, etc.) the following script sends a warning when a deadlock occurs e-mail: ################################################################### # # deadlock_alert.sh # # ################################################################### #!/bin/ksh

Editor=vi; Export EDITOR oracle_sid=pprd10; Export Oracle_sid oracle_base=/data/app/oracle; Export Oracle_base oracle_home= $ORACLE _base/10.2.0; 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: $ORACLE _home/bin:/usr/ Ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; Export PATH dbalist= "www.linuxidc.com,www.linuxidc.net"; export dbalist

Sqlplus-s '/As Sysdba ' <<eof 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 Bloc K DESC; Spool off exit EOF if [' Cat Deadlock.alert | wc-l '-GT 0] then Mailx-s "deadlock alert for PPRD10" $DBALIST < Deadl Ock.alert fi

Four Conclusion 0,20,40 7-17 * * 1-5/dba/scripts/ckinstance.sh >/dev/null 2>&1 0,20,40 7-17 * * 1-5/dba/scripts/cklsnr.sh >/dev/null 2>&1 0,20,40 7-17 * * 1-5/dba/scripts/ckalertlog.sh >/dev/null 2>&1 * * * 0-6/DBA/SC ripts/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 through the above script, can greatly alleviate your work. You can use these to do more important work, such as performance tuning.

Common shell scripts for monitoring Oracle Databases-Go