Monitor and Manage a Specific Managed Database
You can monitor and manage a specific Oracle Database using Database Management Diagnostics & Management, and a database for which Diagnostics & Management is enabled is called a Managed Database.
This section and its topics provide information on the common Diagnostics & Management features available for a single Managed Database, which includes External Databases, Oracle Cloud Databases, and Autonomous Databases.
- For information on the Diagnostics & Management features available for the primary and standby databases that are a part of an Oracle Data Guard configuration, see Monitor Oracle Data Guard.
- For information on the availability of Diagnostics & Management features for various database types and versions, see Diagnostics & Management Feature Support Matrix for Oracle Databases.
The information about a Managed Database in your fleet is displayed on the corresponding Managed database details page. To go to the Managed database details page, you can use one of the following options on the Oracle Database fleet summary page:
- Click the name of the Managed Database in the Members list or table.
- Click the rectangle denoting the Managed Database or RAC instance on the Performance tree map.
On the Managed database details page, you can:
- Set a session credential for that particular session and perform Diagnostics & Management tasks. For information, see Set Session Credentials.
- Click Performance Hub to go to Performance Hub
and analyze and tune the performance of the Managed Database. For information, see
About Performance Hub.
Note
- Performance Hub for Managed Databases only supports the Oracle Database Enterprise Edition and the availability of Performance Hub features depends on the Oracle Database type and version, and requires certain additional privileges. For information on all the conditions that impact the use of Performance Hub for Managed Databases, see OCI: Prerequisite Conditions for Performance Hub (Doc ID 2760305.1) in My Oracle Support.
- Performance Hub for Autonomous Databases can also be
accessed from the Autonomous Database details page, however, the
following advanced features are only available when Database Management is enabled and Performance Hub is accessed
from the Managed database details page in Database Management:
- Top Activity Lite
- Activity Session History report
- Instance-level AWR report
- On-demand Automatic Database Diagnostic Monitor (ADDM) task runs
- SQL tuning
- Click AWR Explorer to visualize historical performance data stored in the AWR, a built-in repository in the Oracle Database. AWR Explorer enables you to explore and analyze AWR data for Managed Databases as well as import and analyze AWR data from other databases. For information, see Use AWR Explorer to Analyze Database Performance.
- Click Dashboards and then click an available option to view the Oracle-defined dashboards that display critical performance metrics. For information, see Oracle-defined Dashboards for Database Management.
- Click Add tags to add tags to the Managed Database. For information, see Working with Resource Tags.
- Click one of the following options to go to the Oracle Cloud Infrastructure Ops Insights service and use its features to obtain
insights into the capacity needs, resource utilization and SQL performance trends of
the Managed Database:
- Capacity Planning: Provides insight into the resource and usage of the database, thereby allowing you to meet both peak and long-term database capacity. For information, see Database Capacity Planning.
- SQL Insights: Provides an important historical archive of detailed SQL performance data, as well as operationally useful insights derived from that data at the SQL statement, database, and fleet levels. For information, see SQL Insights.
When you click the links to access Ops Insights features, the data displayed on the Capacity Planning and SQL Insights pages is in the context of the Managed Database if Ops Insights is enabled for the database. If Ops Insights is not enabled for the database, then a message is displayed with information on the Ops Insights feature and how to get started and the option to enable Ops Insights. Note that for External Databases and Oracle Cloud Databases, the Ops Insights links are only displayed for PDBs and non-CDBs.
- Click Log Explorer to go to the Oracle Cloud Infrastructure Logging Analytics service and view the database logs in Log Explorer. The data displayed in Log Explorer is in the context of the Managed Database, if Logging Analytics is enabled for the database. If Logging Analytics is not enabled for the database, then a message is displayed with information on how to enable it and use its features. For information, see Set Up Database Instance Monitoring.
- View Managed database information, which
includes details such as the database configuration, database type and version, and
deployment type. Additional information is also displayed based on database type and
configuration. For example, for RAC databases, the Instance
status with a See details link is displayed,
which lists the instances that are up and currently in use. In the
Managed database information section, you can:
- Click the More details link to view more details about the database such as Edition and Compartment.
- Monitor the total number of open alarms and the number of alarms
by severity for the Managed Database. Note that the alarms are only
displayed on Database Management Diagnostics &
Management pages, if the OCID of the database is specified using the
resourceId
dimension when creating the alarm. You can click the number of alarms to access the Alarms panel and review the list of open alarms. For information, see Monitor Alarms for Managed Databases.Click Manage alarm definitions to go to the Alarm definitions section and perform tasks such as creating Oracle-recommended alarms and editing alarm values. For information, see Set Up Alarm Definitions.
- For External Databases that are a part of a DB system, click the Database System link to go to the Database System details page. For information on how to use Database Management for External Database Systems, see Database Management for External Database Systems.
- Click the Tags tab to add, view, edit, or remove tags. For information, see Working with Resource Tags.
- Monitor database status and performance attributes in the
Summary section for the time period selected in the
Time period drop-down list. Last 60
min is the default time period, and the visual representations or
charts in the Summary section provide a quick insight into
the status and health of your database during the selected time period and enable
you to analyze data better. You can hover the mouse on the charts to view additional
details; and filter the data displayed in the charts by clicking the dimensions
displayed in the legend.
- Monitoring status timeline: Displays the monitoring status of the database during the selected time period. The monitoring status indicates whether Database Management can collect monitoring metrics for the database. The color of the blocks denotes the monitoring status and the number of blocks denotes the time slots within the selected time period over which status is checked. For example, if the default time period, Last 60 min, is selected, then each block represents a period of two minutes. You can hover the mouse on the timeline to view the history of the database monitoring status with additional details about the potential root cause and the corresponding ORA error, if any.
- Activity class (Avg. Active Sessions): Displays the average active sessions in the selected time period, broken down by CPU, User I/O, and Wait. The maximum CPU threads limit is denoted by a red line.
- Activity: Displays the percentage of CPU utilization, and DB time.
- I/O: Displays the I/O throughput in MBPS and I/O rate in IOPS broken down by Read and Write operations.
- Memory: Displays memory usage in GB, broken down by the memory usage in various components.
- Storage: Displays System storage and User data storage in TB. System storage is broken down by usage in system tablespaces and user data, and User data storage is broken down by usage in the top five user tablespaces. The total storage allocation for the database is denoted by a red line and enables you to monitor storage usage compared to the total allocation.
For CDBs, the Summary section also has a PDBs tab, which lists and provides information on the PDBs in the CDB. By default, PDBs in the same compartment are listed, however, you can select the PDBs from other compartments check box to view PDBs in other compartments.
For RAC databases, the Summary section has an Instances tab, which displays monitoring status and aggregated performance data charts for all the instances in the RAC database. Select an instance in the Scope drop-down list to view performance data charts for that particular instance.
The Summary section is displayed by default on the Managed database details page, however, you can click one of the other options on the left pane under Resources to perform the following tasks:
If you enabled Diagnostics & Management for Autonomous Databases in Database Management or on the Autonomous Database details page prior to September 2024, the monitoring and management options on the left pane under Resources are not listed on the Managed database details page for Autonomous Databases. To enable and use these features for Autonomous Databases, you must provide the connection information in the Edit Database Management panel:
- Go to the Administration Managed databases page in Database Management.
- Click the Actions icon () for the Autonomous Database and click Edit Database Management.
- Alert logs: Monitor the
alert and attention logs generated for the Managed Database in a selected time
period. The Alert logs section has the following tabs:
-
Alert log: An alert log is an XML file that contains a chronological log of error messages and exceptions that occur during database operations.
The Alert log tab displays the 1000 most recent alert log entries generated for the Managed Database along with the level, type, message, and time stamp of the entries. You can use the drop-down lists, fields, and links on this tab to reduce the number of log entries displayed or search for specific log entries:
-
Use the Filter by level and Filter by type drop-down lists to filter the entries by level or type. For example, select Critical in the Filter by level drop-down list and click Search to view all the critical alert log entries.
-
Enter a specific message in the Search field and click Search to search for the alert log entry that contains the message. If you select the Regular expression check box, then you can enter a regular expression such as
^Errors
in the Search field and search for the alert log entry whose message entirely or partially contains the regular expression. -
Visualize the alert log entries by various categories in the following donut charts:
-
Log entries by level: Displays alert log entries categorized by severity levels such as critical, severe, and important. You can click the links in the legend to filter the alert log entries by severity level.
-
Log entries by type: Displays alert log entries categorized by types such as error, warning, and notification. You can click the links in the legend to filter the alert log entries by type.
-
Log entries by ORA error code: Displays alert log entries categorized by ORA error codes such as
ORA-00600
(internal error) andORA-01578
(block corruption error).
-
For each alert log entry, click the icon adjacent to the Level column to view the following information:
-
Message: Message of the alert log entry.
-
File location: Location of the alert log file.
-
Supplemental details: Additional details, if any.
-
-
Attention log: An attention log is a structured, externally modifiable file that contains information about critical and highly visible database events. You can use the attention log to quickly access information about the critical events that require action.
Note
- Attention log is only available for Oracle Database Enterprise Edition version 21c and later.
- Attention log is not available for Autonomous Databases.
As a prerequisite task to viewing Attention log in Database Management, you must log in to the database as a
SYS
user and perform the following steps:create or replace view VW_X$DIAG_ALERT_EXT as select * from X$DIAG_ALERT_EXT; create or replace public synonym VW_X$DIAG_ALERT_EXT for sys.VW_X$DIAG_ALERT_EXT; grant select on VW_X$DIAG_ALERT_EXT to {monitoring user};
The Attention log tab displays the 1000 most recent attention log entries generated for the Managed Database and the urgency, type, message, time stamp, scope, and target user of the attention log entry. You can use the drop-down lists, fields, and links on this tab to reduce the number of log entries displayed or search for specific log entries:
-
Use the Filter by urgency and Filter by type drop-down lists to filter the entries by urgency or type. For example, select Immediate in the Filter by urgency drop-down list and click Search to view all the immediate attention log entries.
-
Enter a specific message in the Search field and click Search to search for the attention log entry that contains the message. If you select the Regular expression check box, then you can enter a regular expression such as
^Errors
in the Search field and search for the attention log entry whose message entirely or partially contains the regular expression. -
Visualize the attention log entries by various categories in the following donut charts and click the links in the legend to filter the attention logs:
-
Log entries by urgency: Displays attention log entries categorized by urgency such as immediate, soon, and deferrable.
-
Log entries by type: Displays attention log entries categorized by types such as error, warning, and notification.
-
For each attention log entry, click the icon adjacent to the Urgency column to view the following information:
-
Message: Message of the attention log entry.
-
Cause: Cause of the attention log entry.
-
Action: Action that can be performed to resolve the issue.
-
File location: Location of the attention log file.
-
Supplemental details: Additional details, if any.
-
- Performance: Use the following features to perform Oracle
Database performance tuning tasks:
- Performance Hub: Use Performance Hub to analyze and tune the performance of the Managed Database. For information, see About Performance Hub.
- AWR Explorer: Explore and analyze AWR data for a Managed Database and use it as a central repository to import, analyze, and compare AWR data from other databases. For information, see Use AWR Explorer to Analyze Database Performance.
- SQL Tuning Advisor: Use SQL Tuning Advisor to analyze SQL statements and obtain recommendations on how to tune SQL statements, along with the rationale and expected benefit. For information, see Analyze SQL with SQL Tuning Advisor.
- SQL tuning sets: Manage SQL tuning sets and perform tasks such as creating and monitoring SQL tuning sets and loading SQL statements into SQL tuning sets. For information, see Manage SQL Tuning Sets.
- SQL plan management: Manage SPM configuration and monitor SQL plan baselines for a Managed Database. For information, see Use SPM to Manage SQL Execution Plans.
- SQL Insights: Use this Ops Insights
feature for detailed SQL performance data, as well as operationally useful
insights derived from that data, for the Managed Database.
Note
For External Databases and Oracle Cloud Databases, the SQL Insights option is only displayed under Resources for PDBs and non-CDBs. For Autonomous Databases, the Full feature set must be enabled in Ops Insights to use SQL Insights.If Ops Insights is enabled for the Managed Database, the SQL Insights section provides an overview of the SQL workload executing in the database. This includes basic information regarding the SQL collected from the database, breakdown of SQL activity by command and module, the comparison of DB time for SQL versus PL/SQL cursors, and top SQL statements that are consuming the most database resources. For more information, see SQL Insights - Database: For database level insights in View SQL Insights.
If Ops Insights is not enabled for the Managed Database, then information regarding the SQL Insights feature and the Ops Insights service is displayed in this section. In addition, you're provided the option of enabling Ops Insights and using its features.
- Optimizer statistics: Monitor the optimizer statistics for a Managed Database, analyze the statistics collection tasks and Optimizer Statistics Advisor tasks, and implement Optimizer Statistics Advisor recommendations. For information, see Monitor and Analyze Optimizer Statistics.
- Management: Use the following features to
perform Oracle Database management tasks:
- Tablespaces: Monitor the tablespaces and datafiles stored in each tablespace in the Managed Database. You can also perform storage management tasks such as creating a tablespace and adding datafiles. For information, see Monitor and Manage Tablespaces and Datafiles.
- Users: View the users
created in the Oracle Database. The Users section
displays the list of users along with information such as user account
status, days left before the account expires and the expiration date, and
profile. You can click the name of a user to go to the User
details page and click the options on the left pane under
Resources to view the roles, system privileges,
consumer group privileges, proxy users and the clients on whose behalf they
can act as proxy, and default container data access associated with the
user.
To view the Users section and the User details page, the following privileges must be assigned:
Grant READ ON <following DB objects> TO <monitoring user>
DBA_USERS
DBA_ROLE_PRIVS
DBA_TAB_PRIVS
DBA_SYS_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
PROXY_USERS
DBA_CONTAINER_DATA
Here's an example:
Grant READ ON DBA_USERS TO DBSNMP
- Database parameters: View and edit the database parameters for the Managed Database. For information, see View and Edit Database Parameters.
- Data Guard: Monitor the performance of
the primary and standby databases that are a part of an Oracle Data Guard
configuration. For information, see Monitor Primary Databases.
Note
The Data Guard option is only displayed under Resources for the primary and standby databases in a Data Guard configuration. - Cluster cache: Monitor cluster cache
metrics to identify processing trends and optimize the performance of your
RAC database.
Note
The Cluster cache option is only displayed under Resources for RAC CDBs and RAC Non-CDBs. This option is not displayed for Autonomous Databases.The charts in the Cluster cache section:
- Display the total elapsed time or latency for a block request. On this chart, the Average GC CR block receive time and Average GC current block receive time (in milliseconds) are displayed and you can click either option in the legend to view the average time it takes to receive data blocks for each block type, current or consistent-read (CR), by instance.
- Display the total aggregated number of blocks received per second by all the instances in the RAC database by way of an interconnect. On this chart, the GC CR block received and GC current block received per second are displayed.
- Display the percentage of logical read operations that retrieved data from the buffer cache of other instances by way of Direct Memory Access and from disk. It's essentially a profile of how much work is performed in the local buffer cache, rather than the portion of remote references and physical reads, which have higher latencies. On this chart, the percentage of Global cache block transfers and Physical reads are displayed.
- Jobs: View the jobs defined for the Managed Database, if any. You can use the options available in the Jobs section to create a job for the Managed Database and monitor job runs and executions. For information, see Create and Monitor Jobs.
- Capacity Planning: Use this Ops Insights
feature for a summary of database CPU, Storage, Memory, and I/O utilization.
Note
For External Databases and Oracle Cloud Databases, the Capacity Planning option is only displayed under Resources for PDBs and non-CDBs. For Autonomous Databases, the Memory and IO charts in the Capacity Planning section do not display data.If Ops Insights is enabled for the Managed Database, the Capacity Planning section provides trends and forecasts for the utilization of resources such as CPU and storage, for the Managed Database. For more information, see Database Capacity Planning.
If Ops Insights is not enabled for the Managed Database, then information regarding the Capacity Planning feature and the Ops Insights service is displayed in this section. In addition, you're provided the option of enabling Ops Insights and using its features.
- Administration: Use the following features to perform Oracle
Database administration tasks:
- Credentials: Set credentials to access, monitor and manage the Managed Database. For information, see Set and Use Credentials.
- Alarm definitions: Create Oracle-recommended alarms and perform other alarm-related tasks in Database Management. For information, see Set Up Alarm Definitions.
- Associated Metric Extensions: View the
Metric Extensions associated with the Managed Database. For information, see
Work with Metric Extensions.
Note
Metric Extensions in Database Management is currently only available for External Databases and any mention of "Managed Databases" in relation to Metric Extensions only refers to External Databases for which Diagnostics & Management is enabled. Also, the Associated Metric Extensions option is only displayed under Resources if Metric Extensions are enabled and deployed on the Managed Database. - Associated database groups: View the Database Groups the Managed Database is a part of, if any. For information, see Create and Use Database Groups.