Hi everybody,
actually I'm trying to configure an Oracle Multitenant Database with one CDB and one PDB in Oracle Integration in Elastic in Cloud on Version 8.12. Oracle Integration is version 1.24.3.
First of all my CDB is called ORCLPCON. Normal sqlplus connection from other host (not db server) looks good:
C:\oracle\client21c>sqlplus c##elastic/MyPassWord@172.26.8.155:1521/ORCLPCON
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 28 12:38:49 2024
Version 21.13.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Thu Mar 28 2024 12:26:20 +01:00
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL>
My PDB is ORCL and connection over sqlplus also fine:
C:\oracle\client21c>sqlplus elastic/MyPassWord@172.26.8.155:1521/ORCL
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 28 12:39:50 2024
Version 21.13.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Thu Mar 28 2024 12:37:21 +01:00
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL>
I created user c##elastic/elastic for connection to the databases to have specified user for Elastic connection only with these SELECT grants:
GRANT CREATE SESSION TO ELASTIC;
GRANT SELECT ON SYS.V_$BUFFER_POOL_STATISTICS TO ELASTIC;
GRANT SELECT ON SYS.V_$SESSTAT TO ELASTIC;
GRANT SELECT ON SYS.V_$STATNAME TO ELASTIC;
GRANT SELECT ON SYS.V_$SESSION TO ELASTIC;
GRANT SELECT ON SYS.V_$SYSSTAT TO ELASTIC;
GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ELASTIC;
GRANT SELECT ON SYS.V_$SYSMETRIC TO ELASTIC;
GRANT SELECT ON SYS.DBA_TEMP_FILES TO ELASTIC;
GRANT SELECT ON SYS.DBA_TEMP_FREE_SPACE TO ELASTIC;
GRANT SELECT ON SYS.DBA_DATA_FILES TO ELASTIC;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO ELASTIC;
GRANT SELECT ON SYS.AUD$ TO ELASTIC;
GRANT SELECT ON SYS.V_$RMAN_BACKUP_JOB_DETAILS TO ELASTIC;
GRANT SELECT ON SYS.V_$RMAN_STATUS TO ELASTIC;
GRANT SELECT ON SYS.DBA_SCHEDULER_JOB_RUN_DETAILS TO ELASTIC;
GRANT SELECT ON SYS.DBA_OUTSTANDING_ALERTS TO ELASTIC;
GRANT SELECT ON SYS.DBA_ALERT_HISTORY TO ELASTIC;
GRANT SELECT ON SYS.V_$PGASTAT TO ELASTIC;
GRANT SELECT ON SYS.V_$sgastat TO ELASTIC;
GRANT SELECT ON SYS.gv_$session TO ELASTIC;
GRANT SELECT ON SYS.v_$process TO ELASTIC;
GRANT SELECT ON SYS.dba_jobs TO ELASTIC;
GRANT SELECT ON SYS.V_$SYSTEM_WAIT_CLASS TO ELASTIC;
For other not multitenant Oracle databases these grants works fine.
Not wondering about the usernames with c## or without. Manual created users in CDBs need this hash at beginning. Thats Oracle stuff.
Now I set these two connection strings in Oracle Integration:
CDB ORCLPCON
'user="c##elastic" password="MyPassWord" connectString="172.26.8.155:1521/ORCLPCON" sysdba=false'
PDB ORCL
'user="elastic" password="MyPassWord" connectString="172.26.8.155:1521/ORCL" sysdba=false'
The connection to CDB ORCLPCON works fine, but connection to PDB ORCL throws this in my Logstream
Error fetching data for metricset sql.query: cannot open connection: testing connection: user="" standalone params={authMode:0 connectionClass:<nil> connectionClassLength:0 purity:0 newPassword:<nil> newPasswordLength:0 appContext:<nil> numAppContext:0 externalAuth:1 externalHandle:<nil> pool:<nil> tag:<nil> tagLength:0 matchAnyTag:0 outTag:<nil> outTagLength:0 outTagFound:0 shardingKeyColumns:<nil> numShardingKeyColumns:0 superShardingKeyColumns:<nil> numSuperShardingKeyColumns:0 outNewSession:0}: ORA-12154: TNS:could not resolve the connect identifier specified
Does anybody already work with multitenant databases from Oracle and Elastic? Maybe we neet some extra configuration? Oracle side configuration should be fine because of working sqlplus connection from other host to both databases.
Thanks and best regards,
David
