Get Started With OCI Database Management

OCI Observability and Management

OCI (Oracle Cloud Infrastructure) provides integrated foundational and advanced services that 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 article will answer those important questions.

OCI Database Management

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

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 and higher.

Key use cases include the following:

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

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. It features out-of-the-box performance dashboards to quickly monitor single and fleets of databases.

There is an integrated view of database activity for easy performance diagnostics, including Active Session History (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 a network operations center (NOC)-style view of the entire Oracle Database fleet for cloud and on-prem.

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-prem.

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 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

Top Activity Lite, a brand-new feature in OCI Database Management, offers a 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

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.

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 higher 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.

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 to 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 templates to run your own SWL scripts against a single database or a group of them. Developers can create templates to perform work across compartments within a group.

OCI Database Management Service Versus 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.

Demo of OCI Database Management

To view a demo of the features discussed here, 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. If you are interested in more content like this, consider attending Quest’s in-person event, BLUEPRINT 4D.