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

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

  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)))