Shulou(Shulou.com)05/31 Report--

This article is about monitoring Oracle database commonly used shell script how to write, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

The editor below describes the responsibilities of DBA in monitoring Oracle databases on a daily basis, and describes how to accomplish these repetitive monitoring tasks through shell scripts. First, I reviewed some Unix commands commonly used in DBA, and explained how to execute DBA scripts regularly through Unix Cron. At the same time, eight important scripts are introduced to monitor the Oracle database:

Check the availability of the instance

Check the availability of the listener

Check for error messages in the alert log file

Empty old log files before the place where log files are stored is full

Analyze table and index for better performance

Check the use of tablespaces

Find invalid objects

Monitor users and transactions

Basic knowledge of Unix required by DBA

Basic UNIX command

Here are some common Unix commands:

Ps-- display process

Grep-- searches for a certain text pattern in a file

Mailx-- reads or sends mail

Cat-- connects to files or displays them

Cut-- selects the columns to display

Awk-- pattern matching language

Df-- displays the remaining disk space

Here are some examples of how DBA uses these commands:

Display the available instances on the server:

$ps-ef | grep smon

Oracle 21832 1 0 Feb 24? 19:05 ora_smon_oradb1

Oracle 898 1 0 Feb 15? 0:00 ora_smon_oradb2

Dliu 25199 19038 0 10:48:57 pts/6 0:00 grep smon

Oracle 27798 1 0 05:43:54? 0:00 ora_smon_oradb3

Oracle 28781 1 0 Mar 03? 0:01 ora_smon_oradb4 、

Show the available listeners on the server:

$ps-ef | grep listener | grep-v grep

(translator's note: the grep command should add the-I parameter, that is, grep-I listener, which ignores case, because sometimes the listener is uppercase and the result will not be seen.)

Oracle 23879 1 0 Feb 24? 33:36 / 8.1.7/bin/tnslsnr listener_db1-inherit

Oracle 27939 1 0 05:44:02? 0:00 / 8.1.7/bin/tnslsnr listener_db2-inherit

Oracle 23536 1 0 Feb 12? 4:19 / 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 lines 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 basic

A crontab file contains six fields:

0-59 minutes

0-23 hours

The day of the month 1-31

Month 1-12

What day is it 0-6, with 0 = Sunday

Unix command or Shell script

To edit a crontab file, type:

Crontab-e

To view a crontab file, type:

Crontab-l

0 4 * 5 / dba/admin/analyze_table.ksh

30 3 * * 3 dba/admin/hotbackup.ksh 6 / dba/admin/hotbackup.ksh / dev/null 2 > & 1

In the above example, the first line shows a script that parses the table running on 4:00am every Friday. The second line shows a 3:00a.m that performs a hot backup every Wednesday and Saturday. Run.

Common Shell scripts for monitoring databases

The eight shell scripts provided below cover 90% of DBA's daily monitoring, and you may need to modify UNIX's environment variables.

Check the availability of Oracle instances

All databases on the server are listed in the oratab file

$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 out the status of the database (on or off)

# # #

# # 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 is executable:

$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 on the availability of the instance:

$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 the availability of Oracle listeners

Here is a similar script to check the Oracle listener. If the listener stops, the script will restart 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

Check the Alert log (ORA-XXXXX)

Some of the environment variables used by each script can be put into 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:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr

/ bin:/usr/sbin:/

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 the environment variables. If any Oracle errors are found, the script also sends a warning email to 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 　　清除旧的归档文件 　　以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件： $ 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 [[$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

Analyze tables and indexes (for better performance)

Below I will show if you pass parameters to a script:

#

# # analyze_table.sh # #

# # #

#! / bin/ksh

# input parameter: 1: password # 2: SID

If (($# 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 　　结论 0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh >

/ dev/null 2 > & 1

0mai 20jue 40 7-17 * * 1-5 / dba/scripts/cklsnr.sh > / dev/null 2 > & 1

0mai 20jue 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 dba/scripts/analyze_table.sh 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

0mai 20jue 40 7-17 * * 1-5 / dba/scripts/deadlock_alert.sh > / dev/null 2 > & 1

Through the above script, you can greatly reduce your work. You can use these to do more important work, such as performance tuning.

The above is how to write common shell scripts for monitoring Oracle databases. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.