Does r2dbc connection string support oracle failover ?

[antoba]

I'm using r2dbc in an oracle cluster I need to specify multiple addresses in connection string (oracle jdbc connection string allows this)

Is there an alternative for r2dbc ?

Thank you in advance...

[Michael-A-McMahon]

Hi, @antoba. Oracle R2DBC supports (DESCRIPTION=... style URLs that can include an ADDRESS_LIST element: https://github.com/oracle/oracle-r2dbc#configuring-an-oracle-net-descriptor

Would that work for you, or are you looking for something different?

[dataatma]

Running into the same issue. @Michael-A-McMahon Can you give an example connection string? It does not seem to honor whatever option tried. composeJdbcUrl method in OracleReactiveJdbcAdapter.java supports only one host.

[Michael-A-McMahon]

The r2dbc: URL should look like this: r2dbc:oracle://?oracle.r2dbc.descriptor=(DESCRIPTION=...)

If that doesn't work, if you to share the URL you have, then I can test it. If you share a URL, it would be good to strip out any real values, like a real host name. Just use a place holder instead of real values.

[dataatma]

@Michael-A-McMahon Here is an example that we tried. r2dbc:oracle:thin://?oracle.r2dbc.descriptor=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=a_host)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=b_host)(PORT=1521)))(FAILOVER=on)(LOAD_BALANCE=off)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name))) The error that we get is [org/springframework/boot/autoconfigure/r2dbc/ConnectionFactoryConfigurations$PoolConfiguration$PooledConnectionFactoryConfiguration.class]: Failed to instantiate [io.r2dbc.pool.ConnectionPool]: Factory method 'connectionFactory' threw exception with message: oracle.r2dbc.descriptor Option has been specified with potentially conflicting Options: [Option{name='database', sensitive=false}]

[Michael-A-McMahon]

I think it can work if you remove the ":thin" component at the beginning. Oracle R2DBC is not supposed to recognized that.

But there error message is awful; It provides no indication of what the problem is. At the very least, I will fix that.

[dataatma]

I think we tried that but will give it a try. This is what finally worked however we are sure its just picking one host and there is no way to specify the failover r2dbc:oracle:thin:@tcps://a_host:1521,b_host:1521/service_name

[dataatma]

Confirm that ":thin" has no effect. Same error as before

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [io.r2dbc.pool.ConnectionPool]: Factory method 'connectionFactory' threw exception with message: oracle.r2dbc.descriptor Option has been specified with potentially conflicting Options: [Option{name='database', sensitive=false}]
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:171) ~[spring-beans-6.0.8.jar!/:6.0.8]
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:655) ~[spring-beans-6.0.8.jar!/:6.0.8]
... 69 common frames omitted
Caused by: java.lang.IllegalArgumentException: oracle.r2dbc.descriptor Option has been specified with potentially conflicting Options: [Option{name='database', sensitive=false}]

[Michael-A-McMahon]

Thanks, I'll test it out later today and update.

[Michael-A-McMahon]

I've tried a few various tests, but haven't been able to reproduce the failure. Here's what I've tried:

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import oracle.r2dbc.OracleR2dbcOptions;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import static io.r2dbc.spi.ConnectionFactoryOptions.DRIVER;
import static io.r2dbc.spi.ConnectionFactoryOptions.PASSWORD;
import static io.r2dbc.spi.ConnectionFactoryOptions.PROTOCOL;
import static io.r2dbc.spi.ConnectionFactoryOptions.USER;

public class AddressList {

  public static void main(String[] args) {

    // TODO: Change these to your real host, port, and service name.
    String host = "TODO";
    int port = 1521;
    String serviceName = "TODO";

    String descriptor =
      "(DESCRIPTION=" +
        "(ADDRESS_LIST=" +
        "(ADDRESS=(PROTOCOL=TCP)(HOST=thishostdoesnotexist)(PORT=1521))" +
        "(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=" + port + ")))" +
        "(FAILOVER=on)" +
        "(LOAD_BALANCE=off)" +
        "(CONNECT_DATA=" +
        "(SERVER=DEDICATED)" +
        "(SERVICE_NAME=" + serviceName + ")))";

    // Test a plain Oracle R2DBC URL
    String plainUrl =
      "r2dbc:oracle://?oracle.r2dbc.descriptor=" + descriptor;
    testUrl(plainUrl);

    // Test an R2DBC Pool URL
    String poolUrl =
      "r2dbc:pool:oracle://?oracle.r2dbc.descriptor=" + descriptor;
    testUrl(poolUrl);

    // Test programmatically configured options without R2DBC Pool
    ConnectionFactoryOptions plainOptions =
      ConnectionFactoryOptions.builder()
        .option(DRIVER, "oracle")
        .option(OracleR2dbcOptions.DESCRIPTOR, descriptor)
        .build();
    testOptions(plainOptions);

    // Test programmatically configured options with R2DBC pool
    ConnectionFactoryOptions poolOptions =
      ConnectionFactoryOptions.builder()
        .option(DRIVER, "pool")
        .option(PROTOCOL, "oracle")
        .option(OracleR2dbcOptions.DESCRIPTOR, descriptor)
        .build();
    testOptions(poolOptions);

  }

  static void testUrl(String url) {
    System.out.println("Testing: " + url);

    ConnectionFactoryOptions parsedOptions =
      ConnectionFactoryOptions.parse(url);
    testOptions(parsedOptions);

    System.out.println("OK");
  }

  static void testOptions(ConnectionFactoryOptions options) {
    // TODO: Change "test" user/password to your own. Consider reading the
    //  values from a secure source, rather than hardcoding them in this file.
    options = options.mutate()
      .option(USER, "test")
      .option(PASSWORD, "test")
      .build();

    ConnectionFactory connectionFactory = ConnectionFactories.get(options);

    Flux.usingWhen(
        connectionFactory.create(),
        connection -> Mono.empty(),
        Connection::close)
      .blockFirst();
  }
}

Some theories I have:

1. I wonder if that error message isn't lying? Perhaps a DATABASE option is being configured somewhere?
2. Could any part of the DESCRIPTION=... contain something that needs to be percent encoded? https://developer.mozilla.org/en-US/docs/Glossary/Percent-encoding
3. Are we using the same versions? I'm testing Oracle R2DBC 1.1.1, and R2DBC Pool 1.0.0.RELEASE

[antoba]

Hi, @antoba. Oracle R2DBC supports (DESCRIPTION=... style URLs that can include an ADDRESS_LIST element: https://github.com/oracle/oracle-r2dbc#configuring-an-oracle-net-descriptor

Would that work for you, or are you looking for something different?

Well, I didn't know it accepted this syntax, I'll try and eventually let you know if it works in my environment

thanks a lot for now. !

[dataatma]

@Michael-A-McMahon Found the issue and the error indeed is correct. This is in our app properties. spring.r2dbc.name=${DB_SCHEMA} - Once we removed this, the(DESCRIPTION= style worked. Thanks a lot!

[Michael-A-McMahon]

@dataatma: Glad to hear it. @antoba: I'll leave the issue open for while, hoping things work for you as well.

[antoba]

@Michael-A-McMahon it works ! We tried on Oracle RAC. As far as I'm concerned you can close the issue.

Thank you very much for your support.

[Michael-A-McMahon]

Glad to hear it!