OCI Observability & Management using Database Management — Part 1

As a database administrators, we manage a fleet of databases composed of Standard and Enterprise Edition databases. We can develop a monitoring framework that includes scripts, tools and utilities to monitor these databases. The scripts running on the database servers provide information about the general health of the database, tablespaces space info, server diskspace information, sending emails with an excerpt of the alert log file, etc.

We can also use Oracle Management Cloud (OMC) to monitor the infrastructure. After deploying the agent on the servers, OMC offers a nice graphical interface with graphs representing CPU and memory consumption, availability of components, etc. OMC also creates alarm if a metric is reaching a pre-defined threshold.

We can also deploy Oracle Enterprise Manager (OEM) to facilitate the use of Tuning and Diagnostic packs that come with Enterprise Edition databases on OCI. Tuning pack helps with features like SQL Tuning Advisor, SQL Profiles, Real-Time SQL Monitoring, etc. Diagnostic pack helps reporting on database performance using Automatic Workload Repository (AWR), Active Session History (ASH), etc. Without this centralized and well-organized GUI console, we could accomplish the same thing using scripts and manual steps but at the cost of more time and effort.

We are always looking for ways to increase efficiency and reduce complexity; we strive to provide better service with less efforts. This is why I would like to explore a new Oracle Cloud Infrastructure service called “Database Management”, which we can find under the “Observability & Management” group of services.

This first blog will cover Database Management’s configuration and enablement. I will write other blogs that will cover in more detail how to efficiently use and leverage this service.

What is OCI Database Management

This is Oracle’s description: “With Database Management Cloud Service, DBAs get a unified console for on-premises and cloud databases with lifecycle database management capabilities for monitoring, performance management, tuning, and administration. Use advanced database fleet diagnostics and tuning to troubleshoot issues and optimize performance. Optimize SQL with real-time SQL monitoring and simplify database configurations.”

For this exercise, we will configure Database Management on Oracle Cloud Database 19c Enterprise Edition.

Configuration of Database Management

In this example, we have structured our tenancy in a way that every department in the company has its own compartment and a dedicated group of operators. I will then deploy Database Management against a database running in the compartment called “AppsInternal” and provide operators with access to this service.

The initial configuration of this service must be performed by a user part of the administrator group of the tenancy.

1. Creation of the Database Management group for the compartment AppsInternal.

Name: DBMGMT_AppsInternal_Group
Description: Database Management Group associated to the compartment AppsInterna

2. Add the operators to the group.

3. Creation of policies in the root compartment

Policy to allow the service Database Management (dpd) to read secret in the Vault service for the tenancy.

Name: DBMGMT_Service_Policy_Tenancy
Description: Service policy required for Database Management service to read secrets in Vault for the tenancy
Policy Builder: Allow service dpd to read secret-family in tenancy

Policy to provide to the group “DBMGMT_AppsInternal_Group” the required permissions.

Name: DBMGMT_User_Policy_AppsInternal
Description: User policy for Database Management service for compartement AppsInternal
Policy Builder:
Allow group 'DBMGMT_AppsInternal_Group' to manage dbmgmt-family in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage metrics in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage alarms in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage objects in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage buckets in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to use database-family in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to use external-database-family in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to use autonomous-database-family in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage virtual-network-family in tenancy where any {target.compartment.name = 'AppsInternal'}
Allow group 'DBMGMT_AppsInternal_Group' to manage secret-family in tenancy where any {target.compartment.name = 'AppsInternal'}

4. Configuration of the database user that would be used by Database Management

GRANT CREATE PROCEDURE TO DBSNMP;
GRANT SELECT ANY DICTIONARY, SELECT_CATALOG_ROLE TO DBSNMP;
GRANT ALTER SYSTEM TO DBSNMP;
GRANT ADVISOR TO DBSNMP;
GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO DBSNMP;
GRANT CREATE SESSION TO DBSNMP;
ALTER USER DBSNMP ACCOUNT UNLOCK;
ALTER USER DBSNMP IDENTIFIED BY "<password>";

5. Save database password in Vault service

Database Management service leverages the service Vault to store the password in a secret.

Create the vault in the compartment “AppsInternal”

Name: AppsInternal_Vault1

Click on the new vault and create a new Master Encryption Key related to the database system

Create the key in the compartment “AppsInternal”

Protection Mode: Software
Name: AppsInternal_srvdbdev02

Click Secret and create the plain text secret containing the password of the user DBSNMP.

Create the secret in the compartment “AppsInternal”

Name: AppsInternal_srvdbdev02_dbsnmp
Description: Password of the user DBSNMP of the database system srvdbdev02
Secret Type Template: Plain-Text
Secret Contents : password of the user DBSNMP

6. Creation of the Database Management Private Endpoint

The database management private endpoint resides in the database subnet and allows the communication between the database and Database Management service.

You can create only 5 private endpoints for single instance per tenancy.

Create the private endpoint in the compartment “AppsInternal”

Name: AppsInternal_DBMGMT_PrivateEndpoint
Description: Database Management PrivateEndpoint for compartment AppsInternal
Use this private endpoint for RAC databases: uncheck.
Virtual cloud network: select the right VCN
Subnet: select the right subnet
Network Security Group: leave blank for now.

Take note of the IP address of the private endpoint. It will be required for the next step. The range IP address of the database subnet will also be required

7. Creation of the Network Security Group (NSG)

The NSG will allow the communication between the database systems and the Database Management service through the private endpoint.

Name: NetworkSecurityGroup_AppsInternal_DatabaseManagement

Ingress rule definition:

Source CIDR: IP address of the Database Management Private Endpoint
Destination Port: 1521
Description: "Connection from Database Management from any port to any database system on port 1521 in the database subnet"

Egress rule definition:

Destination CIDR: Range IP address of the database subnet
Destination Port: 1521
Description: "Database Management is sending request to the any database system on port 1521 in the database subnet"

8. Assign the new NSG to the database system.

Go to the database system page and assign the new NSG to the database system

9. Assign the new NSG to the Database Management Private Endpoint.

Go to the Database Management Private Endpoint page and assign the new NSG.

10. Enable the Database Management service for the database system.

Go to the database system page. As this service is not configured yet, you can see the Metrics section is empty, with a message saying, “Database Management must be enabled…”.

Also, we don’t have access to the “Performance Hub” page.

Let’s enable Database Management by clicking on “Enable”.

The first section of the wizard will be automatically populated

You will have to provide information for the next section

Database user name: DBSNMP
Database user password secret: select the secret from the vault
Private endpoint: select the Database Management Private Endpoint
Management Options: select Basic Management

You can follow the progress of the request under Work Request menu

After couple of minutes, we see metrics in the Database Details page

Since it’s an Enterprise Edition database, we have now access to the “Performance Hub” page

Database Management is now enabled

The deployment of Database “Basic” Management service is now completed. Basic Management doesn’t allow you to enable Database Management on pluggable database, only the container database. You must switch to Full to enable Database Management on pluggable database.

Keep in mind that Basic Database Management is free of charge, while Full Database is charged based on the number of OCPU of the database system.

Next blogs on Database Management

• Leverage metrics from the Basic Database Management
• Switch to Full Database Management and explore the new metrics
• Leverage the new features that come with Full Database Management