Oracle
Introduction
Oracle DB monitoring is the process of collecting and analyzing data about the performance of an Oracle database. This data can be used to identify potential performance problems and take corrective action to improve performance.
Getting Started
Compatibility
The Oracle O11ySource is compatible with all versions of Oracle DB greater than or equal to 11g.
Data Collection Method
vuSmartMaps collects health and performance data for Oracle Database by using an internal data collector. This data collector collects data based on the source configuration.
Prerequisites
Dependent Configuration
To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.
Inputs for Configuring Data Source
- Instance Name: Please enter the name of the Oracle instance. This should be a unique identifier for the specific Oracle deployment you want to monitor.
- DB Details: Please provide the details of DB
- IP Address: IP address needs to be a valid IP Address.
- Port: Enter a valid port
- Credential: This field is required
- DB Name: Name of the database to be polled for metrics
- Metrics Collection Queries: These queries are utilized for Oracle server's metrics.
- Query: Choose the Queries
- Polling Interval [seconds]: How frequently data is gathered. interval should be between 180 -.86400 seconds
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps IP | IP address of the Oracle DB server | 1521* | TCP | Outbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
Configure Metrics Collection from OHS Server Metrics from Oracle Database are gathered by establishing a connection to the server using JDBC.
- To get started with the standard Oracle Database integration, create a read-only
vunetuser with proper access to your Oracle Database server. - Connect to your Oracle Database and execute the following commands to create the vunet user and grant necessary permissions:
sqlplus sys/<PASSWORD>@<IPADDRESS>:<PORT>/<DATABASE NAME> as SYSDBA
CREATE USER vunet IDENTIFIED BY <PASSWORD>;
GRANT CONNECT TO vunet;
GRANT SELECT ON sys.dba_data_files TO vunet;
GRANT SELECT ON sys.dba_free_space TO vunet;
GRANT SELECT ON sys.dba_hist_active_sess_history TO vunet;
GRANT SELECT ON sys.dba_hist_snapshot TO vunet;
GRANT SELECT ON sys.dba_hist_sqlstat TO vunet;
GRANT SELECT ON sys.dba_hist_sqltext TO vunet;
GRANT SELECT ON sys.dba_indexes TO vunet;
GRANT SELECT ON sys.dba_objects TO vunet;
GRANT SELECT ON sys.dba_scheduler_job_run_details TO vunet;
GRANT SELECT ON sys.dba_tablespace_usage_metrics TO vunet;
GRANT SELECT ON sys.dba_tablespaces TO vunet;
GRANT SELECT ON sys.dba_users TO vunet;
GRANT SELECT ON GV_$PROCESS TO vunet;
GRANT SELECT ON gv_$sysmetric TO vunet;
GRANT SELECT ON gv_$active_session_history TO vunet;
GRANT SELECT ON gv_$asm_diskgroup TO vunet;
GRANT SELECT ON gv_$event_name TO vunet;
GRANT SELECT ON gv_$instance TO vunet;
GRANT SELECT ON gv_$lock TO vunet;
GRANT SELECT ON gv_$locked_object TO vunet;
GRANT SELECT ON gv_$log_history TO vunet;
GRANT SELECT ON gv_$metric TO vunet;
GRANT SELECT ON gv_$PGAstat TO vunet;
GRANT SELECT ON gv_$resource_limit TO vunet;
GRANT SELECT ON gv_$segment_Statistics TO vunet;
GRANT SELECT ON gv_$session TO vunet;
GRANT SELECT ON gv_$sesstat TO vunet;
GRANT SELECT ON gv_$sgastat TO vunet;
GRANT SELECT ON gv_$sqlarea TO vunet;
GRANT SELECT ON gv_$statname TO vunet;
GRANT SELECT ON gv_$log TO vunet;
GRANT SELECT ON v_$resource_limit TO vunet;
GRANT SELECT ON v_$active_session_history TO vunet;
GRANT SELECT ON v_$archived_log TO vunet;
GRANT SELECT ON v_$asm_diskgroup TO vunet;
GRANT SELECT ON V_$event_name TO vunet;
GRANT SELECT ON v_$instance TO vunet;
GRANT SELECT ON v_$lock TO vunet;
GRANT SELECT ON v_$log TO vunet;
GRANT SELECT ON v_$log_history TO vunet;
GRANT SELECT ON v_$metric TO vunet;
GRANT SELECT ON V_$PGAstat TO vunet;
GRANT SELECT ON v_$recovery_file_dest TO vunet;
GRANT SELECT ON V_$segment_Statistics TO vunet;
GRANT SELECT ON v_$session TO vunet;
GRANT SELECT ON V_$SESSION_LONGOPS TO vunet;
GRANT SELECT ON v_$sesstat TO vunet;
GRANT SELECT ON V_$SGAstat TO vunet;
GRANT SELECT ON v_$sqlarea TO vunet;
GRANT SELECT ON v_$statname TO vunet;
GRANT SELECT ON GV_$INSTANCE TO vunet;
- Verify that the Oracle user 'vunet' can connect to the database using the specified password.
Configuration Steps
Enablethe Oracle O11ySource.- Select the Sources tab and press the
+button to add Oracle DB instance to be monitored. - Set up metrics collection configurations. The O11ySource already provides predefined metric collection queries. Users have the flexibility to adjust query intervals, modify existing queries, and introduce new queries as needed.
- Once all the required details are filled, click on
Save and Continue
Metrics Collected
| Name | Description | Data Type |
|---|---|---|
| @timestamp | The event timestamp in string format, likely ISO-8601. | String |
| timestamp | Precise timestamp of the event, with milliseconds precision. | DateTime64 |
| target | The target object or resource being monitored. | String |
| type | The type of metric or event. | String |
| HOSTNAME | The hostname of the Oracle server. | String |
| METRIC_NAME | The name of the metric (e.g., CPU usage, memory usage). | String |
| METRIC_UNIT | The unit of the metric (e.g., percentage, seconds). | String |
| ORACLE_DOC_TYPE | The document type, used for classification. | String |
| VALUE | The numeric value of the metric being measured. | UInt64 |
| name | Name of the monitoring event or system. | LowCardinality(String) |
| bu_id | Business unit identifier. | LowCardinality(String) |
| tenant_id | Tenant identifier in a multi-tenant environment. | LowCardinality(String) |
| host | Hostname or identifier of the Oracle instance. | String |
| DatabaseName | The name of the Oracle database being monitored. | String |
| Owner | The owner of the Oracle schema or object. | String |
| databasename | A secondary or related database name. | String |
| DB_ACCOUNT_CREATED_DATE | The creation date of the Oracle database account. | String |
| DB_ACCOUNT_LOCK_DATE | The lock date for the Oracle database account. | String |
| DB_USER_ACCOUNT_STATUS | Status of the Oracle database account (e.g., ACTIVE, LOCKED). | String |
| DB_USERNAME | The name of the user account. | String |
| INSTANCE_NAME | The name of the Oracle instance. | LowCardinality(String) |
| INSTANCE_NUMBER | Instance number in multi-instance setups (e.g., RAC). | UInt64 |
| OBJ_CREATED_TIME | Time the Oracle object was created. | Float64 |
| OBJ_ID | ID of the Oracle object. | Float64 |
| OBJ_LAST_DDL_TIME | Last DDL operation time on the object. | Float64 |
| OBJ_NAME | The name of the Oracle object (e.g., table, index). | LowCardinality(String) |
| OBJ_OWNER | Owner of the object (e.g., schema owner). | LowCardinality(String) |
| OBJ_STATUS | Status of the object (e.g., VALID, INVALID). | LowCardinality(String) |
| OBJ_TYPE | Type of object (e.g., TABLE, INDEX). | LowCardinality(String) |
| BLOCK_INST_ID | Instance ID where a blocking session occurs. | UInt64 |
| BLOCK_SECONDS_IN_WAIT | Time in seconds the session has been waiting for the lock. | UInt64 |
| BLOCK_SERIAL | Serial number of the blocking session. | UInt64 |
| BLOCK_SQL_ID | SQL ID of the blocking session. | UInt64 |
| BLOCK_WAIT_CLASS | The wait class of the blocking session (e.g., User I/O). | String |
| LONGOPS_OPNAME | Name of the long-running operation. | String |
| LONGOPS_REMAINING_MIN | Estimated remaining time in minutes for the long-running operation. | String |
| LONGOPS_SERIAL | Serial number for the long operation. | Float64 |
| LONGOPS_SID | Session ID of the long operation. | Float32 |
| LONGOPS_START_TIME | Start time of the long operation. | String |
| LONGOPS_TOTAL_WORK | Total amount of work for the long operation. | String |
| LONGOPS_WORK_DONE_SOFAR | Amount of work completed so far for the long operation. | String |
| LOCKED_LOCK_MODE | Mode of the lock (e.g., Exclusive, Shared). | String |
| LOCKED_OBJ_NAME | Name of the locked object. | String |
| LOCKED_OBJ_OWNER | Owner of the locked object. | LowCardinality(String) |
| LOCKED_OS_USERNAME | OS username of the user causing the lock. | LowCardinality(String) |
| LOCKED_SID | Session ID of the locked session. | Float64 |
| LOCKED_USERNAME | Oracle username causing the lock. | LowCardinality(String) |
| SGA_FREE_SPACE | Free space available in the System Global Area (SGA). | Float64 |
| SGA_NAME | Name of the SGA component. | String |
| SGA_TOTAL_SPACE | Total space allocated to SGA. | Float64 |
| SGA_USED_PCT | Percentage of SGA space used. | UInt64 |
| SGA_USED_SPACE | Amount of SGA space used. | Float64 |
| INSTANCE_ACTIVE_STATE | The active state of the Oracle instance (e.g., OPEN, MOUNTED). | LowCardinality(String) |
| INSTANCE_ARCHIVER | Indicates whether the archiver is enabled or disabled. | LowCardinality(String) |
| INSTANCE_DB_STATUS | Current status of the database (e.g., OPEN, MOUNTED). | LowCardinality(String) |
| INSTANCE_HOST_NAME | Hostname of the server where the instance is running. | String |
| INSTANCE_LOGINS | Number of logins to the instance. | LowCardinality(String) |
| INSTANCE_ROLE | Role of the instance (e.g., PRIMARY, STANDBY). | LowCardinality(String) |
| INSTANCE_STARTUP_TIME | Time when the instance was started. | String |
| INSTANCE_STATUS | Status of the instance (e.g., STARTED, STOPPED). | LowCardinality(String) |
| INSTANCE_VERSION | Version of the Oracle instance. | String |
| PLAN_HASH | Hash value of the execution plan. | UInt64 |
| BLOCKING_SESSION | ID of the session that is blocking this session. | Float32 |
| BLOCKING_SESSION_STATUS | Status of the blocking session (e.g., ACTIVE, INACTIVE). | String |
| CPU_TIME | Total CPU time consumed by the session. | Float64 |
| ELAPSED_TIME | Total elapsed time of the session. | UInt64 |
| EVENT_ID | ID of the event being waited on. | LowCardinality(String) |
| EVENT | Name of the event being waited on. | String |
| EXECUTIONS | Number of times the SQL statement has been executed. | UInt32 |
| INST_ID | Instance ID in a RAC (Real Application Clusters) environment. | UInt32 |
| MACHINE | Machine name from which the session is connecting. | String |
| MACHINE_SID | Session ID on the machine. | String |
| PROGRAM | Name of the program connected to the instance. | String |
| SAMPLE_TIME | Time when the sample was taken. | String |
| SESSION_SERIALNUM | Serial number of the session. | UInt32 |
| SESSION_STATE | Current state of the session (e.g., ACTIVE, INACTIVE). | LowCardinality(String) |
| SQL_EXEC_ID | Execution ID for the SQL statement. | UInt32 |
| SQL_EXEC_START | Timestamp when the SQL execution started. | String |
| SQL_ID | Unique identifier for the SQL statement. | String |
| SQL_TYPE | Type of the SQL statement (e.g., SELECT, INSERT). | String |
| TIME_WAITED | Total time waited for events. | UInt32 |
| USER_ID | ID of the user connected to the session. | UInt32 |
| USERNAME | Username of the connected user. | String |
| WAIT_CLASS | Class of wait event. | String |
| WAIT_TIME | Total wait time. | UInt32 |
| USER_SESSION_COUNT | Count of user sessions. | UInt32 |
| USER_SESSION_STATUS | Status of the user session (e.g., ACTIVE, INACTIVE). | LowCardinality(String) |
| USER_SESSION_WAIT_CLASS | Wait class of the user session. | LowCardinality(String) |
| SCHD_ACTUAL_START_DATE | Actual start date of the scheduled job. | String |
| SCHD_ERROR_MSG | Error message associated with the scheduled job. | String |
| SCHD_ERROR_NUM | Error number associated with the scheduled job. | Float32 |
| SCHD_JOBNAME | Name of the scheduled job. | String |
| SCHD_OUTPUT_MSG | Output message from the scheduled job. | String |
| SCHD_OWNER | Owner of the scheduled job. | String |
| SCHD_RUN_DURATION | Duration of the scheduled job run. | Float32 |
| SCHD_STATUS | Status of the scheduled job (e.g., SUCCESS, FAILED). | String |
| AGGREGATE_PGA_AUTO_TARGET | Target memory for Automatic PGA management. | UInt64 |
| AGGREGATE_PGA_TARGET_PARAMETER | Current value of the target PGA memory. | UInt64 |
| PGA_CACHE_HIT_PERCENTAGE | Percentage of hits in the PGA cache. | UInt64 |
| PGA_OVER_ALLOCATION_COUNT | Count of times PGA was over-allocated. | UInt32 |
| TOTAL_FREEABLE_PGA_MEMORY | Total freeable memory in the PGA. | UInt64 |
| TOTAL_PGA_ALLOCATED | Total PGA memory allocated. | UInt64 |
| TOTAL_PGA_INUSE | Total PGA memory currently in use. | UInt64 |
| LONGQUERY_PHV | Long query private hash value. | UInt64 |
| LONGQUERY_CHILD | Child number for long-running queries. | Float32 |
| LONGQUERY_EVENT | Event associated with long-running queries. | String |
| LONGQUERY_ICE | Ice for long queries (wait time, etc.). | Float32 |
| LONGQUERY_MACHINE | Machine name for long queries. | String |
| LONGQUERY_OSUSER | OS username for long queries. | String |
| LONGQUERY_PROGRAM | Program name for long queries. | LowCardinality(String) |
| LONGQUERY_SERIAL | Serial number for long queries. | UInt64 |
| LONGQUERY_SID | Session ID for long queries. | UInt32 |
| LONGQUERY_SQL_ID | SQL ID for long queries. | String |
| LONGQUERY_STATE | State of long queries. | LowCardinality(String) |
| LONGQUERY_STATUS | Status of long queries. | LowCardinality(String) |
| LONGQUERY_USERNAME | Username for long queries. | LowCardinality(String) |
| LONGQUERY_WAIT_CLASS | Wait class for long queries. | LowCardinality(String) |
| FIELDS_ROW_WAIT_OBJID | Object ID for row waits. | Float32 |
| TBLS_CONTENTS_NAME | Name of contents for tables. | LowCardinality(String) |
| TBLS_NAME | Name of the table. | LowCardinality(String) |
| TBLS_SIZE | Size of the table. | LowCardinality(String) |
| TBLS_STATUS | Status of the table (e.g., ACTIVE, INACTIVE). | LowCardinality(String) |
| TBLS_USED_PERCENT | Percentage of space used in the table. | Float32 |
| TBLS_USED_SPACE | Amount of space used by the table. | UInt64 |
| REDO_ARCHIVED | Indicates if redo is archived. | LowCardinality(String) |
| REDO_BYTES_MB | Amount of redo in megabytes. | LowCardinality(String) |
| REDO_GROUP | Group number for redo log. | UInt64 |
| REDO_MEMBER | Member number for redo log. | UInt64 |
| REDO_SEQUENCE | Sequence number for redo log. | UInt32 |
| REDO_STATUS | Status of the redo log. | LowCardinality(String) |
| REDO_THREAD | Thread number for redo log in a RAC environment. | UInt16 |
| RESOURCE_CURRENT_UTILIZATION | Current utilization of the resource. | UInt32 |
| RESOURCE_INITIAL_ALLOCATION | Initial allocation for the resource. | UInt32 |
| RESOURCE_LIMIT | Limit for the resource. | LowCardinality(String) |
| RESOURCE_MAX_UTILIZATION | Maximum utilization of the resource. | UInt32 |
| RESOURCE_NAME | Name of the resource. | LowCardinality(String) |
| RESOURCE_USED_PCT | Percentage of the resource that is used. | Float32 |
