Jasper datasources break when using the Load balanced Form of Oracle Connection String
Article ID: 261920
Updated On: 10-05-2023
Products
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
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)))
Feedback
