This agent can monitor several parameters from an Oracle database instance. We've tested it against Oracle release 8, 8i, 9i and 10g, but we strongly recommend you to test it before implementing the final monitorization in production environments.

The Oracle agent has been developed using the functionalities and improves of the Osmius framework and ACE libraries, so it's mandatory the installation of ACE libraries for the correct Osmius installation and execution. This agent also needs Oracle libraries to use the API's known as Oracle C++ Call Interface, you need OCCI libraries and/or Oracle “normal” or “instant” client.

ORACL001 agent provides about a dozen of events with customizable parameters to grow up and expand in a very easy way. The events has been selected according to the experiences and suggestions of the best Oracle DBA group who we know, avoiding no-sense variable monitoring.

The Oracle Osmius agent is prepared to reuse database connections and only try to reconnect when one of the scheduled events fails.This agent is made by cross-platform code and is non-intrusive. You don't need to install the agent in the same machine where the Oracle instance is. The Oracle Osmius agent is prepared to reuse database connections and only try to reconnect when one of the scheduled events fails.

Note to limit the consumption of resources of the agent in an Oracle instance.
Oracle API doesn't provide a way to implement a good “timeout” for locking operations against the database. This situation could happen for example when a database with problems is being mounted and dismounted or by excessive load. One way to limit the excessive time of resources use in Oracle is using “oracle user profiles”:

CREATE PROFILE LIMIT_OSMIUS_QUERY_TIME LIMIT CPU_PER_CALL 1000;
ALTER USER OSMIUS_USER PROFILE LIMIT_OSMIUS_QUERY_TIME;

Note To configure the Oracle libraries in Osmius.

You must specify the path of the Oracle libraries with LD_LIBRARY_PATH variable in order to allow Osmius work properly . This requires modifying the file osm.sh located in the path $OSM_ROOT\osmius\bin\osm.sh, for example:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$OSM_ROOT/lib:/opt/app/oracle/db/lib

Generally speaking each Osmius agent is able to monitor many instances but only one instance type. If you need to review this concept take a look at the Concepts Glosary. Each instance is individually defined in the agent configuration file (more in Agents & Instances, and depending on the Instance Type you must define certain connectio info.

The connection_info is required information to establish connections with Oracle instances. (More at connection_info)

In this case the connection_info format in this:

CONNECTION_INFO= -u <USER> -p <PASSWORD> [-d <ORACLE_SID>]

Where:

• USER: This user must has been granted to access the database. Mandatory.
• PASSWORD: User password to access the database.Passwords are not encrypted. Set the adequate file permissions. Mandatory.
• SID_ORACLE: Optional.
  If it is not declared the agent will use ORACLE_SID or TNS_NAMES as sqlplus.
  If it is declared the resutl ins like in sqlplus user/password@SID

Sample:

CONNECTION_INFO= -u osmius -p pass3344 -d ORACLE_TEST

Defines the instance type about to monitor.

In this particular case:

TYPE= ORACLE01

Here you have a brief description of this agent capabilities. Each event is explained in detail in next sections.

Checks the connection availability against the database instance and performs a “SELECT” sentence over a table and recovers the result.

Extra parameters:
You can use an optional parameter:

Returned values:

Recommended Parameters:

Sample:

AVAILABL = -t 10 -c 1 -w 0 -a 0 -T "Oracle availability using AUTHORS_BOOK table" -U AUTHORS_BOOK

Comments: If an user table is used don't forget grant read permission to Osmius user in the Oracle database.

Performs a “SELECT COUNT” sentence over a user defined table (or DUAL) and returns the number of rows.

Extra parameters:
You can use an optional parameter:

Returned values:

Recommended Parameters:

Sample:

ORSELCNT = -t 60 -c 0 -w 10 -a 20 -T "Rows in Test Table:" -U Test

Comments: Useful event to customize the applications monitoring focused on registries number in an y table that shows the general status of the processes. It can be used to control the pending number of registries, by example, along the week days.

If an user table is used don't forget grant read permission to Osmius user in the Oracle database.

Returns the number of user sessions connected to the instance.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORNUMUSU = -t 300 -c 0 -w 30 -a 50 -T "The number of connected users:"

Comments: The number of users and connected sessions in a while could indicate the database activity and we can go ahead problems like lack of resources or that the sessions are not being released. This event could be used to log the number of users connected depending of the hour of day, helping to establish service level agreements.

Checks the percentage of available maximum number cursors for each session. Returns information about the sessions nearest to reach the maximum consumed cursors at the moment. Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORPRCCUR = -t 300 -c 0 -w 80 -a 95

Comments: It's common that applications use intensively the cursors and by error or lack of experience the cursors are not being released generating errors like ORA-1000 “Maximum open cursors exceeded”. Check percentage and change schedule of cursor use or the OPEN_CURSORS parameter.

Check the number of global locks of Oracle sessions what are locking other sessions.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORNUMLCK = -t 60 -c 0 -w 2 -a 5 -T "The number of global blocking locks is:"

Comments: The number of locks between sessions, if the applications that use database have been well designed, this number should always be zero. In addition, if a lock appears it should be solved quickly. Session locks that persist in time can be hazardous.

Check the number of invalid objects (usually PL/SQL procedures and packages) in database and normally we need to recompile them.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

OROBJINV = -t 3600 -c 0 -w 10 -a 100 -T "Invalid object are:"

Comments: Use this sentence to compile your invalid objects:

select 'alter ' || decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
	|| ' ' || owner || '.' || object_name || ' compile'
      	|| decode(object_type, 'PACKAGE BODY', ' body;', ';')
from dba_objects
where status='INVALID'
  and object_type <> 'SYNONYM'
order by owner,
       decode(object_type, 'PACKAGE BODY', 'AAA', 'PACKAGE', 'AAB',
       substr(object_type, 1, 3)) desc,
       object_name;

Invalid synonyms fall out of recompilation in this case.

Checks the number of tables with chained rows. It gives us a database fragmentation idea related to rows and performance problems.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORCHATBL = -t 86400 -c 0 -w 1 -a 2 -T "Number of tables with chained rows"

Comments: Tables must be analyzed to verify if “chained rows” exist.
Our recommendation: Comment with your DBA and make an automatic analysis plan.

Percentage of Success on Data Cache

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORBUFRAT = -s -t 5 -c 1 -w 98 -a 95 -T "Hit percentage in Oracle data cache:"

Comments: You must take in consideration that the percentage will be low at the first moment of instance usage after being started up.
If buffer cache hit ratio is low it's possible for the sentences to be too open against database tables and perhaps indexes should be reviewed. Consult your DBA and/or Oracle manuals.

Percentage of Success on sentences and instructions cache.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORPARRAT = -t 600 -c 1 -w 95 -a 90 -T  -T "Parse Hit Ratio:"

Comments: You must take in consideration that the percentage will be low at the first moments of instance usage after being started up.
It can give us an idea about the sentences being reused and the proper “bind” variables usage

Occupation percentage in Tablespaces

Extra parameters:
You can use an optional parameter:

Returned values:

Recommended Parameters:

Sample:

  ORPRCTBS = -s -t 1200 -T "Tablespaces:" -c 0 -w 95 -a 98 -X "'SYSTEM','TEMP'"

Comments: In addition to the value this event returns the most occupied tablespace name in the event text.
It's important to predict the space problems in Oracle tablespaces. Difference cold be stopping all process and backup recover or simply give more space (Oracle is able to increase space automatically – See Oracle Administration Manual).

Object with the highest probability of reaching the maximum number of extensions.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORMAXEXT =  -t 300 -T "Object close to maximum extensions" -c 1 -w 5 -a 2

Comments: Objects have a MAX_EXTENTS field what limits the number of extensions that can be used according to their needs of storage.
With this event we can foresee situations where the objects that can't extend anymore, making free space for new records creating problems to processes that could be important.

Returns first object whose next extension wouldn't fit in available free space in their assigned tablespace.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORNXTEXT =  -s -t 3600 -T "Objects with next extensions problems" -c 0 -w 1 -a 1

Comments: When the object tries to grow up in a new extension, if this one cannot fit in the free space of the tablespace we'll get errors and stopped processes.

Percentage of used processes.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORPRCPRO =  -t 300 -T "Process Percentage:" -c 0 -w 85 -a 95

Comments: If this percentage is high contact your DBA to raise “maximum processes” parameter.

Returns number of unarchived “redo logs”. Activate this event only if database is in “ARCHIVE LOG” mode.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORREDLOG =  -t 60 -T "Number of unarchived redo logs" -c 0 -w 4 -a 10

Comments: If redo log are not archived the all activity on database can be stopped.
Warning thresholds depends on redo logs number and size.

MB used in data segments in the whole database.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORSPCDAT =  -t 86400 -c 0 -w 999999 -a 999999 -T "Space used in Tables"

Comments: It could be interesting to recover this parameter once a day, week or month, to see historical evolution of used space in data base tables.
Usefull in trends and capacity planning.

MB used in index segments in the whole database.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORSPCIDX =  -t 86400 -c 0 -w 999999 -a 999999 -T "MBytes of Space used in Indexes is:"

Comments: It could be interesting to recover this parameter once a day, week or month, to see historical evolution of used space in data base indexes.
Usefull in trends and capacity planning.

Heavy sentences with high execution time.

Extra parameters:
No

Returned values:

Recommended Parameters:

Sample:

ORLONOPS =  -t 86400 -c 0 -w 3600 -a 7200

Comments: It could be interesting to recover this parameter once an hour or a day to identify long operations and send it to developers and dbas to fix and rewrite them.

ORARAUPC returns the Archive Area Used percentage (%).

Extra parameters:
This event need an extra parameter to work:

Return values:

Recommended Parameters:

Sample:

ORARAUPC = -s -t 900 -T "Archive Area Used (%)" -c 0 -w 80 -a 90

ORDATAFL checks Data Failure Detected.

Return values:

Recommended Parameters:

Sample:

ORDATAFL = -s -t 300 -T "Data Failure Detected" -c 0 -w 1 -a 1

ORBRKJOB checks Broken Job Count.

Return values:

Recommended Parameters:

Sample:

ORBRKJOB = -s -t 300 -T "Broken Job Count" -c 0 -w 1 -a 1

ORPRCLMT checks Process Limit Usage (%).

Return values:

Recommended Parameters:

Sample:

ORPRCLMT = -s -t 60 -T "Process Limit Usage (%)" -c 0 -w 80 -a 90

ORSESLMT checks Session Limit Usage (%).

Return values:

Recommended Parameters:

Sample:

ORSESLMT = -s -t 60 -T "Session Limit Usage (%)" -c 0 -w 80 -a 90

ORDPAUPC checks Dump Area Used (%).

Extra parameters:
This event need an extra parameter to work:

Return values:

Recommended Parameters:

Sample:

ORDPAUPC = -s -t 900 -T "Dump Area Used (%)" -c 0 -w 95 -a 97

ORFRAUPC checks Usable Flash Recovery Area (%).

Return values:

Recommended Parameters:

Sample:

ORFRAUPC = -s -t 900 -T "Usable Flash Recovery Area (%)" -c 1 -w 15 -a 3

ORINSTAT checks Instance Status

Return values:

Recommended Parameters:

Sample:

ORINSTAT = -s -t 60 -T "Instance Status" -c 0 -w 1 -a 1

ORDBLCOR checks Data Block Corruption

Return values:

Recommended Parameters:

Sample:

ORDBLCOR = -s -t 300 -T "Data Block Corruption" -c 0 -w 1 -a 1

OROUTMEM checks Out of Memory

Return values:

Recommended Parameters:

Sample:

OROUTMEM = -s -t 300 -T "Out of Memory" -c 0 -w 1 -a 1

ORRDLOGC checks Redo Log Corruption

Return values:

Recommended Parameters:

Sample:

ORRDLOGC = -s -t 300 -T "Redo Log Corruption" -c 0 -w 1 -a 1

ORARCHUN checks Archiver Hung

Return values:

Recommended Parameters:

Sample:

ORARCHUN = -s -t 300 -T "Archiver Hung" -c 0 -w 1 -a 1

ORFRARPC checks Recovery Area Free Space (%)

Return values:

Recommended Parameters:

Sample:

ORFRARPC = -s -t 900 -T "Recovery Area Free Space (%)" -c 1 -w 15 -a 3

ORTBLS00 checks Tablespace Used (%)

Extra parameters:
This event need an extra parameter to work:

Return values:

Recommended Parameters:

Sample:

ORTBLS00 = -s -t 300 -T "Tablespace Used (%)" -c 0 -w 85 -a 97

The Oracle database agent has the same execution features and the other Osmius agents. See Strating and stopping agents.

Every Osmius agent can be used in standalone mode. This mode in really useful when testing a new agent in the development process.

To run in this mode yuo must add a new parameter to the configuration file (osm_ag_CODE0001.ini) named “SNDCMD”, as you can read here.

Run the Osmius agent setting the master agent communication port to 0: