5 Replies Latest reply: Aug 8, 2012 11:36 AM by mseberg RSS

    TAF and TCP Timeouts - TNS Config - VIP

    jstem1177
      Hello all,

      I'm reviewing a Dataguard 11gR2 setup (1 primary --> 1 physical standby). I was just curious to know, given the below tns, wouldn't the client receive/wait a TCP timeout after 3 minutes if the primary server was actually complete down.

      Here si the scenario, I do a switchover from primary to standby --> all the connections already connected get passed to the new primary using TAF.
      Now all those already connected before the swtichover are ok, but now the new standby(original primary) goes completely down, would I need to change the TNS entry to use VIP instead for the new connections not to TCP timeout?
      POMS=
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = orap01.intm.com)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = orap02.intm.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = POMSDG.mf.galderma.com)
          )
        )
      Below is a reference Tom Kyte, but was wondering if someone else had already put this into place.
      Here it solution:
      -----------------
      1) tnsnames.ora entry will have connect time failover configured for primary and standby.
      
      2) If primary database is down but primary node is up. The above will work fine. 
      
      3) But if primary database is down and primary node is also down. The each attempt to connect using 
      tnsnames.ora will go through a TCP timeout. 
      
      solution for 3:
      
      b) On another node in primary subnet - bind/start the Failed database node IP. This can be done via 
      a script.
      Make sure to shut this down, when bringing up the failed primary node. (This is kind of a manual 
      VIP failover)
      Thanks in advance for your advice or assistance in thsi matter.

      Jan S.
        • 1. Re: TAF and TCP Timeouts - TNS Config - VIP
          Sebastian Solbach -Dba Community-Oracle
          Hi,

          read this whitepaper:

          http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

          ... and if you then still have questions post again.

          Regards
          Sebastian
          • 2. Re: TAF and TCP Timeouts - TNS Config - VIP
            jstem1177
            Hello,

            Thanks for the document. I do believe I do have the best practices in place.

            This is a standalone instance with dataguard setup.

            For the document I take that if the user is using Oracle Client 11gR2, there are couple options directly at the tns and sqlnet which I can configure. If the client is not using oracle client but some JDBC client, how would I go about dealing with the CFO at database role change.

            Another options I was visiting, is the create a VIP on bother servers, and using 11gR2 feature which allows to call external scripts and just do (stop vip on standby, and start on primary)?:
            ssh root@old_primary
            ifdown eth0:0   
            exit
            su - root
            ifup eth0:0   
            What do you think of this solution. Has anybody ever used something like this. I've not come across it in any documentation and have yet to validate if there is a TCP timeout error when the old primary server is completely unavailable.

            Sincerely

            Jan S.
            • 3. Re: TAF and TCP Timeouts - TNS Config - VIP
              mseberg
              Hello;
               
              Would I need to change the TNS entry to use VIP instead for the new connections not to TCP timeout?
              
              
              POMS=
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = orap01.intm.com)(PORT = 1521))
                  (ADDRESS = (PROTOCOL = TCP)(HOST = orap02.intm.com)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = POMSDG.mf.galderma.com)
                  )
                )
               
              I'm thinking yes. I would expect something like this :
               
               POMS=
                 (DESCRIPTION =
                   (ADDRESS_LIST =
                   (ADDRESS = (PROTOCOL = TCP)(HOST = orap01.intm.com)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = TCP)(HOST = orap02.intm.com)(PORT = 1521))
                   (CONNECT_DATA =
                     (SERVICE_NAME = POMSDG.mf.galderma.com)
                     (FAILOVER_MODE=(TYPE=SELECT) (METHOD=BASIC) (RETRIES=15) (DELAY=12)
                     )
                   )
                )
                
                
                
              I would also expect a connect timeout be set in sqlnet.ora
                SQLNET.OUTBOUND_CONNECT_TIMEOUT = 4
                
                
              Best Regards

              mseberg
              • 4. Re: TAF and TCP Timeouts - TNS Config - VIP
                jstem1177
                Hello mseberg,

                Thanks a lot for the information. This is what I was looking for. I need to confirm that all the end app servers are running the oracle client.

                Maybe just for curiosity, have you ever implemented a call to an external script to stop and start ethernet VIP inside a STARTUP trigger?

                Thanks

                Jan S.
                • 5. Re: TAF and TCP Timeouts - TNS Config - VIP
                  mseberg
                  Great. Kind of odd you gave Sebastian the correct answer then.

                  Will look in my notes for the trigger info.

                  Best Regards

                  mseberg