In this post, we will go through the process of monitoring an Oracle 19c database with containerized architecture (CDB) in Oracle Management Cloud.

Prepare Database for monitoring: Setup Credentials

The very first step is to prepare the database for monitoring by setting up monitoring credentials. You can either use the built-in DBSNMP account or, create a custom account for just OMC monitoring. For the purpose of this post, we will create a new user and provide the required privileges.

This can be done easily using the grantPrivileges.sql script provided by Oracle Creating the Oracle Database monitoring credentials for Oracle Management Cloud (Doc ID 2401597.1).

Since we are going to monitor a CDB, we will create a common user.

[oracle@itnoesis-mbc4r1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 16 15:17:42 2022
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> @grantPrivileges.sql C##OMC_MON <STRONG_PASSWORD> Y
...
...
118 Grants given to user C##OMC_MON
SQL> exit;

Switch to each of the PDB that needs to be monitored and grant connect permission.

SQL> show pdbs;

    CON_ID CON_NAME                        OPEN MODE  RESTRICTED
---------- ------------------------------  ---------- ----------
         2 PDB$SEED                        READ ONLY  NO
         3 PDB1                            READ WRITE NO
         4 PPPSBXDB                        READ WRITE NO
SQL> alter session set container="PPPSBXDB";

Session altered.
SQL> grant connect to C##OMC_MON;

Grant succeeded.

In the case of Oracle EBS database, the below additional grants are also required for the OMC monitoring user. Please note that you should avoid using the traditional GRANT SELECT/ REVOKE STATEMENTS on editioned objects within APPS schema as it might result in invalid objects, use AD_ZD utility instead.

alter session set container="EBSPDB";
ALTER SESSION SET CURRENT_SCHEMA=APPS;

EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_OAM_CONTEXT_FILES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_PRODUCT_GROUPS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONC_PROG_ONSITE_INFO','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_PROGRAMS_VL','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_REQUESTS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_APPLICATION_VL','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_QUEUES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_LOOKUPS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_WORKER_REQUESTS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_QUEUES_VL','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_OAM_FNDUSER_VL','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_FORM_SESSIONS_V','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CP_SERVICES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_PROCESSES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_SVC_COMPONENTS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_LOG_MESSAGES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONCURRENT_PROGRAMS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_CONFLICTS_DOMAIN','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_ORACLE_USERID','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_APP_SERVERS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_NODES','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'ICX_SESSIONS','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_USER','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'FND_RESPONSIBILITY','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'WF_DEFERRED','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'WF_NOTIFICATION_IN','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.GRANT_PRIVS('SELECT', 'WF_NOTIFICATION_OUT','C##OMC_MON',X_GRANT_TO_TABLE=>TRUE);
EXEC AD_ZD.grant_privs('EXECUTE', 'FND_PROFILE', 'C##OMC_MON');
EXEC AD_ZD.grant_privs('EXECUTE', 'FND_OAM_EM', 'C##OMC_MON');
GRANT INHERIT PRIVILEGES ON USER C##OMC_MON TO APPS;

Discover the database in OMC

The next step is to discover the database entity in OMC. Login to OMC and navigate to Administration –> Discovery –> Add Entity

Select “Oracle Database System” as Entity Type and select either “Single Instance” or, “Real Application Clusters (RAC)” based in configuration, based on the type of database you need to monitor.

Provide the database connection details on the next screen

Provide ASM credentials and choose appropriate entity attributes. Enabling “Associate Logs” will enable the database for Log Analytics.

Click on Submit. Navigate to Administration –> Discovery –> “Discovery Job Status” to check the progress of database discovery. Once the job finishes successfully, you should be able to see the “Oracle Database System” entity with its associated CDB and PDB entities being monitored!!

2 thoughts on “Oracle Database 19c Monitoring in Oracle Management Cloud

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s