JDBC connection with Oracle RDS in aws

Hi All,

I am trying to make JDBC adapter connection to connect with Oracle DB hosted in AWS RDS instance.

Have provided with below Details…

Transaction Type–>LOCAL_TRANSACTION
DataSource Class–>oracle.jdbc.pool.OracleDataSource
Server Name → RDS url
User–> name
Password–>password
Database Name–>database name
Port Number–>1521
Network Protocol–>tcp
Other Properties–>sid=ORCL

But when I am trying to enable the connection getting error as
Error encountered
[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource connection:masterdbLOT.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
Invalid Oracle URL specified: OracleDataSource.makeURL

AWS RDS is working fine as I am able to connect with SQLDeveloper tool .

Can anyone please suggest me about possible reason.

Thanks
Baharul Islam

Hello Baharul,

Please provide “driverType=thin” (based on your driver type) in Other properties. Don’t give SID there.

Thanks,
Yogesh

Thanks Yogesh .

But I have tried passing driverType as well earlier but getting error as

[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource connection:masterdbLOT.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Is the dataSource class will be “oracle.jdbc.pool.OracleDataSource” or something else when connecting with aws RDS Oracle instance…

Thanks!

Hi Baharul,

Yes class is correct. Please try below string in other property.

ServiceName=;driverType=thin

Thanks,
Yogesh

What is the Oracle version?

Few things to note during setting up JDBC connection.

  • Make sure you have suitable JDBC drivers located at IntegrationServer\instances/<instance_name>/packages\WmJDBCAdapter\code\jars

  • Restart IS

  • On JDBC connection page, make sure there are no leading/trailer white spaces

  • Make sure you are able to ping/telnet the DB server from the IS host

  • Try with the below driverType

Oracle JDBC Thin Driver:
driverType=thin

Oracle JDBC OCI Driver version 8i:
driverType=oci8

Oracle JDBC OCI Driver version 9i or higher:
driverType=oci

  • Also, verify if you are able to connect to DB via SQL client eg., SQL developer to make sure the params you pass are intact and correct

Hi Yogesh,

I have tried with service name (ServiceName=ORCL_A;driverType=oci ) but still getting error out…

[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource connection:masterdbLOT.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
Invalid Oracle URL specified: OracleDataSource.makeURL

Hi Mahesh,

I am using Oracle as RDS with (Engine version–>12.1.0.2.v14). Also I have tried with sql developer with SID as well service name and its working fine.Jar file as ojdbc7.jar is placed on WmJDBCAdapter\code\jars .

Strange!

Check the instance name correct by running the sql’s

select value from v$parameter where name=‘service_names’;

select name from V$database;

select sys_context(‘userenv’,‘instance_name’) from dual;

Also, enable the logging for JDBC adapter to trace and provide logs.

Hello Baharul,

Do not give any network protocol, try to remove tcp and test.

Thanks,
Yogesh

Thanks Yogesh And Mahesh . Its working fine now after removing network protocol and passing service name in other properties.