Oracle database extension for CDB/PDB

[suhanbongo]

Hi,

The Oracle database extension page is mention "While the extension supports connecting directly to a Pluggable Database (PDB), our recommended approach is to connect your monitoring configuration to the Container Database (CDB) and allow the extension to discover all PDBs automatically."

So, where or how can I enable this automatic discovery? During the endpoint configuration, I did not see any option for this.

An Oracle-level user was created with all permissions granted according to the documentation, and it can connect to the CDB, but the PDBs are never listed unless I manually add them as new endpoints.

[p_devulapalli]

Hi @suhanbongo 

In the config, can you please check if you have the multitenancy feature set enabled?

https://docs.dynatrace.com/docs/shortlink/oraclesql-monitoring#multitenancy

[suhanbongo]

Hi,

Yes, it does. In fact, all of the available features are enabled

[p_devulapalli]

In that case I would review the roles granted to the user account used for monitoring and SELECT permissions on views ,  specifically the below 

The monitoring account may need explicit permission over PDBs. For example, you can use this statement:

alter user <your_username> set container_data=all for sys.v_$pdbs container = current;

[suhanbongo]

This is also configured and confirmed by the Oracle administrators.

[TomásSeroteRoos · Accepted solution]

Hi @suhanbongo,

We are working on updating the permission documentation as we realize they are not very clear at the moment. As a workaround for now, could you run

alter user <your_username> set container_data=all container = current;

and see if that works? So similar to the command @p_devulapalli provided above, but without the specific view name

After running the alter statement, you can also try running the following query from the CDB when logged in as the monitoring user

          SELECT 
                CONT.NAME,
                CAST(CONT.OPEN_TIME AS DATE) AS OPEN_TIME_CASTED,
                CONT.OPEN_MODE,
                CONT.RESTRICTED,
                INST.INSTANCE_NAME,
                CDB.CDB_NAME
            FROM
                GV$CONTAINERS CONT
                JOIN GV$INSTANCE INST ON INST.INST_ID = CONT.INST_ID
                CROSS JOIN (SELECT sys_context('USERENV', 'CDB_NAME') as CDB_NAME FROM DUAL) CDB

This is the query we use to detect PDBs, so if it returns all the PDBs, auto-detection should work.

[suhanbongo]

Thank you, that solved the issue

[Peter_Youssef]

Hello @suhanbongo 

1. First of all you should share the official Oracle DB Monitoring requirements through extension with Oracle team.
2. Ensure communication is conducted properly between the AGs and Oracle DBs on Port 1521 or the custom port by the customer.
3. Ensure the below commands are properly configured on all Oracle instances to fetch PDBs automatically after configuring CDBs

SELECT
                CONT.NAME,
                CAST(CONT.OPEN_TIME AS DATE) AS OPEN_TIME_CASTED,
                CONT.OPEN_MODE,
                CONT.RESTRICTED,
                INST.INSTANCE_NAME,
                CDB.CDB_NAME
            FROM
                GV$CONTAINERS CONT
                JOIN GV$INSTANCE INST ON INST.INST_ID = CONT.INST_ID
                CROSS JOIN (SELECT sys_context('USERENV', 'CDB_NAME') as CDB_NAME FROM DUAL) CDB

• alter user <username> set container_data=all for sys.gv_$containers container=current;
• alter user <username> set container_data=all container = current;
• alter user <your_username> set container_data=all for sys.v_$pdbs container = current;

It's tested way of automatically fetching the PDBs after manual configurations of CDBs.

You can save it for future Oracle DB monitoring cases.

KR, 

Peter.