Pre-requisites for monitoring Oracle Database

eG Enterprise can monitor Oracle database server in an agent-based or agentless manner. The following pre- requisite must be satisfied before you start monitoring Oracle database to enable the eG agent to access the target database and collect metrics.

1. In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

   The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

   create user oraeg identified by oraeg

   create role oratest;

   grant create session to oratest;

   grant select_catalog_role to oratest;

   grant oratest to oraeg;

   The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

   alter session set container=<Oracle_service_name>;

   create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

   Grant create session to <user_name>;                                

   Grant select_catalog_role to <user_name>;

   How to limit the number of connections for a user?

   To prevent any one user from potentially tying up all resources, it is recommended to limit the number of sessions any user can open concurrently. Inorder to achieve this follow the steps given below:

   • A user profile is a set of limits on the database resources and the user password. Once you assign a profile to a user, then that user cannot exceed the database resource and password limits. Find the current profile of a user using the command given below:

     SELECT username, profile FROM dba_users WHERE username = 'EGTEST';

     

     Figure 1 : Finding the existing profile of a user

   • Now, create a new profile with the desired number of sessions count the user can have when connecting to the Oracle database. For this run the command given below:

     CREATE PROFILE limit_connections LIMIT SESSIONS_PER_USER 3

     where, 3 is the desired number of sessions the user can create.

   • Then assign the above-created profile to EGTEST user. For this follow the below command:

     ALTER USER EGTEST PROFILE limit_connections;

   • Again verify the profile of EGTEST user by running the below command, this will show the resource or password limits assigned to this user:

     SELECT username, profile FROM dba_users WHERE username = 'EGTEST';

     

     Figure 2 : Creating and assigning a new profile to the user

   • Now, connect to the database server using EGTEST user. If the number of sessions to the database exceeds the session limit, new sessions fail and return an error. That means, If you try to establish more number of sessions for the EGTEST user than the limited count 3, as in Figure 3 the error message (see Figure 4) will be shown.

     

     Figure 3 : Establishing more number of sessions

     

     Figure 4 : Error message for exceeded sessions