JDBC failover connection string

user_12972628 0
Viewed 5412

I'm trying to create a JDBC connection string that will allow a failover to a secondary database should the primary fail. I currently have the following string but when the primary fails the string doesn't connect to the secondary db. If I put the secondary detail first in the string it works. It appears that if the first part of the string hits a failed db it doesn't continue onto the second part of the string.

jdbc:oracle:thin:@
 (
 DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION=
        (CONNECT_TIMEOUT=3)
        (RETRY_COUNT=3)
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=
                (PROTOCOL=TCP)
                (HOST=11.111.1.111)
                (PORT=1234)
            )
        )
        (
        CONNECT_DATA=(SERVICE_NAME=<servicename1>)
        )
    )
    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=
                (PROTOCOL=TCP)
                (HOST=22.222.2.222)
                (PORT=1234)
            )
        )
        (
        CONNECT_DATA=(SERVICE_NAME=servicename2)
        )
    )
 )

Any advise?

jdbc oracle
1 Answers

In case you're using JDBC drivers 11g. You need to use version 11.2.0.4 and then apply merge patch 24559240 on top of 11.2.0.4.

Patch 24559240: MERGE REQUEST ON TOP OF 11.2.0.4.0 FOR BUGS 19030178 19154304 20426934
Bugs Resolved by This Patch
19030178      ADD TLSV1.1 AND TLSV1.2 IN JDBC THIN
19154304      JDBC: RETRY_COUNT DOES NOT RETRY WHEN SERVICE DOWN AS REQUIRED
20426934      THIN JDBC NEED TO INTERPRET A NEW ERROR SEND BY LISTENER ON REACHING RATE_LIMIT

In case you use JDBC drivers 12c or higher. CONNECT_TIMEOUT is partially ignored. You have to use TRANSPORT_CONNECT_TIMEOUT instead. Just beware TRANSPORT_CONNECT_TIMEOUT value can be in seconds or miliseconds - depending on JDBC driver version.

Try to use connect string like this:

jdbc:oracle:thin:@(DESCRIPTION=
(TRANSPORT_CONNECT_TIMEOUT=5)
(CONNECT_TIMEOUT=5)
(RETRY_DELAY=10)
(RETRY_COUNT=2)(FAILOVER=ON)(LOAD_BALANCE=off)
(ADDRESS=(PROTOCOL=TCP)(HOST=db1-scan)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=db2-scan)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=testsrv)))
user_8362150
Related