The most common shell scripts used by eight DBAs to monitor Oracle databases

This article describes eight common shell scripts for monitoring data. First, I reviewed some common DBA Unix Commands and explained how to regularly execute DBA scripts through Unix Cron. There are also a lot of similar articles on the Internet, but they basically cannot run normally. After some time, you can simply use them.

I. At the same time, the article also introduced eight important scripts to monitor the Oracle database:
1. Check instance availability
2. Check listener availability
3. Check the error information in the alert Log File.
4. Clear old log files before they are fully stored.
5. Analyze table and index for better performance
6. Check the usage of the table space.
7. Find invalid objects
8. Monitor users and transactions

2. Basic Unix knowledge required by DBA
Basic 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 the displayed Column
Awk-pattern matching language
Df -- display the remaining disk space

Here are some examples of how DBAs use these commands:

1. display 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 grep smon

2. display available listeners on the server:
$ Ps-ef grep listener grep-v grep
(The grep command should add the-I parameter, that is, grep-I listener. this parameter is used to ignore case sensitivity, because sometimes the listener is capitalized and the result will not be displayed)
$ 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 grep-I listener

3. view the file system usage of the Oracle archive directory
$ Df-k | grep/data
/Dev/md/dsk/d50 104977675 88610542 15317357 86%/data

4. count the number of rows 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 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

7. 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.

Iii. 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.

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

# This file is used by ORACLE utilities. It is created by root. sh
# And updated by the Database Configuration Assistant when creating
# A database.

# A colon, ':', is used as the field terminator. A new line terminates
# The entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ ORACLE_SID: $ ORACLE_HOME: <N | Y>:
#
# The first and second fields are the system identifier and home
# Directory of the database respectively. The third filed indicates
# To the dbstart utility that the database shocould, "Y", or shocould not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ ORACLE_SID are 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 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': \ 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

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:
$ Sh ckinstance. ksh
Wed May 13 12:51:20 PDT 2009
Oracle Database (s) Status gambels:
Oracle Instance-PPRD: Up
Oracle Instance-PPRD10: Up

2. Check the availability of the Oracle listener
The following script checks the Oracle listener. If the listener stops, the script restarts 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.bkjia.com, www.bkjia.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' is down" $ DBALIST
Lsnrctl start PPRD10
Fi

3. Check Alert logs (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/bin:/usr/ccs/bin:/usr/sbin: /usr/openwin/bin:/opt/bin :.; export PATH
DBALIST = "www.bkjia.com, www.bkjia.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.log
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

1 2 3 Next Page