Oracle Failover from Primary to Secondary Server (SSIS)

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 11, 2014 at 9:55 am

  #285682

  I have some packages that have Oracle as their Data Source.

  I'm not clear as to what I do and\or not need to do.

  The primary Server has been failing so I added entries into the 32 and 64 bit tnsnames.ora file to point to the secondary server.

  I also created a ODBC Connections and Linked Servers.

  I want to failover to the secondary sever as transparent as possible.

  I don't recall where you define which Oracle Instance that you are referring to in the SSIS Package?

  Any advise would be greatly appreciated.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 11, 2014 at 1:25 pm

  #1705460

  I basically came up with a solution

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jo Pattyn

  SSC-Dedicated

  Points: 32347

  More actions

  April 12, 2014 at 11:58 am

  #1705559

  was it Oracle TAF automatic fail over using tnsnames ?

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 12, 2014 at 4:32 pm

  #1705567

  Jo Pattyn (4/12/2014)

  ---

  was it Oracle TAF automatic fail over using tnsnames ?

  Could you please expand on that?

  I do not have control of the Oracle Servers but I can make suggestions to the vendors who Server is constantly failing over.

  Thanks again. 🙂

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jo Pattyn

  SSC-Dedicated

  Points: 32347

  More actions

  April 13, 2014 at 12:23 pm

  #1705604

  You can click on the link TAF in the above post.

  I don't have experience with it but know that you can specify multiple addresses under one TNS-alias. Sometimes an oracle connection manager is used.

  If the first address is barred the Oracle-Net layer connects to the second address to the application (but doesn't recover lost work)

  The mechanism is quite transparent as you only have one tns-alias for the application to connect to.

  You need the full OCI-driver. Another link for java