Configure a database management service for external databases

(gentle music) - Welcome friends to Oracle University. I'm your host, Nicholas. Let's get started. In this module, we're going to review Oracle Cloud Infrastructure Database Management Service and its support for on-premises Oracle Databases on BareMetal, Virtual Machines, and Exadata Cloud Services in OCI. In this lesson we're going to discuss key use cases, database management features, supported deployments, and pricing. Let's start with the summary of the Oracle Cloud Database Management Service. When and who would use it? Oracle Cloud Observability and Management Services, like the Database Management Service, are designed to help customers manage in-cloud native and multi-cloud environments, and add value to existing on-premises based solutions, like Oracle Enterprise Manager for on-premises and hybrid database management. The Database Management Service is a managed service, leverages Oracle's industry-leading performance management methods, uses OCI-native metrics for DevOps, event alarms, and monitoring. Provides a unified user interface to monitor Oracle databases across the Oracle Cloud and other clouds as multi-cloud, on-premises, or hybrid combinations. The Database Management Service supports both on-premises and Oracle Cloud deployments on VM, BM, ExaCS, ExaCC, ADB. Provides real-time SQL monitoring, search SQL or sessions. Compares performance across different periods of times. Has performance HUB with blocking session views and more, and is integrated with other OCI services. Developers and DevOps would use to perform database administration tasks typically for dev and test environments. DBAs would use it to help monitor and manage databases that are being deployed into the cloud supporting production and development activities. And the service provides many benefits like latest Oracle Database support removes risk of unmanaged databases, replace Silo third-party tools, recover hardware and resources, and save money. In-depth diagnostic and SQL metric detail no other vendor can provide. Create once, run across many databases, reduce error, save time. Here are just a few examples use cases of how the Database Management Service might be utilized. And if you're already familiar with managing the Oracle databases on-premise, then you could reuse that knowledge and skills. With Database Management Cloud Service, you get a unified console for on-premises and cloud databases, with lifecycle database management capabilities for monitoring performance management, tuning, and administration. You can use advanced database fleet diagnostics and tuning to troubleshoot issues and optimize performance and optimize SQL with real-time SQL monitoring and simplify database configurations. Now let's Davos-dive into the details of the service, what it consists of, and how it functions. It's a managed cloud-native service, which means it's managed and updated by Oracle and is routinely updated with new features. You can use the service and using the service enables you to Davos perform database management and performance diagnostics for individual databases or altogether. A single consult to perform database management administration and performance management activities for a specific databases to set of databases altogether. Use the unified cloud consult to monitor DB time and average active sessions to evaluate database performance. Monitor IO throughput and bandwidth from a cloud console to proactively detect throughput, bottlenecks, display system storage and user data storage broken down by usage in system table spaces and user data. User data storage is broken down by usage in the top five user table spaces. When you configure the database management service you create a dynamic group and set up the three required OCI object storage permissions the management agent needs. So it can store the results of a query SQL type job into an object storage bucket along with permissions to read buckets and create and inspect objects. Database management supports Oracle database version 11.204 and later. And you can use it to perform database management tasks including monitor the key performance and configuration metrics of your fleet of the Oracle databases. You can also compare and analyze database metrics over a selected period of time. Use performance hub for a single pane of glass view of database performance, which enables you to quickly diagnose performance issues. Use AWR Explorer to visualize historical performance data from AWR snapshots and easy to interpret charts. Group your critical Oracle databases which reside across compartments into a database group and monitor them. Create and schedule SQL jobs to perform administrative operations on a single Oracle database or database group. Use ASH analytics, SQL/Session, Tuning Advisor capabilities to determine database issue root cause and then fix it. Using the database management service you get a unified view of your fleet of databases. Fleet summary enables monitoring of multiple Oracle database services, deployed across OCI compartments or groups from a single screen. The members tab of the database management fleet summary page displays a list view by default and the table option list in a tabular format. In the fleet summary, you can view the status of databases, compare database performance metrics over time, view database current resource usage, like a summary of the fleet CPU utilization and fleet storage utilization. On the resource usage section of the fleet summary page you can obtain a summary of overall CPU, storage allocation and utilization, a change percentage of resource usage between the selected and comparison time period for databases within a compartment and across compartments. Database management buys comprehensive database performance diagnostics and management capabilities to monitor and manage Oracle databases. The performance tab displays a tree map of the performance of your Oracle databases against various database metrics. As an OCI native observability and management service, database management services already integrated with OCI control plane. With access to telemetry natively from OCI object storage, compute, and network, and more, it can utilize OCI monitoring and notification services to notify those in development, DevOps, ITOps roles. Create groups of Oracle databases for monitoring and managing them together, simplifying oversight. Using database groups you can execute a SQL query for the databases in the group. You can view modern obtain insights such as the inventory or the number of Oracle databases across a database compartment or group. Group databases by their purpose. For example, group by container databases, CBDs, and pluggable databases, PBDs, spanning compartments. Then use bulk SQL operations to automate lifecycle operations. You can use database management to monitor a single autonomous database or a fleet of autonomous databases. You can view details such as the database type and version and deployment type, compartment name and ID. Monitor database performance attributes for the time period selected in the time period menu. Last 60 minutes is the default time period. In the visual representations or charts in the summary section provide quick insight into the health of your database. The selected time period enable you to analyze data better. You can also filter the data displayed in the charts by clicking the dimensions displayed in the legend, Performance Hub can be used to analyze and tune the performance of Oracle Cloud infrastructure shared and dedicated autonomous databases and virtual machine BareMetal, Oracle Exadata cloud service and external Oracle databases. With it, you can view real time and historical performance data. Performance hub to analyze and tune the performance of Oracle Cloud infrastructure shared and dedicated autonomous databases and virtual machine, BareMetal, Oracle Exadata cloud services and external Oracle databases. With this tool you can view realtime and historical performance data. The number of tabs displayed in Performance Hub depends on the database management option that is enabled for the database you are monitor. When a database is set to basic management only the performance hub ASH Analytics and SQL Monitor tabs are displayed. When a database is set to full management all the performance hub tabs described on this page are displayed. With real-time SQL monitoring you can perform complex runtime application SQL analysis. Identify and guide optimization of application calls in the database tier. Real-time SQL monitoring provides capabilities to observe and analyze important SQL executions in progress such as parallel and long running queries. Perform detailed and comprehensive execution analysis, visualize query plans interactively and perform real time and historical analysis. The database service offers autonomous and co-managed Oracle database cloud solutions. Autonomous databases are pre-configured fully managed environments that are suitable for either transaction processing or for data warehouse workloads. Co-managed solutions are BareMetal, Virtual Machine and Exadata DB Systems. You can create database homes, databases and pluggable databases at any time by using the console or the database APIs. The ExaCLI Command line Utility allows you to perform monitoring and management functions on Exadata storage servers in a Exadata cloud service instance. AWR is a built-in repository in the Oracle database which collects processes and maintains performance statistics of the database. AWR Explorer in the database management service consists of performance and data visualization tools that display the historical performance data from an AWR snapshots. And easy to interpret charts enables you to visualize AWR data in a single interface, thereby allowing you to analyze performance trends and detect issues. Using AWR Explorer you can explore and analyze AWR data for a managed database. Import AWR data from other databases to a managed database using awrload.sql and analyze the data using AWR Explorer. Generate and download various reports from the database. Using the database management service you can monitor single instance and RAC databases which include container databases or CDBs, EDBs and non-container databases, such as non-CDBs. SQL Tuning Advisor is a mechanism for resolving problems related to sub-optimally performing SQL statements. It takes one or more SQL statements as an input and invokes the automatic tuning optimizer to analyze this statement. The output is in the form of findings and recommendations along with the rationale for each recommendation and its expected benefit. Tuning recommendations include the following. You can choose to accept the recommendations to complete the tuning of the SQL statements. Collection of object statistics, creation of indexes, rewriting SQL statements, creation of SQL profiles, creation of SQL plan baselines, and more. Oracle database administrative privileges is needed to perform tasks in this SQL Tuning Advisor workflow. In addition, the following role and privileges must be assigned. Grant select catalog role, following privileges to admin user. In the user section, view the user create in the Oracle database, and identify the user account status, expiration date and profile. In the user detail section view the system object and consumer group privileges, roles and proxy users. To be able to perform schema management tasks using the database management service, you must have these privileges already assigned. DBA_USERS, DBA_ROLE_PRIVS, DBA_TAB_PRIVS, DBA_SYS_PRIVS, DBA_RSRC_CONSUMER_GROUP_PRIVS, PROXY_USERS, DBA_CONTAINER_DATA. Database Management Service can be used to perform routine database administration tasks, schema upgrades and storage changes on a database or databases. Job automation abilities can be used to automate routine tasks by defined database jobs that can run against a set of databases on a schedule. SQL Scripts can be packaged into templates to run in bulk across databases in a group to automate scheduled database maintenance tasks. You can also create templates to address needs across compartments within a group and apply jobs as templates to run your own SQL scripts against a single database or a group. You can use the database management service to replace an old tool that doesn't support databases in the cloud or hybrid environment, or one that doesn't even support the latest version of Oracle databases. It can be used by Development and DevOps. While DBAs continue to use Oracle Enterprise Manager on-premises. Database management also supports external databases which means databases on your own site or on-premises. For example, Database Management Service could monitor and external Oracle database deployed on an Oracle Exadata databases machine and an external Oracle database deployed by enterprise manager in your own private cloud. Functionality is always subject to change and this is just an example at the time it was published. Monthly, the database management is enhanced with new features and support, so be sure to check the documentation online for the very latest information. Packaging and pricing is always subject to change and this is just an example at the time it was published, so be sure and check online for the very latest information. Because customers have different monitoring and management needs Oracle offers customer choice of services and solutions. Oracle Cloud Observability and Management Services are part of an integrated platform that scales, offers extensibility, and includes years of management experience and the latest technology. Thanks for hanging with me for Oracle Cloud Infrastructure Database Management Service and its support for on-premises Oracle databases. I hope you learned something useful.