Oracle DB Servers

Note: To create a new Oracle database monitor, you should have admin privileges. Minimum User Privileges -> user with CONNECT and SELECT_CATALOG_ROLE roles

Note:

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

Name

 Name of the Oracle server monitor

Oracle Version

 Refers to the Version of the Oracle Database.

Oracle Start Time

 Refers to the time when Oracle server was started.

Availability

 Refers to the status of the database server - available or not available.

Current Status

Availability history graph with uptime

Connection Time

 Refers to the time taken to connect to the database.

Number of Users

Graph for the number of users executing an SQL Statement. vs time

Name

The name of the table space.

Database Created Time

Creation time of the database.

Open Mode

Indicates the Open mode of the Instance which can be either Read Write or Read.

Log Mode

If the transactions are written on the Log, the Log mode will be ARCHIVELOG, or else, the Log mode will be NOARCHIVELOG.

Current role of the database:

• LOGICAL STANDBY
  
• PHYSICAL STANDBY
  
• PRIMARY

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

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.

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)

Indicates whether the next database open allows or requires the resetlogs option

• NOT ALLOWED
  
• ALLOWED
  
• REQUIRED

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.

Database Size

Size of the database in Megabytes.

Average Executions

This is the average number of executions that happen during the execution of every SQL Statement.

Reads

Refers to the number of reads from the database.

Writes

Refers to the number of writes to the database.

Block Size

This refers to the lowest possible storage area for an Instance in bytes.

Buffer

Creation time of the database.

Buffer Cache Size

The total size of the Buffer Cache given in bytes.

Shared Pool Size

The size of the shared pool given in bytes.

Redolog Buffer Size

The size of the buffers for the Redo Logs in bytes.

Library Cache Size

The size of the Library Cache given in bytes.

Data Dictionary Cache Size

The cache size of the data dictionary or row cache in bytes.

SQL Area Size

The size of the SQL Area for usage of SQL/PL statements (bytes).

Fixed Area Size

The size of the SGA, which is fixed throughout the instance.

DB link name

Name of the database link

Name

Refers to the name of the Table space.

Allocated Bytes

Refers to the size of the Table space in bytes.

Allocated Blocks

Refers to the number of allocated blocks in Table space.

Data Files

Refers to the number of data files in Table space.

Name

Refers to the name of the Table space.

Status

Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

Free Bytes

Refers to the available free space in bytes.

Free Blocks

Refers to the number of free blocks in Table space.

Reads

Refers to the number of reads from the Table space.

Writes

Refers to the number of writes on the Table space.

Read Time

Time taken for a single read from the Table space.

Write Time

Time taken for a single write on the Table space.

Data File Name

Name and location of the data file.

Table Space Name

Name of the Table space.

Status

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.

Created Bytes

Size of the Data file in bytes.

Reads

Refers to the number of reads from the Data file.

Writes

Refers to the number of writes to the Data file.

Average Read Time

Refers to the average read time.

Average Write Time

Refers to the average write time.

Owner

Owner name of the table space.

Note:

Data collection is scheduled to take place once a day; the user can use the 'Optimize datacollection' option to change the schedule.

ID

Session Identifier for the connected session.

Status

Current status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped).

Machine

Name of the operating system user.

User Name

Name of the Oracle process user.

Elapsed Time

Time elapsed in seconds after which the user has logged into the oracle server.

CPU Used

CPU centiseconds (divide by 100 to get real CPU seconds) used by this session.

Memory Sorts

Number of memory sorts performed.

Table Scans

Number of table scans performed.

Physical Reads

Physical reads for the session.

Logical Reads

Sum of consistent gets and db block gets.

Commits

Number of commits made by user in a second.

Cursor

Number of cursor currently in use.

Buffer Cache Hit Ratio

Percentage of session logical reads taking place from the buffer (1-physical reads/session logical reads*100).

ID

Session Identifier for the connected session.

User Name

Name of the Oracle process user.

Event

Resource or event for which the session is waiting

State

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

A nonzero value is the session's last wait time. A zero value means the session is currently waiting.

Seconds in Wait

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.

Segment Name

Name of the rollback segment.

Table Space Name

Name of the tablespace containing the rollback segment.

Status

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

Current size in bytes of the rollback segment.

Initial Extent

Initial extent size in bytes.

Next Extent

Secondary extent size in bytes.

Min. Extent

Minimum number of extents.

Max. Extent

Maximum number of extents.

Hit Ratio

Ratio of gets to waits. This should be >= 99%.

HWMSize

High Water Mark of rollback segment size.

Shrinks

Number of times rollback segment shrank, eliminating one or more additional extents each time.

Wraps

Number of times rollback segment wraps from one extent to another.

Extend

Number of times rollback segment was extended to have a new extent.

File Number

Datafile number

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.

Note:

Data collection is scheduled to take place once every 12 hours; the user can use the 'Optimize datacollection' option to change the schedule.

Buffer Hit Ratio

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

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

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.

Buffer Cache Size

The total size of the Buffer Cache given in bytes.

Shared Pool Size

The size of the shared pool given in bytes.

Redolog Buffer Size

The size of the buffers for the Redo Logs in bytes.

Library Cache Size

The size of the Library Cache given in bytes.

Data Dictionary Cache Size

The cache size of the data dictionary or row cache in bytes.

SQL Area Size

The size of the SQL Area for usage of SQL/PL statements (bytes).

Fixed Area Size

The size of the SGA, which is fixed throughout the instance.

Buffer Hit Ratio

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

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

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

Refers to the size of the free memory in bytes.

Buffer Gets

Number of buffer gets for the child cursor

Executions

Number of executions that took place on the object since it was brought into the library cache

Buffer Gets per Executions

The ratio of buffer gets to execution in the current polling interval

Query

First thousand characters of the SQL text for the current cursor

Disk Reads

Number of disk reads for this child cursor

Executions

Number of executions that took place on this object since it was brought into the library cache

Disk Reads per Executions

The ratio of disk reads to execution in the current polling interval

Query

First thousand characters of the SQL text for the current cursor

Id

Session identifier of Session holding a lock

Serial 

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

Operating system machine name.

PROGRAM

Operating system program name.

Waiting Session ID

ID of Session waiting for lock.  

Holding Session ID

ID of Session holding lock.

Lock Type

The lock type.

Mode Held 

The mode held.

Object Name

Name of the locked object.

Session Id 

Session Id of locked object.

Serial   

Session serial number. Used to uniquely identify a session's objects.

Lock Mode

Mode of lock.

Job name

Name of the job.

Current state of the job (DISABLED, RETRY SCHEDULED, SCHEDULED, RUNNING, COMPLETED, BROKEN, FAILED, REMOTE, SUCCEEDED, CHAIN_STALLED).

Session Key

Session identifier.

Note:

Data collection is performed only for backup jobs whose status is 'FAILED' . Data collection is scheduled to take place once every 12 hours; the user can use the 'Optimize datacollection' option to change the schedule.

Process Id

Oracle process identifier.

Program Name

Program in progress.

PGA used (MB)

Amount of PGA memory consumed in megabytes.

PGA Allocated (MB)

Maximum PGA memory allocated at one time since instance startup.

Max PGA Used (MB)

PGA memory currently used by the process.

Freeable PGA (MB)

Total PGA used (MB)

Indicates how much PGA memory is currently consumed.

Total PGA allocated (MB)

Current amount of PGA memory allocated by the instance.

Maximum PGA allocated (MB)

Maximum amount of PGA memory consumed .

Total freeable PGA (MB)

Number of bytes of PGA memory in all processes that could be freed back to the operating system.

PGA freed back to OS (MB)

Resource being used by processes

Resources (locks or processes) currently being used.

Free Resource

Percentage of unused resources.

Process Id

Oracle process identifier.

PGA used (MB)

Indicates how much PGA memory is currently consumed.

Process Id

Oracle process identifier.

Disk Group Number

Cluster-wide number assigned to the disk group.

Last log sequence created in primary server

Shows the last log sequence that was applied to the primary database

Transport Lag

Amount of redo data generated by the primary database is not available or applicable on the standby database at the time of computation.

Dest Id

Identifies the log archive destination parameter

• LOCAL - Local to primary database

• PHYSICAL - Physical standby

• CROSS-INSTANCE - An instance of the primary

• LOGICAL - Logical standby

• MAXIMUM PROTECTION

• MAXIMUM AVAILABILITY

• RESYNCHRONIZATION

• MAXIMUM PERFORMANCE

• UNPROTECTED

• SYNCHRONOUS

• PARALLELSYNC

• ASYNCHRONOUS

• 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

Dest Id

Log archive destination parameter identifier