Oracle Database Servers

Overview

Applications Manager's Database monitoring tool provides out-of-the-box performance metrics and helps you visualize the health and availability of an Oracle Database server farm. Database administrators can login to the web client and visualize the status and Oracle performance metrics.

Creating a new Oracle DB monitor

Supported Versions: Applications Manager supports monitoring of Oracle database servers of versions 9i, 10g, 10.1.3, 11g, 12c, 18c, 19c, 21c & 23ai.

Prerequisites for monitoring Oracle DB metrics: Click here

Using the REST API to add a new Oracle DB monitor: Click here

Follow the given steps to create a Oracle database server monitor:

  1. Click on New Monitor link.
  2. Select Oracle DB Server.
  3. Enter the IP Address or hostname of the host.
  4. Enter the Subnet Mask of the network.
  5. Enter the port number in which the Oracle is running.
  6. Enter the polling interval time in minutes.
  7. If you are adding a new monitor from an Admin Server, select a Managed Server.
  8. Provide the user name of the admin user ('system' is the default username) and its corresponding password.
  9. Provide a valid System Identifier / Host Connection String.
  10. Select the 'SSL is enabled' option if you have enabled SSL authentication for a given port in Oracle.
  11. Choose Discover Pluggable Database (PDB) as 'Yes' to discover and monitor pluggable databases associated with the Oracle DB server (Applicable only for CDB instance of an Oracle Multitenancy-enabled server). To add only the required PDB(s), choose 'No' and add the PDB(s) via Add PDB link available under 'PDB' tab in Oracle DB monitor.
  12. Provide the Socket Read Timeout and Query Execution Timeout values in seconds.
  13. Choose the Monitor Group from the combo box with which you want to associate Oracle database server Monitor (optional). You can choose multiple groups to associate your monitor.
  14. Click Add Monitor(s). This discovers Oracle database server from the network and starts monitoring them.

Monitored Parameters

Go to the Monitors Category View by clicking the Monitors tab. Click on Oracle Server under the Database Servers Table. Displayed is the Oracle servers bulk configuration view in three tabs:

  • Availability tab gives the availability history for the past 24 hours or 30 days.
  • Performance tab gives the health status and events for the past 24 hours or 30 days.
  • List view enables you to perform bulk admin configurations.
Note: To create a new Oracle database monitor, you should have admin privileges. Minimum User Privileges → user with CONNECT and SELECT_CATALOG_ROLE roles.

Click on the monitor name to see all the server details listed under the following tabs:

Note: The server details marked with * symbol is supported in Oracle database servers version 10g and above.

Overview

Note: Metrics marked with ** are the only Database Status metrics that are mapped under Settings → Performance Polling.

Applications Manager allows you to enable/disable data collection of the selected metrics. Once enabled, you can also customize the polling interval of these metrics. To enable metrics monitoring:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • For Oracle DB Links metrics, select Oracle DB Links from the Metric Name drop-down menu. For Average Executions**, select Average Executions for database instance and select Database Size Statistics for Database Size** monitoring.
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
Note: To ignore certain Oracle log alerts, go to Settings → Performance Polling → Database Servers, and enter the necessary errors in the Errors to Ignore for Oracle Alert Log field. By enabling the 'Clear scheduled job run status alerts in the next poll' option, any critical or warning alert that is generated for the Last Run Status attribute will be automatically cleared in the next poll.
 
ParametersTypeDescription
Monitor Information
Name String Name of the Oracle server monitor
Oracle Version String The version of the Oracle Database.
Oracle Start Time String The time when Oracle server was started.
Availability String The status of the database server - available or not available.
Today's Availability
Current Status String Availability history graph with uptime
Connection Statistics
Response Time String The time taken to connect to the database.
Session Activity
Number of Session Users Numeric Graph for the number of users executing an SQL Statement. vs time
Table spaces with least free bytes
Name String The name of the table space.
Free Bytes (MB) Numeric The free space available in bytes.
% of Free Bytes Numeric The percentage of free space available in bytes.
Database Details
Version Numeric Version of the database.
DB Type String Indicates whether the added Oracle DB Sever is a CDB instance or Non-CDB instance.
Database Created Time String Creation time of the database.
Open Mode String Indicates the Open mode of the Instance which can be either Read Write or Read.
FRA Status String Indicates whether Flashback is enabled for the database instance.
Log Mode String The two Log Modes are:
  • NOARCHIVELOG - When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log.
  • ARCHIVELOG - When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log.
DB Role String Current role of the database:
  • LOGICAL STANDBY
  • PHYSICAL STANDBY
  • PRIMARY
Control File type String Type of control file:
  • STANDBY - Indicates that the database is in standby mode
  • CLONE - Indicates a clone database
  • BACKUP | CREATED - Indicates the database is being recovered using a backup or created control file
  • CURRENT - database is available for general use
Switch over status String Indicates whether switchover is allowed:
  • NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases.
  • SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated.
  • SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
  • SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
  • TO PRIMARY - This is a standby database and is allowed to switch over to a primary database.
  • TO STANDBY - This is a primary database and is allowed to switch over to a standby database.
  • RECOVERY NEEDED - This is a standby database that has not received the switchover request.
  • PREPARING SWITCHOVER - Either this is a primary database that is accepting redo data from a logical standby database in preparation for switch over to the logical standby database role, or it is a logical standby database sending redo data to a primary database and other standby databases in preparation for switch over to the primary database role. In the latter case, a completed dictionary has already been sent to the primary database and other standby databases.
  • PREPARING DICTIONARY - This is a logical standby database that is sending redo data to a primary database and other standby databases in the configuration in preparation for switch over to the primary database role.
  • TO LOGICAL STANDBY - This is a primary database that has received a complete dictionary from a logical standby database.
  • FAILED DESTINATION -On a primary database, this status indicates that one or more standby destinations are in an error state.
  • RESOLVABLE GAP - On a primary database, this status indicates that one or more standby databases have a redo gap that can be automatically resolved by fetching the missing redo from the primary database or from another standby database.
  • UNRESOLVABLE GAP - On a primary database, this status indicates that one or more standby databases have a redo gap that cannot be automatically resolved by fetching the missing redo from the primary database or from another standby database.
  • LOG SWITCH GAP - On a primary database, this status indicates that one or more standby databases are missing redo due to a recent log switch.
Protection Mode String Protection mode currently in effect for the database:
  • MAXIMUM PROTECTION - Database is running in maximized protection mode
  • MAXIMUM AVAILABILITY - Database is running in maximized availability mode
  • RESYNCHRONIZATION - Database is running in resynchronization mode
  • MAXIMUM PERFORMANCE - Database is running in maximized protection mode
  • UNPROTECTED - Database is unprotected (this normally occurs when the primary database is mounted and not open)
Open reset logs String Indicates whether the next database open allows or requires the resetlogs option
  • NOT ALLOWED
  • ALLOWED
  • REQUIRED
Guard Status String Protects data from being changed:
  • ALL - Indicates all users other than SYS are prevented from making changes to any data in the database.
  • STANDBY - Indicates all users other than SYS are prevented from making changes to any database object being maintained by logical standby.
  • NONE - Indicates normal security for all data in the database.
Force logging String Indicates whether the database is under force logging mode (YES) or not (NO).
Database Status
Database Size ** Numeric Size of the database in Megabytes.
Free Space ** Numeric Size of free extents in all tablespaces in the database (Size in MB).
Used Space ** Numeric Size of used extents in all tablespaces in the database (Size in MB).
Used Space Percentage ** Numeric Percentage of used extents in all tablespaces in the database.
Free Space Percentage ** Numeric Percentage of free extents in all tablespaces in the database.
Database Block Size Numeric This refers to the lowest possible storage area for an Instance in bytes.
Invalid/Unusable Index Count Numeric Number of Invalid/Unusable indexes in the database.
Average Executions ** Numeric This is the average number of executions that happen during the execution of every SQL Statement.
Reads Numeric Refers to the number of reads from the database.
Writes Numeric Refers to the number of writes to the database.
Database CPU Time Ratio Numeric Ratio of DB CPU time to DB time. Ideal value is greater than 90%.
Data transferred via SQL*Net Numeric Total data sent and received via SQL*Net services in MB.
Current SCN Numeric Current System Change Number (SCN) of the database.
Hit Ratio
Buffer Cache Hit Ratio Numeric The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. It is the percentage of cache hits taking place from the buffer cache.
Data Buffer Hit Ratio Numeric The Data Buffer Hit Ratio Oracle metric is a measure of the effectiveness of the Oracle data block buffer. It is the percentage of logical reads taking place from the data block.
Data Dictionary Numeric The ratio of the data gets to the data misses in the row cache is Data Dictionary Hit Ratio.
Library Numeric The Library cache stores all shared SQL and PL/SQL blocks, along with their parse trees. In OLTP environments where a large numbers of users are entering and exchanging data, there is a great chance for overlapping the parse and execute needs of those different queries. Such an overlap in the library is called a cache hit and the ratio determined to the misses and hits is called Library Cache Hit Ratio.
SGA Details
Buffer Cache Size Numeric The total size of the Buffer Cache given in bytes.
Shared Pool Size Numeric The size of the shared pool given in bytes.
Shared Pool Used Size Numeric Amount of space utilized in the shared pool in percentage.
Redolog Buffer Size Numeric The size of the buffers for the Redo Logs in mb.
Library Cache Size Numeric The size of the Library Cache given in bytes.
Data Dictionary Cache Size Numeric The cache size of the data dictionary or row cache in bytes.
SQL Area Size Numeric The size of the SQL Area for usage of SQL/PL statements in bytes.
Fixed Area Size Numeric The size of the SGA, which is fixed throughout the instance.
Oracle DB Links
DB link name String Name of the database link
Owner String Owner of the database link
User String Name of the user when logging in
Oracle Net connect string String Host Name
Creation time String Creation time of the database link
Invalid Objects
Owner String Owner of the invalid dba object.
Object Name String Name of the invalid object.
Status String Status of the invalid object.
Invalid/Unusable Indexes
Index Name String  Name of the index
Tablespace Name String  Name of the tablespace containing the index
Owner String  Owner of the index
Table Name String  Name of the indexed object
Index Status String Indicates whether a nonpartitioned index is VALID or UNUSABLE. We list all UNUSABLE indexes.
Datafiles Utilization
Maximum Allocated Datafiles Numeric The maximum number of database files that can be opened for this database.
Datafiles Used Numeric Number of datafiles used in the database.
Datafiles Available Numeric Number of datafiles available in the database.
Datafiles Used Percentage Numeric The percentage of the used datafiles in the database.
Datafiles Available Percentage Numeric The percentage of the free datafiles in the database.

 

PDB

ParameterTypeDescription
Pluggable Database (PDB) Details
Name String Name of the pluggable database.
Open Mode String Displays the open mode information like MOUNTED, READ WRITE, READ ONLY.
Database Size Numeric Size of the pluggable database in MB.
Database Block Size Numeric Size of the pluggable database block in MB.

By clicking over the PDB name, you will be redirected to the Oracle PDB monitoring dashboard.

Tablespace

Applications Manager allows you to enable/disable data collection of the Tabespace metrics. Once enabled, you can also customize the polling interval of these metrics. To enable metrics monitoring:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • For metrics of the Tablespace Details and Table Space Status, select TableSpace Statistics from the Metric Name drop-down menu. For metrics of the Performance of Data Files, select Performance of Data Files. For metrics of the Objects Approaching MAX Extents, select Objects Approaching MAX Extents.

    Note: Data collection is scheduled to take place during each poll. Users have the option to change the schedule using the 'Optimize Data Collection' feature. In the event of Objects approaching MAX Extents, the default data collection time is set to 24 hours.

  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Table Space Details
Name String Refers to the name of the Table space.
Allocated Bytes Numeric Refers to the size of the Table space in bytes.
Allocated Blocks Numeric Refers to the number of allocated blocks in Table space.
Data Files Numeric Refers to the number of data files in Table space.
Table Space Status
Name String Refers to the name of the Table space.
Status String Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Free Bytes Numeric Refers to the available free space in bytes.
Free Blocks Numeric Refers to the number of free blocks in Table space.
Reads Numeric Refers to the number of reads from the Table space.
Writes Numeric Refers to the number of writes on the Table space.
Read Time Numeric Time taken for a single read from the Table space.
Write Time Numeric Time taken for a single write on the Table space.
Performance of Data Files
Data File Name String Name and location of the data file.
Table Space Name String Name of the Table space.
Status String If a data file is a part of the system table space, its status is SYSTEM (unless it requires recovery). If a data file in a non-SYSTEM table space is online, its status is ONLINE. If a data file in non-SYSTEM table space is offline, its status can be either OFFLINE OR RECOVER.
AutoExtend String Indicates whether AutoExtend is enabled for this data file or not.
Created Bytes Numeric Size of the Data file in bytes.
Reads Numeric Refers to the number of reads from the Data file.
Writes Numeric Refers to the number of writes to the Data file.
Average Read Time Numeric Refers to the average read time.
Average Write Time Numeric Refers to the average write time.
Objects Approaching MAX Extents
Owner String Owner name of the table space.
TableSpace Name String Name of the TableSpace.
Segment Name String Name of the segment approaching maximum extents
Segment Type String The type of segment approaching maximum extents
Extents Numeric Number of extents allocated to the segment
Max Extents Numeric Maximum allowed extents that can be filled in a segment
Next Extent (MB) Numeric Next extent size in MB

Session

Applications Manager allows you to enable/disable data collection of Session metrics. Once enabled, you can also customize the polling interval of these metrics. To monitor query metrics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • For Session Details and Session Wait metrics, select Session Details from the Metric Name drop-down menu. For the Users metrics, select Users from the Metric Name drop-down
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Session Details
ID String Session Identifier for the connected session.
Status String Current status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped).
Machine String Name of the operating system user.
User Name String Name of the Oracle process user.
Elapsed Time Numeric Time elapsed in seconds after which the user has logged into the oracle server.
CPU Used Numeric CPU centiseconds (divide by 100 to get real CPU seconds) used by this session.
Memory Sorts Numeric Number of memory sorts performed.
Table Scans Numeric Number of table scans performed.
Physical Reads Numeric Physical reads for the session.
Logical Reads Numeric Sum of consistent gets and db block gets.
Commits Numeric Number of commits made by user in a second.
Cursor Numeric Number of cursor currently in use.
Buffer Cache Hit Ratio Numeric Percentage of session logical reads taking place from the buffer (1 - physical reads/session logical reads * 100).
Session Waits
ID String Session Identifier for the connected session.
User Name String Name of the Oracle process user.
Event Numeric Resource or event for which the session is waiting
State Numeric Wait state:
0 - WAITING (the session is currently waiting)
-2 - WAITED UNKNOWN TIME (duration of last wait is unknown)
-1 - WAITED SHORT TIME (last wait <1/100th of a second)
>0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
Wait Time Numeric A non-zero value is the session's last wait time (in milliseconds). A zero value means the session is currently waiting.
Seconds in Wait Numeric If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.
Users
Username String The username assigned to the user.
Expiry Date String The date when the user account expires.
Days to Expiry Numeric The number of days to account expiration.
Account Status String The current status of the user account.
User Profile String The profile name of user.

Rollback & Corrupted blocks

Applications Manager allows you to enable/disable data collection of the metrics of the Blocks Corrupted. Once enabled, you can also customize the polling interval of these metrics. To enable metrics monitoring:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • For the metrics of Blocks Corrupted, select Backup jobs and Blocks Corrupted from the Metric Name drop-down menu.
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).

Note: Data collection is scheduled to take place once every 12 hours. Users can utilize the 'Optimize Data Collection' option to modify the schedule.

ParameterTypeDescription
Rollback Segment
Segment Name String Name of the rollback segment.
Table Space Name String Name of the tablespace containing the rollback segment.
Status String ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Current Size Numeric Current size in bytes of the rollback segment.
Initial Extent Numeric Initial extent size in bytes.
Next Extent Numeric Secondary extent size in bytes.
Min. Extent Numeric Minimum number of extents.
Max. Extent Numeric Maximum number of extents.
Hit Ratio Numeric Ratio of gets to waits. This should be >= 99%.
HWMSize Numeric High Water Mark of rollback segment size.
Shrinks Numeric Number of times rollback segment shrank, eliminating one or more additional extents each time.
Wraps Numeric Number of times rollback segment wraps from one extent to another.
Extend Numeric Number of times rollback segment was extended to have a new extent.
Blocks Corrupted
File Number Numeric Datafile number
Data File Name String Name of the database file.
Block Number Numeric First block of the corrupted range
Number of corrupted blocks Numeric Number of contiguous blocks in the corrupted range
Type of block corruption String Type of block corruption in the datafile:
  • ALL ZERO - Block header on disk contained only zeros. The block
    may be valid if it was never filled and if it is in an Oracle7 file. The
    buffer will be reformatted to the Oracle8 standard for an empty
    block.
  • FRACTURED - Block header looks reasonable, but the front and back
    of the block are different versions.
  • CHECKSUM - optional check value shows that the block is not
    self-consistent. It is impossible to determine exactly why the check
    value fails, but it probably fails because sectors in the middle of the
    block are from different versions.
  • CORRUPT - Block is wrongly identified or is not a data block (for
    example, the data block address is missing)
  • LOGICAL - Specifies the range is for logically corrupt blocks.
    CORRUPTION_CHANGE# will have a nonzero value.
Fast Recovery Area
FRA Name String Current location of the FRA.
FRA Space Limit Numeric The maximum amount of disk space that the database can use for the flash recovery area (in MB).
FRA Space Used Numeric The amount of disk space used by flash recovery area files created in current and all previous flash recovery areas (in MB).
Used Percentage Numeric The percentage of disk space used by flash recovery area files created in current and all previous flash recovery areas.
FRA Space Reclaimable Limit Numeric The amount of flash recovery area that is reclaimable (in MB).
FRA Reclaimable Percentage Numeric The percentage of the amount of flash recovery area that is reclaimable.
FRA Actual Used Percentage Numeric The percentage of the amount of flash recovery area used, including reclaimable space.

SGA

ParameterTypeDescription
SGA Performance: Graph displaying value (in %) of Buffer Cache Hit Ratio, Data Buffer Hit Ratio, Data Dictionary Hit Ratio and Library Hit Ratio vs time
Buffer Cache Hit Ratio Numeric Percentage of cache hits taking place from the buffer cache.
Data Buffer Hit Ratio Numeric When a scan of the buffer cache shows that there are no free buffers, Database Block Writer determines which blocks to be eliminated based on a least recently used algorithm or LRU. Having a block required by a user process in the buffer cache already is called a buffer cache hit or is determined as a ratio. Hits are good because they reduce the amount of disk I/O required for the user process.
Data Dictionary Hit Ratio Numeric The purpose of the row or dictionary cache is to store rows of information from the data dictionary in memory for faster access. The row cache is designed to hold the actual rows of data from objects in data dictionary. While this data is held in the row cache, the users of the database may access that information more quickly than if Oracle had to read the data into memory from disk. The ratio of the data gets to the data misses in the row cache is Data Dictionary Hit Ratio.
Library Hit Ratio Numeric The Library cache stores all shared SQL and PL/SQL blocks, along with their parse trees. In OLTP environments where a large numbers of users are entering and exchanging data, there is a great chance for overlapping the parse and execute needs of those different queries. Such an overlap in the library is called a cache hit and the ratio determined to the misses and hits is called Library Cache Hit Ratio.
SGA Details
Buffer Cache Size Numeric The total size of the Buffer Cache given in bytes.
Shared Pool Size Numeric The size of the shared pool given in bytes.
Shared Pool Used Size Numeric Amount of space utilized in the shared pool in percentage.
Redolog Buffer Size Numeric The size of the buffers for the Redo Logs in bytes.
Library Cache Size Numeric The size of the Library Cache given in bytes.
Data Dictionary Cache Size Numeric The cache size of the data dictionary or row cache in bytes.
SQL Area Size Numeric The size of the SQL Area for usage of SQL/PL statements in bytes.
Fixed Area Size Numeric The size of the SGA, which is fixed throughout the instance.
SGA Status
Buffer Hit Ratio Numeric When a scan of the buffer cache shows that there are no free buffers, Database Block Writer determines which blocks to be eliminated based on a least recently used algorithm or LRU. Having a block required by a user process in the buffer cache already is called a buffer cache hit or is determined as a ratio. Hits are good because they reduce the amount of disk I/O required for the user process.
Data Dictionary Hit Ratio Numeric The purpose of the row or dictionary cache is to store rows of information from the data dictionary in memory for faster access. The row cache is designed to hold the actual rows of data from objects in data dictionary. While this data is held in the row cache, the users of the database may access that information more quickly than if Oracle had to read the data into memory from disk. The ratio of the data gets to the data misses in the row cache is Data Dictionary Hit Ratio.
Library Hit Ratio Numeric The Library cache stores all shared SQL and PL/SQL blocks, along with their parse trees. In OLTP environments where a large numbers of users are entering and exchanging data, there is a great chance for overlapping the parse and execute needs of those different queries. Such an overlap in the library is called a cache hit and the ratio determined to the misses and hits is called Library Cache Hit Ratio.
Free Memory Numeric Refers to the size of the free memory in bytes.

Query

Query attributes are not monitored by default. Applications Manager allows you to enable/disable data collection of query metrics. Once enabled, you can also customize the polling interval of these metrics. To monitor query metrics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Top 10 Queries by Buffer Gets (or) Top 10 Queries by Disk Reads from the Metric Name drop-down menu.
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Buffer Gets - Top 10
Buffer Gets Numeric Number of buffer gets for the child cursor
Executions Numeric Number of executions that took place on the object since it was brought into the library cache
Buffer Gets per Executions Numeric The ratio of buffer gets to execution in the current polling interval
Query String First thousand characters of the SQL text for the current cursor
Disk Reads
Disk Reads Numeric Number of disk reads for this child cursor
Executions Numeric Number of executions that took place on this object since it was brought into the library cache
Disk Reads per Executions Numeric The ratio of disk reads to execution in the current polling interval
Query String First thousand characters of the SQL text for the current cursor

Lock Statistics

Lock statistics are not monitored by default. Applications Manager allows you to enable/disable data collection of Lock statistics. Once enabled ,you can also customize the polling interval of these metrics. To monitor Lock statistics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Lock and Wait Statistics from the Metric Name drop-down menu.
  • Select how you wish to configure the Lock and Wait Statistics:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Sessions under lock
Id Numeric Session identifier of Session holding a lock
Serial  Numeric Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
Machine String Operating system machine name.
PROGRAM String Operating system program name.
Lock Wait  String Address of lock waiting for; null if none.
Sessions waiting for a lock
Waiting Session ID Numeric ID of Session waiting for lock.
Holding Session ID Numeric ID of Session holding lock.
Lock Type String The lock type.
Mode Held  String The mode held.
Mode Requested String The mode requested.
Lock ID1, ID2 String The Lock IDs.
Lock Statistics
Object Name String Name of the locked object
Session Id  Numeric The session ID of a locked object.
Serial    Numeric The session serial number. Used to uniquely identify a session's objects.
Lock Mode Numeric The lock mode in which the session holds the lock.
OS Process ID Numeric The operating system process identifier.
Blocked Session Time Numeric If the session STATUS is currently ACTIVE, then the value represents the elapsed time in minutes since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time in minutes since the session has become inactive.
Time of logon String Login time.
Blocked Sessions
Waiting Session Serial Numeric Serial number of the session waiting for lock.
Waiting Session ID Numeric Session ID of the session waiting for lock.
Blocking Session ID Numeric Session ID of the blocking session.
Waiting Query String The SQL query that waiting for lock.
Blocked Time Numeric If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in minutes) since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in minutes) since the session has become inactive.
Instance ID Numeric ID of the database instance.
OS Process ID Numeric The operating system process ID.
Machine String Name of the machine where the blocking session is running.
Program String Name of the program where the blocking session is running.
LogOn Time String Date and time of logon.

Jobs & Backup

Applications Manager allows you to enable/disable data collection of the metrics of the Blocks Corrupted. Once enabled, you can also customize the polling interval of these metrics. To enable metrics monitoring:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • For metrics of the Blocks Corrupted, select Backup jobs and Blocks Corrupted from the Metric Name drop-down menu. For the metrics of Schedule jobs details and Schedule Jobs Stats select the Oracle Scheduled Jobs metric.
    Note:
    • Data collection for Backup Jobs and Blocks Corrupted is scheduled to occur every 12 hours. Users can utilize the 'Optimize Data Collection' option to modify the schedule.
    • Users can manually clear critical / warning alerts that are created for the Last Run Status attribute by enabling the 'Clear Last Run Status alert ' option present in the actions dropdown of the Schedule Jobs Details table under the Jobs and Backup tab.
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Schedule jobs details
Job Name String Name of the job.
Current State String Current state of the job (DISABLED, RETRY SCHEDULED, SCHEDULED, RUNNING, COMPLETED, BROKEN, FAILED, REMOTE, SUCCEEDED, CHAIN_STALLED)
Last Run Status String Status of the job run.
Last Run Date String Last date on which the job started running.
Last Run Duration  (sec) Numeric Amount of time the job took to complete during the last run (in seconds)
Next Run Date String Next date on which the job is scheduled to run.
Schedule Jobs Stats
Job Name String Name of the job.
Run Count Numeric Number of times the job has run.
Failure Count Numeric Number of times the job has failed to run.
Retry Count Numeric Number of times the job has retried, if it is retrying.
Elapsed Time Numeric Elapsed time since the job was started (in secs).
Running Backup Jobs Count
DB FULL Numeric Number of jobs running of type DB FULL.
RECVR AREA Numeric Number of jobs running of type RECVR AREA.
DB INCR Numeric Number of jobs running of type DB INCR.
DATAFILE FULL Numeric Number of jobs running of type DATAFILE FULL.
DATAFILE INCR Numeric Number of jobs running of type DATAFILE INCR.
ARCHIVELOG Numeric Number of jobs running of type ARCHIVELOG.
CONTROLFILE Numeric Number of jobs running of type CONTROLFILE.
SPFILE Numeric Number of jobs running of type SPFILE.
Backup Jobs Details
Session Key Numeric The Session identifier.
Backup Type String Type of input files backed up in this session. Can have one of the following values:
  • DB FULL
  • RECVR AREA
  • DB INCR
  • DATAFILE FULL
  • DATAFILE INCR
  • ARCHIVELOG
  • CONTROLFILE
  • SPFILE
Last Backup Time String Last backup start time.
Backup Status String Back up status with the value FAILED.
Time Taken Numeric Time Taken to complete last back-up (in seconds).
Backup Size (MB) Numeric Backup size in megabytes.
Backup Jobs History Recent Backups
Backup Type String Type of input files backed up in this session.
Session Key Numeric The Session identifier.
Last Backup Time String Last backup start time.
Backup Status String Back up status with the value FAILED.
Time Taken Numeric Time Taken to complete last back-up (in seconds).
Backup Size (MB) Numeric Backup size in megabytes.

Click the Recent backups link on the top right-hand corner of the Backup Job Details table to view recent backup job details for each backup type.

PGA

ParameterTypeDescription
TOP 10 Process using PGA - Graphical representation of the PGA used by processes.
PGA Stats Per Process (TOP 10)
Process Id Numeric Oracle process identifier.
Program Name String Program in progress.
PGA used (MB) Numeric Amount of PGA memory consumed in megabytes.
PGA Allocated (MB) Numeric Maximum PGA memory allocated at one time since instance startup.
Max PGA Used (MB) Numeric PGA memory currently used by the process.
Freeable PGA (MB) Numeric Allocated PGA memory which can be freed.
PGA Stats
Total PGA used (MB) Numeric Indicates how much PGA memory is currently consumed.
Total PGA allocated (MB) Numeric Current amount of PGA memory allocated by the instance.
Maximum PGA allocated (MB) Numeric Maximum amount of PGA memory consumed .
Total freeable PGA (MB) Numeric Number of bytes of PGA memory in all processes that could be freed back to the operating system.
PGA freed back to OS (MB) Numeric Number of bytes of PGA memory freed back to the operating system.
Cache hit percentage Numeric Percentage of PGA memory component, cumulative since instance startup
Resource being used by processes (%) Numeric Percentage resources currently being used by processes.
Resouce limit for processes
Resource being used by processes Numeric Resources (locks or processes) currently being used.
Free Resource Numeric Percentage of unused resources.

Process

ParametersTypeDescription
Session Details
Process Id Numeric Oracle process identifier.
PGA used (MB) Numeric Indicates how much PGA memory is currently consumed.
PGA Stats Per Process (TOP 10)
Process Id Numeric Oracle process identifier.
Program Name String The program in progress.
PGA used(MB) Numeric Indicates how much PGA memory is currently consumed.
PGA Allocated(MB) Numeric Current amount of PGA memory allocated by the instance.
Max PGA Used(MB) Numeric Maximum amount of PGA memory consumed .
Freeable PGA(MB) Numeric Number of bytes of PGA memory in all processes that could be freed back to the operating system.

ASM

Applications Manager allows you to enable/disable data collection of several metrics. Once enabled ,you can also customize the polling interval of these metrics. Data collection for ASM is not enabled by default and here is how you can enable it:

Enabling ASM Metrics

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select ASM Details from the Metric Name drop-down menu.
  • Select how you wish to configure the metrics:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
  • Click Save.
ParameterTypeDescription
Disk Group Details
Disk Group Number Numeric Cluster-wide number assigned to the disk group.
Name String Name of the disk group.
State String State of the disk group relative to the instance (Connected, Broken, Unknown, Dismounted, Mounted).
Type String Redundancy type for the disk group (EXTERN, NORMAL, HIGH).
Disk Total(GB) Numeric Total capacity of the disk group (in GB).
Disk Free(GB) Numeric Unused capacity of the disk group (in GB).
Disk Used (GB) Numeric Used capacity of the disk group (in GB).
Disk Free Percentage(%) Numeric The percentage of the amount of free disk space.
Disk Used Percentage (%) Numeric The percentage of the amount of disk space used.

RPO

RPO statistics are not monitored by default. Applications Manager allows you to enable/disable data collection of RPO statistics. Once enabled, you can also customize the polling interval of these metrics. To monitor RPO statistics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Log Apply Gap details in Primary Server/Log Apply Lag details in Standby Server from the Metric Name drop-down menu.
  • Select how you wish to configure the Log Apply Gap details:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).

Note: Log Apply Gap details polling occurs in the Primary Server once every 24 hours.

Log Apply Gap Details

For Primary and Physical Standby Databases:

ParameterTypeDescription
Thread Numeric Indicates the thread number.
Last Sequence Received Numeric Shows the last log sequence that was received by the database.
Last Sequence Applied Numeric Shows the last log sequence that was applied to the database.
Log apply gap Numeric Shows the difference between log sequence received and the log sequence applied.

For Logical Standby databases:

ParameterTypeDescription
Type String Type of the log applied.
SCN Numeric The System Change Number (SCN) of the database.
Time String Timestamp of the log that was applied.
Sequence Numeric Sequence number of the log that was applied.

Log Apply Lag Details

ParameterTypeDescription
Log Apply Lag Details (only from standby)
Transport Lag Numeric Transport lag is a measure of the degree to which the transport of redo to the standby database lags behind the generation of redo on the primary database. (no units)
Log Apply Lag Numeric Log Apply lag is a measure of the degree to which the redo data on the standby database lags behind the primary database. (no units)
Apply Finish Time Numeric Estimated time before log apply services will finish applying the redo data available on the standby database. (in minutes)

Archive Log Destination Details

Archive Log Destination Details are not monitored by default. Applications Manager allows you to enable/disable data collection of archive log destination metrics. Once enabled ,you can also customize the polling interval of these metrics. To monitor metrics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Archive Log Destination Monitoring from the Metric Name drop-down menu.
  • Select how you wish to configure the queries:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
ParameterTypeDescription
Archive Log Destination Details (only from primary)
Dest Id Numeric Identifies the log archive destination parameter
Name String Archived log file name.
Status String Status of the archived log.
Type String Type of archival destination database:
  • LOCAL - Local to primary database
  • PHYSICAL - Physical standby
  • CROSS-INSTANCE - An instance of the primary
  • LOGICAL - Logical standby
Destination String Original destination from which the archive log was generated.
Protection Mode String Indicates whether the database is protected:
  • MAXIMUM PROTECTION
  • MAXIMUM AVAILABILITY
  • RESYNCHRONIZATION
  • MAXIMUM PERFORMANCE
  • UNPROTECTED
Transmit mode String Specifies network transmission mode:
  • SYNCHRONOUS
  • PARALLELSYNC
  • ASYNCHRONOUS
Recovery mode String Current mode of media recovery at the archival destination database:
  • IDLE - Managed recovery is not active
  • MANUAL - Manual media recovery active
  • MANAGED - Managed recovery is active
  • MANAGED REAL TIME APPLY - Log apply services recover redo data from standby redo logs at the same time the logs are being written to, as opposed to recovering redo from archived redo logs when a log switch occurs
Archive Log Destination Stats (only from primary)
Dest Id Numeric Log archive destination parameter identifier
Log sequence Numeric Identifies the sequence number of the last archived redo log to be archived
Net timeout Numeric Number of seconds the log writer process will wait for status from the network server of a network operation issued by the log writer process
Fail Sequence Numeric Sequence number of the archived redo log being archived when the last error occurred
Archived Sequence Numeric Identifies the log sequence number of the most recent archived redo log received at the destination
Applied Sequence Numeric Identifies the log sequence number of the most recent applied redo log received at the destination
Delay (Mins) Numeric Identifies the delay interval (in minutes) before the archived redo log is automatically applied to a standby database
Error String Displays the error text

Alert Logs

Alert Logs are not monitored by default. Applications Manager allows you to enable/disable data collection for Lock statistics. Once enabled, you can also customize the polling interval of these metrics. To monitor Alert Log Statistics:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Oracle Alert Log from the Metric Name drop-down menu.
  • Select how you wish to configure the Alert Log Statistics:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type will have the default polling status, until any exclusive customization is performed. You can choose between three different polling settings: Never collect data, Collect data at every poll and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of a particular monitor(s).

Oracle Error List table lists only recent errors. To view history of ORA errors, click View Alert Log history.

Monitored Parameters

ParameterTypeDescription
Oracle ORA Error Count
Critical / Severe / Important / Normal Numeric The number of critical errors, severe errors, important message and normal messages.
Oracle ORA Error List
Record ID Numeric The record number for the message (this value is same as the row number).
Message Level String Level the message belongs to. Lower level values imply higher severity for errors. Possible values include:
  • 1: CRITICAL: critical errors
  • 2: SEVERE: severe errors
  • 8: IMPORTANT: important message
  • 16: NORMAL: normal message
Originating Time String The date and time at which the message was generated.
Host Address String The IP of other network address of the originating host for the mesaage.
Message Text String The fully formed and localized text of the message.

Redo Logs

Applications Manager allows you to enable/disable data collection of the Redo Logs metrics. Once enabled, you can also customize the polling interval of these metrics. To enable metrics monitoring:

  • Go to the Settings tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Polling, click on the Optimize Data Collection tab.
  • Select Oracle from the Monitor Type drop-down menu.
  • Select Redo Logs from the Metric Name drop-down menu.
  • Select how you wish to configure the Redo Logs:
    • By Monitor Type - This will be the default polling status, for any new monitor added. All the monitors of this type, will have the default polling status, until any exclusive customization is done for the monitor. You can choose between three different polling settings: Never collect data, Collect data in every polling and Collect data at customized time interval. Changing the polling status of 'Monitor Type', will also change any configuration done for its 'Monitors'.
    • By Monitors - Use this option to change the polling status of any particular monitor(s).
    ParameterTypeDescription
    REDO PERFORMANCE
    Buffer Allocation Retries Ratio Numeric The ratio of redo buffer allocation retries/redo writes in Percentage. Ideal value is upto 1%.
    Space Request Ratio Numeric The ratio of redo log space requests/redo entries in Percentage.Ideal value is upto 1%.
    Space Wait Ratio Numeric The ratio of redo log space requests/redo writes in Percentage.Ideal value is upto 1%
    REDO LOG DETAILS
    RedoLog Buffer Size Numeric The size of the buffers for the Redo Logs in Mb.
    Log switches in past 1 hour Numeric The average number of log switches in the last hour.
    Current Redo Log Usage Numeric The percentage of redo logs used/filled up.
    REDO LOG GROUPS INFORMATION
    Group Numeric The redo log group identifier.
    Thread Numeric The number of redo log threads. (The redo log file groups of an instance are collectively called a redo log thread.)
    Log Size  (MB) Numeric The size of each log in the redo log groups.
    Member Count Numeric The number of members in the log group.
    Archive Status String Archive status: YES or NO. (Oracle lets you save filled groups of redo log files to one or more offline destinations, known as the archived redo log.)
    Log Status String Log status:
    • UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
    • CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
    • ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
    • CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
    • CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
    • INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
    REDO LOG MEMBERS INFORMATION
    Member Name String The redo log member name.
    Group Numeric The redo log group identifier.
    Member Status String The status of the log member:
    • INVALID - File is inaccessible
    • STALE - File's contents are incomplete
    • DELETED - File is no longer used
    • IN USE - File is in use
    Member Type   String The member status type:
    • ONLINE
    • STANDBY