Versions Compared

Old Version 51

changes.mady.by.user Jroy

Saved on

compared with

New Version Current

changes.mady.by.user Jean Gabes

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Clean check suprimés car illisibles.

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 lock
  • etc
Steps:

 

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

  • Understand what is already available (in the Shinken Entreprise installation) 
  • Setup the oracle user account
  • Test the connection to the database 
  • Setup your server host definition  
Panel
titleOn this page

Table of Contents
maxLevel3

What is already available in the Shinken Installation

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

  1. Installation of Oracle instant Client
  2. Installation of check_oracle_health plugin : /var/lib/shinken/libexec/check_oracle_health
  3. Several host templates are ready to be used

 

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

Setup the oracle user account

Create the database user
 

Tip
titleNote

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

Connect to your database as sysadmin on the oracle server :

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


And then create your shinken account on the database:

Image Added
Code Block
languagesql
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: 
Image Added
 Code Block
languagesql
---- 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;
 
 
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
    • Installation of check_oracle_health plugin :
     
    /var/lib/shinken/libexec/check_oracle_health
  • Creation of several host templates
    •  Tip
    titleNote
    We suppose here that the Oracle machine you want to monitor is named srv-lin-1 and is a linux. Please change the configuration and commands according with the real name of your server.
    Test the connection
    To see if the connection to the database named PROD is ok, just launch :
    Image Added
     Code Block
    languagebash
    /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
      
     Tip
    titleTip
    The DATABASES data you will find in the host template matches to the SID of you Oracle database. 
    Manage more than 1 database on the same host
    All checks presents in Oracle templates provided by Shinken Entreprise use the "Duplicate For Each" functionality.
    For each database name listed in the DATA "DATABASES", check will be duplicated. An host, having 2 databases, will then have in double each checks with the datatabase name in the check.
     Info
    titleExample
    Let's look an example: an host has the following:
    • The data "DATABASES" set to "DB1,DB2,DB3"
    • The oracle template attached on it.
    • Let's consider the Oracle-$KEY$-corrupted-blocks checks. He will then have 3 checks:
      • Oracle-DB1-corrupted-blocks
      • Oracle-DB2-corrupted-blocks
      • Oracle-DB3-corrupted-blocks
    If you need to set a specific port of connection for 1 of the database, you can provide it as parameters of the Database:
     Info
    titleExample
    Let's look image the DB2 don't use the 1521 port set by default:
    • Change the data "DATABASES" in that way "DB1,DB2$(5000)$,DB3".
    • For the duplication on the DB2, then the value1 5000 will be provided to each check and will overload the default port.
    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
    titleTip
    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
    • If not configured in the Oracle-connection-method host template
      • 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)
     
     Tip
    titleTip
    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

    Public templates
    oracle
    This is the very basic Oracle template allowing to know if your Oracle 
    database is running and usable.
    Every Oracle templates (except Oracle-connection-method) use it as a parent template. 
    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
    oracle-full
    Contains all Oracle host templates listed in the Shinken Adminstrators templates.
     Warning
    titleWarning
    Using the Oracle oracle-full template could lead to slow issues. You should use the really needed templates insteadwill require a lot of resources on your poller daemon. We advise to use only needed templates.
    Shinken Administrator templates 
    (can't be seen by other users)
    oracle-connection-method
    This template describes the method to connect to an Oracle database. Every Oracle templates use it as a parent template.
     Info
    titleNote
    The parameters common to every Oracle server monitored shoud be setted in the oracle-connection-method host template.
    If you have many connection method, do you own template and attach it to host.


    oracle-availability
     This template gives informations about the database availability.
    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
    enqueueconnection-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
    timeTime to contact the database0 to n seconds0.51
    Oracle-datafile
     This template gives informations about the datafile availability.
    Oracle-events
    eventwaitingHow many percent of the elapsed time has an event spend with waiting0.5
    CheckDetailCheck rangeDefault WarningDefault Critical
    eventdatafile-io-waitstrafficSum of IO-Operationes from Datafiles per secondWaits/sec from system eventsn/sec1010001005000
    datafiles-existingPercentage of max possible datafiles0% to 100%0.18090
    Oracle-flash-recovery-area
    This template allow to know about the used diskspace in the flash recovery area.
     
     Note
    To be used only if you are using flash recovery area.

    CheckDetailCheck rangeDefault WarningDefault Critical
    flash-recovery-area-usageUsed diskspace in the flash recovery area0% to 100%9098
    Oracle-
    latch
    pga
    This template allows to know the percentage of sorts that are done to disk vs. in-memory.
    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
    This template gives complete informations about the redolog.
    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
    This template indicates if there are any RMAN backup problem in the last 3 days.
     Note
    To be used only if you are using RMAN.

    CheckDetailCheck rangeDefault WarningDefault Critical
    rman-backup-problemsNumber of RMAN-errors during the last three days0 to n1 2
    Oracle-rollback-segment
    This template gives informations about the Rollback segment. Rollback segment record the actions of transactions in the event that a transaction is rolled back.

    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
    This template gives complete information about the SGA (System Global Area).
    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
    This template gives information about soft parse. It can give you an idea if an application and corresponding SQL statements are being used inefficiently
    CheckDetailCheck rangeDefault WarningDefault Critical
    soft-parse-ratioPercentage of soft-parse-ratio0% to 100% 90:98:
    Oracle-stale-statistics
    This template allows to know about stale statistics.
    CheckDetailCheck rangeDefault WarningDefault Critical
    stale-statisticsSum of objects with obsolete optimizer statisticsn10 100
    Oracle-
    sysstat
    tablespace
    This template gives informations about tablespace such as fragmentation, if it's possible to allocate the next extent and potentially when a tablespace will be full.
    CheckDetailCheck rangeDefault WarningDefault Critical
    sysstatChanges/sec for any value from v$sysstat n/sec10 100
    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
    This template give global statistics.
    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
    This template indicates the percentage of maximum possible sessions and how many users are connected.
    CheckDetailCheck rangeDefault WarningDefault Critical
    session-usagePercentage of max possible sessions0% to 100%8090
    Connected usersNumber of currently connected users0 to n50100
    How to 
     
    Configuration of Oracle-connection-method host template
    Click on Hosts then on Hosts Templates in the Elements menu
    		Image Added
    In the Name field, type oracle.
    Then clic on Oracle-connection-method
    		Image Added
    Clic on Data tab 
    You can setup the following DATA : 
    • DATABASES : the name(s) of the Oracle SID database(s) to be monitored.
    • ORACLE_CONNECTION_STRING : this is the Oracle connection string which allow to connect and do request toward the databases.
      Unless you know what your doing, you will rarely have the need to modify it.
    • ORACLE_PASSWORD : the oracle password of the user used to connect to database
    • ORACLE_USER : the oracle user name used to connect to database
    • ORACLE_PORT : the listening port of the Oracle database server
     
    Image Added
     
     
     
     

    The parameters specifics to each server, such as database name (AKA SID) for example, has to be done in the host's data itself.
     Tip
    titleTip
    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.
     Warning
    titleImportant (Changing the connection port per database)
     
    Attach an oracle template to you host
    Clic on Hosts in the Elements menuImage Added
    Add the chosen Oracle host template to the Host Templates to inherit field.
    For example the Oracle host template.
    		Image Added
    Click on the Data tab
    		Image Added
    Look for the field DATABASES in the From templates data
    		 
    Type the name of the database to be monitoring
    		Image Added
    Check if the Following data are ok.Image Added
     
     
     Tip
    titleTip
    You can monitor multiple database on the same host in typing the name of the database SID separated by a comma.
    Exemple : DB1,DB2,DB3
    How to create you own template
     Info
    TODO
    create you own pack!
     Info
    TODO
    Use your own plugin
     Info
    TODO