Re: Configuring a client TNS entry for TAF

The document posted by Seth is THE reference for setups like this.

If you're somewhat new to all this it may be a bit overwhelming, but I'd highly recommend studying every last sentence of that paper. It has helped me a lot in the past(it existed for older versions as well).

What makes it difficult is that there's many different major technologies at play here - you have RAC, you have Data Guard. And then there's the dreaded users.

To get the best possible outcome you need to first get a clear understanding of how your applications are connecting to the database. OCI driver? JDBC?

The next key thing to understand is that for certain features to work (best example is TAF - Transparent Application Failover) - which is a RAC feature and allows a connection to fail over to a different RAC instance - the client / application must be designed to support this. This isn't something you can just set up on the database server and be done with it, unfortunately. It's also somewhat limited in what scenarios exactly can and can not fail over cleanly. But I wouldn't worry about this too much - from what you said earlier it seems your main concern is transparency to the users with regards to the primary/standby locations.

From a TNS / connectivity point of view, what you want to ensure is that connections are routed to whatever RAC instance is available. This means the endpoint to use when connecting to (either the primary or the standby cluster) should be the SCAN listener's hostname). The second part to the puzzle is then to ensure that clients can use a single "name" to connect to your environment, regardless of which one happens to be the primary or standby in this case.

To set up the TNS configuration to also support that scenario, Seth's linked MAA document is fabulous. What we used to do before all this was to create a startup trigger that created a service based on the database role
(e.g. if it was primary, start the service "APPLICATION_RW") if it was a
standby it wouldn't be started. Nowadays, the Oracle Grid Infrastructure does that for you, using srvctl to create a service with role affinity. The clusterware will then make sure that it is properly handled, even with role transitions.

Finally, the client configuration also needs to include all this. Again, from Seth's link on page 12 there is a clear example how to do this:

SALES= (DESCRIPTION= (FAILOVER=on)
(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS_LIST= (LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=prmy-scan)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=stby-scan)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=oltpworkload)))

The key bits here are:
- The FAILOVER is directly in the DESCRIPTION entry. This means that each entry in the ADDRESS_LIST will be contacted. - Use the timeout parameters to control how long to spend to try and connect.
- The ADDRESS_LIST entries will be tried in order - this means if you're on the secondary site which is listed second in the list, connections may be slower. You can tune that using the timeout settings - The RAC connection strings for each of the sites are using the SCAN
(prmy-scan / stby-scan).

- The SERVICE_NAME is a service you define with srvctl with role affinity
(the document also tells you how to do that). This is key to make this work.

HTH Stefan

On Wed, Sep 26, 2018 at 9:48 PM, Will Beldman <wbeldma_at_uwo.ca> wrote:

> On Tuesday September 25 2018 02:48:56 PM Seth Miller wrote:
> > A valuable lesson to take away from this is never give end users the
> > default service. Always create one or more services so that you can
> > customize, migrate, failover, change, upgrade, limit, report on, etc. the
> > end user's service without requiring client connection string updates.
>
> Thank you! This has been insightful!

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l