Overview of Oracle Cloud Infrastructure Database Management

OCI Observability and Management

OCI (Oracle Cloud Infrastructure) provides many services including OCI Database Management. The integrated foundational and advanced services work together to accomplish more, faster, in categories of compute, storage, network, and security.

Foundational services include Metric, Log/Logging Analytics, Alarms, Traces, and Ops Insights Repository. As metrics, logs, and traces are popular discussion topics for today’s world, these services are especially significant.

Advanced services cover Application Performance Monitoring, Stack Monitoring, Logging Analytics, Database and OS Management, and Capacity Planning with SQL Insights. These allow you to create and provision databases for your specific use cases.

Application performance monitoring is advantageous for app developers who need to find out about their app’s performance. Beyond that, deploying a stack means you’ll need to know what your users are talking about. Is the user experience great? Is it different than expected? Stack monitoring is essential for user insights.

In terms of database management, it’s important to keep in mind both long-term and short-term goals. What do you do for day-to-day use cases? And what is the capacity for long-term planning and analysis? This blog post will answer those important questions.

OCI Database Management

OCI Database Management allows you to administer databases seamlessly across clouds, on-premise applications, or with an Oracle Cloud Autonomous database XSES, XSEC, or an authorized cloud deployment in AWS.

Key Use Cases

The OCI Database Management service is an on-demand subscription-based cloud service. It utilizes leading database performance diagnostics, combining back-end instrumentation and tools with visualization-driven interfaces.

With OCI Database Management, you can view all your databases, deployed anywhere, on a single pane of glass. The service is cloud native, making it fully managed by Oracle for upgrades, patching, etc. It leverages true cloud elasticity with low operations cost and connects to Oracle Database 11.2.0.4 and above.

Key use cases include:

• Monitoring and administering databases
• Obtaining real-time performance diagnostics and administration capabilities
• Managing databases deployed on multi-cloud or on-premise

You can leverage one console for monitoring and management from anywhere. The service is simple to adopt and use with a fully managed cloud service.

Fleet-wide administration empowers greater operational efficiency. You can quickly troubleshoot any issues with DB diagnostics through the unified database diagnostics and tuning. This includes optimized SQL with real-time SQL monitoring and optimized database performance.

OCI Database Management simplifies database administration, offering capabilities to schedule SQL jobs, manage database parameters, and manage tablespaces from a single service.

OCI Database Management features out-of-the-box performance dashboards to quickly monitor single and fleet of databases.

There is an integrated view of database activity for easy performance diagnostics, including ASH analytics, SQL/Session details, blocking sessions, and SQL Tuning Advisor. Additional features include advanced execution plan analysis for monitoring and optimization, and database administration for tablespace management, database parameter configuration, user management, TCPS support and backup management, and more.

Monitoring and Management

In terms of monitoring and management, OCI Database Management offers a unified NOC-style view of the entire Oracle Database fleet for Cloud and On-Premises. There’s native OCI telemetry for DevOps events and monitoring, as well as fleet-style management with SQL job execution. Capabilities also include enabling Database Groups for cross-compartment fleets.

Autonomous Database Monitoring

On the Fleet Summary page, you can monitor multiple databases including autonomous, Exadata Cloud, DBS, and on-premises. Each autonomous database has drill down capabilities so you can monitor:

• Activity class (Average Active Sessions)
• CPU utilization
• Storage usage
• Number of queued and running SQL statements

Performance Hub

From the performance hub of OCI Database Management, you have access to integrated system-wide and session-specific views of database activity. These include ASH analytics, SQL details, blocking sessions, and guided problem resolutions. The single view of database performance allows you to seamlessly switch between ASH analytics, workload view, ADDM findings, and SQL monitoring.

Performance results are available for real-time and historical mode, the latter of which offers a view of SQL monitoring reports.

Real-Time SQL Monitoring

Real-time SQL monitoring continues to receive enhancements from Oracle. It offers in-depth application performance analysis, identifying poorly written SQL statements and guiding optimization of application calls in the data tier. It also captures fine-grained SQL statistics at each step of the execution plan, includes interactive visualization, and can analyze current and historical SQL statements.

Top Activity Lite

A brand-new feature in OCI Database Management, Top Activity Lite offers a Network Operations Center (NOC) centric view of database performance. It is a single-page view of all the critical information required for diagnostics and includes the following features:

• Simplified version of Performance hub
• Optimized for quick response under heavy loads
• Displays last-hour activity efficiently, even under heavy load
• Auto refresh provides the latest snapshot of opted preference: Off, 15, 30, and 60 seconds
• Integrated ASH analytics and SQL monitoring tabs
• Time viewport/Time slider function supports 5-minute fixed span of database activity

Exadata Monitoring

On the Exadata tab, you’re able to analyze outliers that affect your database performance—including finding a slow disk component that is affecting the system—and performance characteristics of multiple databases deployed in an Exadata system. You can also identify a high I/O load and classify the load as backup, rebalance, user I/O, or other. Furthermore, you can identify CPU bound cells to determine cell offload and configurations (such as versions and disk status).

AWR Explorer

AWR Explorer visualizes AWR data for advanced diagnostics. It is useful for enabling DBAs to perform advanced performance analysis and integrating Oracle performance tools with data visualization. The tool provides different aspects of Oracle database performance data, which can be very helpful in issue detection. You can visualization performance trends easily, without needing to toggle between hourly AWR reports, and you can generate any of the following reports: AWR, ASH, Perfhub, SQL, and more.

Oracle Real Application Clusters (RAC) Monitoring

With Oracle RAC Monitoring, you have access to a RAC-aware fleet dashboard and RAC-centric database summary page. From the dashboard, you can identify databases contending for CPU and memory. Through the database summary page, you’ll be able to view database instance specific details such as DB Time, Average Active Sessions, IO Throughput and Bandwidth, and Memory Components. You can also see cluster cache activity to monitor interconnect traffic and RAC cluster wait events.

SQL Tuning Advisor

The SQL Tuning Advisor gives suggestions on the various problems identified during the diagnosis phase. It uses the same CBO but has more time to perform comprehensive analysis. Identifying alternate execution plans using real-time and historical performance data, the SQL Tuning Advisor can be very advantageous. It even recommends a parallel profile that could significantly improve SQL performance.

The comprehensive SQL tuning recommendations can do the following:

• Gather missing or stale statistics
• Create a SQL profile
• Add missing access structures
• Modify SQL constructs
• Adopt alternative execution plan

Schema Management

From a schema management perspective, it’s very important to understand how many users were created in the Oracle Database and the user account status, expiration date, and profile. You can access this, as well as user details including system, object, consumer group privileges, roles, and proxy user. To view the Users section and User Details page, the applicable privileges must be assigned.

Tablespace Monitoring & Management

Tablespace Monitoring allows basic tablespace administration and visibility into usage and allocation. You’ll gain insight into advanced configuration information such as encryption, compression, Big File, and more. User access is allowed via OCI, CLI, and API.

Each of the following are Tablespace Management tasks:

• Create, modify, and drop tablespaces
• Support for OMF and ASM
• Support for Big Files
• Set auto extend for datafiles
• Set default tablespace
• Show SQL feature to extract SQL for various actions

Alert Logs

Alert logs are equipped to deliver real-time diagnostics. There is support for Oracle Database 12.2 and above, with support for regular expression to search for various levels of database errors and events. You can filter by levels of error (Critical, Normal, Important) and related messages or time stamps.

The Attention Log for critical events has support for Oracle Database 21c and above with support for regular expression to search for various levels of database errors and events. You can filter by levels of error, just as with alert logs, and quickly access information about critical events that need attention.

Monitor and Analyze Optimizer Statistics

It’s very important to view optimizer statistics related to data. When monitoring optimizer statistics, you can access status views of Automatic, High-Frequency, and Manual Statistics jobs by completion, failed, detailed drill by objects, and more. The Assuring Quality of Optimizer Statistics analyzes advisor tasks to examine poor and missing statistics to generate recommendations. You can also implement optimizer statistics advisor recommendations.

Preferred Credentials

OCI Database Management allows you to set preferred credentials, simplify access, and perform specific database tasks through the following tools:

• Basic Monitoring – Automatically set for the monitoring user when database management is enabled
• Advanced Diagnostics – Used to automatically apply diagnostic features and for read operations in the managed database
• Administration – Used to autofill database credentials for perform the write operations in the managed database

OCI Database Administration includes basic administration tasks, database storage management, and job automation.

In terms of basic administration, you can use database configuration parameters, group and manage databases by purpose, and view the users created in the database alongside information such as user account status, expiration date, and profile.

Managing database storage includes tablespaces and data files.

Job automation allows you to apply jobs as template to run your own SWL scripts against a single database or a group of them. Developers can create template to perform work across compartments within a group.

OCI Database Management Service vs. Oracle Enterprise Manager

The question arises, “What is the difference between OCI Database Management Service and Enterprise Manager?” The key difference is that OCI Database Management is continuously receiving new features. Eventually, the features will also be added to Oracle Enterprise Manager. Essentially, you have the choice and flexibility to pick the deployment model that works for you. Would you prefer a customer-managed or a managed service?

While developers and DevOps don’t need all of the functionality that Enterprise Manager has to offer, these users are often called upon to perform database management tasks for Oracle databases. OCI Database Management Service, the on-demand managed cloud service, can be used to replace or fill the gaps left by third party tools that can’t support the latest versions of Oracle databases, require multitenant abilities (PDBs and CDBs), or pose a security risk.

OCI Database Management Pricing

Below is a rundown of the pricing for OCI database management:

Demo of OCI Database Management

To view a demo of the features discussed in this post, check out the presentation that was given by Oracle’s Sriram Vrinda, as a part of the Quest Oracle Community’s Database and Technology Week. Start the video at 38:43.