Category Archives: jdbc

Oracle and OCI & JDBC Connection Strings

<b>Sample for tnsnames.ora</b>
=======================
culpdbs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-primary-host-name)(PORT = 49130))
(ADDRESS = (PROTOCOL = TCP)(HOST = your-standby-host-name)(PORT = 49130))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydbs)
(failover_mode =
(type = select)
(method = preconnect)
(retries = 180)
(delay = 10)
)
)
)

(LOAD_BALANCE = no)  ==&gt; You cannot load balance between Dataguard servers

Here is a sample JDBC connect string for failover scenarios:

Jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = primary-scan)(PORT = 49125)) ADDRESS = (PROTOCOL = TCP)(HOST = standby-scan) (PORT = 49125))(LOAD_BALANCE = no)(FAILOVER = yes))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = someserv_svc_01.bigbusiness.com)))

Jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = primary-scan)(PORT = 49125)) ADDRESS = (PROTOCOL = TCP)(HOST = standby-scan) (PORT = 49125))(LOAD_BALANCE = no)(FAILOVER = yes))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = someserv_svc_01.bigbusiness.com)))

there are a few different parameters here that are good to note what they do:

If TAF is configured there are 2 types which are defined in the CONNECT_DATA portion of the connect string, if you do not have TAF configured these parameters should not be configured as they will cause issues during connection retries.

TYPE

Specifies the type of failover. Three types of Net8 failover functionality are available by default to Oracle Call Interface

(OCI) applications:
SESSION: Fails over the session; that is, if a user’s connection
is lost, a new session is automatically created for the user on
the backup. This type of failover does not attempt to recover
selects.
SELECT: Enables users with open cursors to continue
fetching on them after failure. However, this mode involves
overhead on the client side in normal select operations.

 

Server side TAF settings override client-side counterparts that might be configured in TNS connect descriptors

select name, failover_method, failover_type from dba_services;

SELECT inst_id, sid, serial#, username, failover_type, failover_method, service_name, failed_over FROM gv$session where username = UPPER(‘&your_user_name’);

 

Failover Descriptions

TAF is transparent application failover which will move a session to a backup connection if the session fails.

Note: TAF will not work with JDBC thin.

 

 

 

Here is a good article for jdbc FCF configuration, keep in mind there are a few prereqs to have in place before you can do this.

FCF jdbc config

 

Failover Connect Strings

METALINK Articles

Information Center: Oracle JDBC 11g community

https://support.oracle.com/epmos/faces/SrCreate?_adf.ctrl-state=ghjvpcjk5_4&_afrLoop=30270398455552

 Information Center: Using Oracle JDBC 11g [ID 1371705.2]

Information Center: Installing and Configuring Oracle JDBC 11g [ID 1372203.2]

——————————————————————————————————————————————-

Can the JDBC Thin Driver Do Failover by Specifying FAILOVER_MODE?  Oracle Doc ID 465423.1

The Oracle note is quite specific on this topic

A string formatted with the FAILOVER_MODE is not supported with jdbc thin. FCF is however supported.

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1vip)(PORT = 1525)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2vip)(PORT = 1525)) (LOAD_BALANCE = on)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ractest) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 2) (DELAY = 1))))

 

Master Note for Oracle JDBC High Availability Features [ID 1100024.1]

JDBC Connection String to RAC System

The following notes contain information for setting a valid JDBC connection string to a RAC system:

Document 390923.1 Connection-String For Jdbc Thin-Client In A Data guard-Environment

Document 414465.1 How to Set JDBC Thin Connection String to RAC Without Referencing Every Node in the Cluster

Document 391015.1 How to Connect to a RAC Database from a Java Application That Takes a HOST PORT SID Argument

Document 1081179.1 How to Access a RAC Database configured with SCAN via Java Thin Driver

Document 1290193.1 Using SCAN With Oracle JDBC 11g Thin Driver

JDBC Drivers and Failover Mechanisms (FCF, TAF, SCAN) [ID 1080674.1]

The following documentation provides an overview of Fast Connection Failover, how it works and how to use it, and a comparison between Fast Connection Failover and Transparent Application Failover:

Version 11.2 Oracle® Database JDBC Developer’s Guide, 11g Release 2 (11.2)
Chapter 27: Fast Connection Failover
http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/fstconfo.htm
Version 11.1 Oracle® Database JDBC Developer’s Guide and Reference, 11g Release 1 (11.1)
Chapter 26: Fast Connection Failover
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/fstconfo.htm#CIHJBFFC
Version 10.2 Oracle® Database JDBC Developer’s Guide and Reference 10g Release 2 (10.2)
Chapter 27: Fast Connection Failover
http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/fstconfo.htm
Version 10.1 Oracle® Database JDBC Developer’s Guide and Reference 10g Release 1 (10.1)
Chapter 8: Fast Connection Failover
http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/fastconnfail.htm

 

Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]

This is an excellent article describing and testing the FCF functionality within Oracle 11g

How to Implement Connect Failover Using JDBC Thin [ID 213412.1]

How to Use Connect-Time Failover Across Independent, Non-RAC Databases From JDBC [ID 1067811.1]

JDBC TCPS Connections Do Not Failover For SCAN Listeners [ID 1289244.1]

JDBC With FAN Events
The Oracle RAC FAN APIs enable application code to receive and respond to FAN event notifications sent by Oracle RAC. This is achieved by enabling the code to respond to FAN events in the following way:

•Listening for Oracle RAC service down, service up, and node down events
•Listening for load balancing advisory events and responding to them
More information is available here!!

FAN Event Documentation

SCAN

Using SCAN is entirely different that what is described above

SALES=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Austin-scan)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=OrderEntry)))
This is a typical SCAN entry this is based on a single RAC cluster in one location. Load balancing is automatically done within the nodes of the cluster, there is no need to set any additional parameters for a SCAN setup locally. If a cluster has additional nodes added or deleted the SCAN string remains the same with no outage or additional work to be done. The client changes nothing.