How to Enable Database Management for Oracle Cloud Databases

Introduction

Database Management is a cloud service that provides broad capabilities for managing and monitoring your Oracle Databases on-premises and in the cloud. The new release of Database Management service now supports Oracle Databases on Virtual Machine, Bare Metal, and Exadata Cloud Service in OCI without installing the Management Agent.

For databases running on OCI IaaS, ExaCC, on-premises, or other clouds, you need to install a Management Agent and register the database as External Database resource in the Database Management service.

Database Management service provides the following features:

Fleet Monitoring and Management

  • Unified view for monitoring and managing Oracle Databases across on-premises and cloud

Performance Diagnostics

  • Integrated view of database activity for easy performance diagnostics
  • Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) Analytics, SQL monitoring, blocking sessions, and metrics
  • Advanced SQL execution plan analysis

Database Administration

  • Tablespace management
  • Database parameter configuration
  • Scheduled jobs, and more…

This blog post provides you a step-by-step guide to enable Database Management service for Oracle Cloud databases.

The Environment

  • Oracle Database on VM DB Systems in OCI
  • OCI Vault for secrets management

Preparation

Step 1: Set the monitoring user password and privileges in the database

Grant a database user, e.g. SYSTEM or DBSNMP, the required privileges needed for monitoring and managing the database.

SQL> GRANT CREATE PROCEDURE TO dbsnmp;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY, SELECT_CATALOG_ROLE TO dbsnmp;

Grant succeeded.

SQL> GRANT ALTER SYSTEM TO dbsnmp;

Grant succeeded.

SQL> GRANT ADVISOR TO dbsnmp;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO dbsnmp;

Grant succeeded.

The level of password checks in the Database Management service is different from the one in the database. For Database Management service, the user password must be FIPS-compliant following these rules:

  • Password length must be between 14 to 127 characters.
  • Password must have at least one digit, one lowercase, one uppercase, and one special character.

The database verification might have stronger rules at some points, e.g. having at least two digits, two lowercase, two uppercase, and two special character.

For example, AA11__aaaaaaaa complies with both, the FIPS and database verification rules.

SQL> alter user dbsnmp account unlock;

User altered.

SQL> alter user dbsnmp identified by "X1N4_$T@Xp<>0#7n";

User altered.

Step 2: Save the monitoring user password as a Secret in OCI Vault

Search for the Vault service in Identity & Security.

Create a new Vault if needed. Within your Vault, create a key that will be used to encrypt the user password.

Create a secret providing the database user password as Secret Content.

Step 3: Assign IAM permissions to Database Management service

Grant the Database Management service the permission to:

  • Read the database monitoring user password stored in OCI Vault secret
  • Write results of the scheduled jobs for Oracle Cloud Databases to the Object Storage service

Create a new IAM Policy with the following statements:

Allow service dpd to read secret-family in compartment ABC
Allow service dpd to manage objects in compartment ABC

You can restrict these permissions further to specific resources by using the IAM WHERE clause.

Step 4: Assign IAM permissions to your OCI user

To enable and use Database Management, you must belong to your tenancy’s Administrators group or to a group (here named DB-MGMT-ADMIN) that have the following permissions:

Allow group DB-MGMT-ADMIN to manage dbmgmt-family in tenancy
Allow group DB-MGMT-ADMIN to read database-family in tenancy
Allow group DB-MGMT-ADMIN to manage vnics in tenancy
Allow group DB-MGMT-ADMIN to use subnets in tenancy
Allow group DB-MGMT-ADMIN to use network-security-groups in tenancy
Allow group DB-MGMT-ADMIN to use security-lists in tenancy
Allow group DB-MGMT-ADMIN to manage secret-family in compartment ABC
Allow group DB-MGMT-ADMIN to read buckets in compartment ABC

You can restrict these permissions further to specific resources by using the IAM WHERE clause.

Network Configuration

Step 5: Create a Private Endpoint in your VCN

Database Management service resides in an Oracle-managed tenancy and interacts with your Cloud databases via a Private Endpoint that is created within your VCN. The Private Endpoint does not need to be in the same subnet as your Cloud databases but must be on a subnet that allows communication with your Cloud databases.

Search for Administration service in Database Management,

Click on the Private Endpoints link, then Create Private Endpoint. Name your Private Endpoint, choose a compartment, select and VCN, a subnet, and a Network Security Group. Finally, click Create Private Endpoint.

A private IP address will be assigned to your Private Endpoint.

Step 6: Allow network traffic between the Private Endpoint and your Cloud databases

Add the appropriate ingress and egress security rules to your Security Lists or Network Security Groups to enable Database Management service to communicate with your Cloud databases via the Private Endpoint. For RAC databases, use the SCAN IPs for communicating with the Database Management service.

Add stateful security rules as follows:

  • In the Network Security Group of your VM DB System, add the private IP of the Private Endpoint into the Ingress Rules, so your database can receive traffic from the Database Management service.
  • In the Network Security Group of the Private Endpoint, add the private IP of the VM DB System into the Egress Rules, so the Database Management service can send requests to the database.

Enable Database Management

Step 7: Enable Database Management for your Cloud database

When you enable Database Management, you can select the full or basic management option:

  • Full Management: includes all database management features for EE. The Performance Hub features are not available for SE.
  • Basic Management: includes 14 basic monitoring metrics such as CpuUtilization and StorageAllocated. It also includes ASH Analytics and SQL Monitoring features in Performance Hub for CDBs, but not for PDBs.

From the Database Details page, click on the Enable link for Database Management in the Associated Services section.

Enter the database user name DBSNMP, choose the Secret created in step 2, choose the Private Endpoint created in step 5, and click the Enable Database Management button.

If the user password was not FIPS-compliant, enabling will fail. Make sure you create a FIPS-compliant password for your DBSNMP user and save it in the OCI Vault Secret!

Once the enabling process successfully completes, you’ll see the Metrics on the Metric page.

If the metric page is enabled, but not being populated with any data, check the database user privileges needed as described in step 1.

For ASH Analytics, SQL Monitoring, and Blocking Session, click on Performance Hub on the Database Details page.

If your database user is missing any needed privileges, Performance Hub will indicate this in an error message on the upper right side of the page.

Further Information

  • Database Management supports the monitoring of RAC databases.
  • In Data Guard Environments, Database Management can be enabled for both primary and standby databases.
  • You must disable Database Management before being able to terminate the VM DB System.
  • Cross-region monitoring and management of Oracle Databases is not yet available.
  • Full Management is an extra-cost option. Check pricing.

Conclusion

Enabling Database Management service for Oracle Cloud databases on Virtual Machines, Bare Metal, and Exadata Cloud Service is easier than ever. Database Management also allows you to monitor and manage your Oracle Databases on-premises and on other clouds, providing a single view of all your databases in one place. Get started and benefit from comprehensive database performance diagnostics, monitoring, and management capabilities.

Further Reading

Would you like to get notified when the next post is published?

  1 comment for “How to Enable Database Management for Oracle Cloud Databases

Comments are closed.