Versions Compared

Old Version 31

changes.mady.by.user David Labardin

Saved on

compared with

New Version 32

changes.mady.by.user David Labardin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
bstract

Abstract

This document describes how you can monitor an Oracle database server such as:

  • Connection time
  • A recent restart
  • The number of connections
  • Cache hit
  • Dead locks
  • etc ...

Introduction

 

Steps

There are some steps you’ll need to follow in order to monitor a new database machine. They are:

  • Setup the oracle user account
  • Update your server host definition for oracle monitoring

What’s Already Done For You

To make your life a bit easier, a few configuration tasks have already been done for you:

  • Installation of Oracle Instant Client

    Code Block
    oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
oracle-lib-compat-11.2.0.12-1.el6.x86_64
oracle-instantclient-sqlplus-selinux-11.2.0.2-1.el6.noarch
perl-DBD-Oracle-1.62-3.el6.x86_64
oracle-nofcontext-selinux-0.1.23.36-1.el6.noarch
nagios-plugins-oracle-1.4.16-10.el6.x86_64
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
oracle-instantclient-selinux-11.2.0.2-1.el6.noarch
  • Installation of check_oracle_health plugin
    • /var/lib/shinken/libexec/check_oracle_health

 

 

Tip

We suppose here that the Oracle machine you want to monitor is named srv-lin-1 and is a linux. Please change the above lines and commands with the real name of your server of course.

Setup the oracle user account

Create the database user

Tip

You will need to configure the user for all your oracle databases.

Connect to your database as sysadmin on the oracle server :

Code Block
srv-lin-1:oracle# sqlplus "/ as sysdba"

And then create your shinken account on the database:


Code Block
CREATE USER shinken IDENTIFIED BY shinkenpassword;GRANT CREATE SESSION TO shinken;
GRANT SELECT any dictionary TO shinken;
GRANT SELECT ON V_$SYSSTAT TO shinken;
GRANT SELECT ON V_$INSTANCE TO shinken;
GRANT SELECT ON V_$LOG TO shinken;
GRANT SELECT ON SYS.DBA_DATA_FILES TO shinken;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO shinken;

 

And for old 8.1.7 database only:

 

Code Block
---- if somebody still uses Oracle 8.1.7...
GRANT SELECT ON sys.dba_tablespaces TO shinken;
GRANT SELECT ON dba_temp_files TO shinken;
GRANT SELECT ON sys.v_$Temp_extent_pool TO shinken;
GRANT SELECT ON sys.v_$TEMP_SPACE_HEADER TO shinken;
GRANT SELECT ON sys.v_$session TO shinken;

 

 

Test the connection

To see if the connection to the database named PROD is ok, just launch :

Code Block
/var/lib/shinken/libexec/check_oracle_health --connect "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = srv-lin-1)(PORT = 1521)))(CONNECT_DATA =(SID = PROD)))" --user "shinken" --password "shinkenpassword" --mode connection-time

Configuring host template and host for monitoring

The parameters common to every Oracle server monitored shoud be setted in the host template "oracle-connection-method".

The parameters specifics to each server, such as database name for example, have to be done in the host's data itself.

Tip

It's a best practice to use the same database monitoring user on every Oracle server monitored. Doing so, you can configure the database user/password only once in the host template "oracle-connection-method".

Configuration of Oracle-connection-method host template

  • select the Oracle-connection-method host template
  • Clic on "Data" tab
  • Type the oracle user in the ORACLE_USER field (shinken in the previous example)
  • Type the oracle password in the ORACLE_USER field (shinkenpassword in the previous example)

Configuration of a host

  • Add the Oracle choosen host template to the Host Templates to inherit field
  • Clic on "Data" tab
  • Look for the field DATABASES in the "From templates" datas
  • Type the name of the database to be monitoring

 

Tip

You can monitor multiple database on the same host in typing the name of the database separated by a comma.

Exemple : DB1,DB2,DB3

What is checked with the templates

Oracle

 

CheckDetailCheck rangeDefault WarningDefault Critical
tnspinglistener   
process-usagePercentage of max possible processes0% to 100%8090
sga-shared-pool-freeFree Memory in the Shared Pool0% to 100%10:5:
tablespace-usageUsed diskspace in the tablespace0% to 100%9098
corrupted-blocksNumber of corrupted blocks in database0 to n110
invalid-objectsSum of faulty Objects, Indices, Partitions0 to n0.10.1


Oracle-datafile

 

CheckDetailCheck rangeDefault WarningDefault Critical
datafile-io-trafficSum of IO-Operationes from Datafiles per secondn/sec10005000
datafiles-existingPercentage of max possible datafiles0% to 100%8090

Oracle-enqueue

CheckDetailCheck rangeDefault WarningDefault Critical
enqueue-contentionEnqueue wait/request-Ratio0% to 100%110
enqueue-waitingHow many percent of the elapsed time since the last run has an Enqueue spend with waiting0% to 100%0.000330.00033

Oracle-events

CheckDetailCheck rangeDefault WarningDefault Critical
event-waitsWaits/sec from system eventsn/sec10100
event-waitingHow many percent of the elapsed time has an event spend with waiting0% to 100%0.10.5

Oracle-flash-recovery-area

CheckDetailCheck rangeDefault WarningDefault Critical
flash-recovery-area-usageUsed diskspace in the flash recovery area0% to 100%9098

Oracle-latch

CheckDetailCheck rangeDefault WarningDefault Critical
latch-contentionLatch misses/gets-ratio. With –name a Latchname or Latchnumber can be passed over. (See list-latches)0% to 100%12
latch-waitingHow many percent of the elapsed time since the last run has a Latch spend with waiting0% to 100%0.11

Oracle-pga

CheckDetailCheck rangeDefault WarningDefault Critical
pga-in-memory-sort-ratioPercentage of sorts in the memory0% to 100% 99:90:

Oracle-redo

CheckDetailCheck rangeDefault WarningDefault Critical
switch-intervalInterval between RedoLog File Switches0 to n600: 60:
retry-ratioRetry-Rate in the RedoLog Buffer0% to 100%110
redo-io-trafficRedolog IO in MB/secn/sec199200

Oracle-rman

CheckDetailCheck rangeDefault WarningDefault Critical
rman-backup-problemsNumber of RMAN-errors during the last three days0 to n1 2

Oracle-rollback-segment

CheckDetailCheck rangeDefault WarningDefault Critical
roll-header-contentionRollback Segment Header Contention0% to 100% 12
roll-block-contentionRollback Segment Block Contention0% to 100%12
roll-hit-ratioRollback Segment gets/waits Ratio0% to 100%99:98:
roll-extendsRollback Segment Extends nn/sec1100
roll-wrapsRollback Segment Wraps nn/sec1100

Oracle-sga

CheckDetailCheck rangeDefault WarningDefault Critical
sga-data-buffer-hit-ratioHitrate in the Data Buffer Cache0% to 100% 98:95:
sga-library-cache-gethit-ratioHitrate in the Library Cache (Gets)0% to 100%98:95:
sga-library-cache-pinhit-ratioHitrate in the Library Cache (Pins)0% to 100%98:95:
sga-library-cache-reloadsReload-Rate in the Library Cachen/sec1010
sga-dictionary-cache-hit-ratioHitrate in the Dictionary Cache0% to 100%95:90:
sga-latches-hit-ratioHitrate of the Latches0% to 100%98:95:
sga-shared-pool-reloadsReload-Rate in the Shared Pool0% to 100%110

Oracle-soft-parse

CheckDetailCheck rangeDefault WarningDefault Critical
soft-parse-ratioPercentage of soft-parse-ratio0% to 100% 90:98:

Oracle-stale-statistics

CheckDetailCheck rangeDefault WarningDefault Critical
stale-statisticsSum of objects with obsolete optimizer statisticsn10 100

Oracle-sysstat

CheckDetailCheck rangeDefault WarningDefault Critical
sysstatChanges/sec for any value from v$sysstat n/sec10 xx

Oracle-tablespace

CheckDetailCheck rangeDefault WarningDefault Critical
tablespace-fragmentationFree Space Fragmentation Index100 to 130:20:
tablespace-can-allocate-nextChecks if there is enough free tablespace for the next Extent   
tablespace-remaining-timeSum of remaining days until a tablespace is used by 100%. The rate of increase will be calculated with the values from the last 30 days. (With the parameter –lookback different periods can be specified)Days90:30:

Oracle-top10-stats

CheckDetailCheck rangeDefault WarningDefault Critical
seg-top10-logical-readsSum of the userprocesses under the top 10 logical readsn19
seg-top10-physical-readsSum of the userprocesses under the top 10 physical readsn19
seg-top10-buffer-busy-waitsSum of the userprocesses under the top 10 buffer busy waitsn19
seg-top10-row-lock-waitsSum of the userprocesses under the top 10 row lock waitsn19

Oracle-usage

CheckDetailCheck rangeDefault WarningDefault Critical
session-usagePercentage of max possible sessions0% to 100%8090
Connected usersNumber of currently connected users0 to n50100

Oracle-full

 

 

...