oemctl

The next monitoring utility that will be explained is used as part of Oracle Enterprise Manager (OEM) to provide many shared services for managing databases. OEM is the database management solution provided by Oracle Corporation. The OEMCTL utility manages the Oracle Management Server (OMS) used by OEM.

The OMS manages back-end application logic and stores information in the OEM repository. The functionality provided by the management server includes events, jobs, data sharing, and reports. The OMS serves as the middle tier between the management pack GUIs and the databases being managed. Multiple OMSs can exist in order to evenly distribute heavy workloads across this middle tier, although only one OMS can exist per node.

The repository used by OMS is simply a set of tables in a particular instance that support the management pack (Change, Diagnostics, Tuning) components of OEM.

Many commands are available from the oemctl executable at the OS level that help the DBA to manage the OMS. The following command will start the OMS:

oemctl start oms

If already running, the feedback from the command will respond, “The requested service has already been started.”

The OMS is a Java application and when started, the Java Runtime (JRE) will be loaded into memory and consume approximately 40MB of RAM.

The method used to see if an OMS server is up and running is similar to the method in which a network administrator would check for TCP/IP communication to a remote host – ping.

oemctl ping oms

The ping command reports “the management server is running”. To get a more detailed status of the OMS, the status command can be executed. This command, along with the stop command, requires the OMS Administrator’s username and password in order to work. Optionally, the host name of the machine containing the OMS process can be specified if not the local machine. The default OMS Administrator username is password is sysman/oem_temp.

oemctl status oms <EM Username>/<EM Password>[@<OMS-hostname>]

This command requires the OMS administrator username and password. If not supplied, a Java application window will pop up and prompt for the username and password, as shown in Figure 5.3 below. Therefore, it is critical that the machine that initiates the command supports Java, otherwise the command will hang. For instance, if an attempt is made to obtain the status of the OMS by telnetting from a Windows machine to a UNIX box, the command will hang because the Java GUI application is unable to be loaded. Again, this only applies when not specifying the username and password on the command line.

Figure 5.3 – OMS Login Screen 4

The result of the oemctl status command is shown below. Note the additional information that status provides over the ping command.

The Oracle Management Server on host [moorepc] is functioning properly.

The server has been up for 0 00:02:45.406

Target database session count: 0 (session sharing is off)

Operations queued for processing: 1

Number of OMS systems in domain: 1 (moorepc)

Number of administrators logged in: 1

Repository session pool depth: 15

Repository session count: 7 in-use and 4 available, pool

efficiency: 35%

The status command provides more details for the OMS environment. These include:

· Target database session count – The number of database connections established under the Databases folder in the navigator component of OEM.

· Operations queued for processing – Indicates the number of operations that are waiting to be processed. The activities may include sending e-mail, deleting events, submitting jobs, etc.

· Number of OMS systems in domain – Displays the number of OMS servers running. Multiple OMS servers can be created for scalability in large environments.

· Number of administrators logged in – The number of OEM administrators logged in to the OMS.

· Repository session pool depth – The number of database sessions reserved for OMS to submit jobs and register events. The default is 15 sessions and can be increased when heavy activity occurs.

· Repository session count – The number of repository sessions currently in the pool. The OMS requires 7 sessions on startup. A repository session is needed to access information in the OEM repository. A specific number of sessions are retained in a pool to keep OMS from re-opening and closing sessions – a resource consumer. This parameter is not modifiable.

Stopping the OMS is performed by the command below. The stop command also requires the username and password.

oemctl stop oms <EM Username>/<EM Password>

Now that the basic operations of the oemctl executable have been covered, the next step is to enhance the functionality provided by the OMS. The DBA will decide which events he is concerned about and what should be done about them. This is done through OMS Event Handlers.

OMS Event Handlers

Event handlers listen for events and respond in ways specified by the OMS Administrator. The OMS is capable of responding in two ways:

· Event Logging – Logs events to log files.

· Command Execution – Executes operating system commands in response to an event.

A triggered event can utilize one or both of these components. Event logging applies to those events in which the DBA might not possess a “ready made” automated resolution. For instance, the administrator might want to be notified (logged) when the archive destination directory is 90 percent full even though there is nothing that can automatically be done about it. Likewise, the DBA utilizes command execution when addressing events that can be handled automatically. For instance, when a tablespace becomes 95 percent full, a script could be executed that would add a datafile providing the needed space for the tablespace.

The following steps must be performed to set up event handling.

1. Stop the OMS.

oemctl stop oms sysman/oem_temp

2. Enable the event handler.

oemctl enable eventhandler

Optionally, the default event handler can be viewed by:

oemctl dump eventhandler

***Current Configuration ***

The event handler is enabled for the OMS repository.

The event handler is enabled for the following OMS':

moorepc

The event handler will not respect blackouts

There are 1 global filter(s):

Filter Name: allnodes

This filter will forward a notification if the following condition is met:

node = *

There are 3 adapter(s)

Adapter name: commandexecutor

Enabled: true

There are 1 filter(s) for this adapter

Filter Name: allevents

This filter will forward a notification if the following condition is met:

node = *

The command executor's execution timeout is 40 sec

The command executor has the following templates(s).

For all matching templates, the corresponding command will be executed.

Template Name: allevents2

Template String: echo %eventname%

This template will be used if the following filter is satisfied:

Filter Name: allevents2

This filter will forward a notification if the following condition is met:

eventname = *

Adapter name: eventlogger

Enabled: true

There are 1 filter(s) for this adapter

Filter Name: allevents

This filter will forward a notification if the following condition is met:

eventname = *

The event logger has the following templates(s):

Template Name: allevents

Template String: EVENTNAME:%eventname% NODE: %node% TARGET: %targetname% SE

VERITY: %severity% TIMESTAMP: %timestamp%

This template will be used if the following filter is satisfied:

Filter Name: allevents

This filter will forward a notification if the following condition is met:

eventname = *

If the filter condition is met, the message will be logged to file C:\oracle9i/s

ysman/log/eventhandler.log

Adapter name: plsqlexecutor

Enabled: true

There are 0 filter(s) for this adapter

The PL/SQL executor has the following templates(s).

For all matching templates, the corresponding command will be executed.

3. Start the OMS.

oemctl start oms

Loading event handlers is as simple as stopping the OMS, enabling the event handler, and then restarting the OMS. The next section will discuss ways to modify the event handler to provide the functionality we need.

Modifying the Event Handler

Modifying the event handler requires exporting it, editing it, and then loading it back into the OMS via the import command. The export command simply specifies the name of the file in which to export the event handler. The export command below will populater the file myhandler with the contents of the event handler.

oemctl export eventhandler myhandler

The event handler can then be edited with any text editor. On Windows systems, notepad will work (Figure 5.4).

Figure 5.4 – Event Handler Editing with Notepad

The event handler file contains three main regions – blackouts, filters, and templates.

Blackouts are used to tell the OMS not to act on an event. For certain nodes, a DBA may not care about some events and they should not be handled. A setting of true tells the event handler not to process the event. A setting of false indicates that no blackout exists and the event should be processed.

Filters are used to determine which events are passed on to event logging or command execution. Each filter has a unique name. The default event handler above allows all events to be passed to both the command executor and event logger.

Templates specify exactly how to respond to the event. Templates for the event logger will determine how the data is formatted to the log. Templates specified for command execution include the actual OS command to execute for the event.

After any changes are made, the event handler needs to be imported back into the OMS:

oemctl import eventhandler myhandler

Now the OMS can be started again.

oemctl start OMS

To disable defined event handlers, execute the following command:

oemctl disable eventhandler

And finally, to view any errors encountered while using oemctl, view the log file in the $ORACLE_HOME/sysman/log directory.

Tuning the OMS

Manually tuning the OMS should rarely, if ever, be required. However, certain parameters are stored in the file omsconfig.properties located in the $ORACLE_HOME/sysman/config directory.

< omsconfig.properties

#OMS Configuration Properties

#Mon Feb 04 14:38:27 CST 2002

OEM.REPOSITORY.PASSWORD=e642e7ff415aa78c

oem.BootHost=localhost

OEM.EMCA_SHOW_WELCOME_PAGE=true

OEM.REPOSITORY.DATABASE=ORCL92

oem.BootPort=7771

OEM.REPOSITORY.USER=OMSUSER

OEM.REPOSITORY.DATABASE_ORIG=ORCL92

#The parameters below are used for tuning OMS

oms.vdg.max_out_conns=64

oms.vdg.max_in_conns=32

oms.repository.connect_timeout=120

oms.repository.connect_numTries=12

oms.vdp.ping_interval=2

oms.vdg.conn_retries=1

oms.vdg.conn_retries_delay=5

The parameters beginning with “OEM” are ones supplied by default in the properties file. These were set when the OMS database was created.

The parameters beginning with “oms” are not part of the default properties file. These are the parameters that are available to help the Administrator tune the OMS environment.

These include:

· oms.vdg.max_out_conns – The number of simultaneous outgoing connections supported by the OMS. If the number of servers managed by OMS is more than 64, set this parameter to that number. Otheriwse, keep the default of 64.

· oms.vdg.max_in_conns – The number of simultaneous incoming transactions supported by the OMS. This should equal half the number of outgoing connections.

· oms.repository.connect_timeout – The number of seconds that the OMS will wait for a database that was shut down (the one holding the repository), to start back up again. This number should be set high enough to give the database ample time to start.

· oms.repository.connect_numTries – The number of tries that the OMS will make in order to connect to the repository database.

· oms.vdp.ping_interval – The number of minutes between OMS pings. The OMS pings each monitored server at the interval specified. If unable to ping a managed database, the OMS signals that the database is down. This ping interval should be set high enough to give the OMS enough time to ping all of the managed databases. If a low number is specified and a high number of databases are being monitored, many will be inaccurately flagged as down.

· oms.vdg.conn_retries – The number of times the OMS will attempt to re-ping one node in case the first ping failed.

· oms.vdg.conn_retries_delay – The amount of time between retries, and hence pings, of a managed database.

The oemctl utility provides options for the Administrator to manage the Oracle Management Server. The executable provides a mechanism to start, stop, ping, check the status, and even manage events. The OMS needs to be monitored and tuned just like any other software resource in order to maxmize its efficiency.

Application SQL is one of the most monitored elements of any database environment. The dbms_xplan utility provides an easy way for a DBA to view the optimizer execution path.