Monitoring Oracle CDB and PDB Databases in Foglight: Requirements, Limitations, and Setup (4277458)

This article explains how to monitor Oracle 12c and higher multitenant databases in Foglight, including both Container Databases (CDBs) and Pluggable Databases (PDBs). It outlines the requirements, known limitations, and steps for successful configuration.

• CDB Monitoring requires a common user that can connect to the CDB$ROOT container.
• PDB Monitoring is supported starting with Oracle cartridge 7.3.2.11, but only when using a local PDB user.
• A known Oracle limitation affects some metrics (e.g., Datafile and Tablespace Fragmentation), which may only return data from CDB$ROOT.
• Attempting to monitor a PDB or CDB with incorrect user configuration may result in errors such as:
  • ORA-65096: invalid common user or role name
  • Insufficient privileges or schema validation failures

1. For CDB Monitoring:

   • Use a common user (e.g., c##foglight) created in the CDB$ROOT container.
   • Ensure the user has the necessary privileges and can connect to CDB$ROOT.

2. For PDB Monitoring (Oracle cartridge 7.3.2.11+):

   • Create a local user directly in the PDB.
   • Grant required privileges (e.g., CREATE SESSION, CONNECT).
   • Connect using the PDB service name
3.  Add the Database in Foglight:
   • Use the Monitor wizard in the Databases dashboard.
   • Provide the correct service name and user credentials.
   • Grant additional privileges if prompted.
4. Troubleshooting:
   • Avoid using the SYSTEM account; use a dedicated user with appropriate roles.
   • Run diagnostic queries to verify the environment.
   • Provide logs and screenshots to Support if issues persist.

Notes:

• Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.
• PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the CREATE SESSION privilege to enable connections.
• The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.
• The error "ORA-65096: invalid common user or role name when running the script on the container" may appear if the common user name is not valid. By default in Oracle the name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.) Further details here.
• To create a Common user like 'foglight' without the prefix, before creating the user run: 
  alter session set "_ORACLE_SCRIPT"=true;
  See in this webpage.

If connection problems continue, Support Team needs the following:

1. Screenshot of the issue with the timestamp.
2. Try using an Oracle account not named SYSTEM that has one of the following privileges:
   • SYSDBA
   • SYSOPER
   • SYSBACKUP
   • SYSDG
   Earlier versions of the Oracle cartridge have shown issues when using the SYSTEM account.
3. Provide the results of the following queries using the SYSTEM account:

   SELECT decode(cdb,'YES',1,0) AS is_pluggable FROM v$database;
   SELECT cdb FROM v$database;
   SELECT sid, con_id FROM V$session WHERE sid = sys_context('USERENV','SID');
   

4. Please try a different user account that is not named SYSTEM for testing purposes,
5. Provide valid logs from the FGLAM support bundle for investigation.