Jasper datasources break when using the Load balanced Form of Oracle Connection String
book
Article ID: 261920
calendar_today
Updated On:
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
- Open Clarity NSA.
- Navigate to Database section.
- Check the "Use URL" flag for the database.
- 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

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)))
Feedback
thumb_up
Yes
thumb_down
No