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 supports Oracle Database versions 11g and later.
Data Collection Method
vuSmartMaps collects health and performance data from Oracle server through a data collector deployed within the vuSmartMaps. Logs are collected using the log collection probe of the OmniAgent.
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 database details.
- IP Address: The IP address must be a valid IP address.
- Port: Please enter a valid port number.
- Credential: This field is required.
- DB Name: Please enter the name of the database to connect to.
nan
- Oracle Log Path(s): Enter one or more full log file paths (comma-separated). Each path must be a valid Linux (/path/to/file) or Windows (C:\path\to\file) path, must not end with / or \, and may end with patterns like ., xyz?.log, or xyz*.
- Metrics Collection Queries: These queries are used to collect Oracle server metrics.
- Query: Enter the SQL query to execute for metric collection.
- Polling Interval [seconds]: Defines how frequently Oracle metrics and queries are collected from the database.
- Collection Offset [in seconds]: Specify the delay (in seconds) from the scheduled execution time to avoid simultaneous execution of queries.
- Custom SQL Queries: Optional.
- Query Name: Enter a unique query name (max 63 chars; letters/numbers/_/-).
- Query Topic: This field is optional.
- Encoding: Select the file encoding to correctly read logs that contain international characters. If no encoding is selected, plain (ASCII) will be used by default.
- Max Bytes: The maximum number of bytes that a single log message can have. All bytes after max_bytes are discarded and not sent. This setting is especially useful for multiline log messages, which can get large. The default is 10MB (10485760).
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps Collection/Ingress node IP | IP address of the Oracle Database server | 1521 | TCP | Outbound |
| IP address of the Oracle Database server | vuSmartMaps Collection/Ingress node IP | 443* | TCP | Inbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
Configure Metrics Collection from Oracle Server Metrics from Oracle Database are collected by establishing a JDBC connection to the server.
To get started, create a read-only vunet user with the required access on your Oracle Database server.
Connect to your Oracle Database and execute the following commands to create the vunet user and grant the required permissions:
sqlplus sys/<PASSWORD>@<IPADDRESS>:<PORT>/<SERVICE_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
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 |
| db_name | Database Instance name of Oracle Server | 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 |
| @timestamp | Original timestamp as a string format for logging purposes. | String |
| message | Detailed log message containing event or error information. | String |
| _doc | Document type or identifier used for indexing or classification. | LowCardinality(String) |
| log_file_path | Path to the log file where the log entry was recorded. | String |
| oradb_status | Status indicator of the Oracle database (e.g., Online, Offline, Maintenance). | String |
| category | Log category indicating the type or nature of the log (e.g., Error, Warning, Info). | String |
| priority | Priority level of the log (e.g., High, Medium, Low). | LowCardinality(String) |
| ORACode | Oracle error code (e.g., ORA-12345) associated with the log. | String |
| db_server | Name or identifier of the database server where the log entry was generated. | LowCardinality(String) |
| target | Target component or system referenced in the log. | String |
| host | Hostname or IP address of the server generating the log. | String |
| timestamp | Timestamp in precise DateTime format with millisecond accuracy for better temporal queries. | DateTime64(3) |
| type | Type of the log or component (e.g., Application, Database, Network). | LowCardinality(String) |
| NAME | Name of the ASM diskgroup. | String |
| FREE_MB | Amount of free space in the ASM diskgroup in MB. | UInt64 |
| TOTAL_MB | Total size of the ASM diskgroup in MB. | UInt64 |
| REQUIRED_MIRROR_FREE_MB | The free space required for mirroring in the ASM diskgroup. | UInt64 |
| USABLE_FILE_MB | Usable file space in the ASM diskgroup in MB. | UInt64 |
| PERCENTAGE | Percentage of free space in the ASM diskgroup. | Float64 |
| FREE_BYTES | Free space in the ASM diskgroup in bytes. | UInt64 |
| USED_MB | Amount of used space in the ASM diskgroup in MB. | UInt64 |
| USED_BYTES | Amount of used space in the ASM diskgroup in bytes. | UInt64 |
| TOTAL_BYTES | Total size of the ASM diskgroup in bytes. | UInt64 |
| USABLE_FILE_BYTES | Usable file space in the ASM diskgroup in bytes. | UInt64 |
| STATE | The current state of the ASM diskgroup (e.g., MOUNTED, DISMOUNTED). | String |
| REDUNDANCY_TYPE | The redundancy type of the ASM diskgroup (e.g., EXTERNAL, NORMAL, HIGH). | String |
| QUERY_SID | The session ID of the executing query. | UInt64 |
| QUERY_SERIAL | The serial number of the executing query session. | UInt64 |
| QUERY_USERNAME | The username associated with the executing query. | String |
| QUERY_STATE | The current state of the session (e.g., WAITING, ACTIVE). | String |
| QUERY_STATUS | The status of the session (e.g., OPEN, CLOSED). | String |
| QUERY_EVENT | The event the session is waiting for. | String |
| QUERY_PROGRAM | The program initiating the SQL query execution. | String |
| QUERY_MACHINE | The machine where the session is executing. | String |
| QUERY_OSUSER | The OS username for the session. | String |
| QUERY_SQL_ID | The SQL ID of the executing query. | String |
| QUERY_CHILD | The child number for the executing query. | UInt64 |
| QUERY_WAIT_CLASS | The wait class for the session's event (e.g., CPU, I/O). | String |
| QUERY_PHV | The SQL plan hash value of the executing query. | UInt64 |
| QUERY_SQL_PROFILE | The SQL profile used by the query. | String |
| QUERY_ICE | The total elapsed time in seconds for the query execution. | UInt64 |
| BLOCKING_SESS_ID | The session ID of the blocking session, if applicable. | UInt64 |
| BLOCKING_SESS_STATUS | The status of the blocking session. | String |
| BLOCK_SESS_SEC_IN_WAIT | Time the blocking session has been in wait, in seconds. | UInt64 |
| ROW_WAIT_OBJID | The object ID of the row being waited on. | UInt64 |
| LONGOPS_SQL_TXT | SQL text of the long-running operation | String |
| TOTAL_PGA_USED_PERCENTAGE | Shows the percentage of Oracle Program Global Area (PGA) memory currently being utilized compared to the configured PGA memory limit. | Float64 |
