-
Title
Monitoring Oracle 12c and higher support with common user and cdb$root access -
Description
Unable to monitoring Oracle 12c PDB with common user and cdb$root access
The following error may appear when using trying to use an invalid common user name or role name.
ORA-65096: invalid common user or role name when running the script on the container -
Resolution
The Oracle cartridge for Foglight monitors Oracle 12c and higher (with pluggable databases) only via the CDB$ROOT container and using a common user. The user must connect to the CDB$ROOT container.All the information about the pluggable databases is available via the "Pluggable Databases" dashboard so there is no need to add each PDB separately. By monitoring via common user the CDB users will have a full monitoring for all the PDB's as well.
The following script can be run in SQL using the Foglight agent user to check which databases the account has access to see
SELECT distinct c.con_id,nvl(d.shares,default_share.shares) shares,c.name, op_timestamp created_Time,cloned_from_pdb_name FROM cdb_cdb_RSRC_PLAN_DIRECTIVES d RIGHT OUTER JOIN v$containers c ON pluggable_database=c.name LEFT OUTER JOIN CDB_PDB_HISTORY his ON his.pdb_name=c.name AND operation in ('CLONE','CREATE'), (SELECT shares FROM cdb_cdb_RSRC_PLAN_DIRECTIVES WHERE plan='DEFAULT_CDB_PLAN' AND pluggable_database='ORA$DEFAULT_PDB_DIRECTIVE') default_share WHERE rownumBy 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.) Ref: https://docs.oracle.com/database/121/ADMQS/GUID-DA54EBE5-43EF-4B09-B8CC-FAABA335FBB8.htm
To create a Common user like 'foglight' without the prefix, before creating the user run:
alter session set "_ORACLE_SCRIPT"=true;
Ref: http://www.dba-oracle.com/t_ora_65096_create_user_12c_without_c_prefix.htm