<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) ==> 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
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');
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.
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]
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!!
SCAN
Using SCAN is entirely different that what is described above