Jasper datasources break when using the Load balanced Form of Oracle Connection String

Products

• Clarity PPM On Premise

Issue/Introduction

A customer implemented the following connection format as they want to leverage the Load Balance feature between multiple oracle hosts.

jdbc:oracle:thin:@(DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=10)(ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=server1.example.com)(PORT=1561)) (ADDRESS=(PROTOCOL=tcp)(HOST=server2.example.com)(PORT=1561))) (CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))

Steps to Reproduce

1. Open Clarity NSA.
2. Navigate to Database section.
3. Check the "Use URL" flag for the database.
4. Use the following connection format.

jdbc:oracle:thin:@(DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=10)(ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=server1.example.com)(PORT=1561)) (ADDRESS=(PROTOCOL=tcp)(HOST=server2.example.com)(PORT=1561))) (CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))
5. Run "admin update jasperParameters". 

 

Actual Result: Clarity and Reports both should be running successfully 

Expected Result: Clarity runs file however reports are failing with database connectivity and below is the errors seen 

Environment

Release : 16.0.3 onwards 

Cause

Jaspersoft is currently unable to connect using the Load balancer configuration 

DE68875

Resolution

This is addressed in 16.1.2

Additional Information

Workaround :- 

• Oracle: 
  • Oracle URL without Encryption - jdbc:oracle:thin:@//<hostname>:<port>/<service_name> 
  • Oracle URL with Encryption - jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=****))(CONNECT_DATA=(SERVICE_NAME=***)(SERVER=dedicated))(SECURITY=(ENCRYPTION_CLIENT=requested)(ENCRYPTION_TYPES_CLIENT=AES256)))
  • Oracle URL with SSL - jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=server.example.com)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)(SERVER=dedicated))(CLARITY_PARAMETERS=(javax.net.ssl.trustStore=/opt/keystore)(javax.net.ssl.trustStorePassword=XXX)))
  • Oracle URL Load Balanced between multiple hosts - jdbc:oracle:thin:@(DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=10)(ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=server1.example.com)(PORT=XXXX)) (ADDRESS=(PROTOCOL=tcp)(HOST=server2.example.com)(PORT=XXXX))) (CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))