Contexte

Cette page vous décris comment superviser un serveur de base Oracle :

  • Les temps de connexions
  • Un redémarrage récent
  • Le nombre de connexion
  • Les accès à la mémoire cache
  • Surveiller les Dead lock
  • ...


Vous devez suivre quelque étape pour mettre en place la supervision d'une nouvelle machine servant de serveur de base de données: 

  • Comprendre ce qui est mis à disposition dans le pack de supervision 
  • Tester la connexion à la base de données
  • Définissez votre l’hôte qui utilisera les modèles de supervision de la base Oracle




Ce qui est déjà disponible dans l'installation de Shinken

Pour vous faciliter un peu la vie, quelques tâches de configuration ont déjà été effectuées pour vous :

  1. Installation d'Oracle instant Client

  2. Installation du plugin check_oracle_health : /var/lib/shinken/libexec/check_oracle_health

  3. Plusieurs modèles d'hôtes sont prêts à être utilisés


La machine que nous allons surveiller pour les exemples s'appelle srv-lin-1 et est un un serveur Oracle sur un OS linux.


Mise en place d'un compte de supervision  Oracle

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

Connect to your database as sysadmin on the oracle server :


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



And then create your shinken account on the database:


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:


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


/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





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.

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:

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.


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

Contains all Oracle host templates listed in the Shinken Adminstrators templates.

Using the oracle-full template will 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.

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.

CheckDetailCheck rangeDefault WarningDefault Critical
connection-timeTime to contact the database0 to n seconds0.51


Oracle-datafile

 This template gives informations about the datafile availability.

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-flash-recovery-area

This template allow to know about the used diskspace in the flash recovery area.


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

This template allows to know the percentage of sorts that are done to disk vs. in-memory.

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.

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

In the Name field, type oracle.

Then clic on Oracle-connection-method

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







The parameters specifics to each server, such as database name (AKA SID) for example, has to be done in the host's data itself.

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.




Attach an oracle template to you host

Clic on Hosts in the Elements menu

Add the chosen Oracle host template to the Host Templates to inherit field.

For example the Oracle host template.

Click on the Data tab

Look for the field DATABASES in the From templates data


Type the name of the database to be monitoring

Check if the Following data are ok.



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


Version des scripts livrés

check_oracle_health : 3.2.1.8