Oracle Enterprise Manager

Overview

Oracle Enterprise Manager (Oracle EM) is a distributed application for centralized monitoring, diagnosing, and resolving issues related to Oracle products.

Oracle EM collects and stores statistics from remote databases, applications, and systems in an Oracle database (SYSMAN.gc$metric_values table) which Axibase Collector queries every 15 minutes to offload incremental data into the Axibase Time Series Database for long-term retention and operations analytics.

Requirements

  • Oracle Enterprise Manager 11+

Installation Steps

Import Oracle EM JDBC Job

  • On the Jobs page select Import from the split-button below the table and upload the oracle-em-jobs.xml file.

Configure Oracle EM Database Connection

  • Open the Data Sources > Databases page and select the OracleEM database.
  • Provide connection parameters to the target Oracle EM database as displayed below:

  • Read-only/SELECT privileges to SYSMAN.GC$TARGET and SYSMAN.gc$metric_values tables are sufficient.
  • Execute test query to check the connection:
SELECT NULL FROM dual
  • Click Meta Data and check that the SYSMAN schema is present in the list of schemas.

Verify Job Configuration

  • Open Oracle EM JDBC job.
  • Set Data Source to OracleEM.

  • Choose one of target ATSD instances if your Collector instance is connected to multiple ATSD servers.
  • Save the Job.
  • Open each configuration, click Test, and review the output. See Data Queries for more information.

Schedule the Job

  • Open the JDBC Job page and click Run for the Oracle EM JDBC job.
  • Ensure that the job status is COMPLETED and Items Read and Sent commands are greater than 0.

  • If there are no errors, set job status to Enabled and save the job.

Verify Metrics in ATSD

  • Log in to ATSD.
  • Click the Metrics tab and filter metrics by name oem.*.

Viewing Data in ATSD

Metrics

Properties

Entity Groups

  • oracle-databases
  • oracle-hosts

Portals

Data Queries

  • Metrics Queries select most recent statistics
SELECT ENTITY_NAME, ENTITY_TYPE || '.' || REPLACE(REPLACE(METRIC_GROUP_LABEL, ',', ' '), ' ', '_') || '.' ||
REPLACE(REPLACE(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(METRIC_COLUMN_LABEL, ' - ', '-'), ',', ' '), ')', ' '), '(', ' ')), ' ', '_'), '__', '_') AS METRIC,
NULLIF(KEY_PART_1, '%') AS KEY, NULLIF(KEY_PART_2, '%') AS KEY_2, NULLIF(KEY_PART_3, '%') AS KEY_3, NULLIF(KEY_PART_4, '%') AS KEY_4,
NULLIF(KEY_PART_5, '%') AS KEY_5, NULLIF(KEY_PART_6, '%') AS KEY_6, NULLIF(KEY_PART_7, '%') AS KEY_7, COLLECTION_TIME_UTC, VALUE
FROM SYSMAN.gc$metric_values
WHERE ENTITY_TYPE = 'oracle_database' AND METRIC_GROUP_LABEL_NLSID IS NOT NULL AND COLLECTION_TIME_UTC >= ? ORDER BY COLLECTION_TIME_UTC
SELECT ENTITY_NAME, ENTITY_TYPE || '.' || REPLACE(REPLACE(METRIC_GROUP_LABEL, ',', ' '), ' ', '_') || '.' ||
REPLACE(REPLACE(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(METRIC_COLUMN_LABEL, ' - ', '-'), ',', ' '), ')', ' '), '(', ' ')), ' ', '_'), '__', '_') AS METRIC,
NULLIF(KEY_PART_1, '%') AS KEY, NULLIF(KEY_PART_2, '%') AS KEY_2, NULLIF(KEY_PART_3, '%') AS KEY_3, NULLIF(KEY_PART_4, '%') AS KEY_4,
NULLIF(KEY_PART_5, '%') AS KEY_5, NULLIF(KEY_PART_6, '%') AS KEY_6, NULLIF(KEY_PART_7, '%') AS KEY_7, COLLECTION_TIME_UTC, VALUE
FROM SYSMAN.gc$metric_values
WHERE ENTITY_TYPE = 'host' AND COLLECTION_TIME_UTC >= ? ORDER BY COLLECTION_TIME_UTC
  • Properties Query selects current properties
SELECT 'oem.' || TARGET_TYPE AS TTYPE, TARGET_NAME, HOST_NAME, DISPLAY_NAME, TIMEZONE_REGION, TYPE_QUALIFIER1 AS TYPE
FROM SYSMAN.GC$TARGET
WHERE TARGET_TYPE IN ('host', 'oracle_database')