How to create a DB Link to a Database which has failover in TNS

database-linkoracleoracle-12c

Could you please help me to understand how my DB LINK creation sql look like to a database (RAC env, Oracle 12c) which have fail over option in tns entry?

I read below post from Oracle-(not having access to view below url since I do not have Oracle Support membership)

Don't create a DBLink using a TNS connect string configured for failover. #Database links don't support TAF or Failover. See the following: Database Link: DBLink Creation using TAF or SCAN TNS Connect String (Doc ID 399453.1)
https://support.oracle.com/knowledge/Oracle%20Database%20Products/399453_1.html

Does it means that we have to remove fail-over, connect etc options from tns entry and then create db link?
My TNS entry is below(just a sample)

panda.host.com=
(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521)) 
(ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))

I am now trying to create a DB link in another database pointing to this database so based on above fact which of below sql is correct and will be working? In 'using' clause I am providing connection descriptor since I am not allowed to make changes in tnsnames.ora.

1. 

   CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
    (CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521)) 
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';



2. 

CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521)) 
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';

//failover and connect options are removed.

Best Answer

The referenced MOS just states the following:

  • Dblinks do not support TAF. This refers to FAILOVER_MODE parameter, not FAILOVER. The first parameter is about TAF, the second is about connect-time failover.
  • It is not recommended to use the SCAN address for dblinks in case of RAC. Instead of the SCAN address, use the VIP addresses.

Removing FAILOVER parameter will not change anything, the default parameter for it is ON.

FAILOVER

You do not need to remove connect-time failover parameters.