Shulou(Shulou.com)06/02 Report--

I. 8 important scripts to monitor the Oracle database:

1. Check the availability of the instance

two。 Check the availability of the listener

3. Check for error messages in the alert log file

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

5. Analyze table and index for better performance

6. Check the use of tablespaces

7. Find invalid objects

8. Monitor users and transactions

II. Basic knowledge of Unix required by DBA

Basic UNIX commands, here are some commonly used 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:

1. Display the available instances on the server:

$ps-ef | grep smon

Oracle 22086 10 02:32:24? 0:04 ora_smon_PPRD10

Oracle 5215 28972 0 08:10:19 pts/4 0:00 grep smon

two。 Show the 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, which ignores case, because sometimes the listener is uppercase and the result will not be seen.)

$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 10 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 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 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. Basic CRONTAB

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

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

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

1. Check the availability of Oracle instances

All databases on the server are listed in the oratab file

$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::

#

# 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 should, "Y", or should 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 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:

$sh ckinstance.ksh

Wed May 13 12:51:20 PDT 2009

Oracle Database (s) Status gambels:

Oracle Instance-PPRD: Up

Oracle Instance-PPRD10: Up

two。 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

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` is down" $DBALIST

Lsnrctl start PPRD10

Fi

3. Check the 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.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 4. 清除旧的归档文件 以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件： $ df -k | grep arch Filesystem kbytes used avail capacity Mounted on /dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive 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

5. 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 ($# & 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.

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.