DB Vendor Oracle connection error

[cgabriel1304]

Describe the bug

There is an error while connecting to Oracle DB using environment variables, in which the error received states taht the SID is not available. I fixed this issue by editing "/opt/jboss/tools/cli/databases/oracle/change-database.cli", and replacing in line 2 the connection url:
OLD VALUE:
connection-url=jdbc:oracle:thin:@${env.DB_ADDR:oracle}:${env.DB_PORT:1521}:${env.DB_DATABASE:XE}${env.JDBC_PARAMS:}
NEW VALUE:
connection-url=jdbc:oracle:thin:@${env.DB_ADDR:oracle}:${env.DB_PORT:1521}/${env.DB_DATABASE:XE}${env.JDBC_PARAMS:}
CHANGE:
Replace ":" with "/" between DB_PORT and DB_DATABASE.

Version

16.1.1

Expected behavior

Connection to Oracle DB successful - SID identified

Actual behavior

Connection to Oracle DB not successful - SID not identified

How to Reproduce?

Setup environment variables for DB_VENDOR oracle. The Oracle DB version tested on is ORACLE 19c.

Anything else?

No response

[crowne]

The problem is that the Oracle jdbc driver uses varying url formats depending on how the database has been set up.
Two variants of the thin driver use the following formats as described at orafaq:

• jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID> - SID (no longer recommended by Oracle to be used)
• jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

Also see this thread https://keycloak.discourse.group/t/keycloak-docker-container-not-working-with-oracle-db/6201

Older databases may still need to connect using the SID url syntax

[sanasz91mdev]

to connect using the SID url syntax

i am using the correct format

ENV KC_DB_URL=jdbc:oracle:thin:user/pass@//192.168.7.228:1521/QM13

still it gives error.

2022-07-29 07:57:57,188 WARN  [io.agroal.pool] (agroal-11) Datasource '<default>': No suitable driver found for jdbc:oracle:thin:user/pass@//192.168.7.228:1521/QM13
2022-07-29 07:57:57,208 WARN  [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator] (JPA Startup Thread: keycloak-default) HHH000342: Could not obtain connection to query metadata: java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:iris/tpstps@//192.168.7.228:1521/QM13
        at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:191)
        at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:352)
        at io.agroal.pool.ConnectionFactory.createConnection(ConnectionFactory.java:216)
        at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:513)
        at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:494)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at io.agroal.pool.util.PriorityScheduledExecutor.beforeExecute(PriorityScheduledExecutor.java:75)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1126)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)

Here is my Dockerfile

FROM quay.io/keycloak/keycloak:latest as builder

ENV KC_HEALTH_ENABLED=true
ENV KC_METRICS_ENABLED=true
ENV KC_FEATURES=token-exchange,authorization,authentication
ENV KC_DB=oracle
# Install custom providers
RUN curl -sL https://github.com/aerogear/keycloak-metrics-spi/releases/download/2.5.3/keycloak-metrics-spi-2.5.3.jar -o /opt/keycloak/providers/keycloak-metrics-spi-2.5.3.jar
RUN /opt/keycloak/bin/kc.sh build

FROM quay.io/keycloak/keycloak:latest
COPY --from=builder /opt/keycloak/ /opt/keycloak/
WORKDIR /opt/keycloak
# for demonstration purposes only, please make sure to use proper certificates in production instead
RUN keytool -genkeypair -storepass password -storetype PKCS12 -keyalg RSA -keysize 2048 -dname "CN=server" -alias server -ext "SAN:c=DNS:localhost,IP:127.0.0.1" -keystore conf/server.keystore
# change these values to point to a running postgres instance
ENV KC_DB_URL=jdbc:oracle:thin:user/pass@//192.168.7.228:1521/QM13
ENV KC_DB_USERNAME=user
ENV KC_DB_PASSWORD=pass
ENV KC_HOSTNAME=localhost
ENV KEYCLOAK_ADMIN=admin
ENV KEYCLOAK_ADMIN_PASSWORD=admin
ENTRYPOINT ["/opt/keycloak/bin/kc.sh", "start-dev"]

[crowne]

Your jdbc url format looks ok.
The error indicates that the jvm can't find the oracle jdbc class files in the classpath.

[sanasz91mdev]

the jvm can't find the oracle jdbc class files in the classpath.

what should i do to fix it then? any related documentation? i am using : quay.io/keycloak/keycloak:latest image

[crowne]

You should ensure that the oracle jar file is available on the classpath.

[stianst]

With Keycloak 20 the WildFly based distribution is no longer supported. For the newer Quarkus distribution of Keycloak, check out the new documentation, or the updated container sources.