Monitoring the Oracle

Objective

The objective is to describe the necessary steps to verify several patterns of an Oracle database.

Target audience

It is intended for OpMon administrators and for IT teams that need:

• Information about what is occurring with the Oracle database at a specific moment or that happened previously;
• To find the queries responsible for the most recent peak in the activities and for the degradation of server response time;
• To obtain results about several hours or days of monitoring, without sacrificing a specific determination of time, being able to identify, with accuracy, the exact moments in which peaks of use happen/happened, identifying, also, high response time or degradation of performance.
• To monitor what kinds of locks are happening in a specific base of production, once locks are sporadic and the user cannot wait for them to happen.

Requirements

To start monitoring the Oracle, the following requirements are necessary:

• Creating a user and password on the Oracle;
• Applying the GRANTS (create session, select any dictionary a select any table);
• Files tnsnames.ora with the settings for the connection with the databases;
• In case it comes to be used, it is also necessary the file sqlnet.ora.

Troubleshooting

In case you find any problem or error during the process, find your answer in our knowledge base, clicking here.

1) Creating a user

In order to monitor the elements available on the Oracle template, a user name and password must be created. Also, the GRANTS should be applied, according to the following commands:

CREATE user opmon IDENTIFIED BY oradbmon;
GRANT CREATE session TO opmon;
GRANT SELECT any dictionary TO opmon;

If the development of specific queries on the system becomes necessary, you will need the following GRANT:

GRANT SELECT any table TO opmon

2) Activating the Oracle on the OpMon

Before continuing, make sure you have the file tnsnames.ora. If the OpMon does not have a DNS internal network, do not use names (server-ora.com.br) on the file tnsnames.ora. Create the following directory tree and the file sqlnet.ora. Remember that you need to be logged in with the root user of the system in order to execute the commands. Follow the steps below:

 [root@localhost ~] mkdir -p /usr/lib/oracle/11.1/client/network/admin

Using a text editor mode, create the following file as shown below:

[root@localhost admin]$ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES)

Afterwards, make a copy of the file tnsnames.ora to the following directory: /usr/lib/oracle/11.1/client/network/admin.

3) Testing the access to the bank

Before moving on with the process, you need to know what instances are defined on the file  tnsnames.ora. One tip is to open the file with a text mode editor such as Vi / Vim, and then you will find something similar:

= NOME DA INSTANCIA
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP) (HOST = IP DO HOST) (PORT = 1521))
  )
  (CONNECT_DATA = 
   (SERVICE_NAME = )
  )
)

In order to test the access, simply use an oracle client text mode available in OpMon, as follows:

[root@localhost ~]$ sqlplus USUARIO/SENHA@INSTANCIA

You will find a similar output

[root@localhost ~]# sqlplus USUARIO/SENHA@INSTANCIA
SQL*PLUS: Release 11.1.0.7.0 - Production on Wed Aug 3 15:12:41    2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
SQL>

4) Using the check_oracle_health

To learn more how the plugging works, see the Help section on the OpMon or via Shell (SSH).

[root@localhost ~]$ /usr/local/opmon/libexec/check_oracle_health --help

Below is an example of how to check with a query which returns a single numeric result:

[root@localhost ~]$ /usr/local/opmon/libexec/check_oracle_health --connect= --user= --password= --mode=sql --name2="Usuarios conectados" --name='SELECT COUNT (*) FROM v$session WHERE type = 'USER' --warning 90 --critical 95
OK - usuarios connectados:0 | 'usuarios'=0;90;95