This discussion is archived
6 Replies Latest reply: Feb 11, 2013 1:08 PM by SherrieK RSS

TNS Entry to handle primary connection as well as RAC load balancing

SherrieK Newbie
Currently Being Moderated
Environment: Oracle RAC 11.1.0.7
DataGuard, using DG Broker
Services are always used for clients to connect

We have a primary cluster, and a standby cluster with their respective databases.

Our DataGuard environment is performing well, we can switchover to the standby, see logs apply, and then switch back again, and do this repeatedly.

We want to be sure that we have the best TNS entry for our clients to switch to the primary and also continue with the RAC load balancing that we have always had.

One example (A) (ETHEL) we were given has a description list with 2 descriptions, one for the primary, the other for the standby. Another example (B) (FRED) shows one description with all nodes for the primary and standby together. Example A does not work, Example B does - but is it correct? Is there a preferred method or something that we are missing?

Example A:
ETHEL =
(DESCRIPTION_LIST =
(LOAD_BALANCE = off)
(FAILOVER = TRUE)(CONNECT_TIMEOUT = 5) (RETRY_COUNT = 2)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tpadw01da-vip)(PORT = 1523))
(FAILOVER = on)
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGSAND.ad.swfwmd.net)
(FAILOVER_MODE =
(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac01-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac02-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac03-vip)(PORT = 1523))
(FAILOVER = on)
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SANDBOX.ad.swfwmd.net)
(FAILOVER_MODE =
(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
)
)
)
)

Example B:
FRED =
(DESCRIPTION_LIST =
(LOAD_BALANCE = off)
(FAILOVER = TRUE)(CONNECT_TIMEOUT = 5) (RETRY_COUNT = 2)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac01-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac02-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = bkvlrac03-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = tpadw01da-vip)(PORT = 1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = tpadw02da-vip)(PORT = 1523))
(LOAD_BALANCE = yes)
(FAILOVER = TRUE)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ricky.ad.swfwmd.net)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
)
  • 1. Re: TNS Entry to handle primary connection as well as RAC load balancing
    Sebastian Solbach (DBA Community) Guru
    Currently Being Moderated
    Hi,

    see here:

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-clientfailoverbestprac-129636.pdf

    Regards
    Sebastian
  • 2. Re: TNS Entry to handle primary connection as well as RAC load balancing
    brunors Explorer
    Currently Being Moderated
    Hello!!

    Read theses <http://www.usn-it.de/index.php/2007/06/28/how-to-fail-over-a-client-transparently-in-a-dataguard-switchoverfailover-scenario/> and <http://docs.oracle.com/cd/B28359_01/server.111/b28282/configbp009.htm> .

    I believe that can help you about your question.

    Kind regards,
    Bruno Reis.
    www.brunors.com
  • 3. Re: TNS Entry to handle primary connection as well as RAC load balancing
    SherrieK Newbie
    Currently Being Moderated
    Thanks guys.

    We are using TAF and using RAC, so we define our services in the database with srvctl. Through testing we've found that we don't need a database startup trigger when we switchover, the services start automatically.

    Because we define our services at the server level, we are thinking that we don't need tns entries for things like:


    (CONNECT_TIMEOUT = 5)
    (RETRY_COUNT = 2)

    and

    FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )

    Through documentation and examples, it's hard to tell what applies to RAC and what applies to DG, or if they are one and the same.
    TAF, I'm seeing, applies to both.

    For now we've stripped down our TNS entry for a service to be:
    WILMA =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testlrac01-vip)(PORT = 1529))
    (ADDRESS = (PROTOCOL = TCP)(HOST = testlrac02-vip)(PORT = 1529))
    (ADDRESS = (PROTOCOL = TCP)(HOST = testrac03-vip)(PORT = 1529))
    (ADDRESS = (PROTOCOL = TCP)(HOST = ntdw01da-vip)(PORT = 1529))
    (ADDRESS = (PROTOCOL = TCP)(HOST = ntdw02da-vip)(PORT = 1529))
    (LOAD_BALANCE = yes)
    (FAILOVER = TRUE)
    (CONNECT_DATA =
    (SERVICE_NAME = wilma.ad.swfwmd.net)
    )
    )

    Testing so far has been
  • 4. Re: TNS Entry to handle primary connection as well as RAC load balancing
    Sebastian Solbach (DBA Community) Guru
    Currently Being Moderated
    Hi,

    in 11gR1 you needed the startup trigger for 2 reasons:
    a.) Single Instance. Now if you use RAC on both sides, that is o.k. 11gR2 solved this with having GI also for single instance.
    b.) Active Dataguard. So you could have a service active on both sides, but one would only be read only. This is also solved in 11gR2, since you can specify a role in srvctl for the service (e.g. PRIMARY or STANDBY).

    Why do you still want a startup trigger (more a trigger which shuts down the service, than start it up):
    => If your DG failed over to the other server, and the old primary is coming back online (maybe accidentally as ADG), and by any chance starts the service, your clients will be evenly spread over all instances (primary and standby). I don't think you want that to happen. The startup trigger will in any case prevent that...

    Now regarding your questions:
    a.) There is no difference between DG and RAC per se. So all entries apply.
    b.) CONNECT_TIMEOUT and RETRY_COUNT are 11.2 client parameters (not known to older client), and they do make sense on the following circumstance:

    RETRY_COUNT: During a switchover (or failover) the service will not be registered at all in any of the listeners. If you did not specify retry_count the client will get an error. If you specified retry_count he will try again, and then get a valid connection, because the server did start up. The retry_count is a generic parameter also available for THIN clients (which do not know TAF) and works also if you did not configure TAF. Since you seem to be only using Thick Clients and having TAF configured, the "RETRIES" of the TAF entry will take the work of "RETRY_COUNT" at the higher level.

    CONNECT_TIMEOUT: You probably did not do a test where the server was down (not just the listener/and or the database). If your server is down, the client will wait for the TCP/IP timeout till it reacts and advances to the next address in the list. The TCP/IP timout is platform dependent, but I have seen it go up to 30 minutes. Do you want a client to wait for 5 minutes, till he tries the next address in the address list (which might also be down)? Therefore you would like to only wait 5 seconds before the next address is used. However if you are using sqlnet.ora with the CONNECT_OUTBOUND Parameter, this does the same.....

    c.) TAF Parameters: If they are defined on server side, these will be used. So yes there is no need to specify them additionally on the client side.
    d.) I would sort the list differently (with 2 address_list lines), otherwise all addresses will always be tried, and you do want to contact one of the primary addresses first:
    WILMA =
      (DESCRIPTION =
      (ADDRESS_LIST =
        (LOAD_BALANCE = yes)
        (ADDRESS = (PROTOCOL = TCP)(HOST = testlrac01-vip)(PORT = 1529))
        (ADDRESS = (PROTOCOL = TCP)(HOST = testlrac02-vip)(PORT = 1529))
        (ADDRESS = (PROTOCOL = TCP)(HOST = testrac03-vip)(PORT = 1529))
      )
      (ADDRESS_LIST =
        (LOAD_BALANCE = yes)
        (ADDRESS = (PROTOCOL = TCP)(HOST = ntdw01da-vip)(PORT = 1529))
        (ADDRESS = (PROTOCOL = TCP)(HOST = ntdw02da-vip)(PORT = 1529))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = wilma.ad.swfwmd.net)
      )
    )
    Note: FAILOVER defaults to TRUE so no need to set this
    LOAD_BALANCE only is in the inner ADDRESS_LIST part, that you do not traverse the standby listeners first.
    So your order is totally random. Could be [1-5], while the above does do [1-3] first, then [4-5].

    Regards
    Sebastian
  • 5. Re: TNS Entry to handle primary connection as well as RAC load balancing
    SherrieK Newbie
    Currently Being Moderated
    Thank you Sebastian. You have been very helpful. I apoligize for my last post getting cut off, weird things happening here!
    We will give your suggestions a try. I appreciate it.
  • 6. Re: TNS Entry to handle primary connection as well as RAC load balancing
    SherrieK Newbie
    Currently Being Moderated
    Thank you Sebastian for your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points