JDBC connection with Oracle RDS in aws

, ,
1

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

2

Hello Baharul,

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

Thanks,
Yogesh

3

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!

4

Hi Baharul,

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

ServiceName=;driverType=thin

Thanks,
Yogesh

5

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
6

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 .

7

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.

8

Hello Baharul,

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

Thanks,
Yogesh

9

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