Oracle Pluggable Database (PDB) Servers


Overview

Multitenancy is the new feature introduced in Oracle 12c and it is the concept of consolidating Pluggable Databases (PDB) into a single database server. Starting in Oracle Database 12c Release 1 (12.1), you must create a database as either a Multitenant Container Database (CDB) or as an Oracle database that is non-CDB. A Pluggable Database (PDB) is a portable collection of Schemas, Schema Objects, and Non-Schema Objects that appears as a separate database with its own instance name.

Monitored Parameters

Prerequisites for monitoring Oracle PDB metrics: Click here

Go to the Monitors Category View by clicking the Monitors tab. Click on Oracle PDB Server under the Database Servers table. Displayed is the Oracle PDB 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.

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

Overview

Parameters Type Description
Database Details
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.
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.
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.
Database Status
Database Size ** Numeric Size of the database in Megabytes.
Average Executions Numeric This is the average number of executions that happen during the execution of every SQL Statement.
Total Reads Numeric Number of reads from the database.
Total Writes Numeric Number of writes to the database.
Database Block Size Numeric Refers to the lowest possible storage area for an Instance (in MB).
Free Space ** Numeric Size of free extents in all tablespaces in the database (in MB).
Invalid/Unusable Index Count Numeric Number of Invalid/Unusable indexes in the database.
Current SCN Numeric Current System Change Number (SCN) of the database.
Data transferred via SQL*Net Numeric Total data sent and received via SQL*Net services (in MB).
Database CPU Time Ratio Numeric Ratio of DB CPU time to DB time. Ideal value is greater than 90%.
Connection Statistics
Response Time Numeric The time taken to connect to the database (in milliseconds).
Session Activity
Number of User Sessions Numeric The number of user sessions running in the database.
Table spaces with least free bytes
Name String The name of the TableSpace.
Free Bytes Numeric The free space available in MB.
% of Free Bytes Numeric The percentage of free space available in MB.
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 using the database link.
Oracle Net Connect String String The Oracle Net Connection String of the database link.
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.

Note: Metrics marked with ** are mapped under Admin → Performance Polling.

Tablespace

Parameter Type Description
TableSpace Details
Name String Name of the Table space.
Allocated Bytes Numeric Allocated size of the TableSpace in MB.
Total Allocated Bytes Numeric Allocated size of the TableSpace in MB.
Used Bytes Numeric Used size of the TableSpace in MB.
Free Bytes Numeric Available free space of the TableSpace in MB.
Percentage of Used Bytes Numeric Used size of the TableSpace in percentage.
Percentage of Free Bytes Numeric Available free space of the TableSpace in percentage.
Allocated Blocks Numeric Number of allocated blocks in TableSpace.
Free Blocks Numeric Number of free blocks in TableSpace.
TableSpace Status
Name String Name of the Table space.
Status String Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Data Files Numeric Number of data files in TableSpace.
Reads Numeric Number of reads from the TableSpace per minute.
Writes Numeric Number of writes on the TableSpace per minute.
Read Time Numeric Time taken for a single read from the TableSpace in centiseconds.
Write Time Numeric Time taken for a single write on the TableSpace in centiseconds.
Performance of Data Files
Data File Name String Name and location of the data file.
TableSpace 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 MB.
Reads Numeric Number of reads from the Data file.
Writes Numeric Number of writes to the Data file.
Average Read Time Numeric The average time taken for read operations to occur in milliseconds.
Average Write Time Numeric The average time taken for write operations to occur in milliseconds.
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 Numeric Size of the Next Extent in MB

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

Session

Parameter Type Description
Session Details
ID Numeric Session Identifier for the connected session.
Serial String Serial number of the connected session.
Status String Current status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped).
Machine String Name of the machine.
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 Amount of time the CPU was in use by the session in centiseconds.
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 Amount of session logical reads taking place from the buffer in percentage.
Sessions Summary
Machine String Name of the machine.
Program String Name of the program.
Session Status String Status of the sessions.
Session Count Numeric Number of sessions available.
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 nonzero 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.

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 Admin tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Data Collection, Click on the Optimize Data Collection tab.
  • Select Oracle PDB from the Monitor Type drop-down menu.
  • Select Top 10 Queries by Buffer Gets 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).
Parameter Type Description
Buffer Gets
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 Admin tab.
  • Click Performance Polling under Discovery and Data Collection.
  • Under Performance Data Collection, Click on the Optimize Data Collection tab.
  • Select Oracle PDB 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).
Parameter Type Description
Sessions holding a 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

Parameter Type Description
Schedule Job 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 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 Job 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 seconds).
Is Enabled String Indicates whether the job is enabled or not.