Re: Configuring a client TNS entry for TAF
Date: Tue, 25 Sep 2018 15:10:37 -0400
Message-ID: <3665450.Sgv9FDGx0o_at_wbeldma>
Thank you,
I have a poor understanding of Oracle Services and maybe TAF is the wrong term
so sorry for the confusion. Perhaps what I'm trying to achieve can't actually
be done.
To clarify my configuration, the primary is only a two node cluster and all
clients connect to either node through the SCAN address equally. The standby
is also a two node cluster.
vice versa.
If I am understanding you correctly, and if this can be done, I should be creating a custom service that INCLUDES the standby instances (call it, say, CUSTOM_SERVICE_NAME) and configuring the clients to point to that service instead (as per the tnsnames.ora example you provided)?
Here's the config I have today:
$ srvctl config database -db PRIM
Database unique name: PRIM
Database name: PRIM
...
Start options: open
Stop options: immediate
Database role: PRIMARY
...
Services: <-----------------EMPTY!
...
$ srvctl config database -db STBY
Database unique name: STBY
Database name: STBY
...
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
...
Services: <-----------------EMPTY!
...
So after creating CUSTOM_SERVICE_NAME, both PRIM and STBY should identify themselves as part of the CUSTOM_SERVICE_NAME service?
The other thing I read is 11g eliminated the need to use database triggers or manually attempting to manage the service after a switchover. Is this accurate? After a switchover, the service on the old primary should be disabled and on the new primary should be automatically enabled? Am I reading this correctly or is there still some manual intervention required after a switchover?
On Tuesday September 25 2018 12:32:32 AM Andrea Monti wrote:
> Hi Will
>
> If you want to use connect-time failover you should configure a custom
> service_name and a tns entry like
>
> CONNECT_TIME_FAILOVER =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (FAILOVER=on)
> (LOAD_BALANCE=on) #optional - only use this to process different
> addresses from first to last
> (ADDRESS = (PROTOCOL = TCP)(HOST = primHost)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = stbyHost)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME=CUSTOM_SERVICE_NAME)
> (failover_mode =
> (RETRIES = 180)
> (DELAY = 5)
> )
> )
> )
>
> This will work, as lons as the service CUSTOM_SERVICE_NAME will only be
> active on the PRIMARY database.
>
> If you want to use TAF you should use Oracle RAC rather than Oracle Data
> Guard. TAF will protect your sessions from instance crash; additionally,
> TAF may protect your select and your transactions, too (check how to use
> FAILOVER_METHOD!).
> However, TAF will not protect you from database failures:Data Guard and
> site switchover will protect you in case of a database failure, but they
> will not protect your ongoing select and transactions, as long as I know.
>
> regards
>
> Andrea
>
>
> Il giorno lun 24 set 2018 alle ore 23:07 Will Beldman <wbeldma_at_uwo.ca> ha
>
> scritto:
> > I have two databases configured under Data Guard:
> > * PRIM - Usually the primary
> > * STBY - Usually the standby
> >
> > From what I read, the following configuration should work:
> > ===============
> > PRIM =
> >
> > (DESCRIPTION =
> >
> > (ADDRESS_LIST =
> >
> > (ADDRESS = (PROTOCOL = TCP)(HOST = primHost)(PORT = 1521))
> >
> > )
> > (CONNECT_DATA =
> >
> > (SERVICE_NAME = PRIM)
> > (FAILOVER_MODE = (BACKUP =
> >
> > STBY)(METHOD=basic)(TYPE=select)(RETRIES=10)
> > (DELAY=10))
> >
> > )
> >
> > )
> >
> > STBY =
> >
> > (DESCRIPTION=
> >
> > (ADDRESS_LIST=
> >
> > (ADDRESS=(PROTOCOL=TCP)(HOST=stbyHost)(PORT=1521))
> >
> > )
> >
> > (CONNECT_DATA=(SERVICE_NAME=STBY))
> >
> > )
> >
> > ===============
> >
> > I can successfully switch over the database.
> >
> > My intent is for the client to *attempt* to connect to PRIM, but if that
> > fails, the connection passes through to STBY instead.
> >
> > (Since PRIM is now a standby but still listening for connections, to
> > ensure
> > failure on PRIM, I completely shut off the database. More on this later)
> >
> > As expected, my connection to STBY is perfect:
> > ===============
> > sqlplus system/******_at_STBY
> >
> > ...
> >
> > SQL>select DATABASE_ROLE from v$database;
> >
> > DATABASE_ROLE
> > ----------------
> > PRIMARY
> > ===============
> >
> > However, as *not* expected, my connection to PRIM fails immediately:
> > ===============
> > sqlplus system/******_at_PRIM
> >
> > SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 24 16:52:52 2018
> >
> > Copyright (c) 1982, 2014, Oracle. All rights reserved.
> >
> > ERROR:
> > ORA-12514: TNS:listener does not currently know of service requested in
> > connect
> > descriptor
> > ===============
> > It looks like RETRIES and DELAY is not respected here as the failure is
> > immediate.
> >
> > So a few questions:
> > 1. As is, what am I not understanding? Why isn't my configuration doing
> > anything I expect it to?
> > 2. Obviously I have another issue where PRIM is in a MOUNT status and
> > "accepting" connections. So my connections to PRIM produced ORA-01033.
> > What is
> > the preferred way to configure TAF with Data Guard in a RAC environment? I
> > have a two instance primary and a two instance standby. This is my
> > ultimate
> > goal but achieving success by shutting off PRIM after a switchover is a
> > good
> > start.
> > 3. I read many who recommend complimenting a switchover with a DNS update
> > or a
> > quick tnsnames.ora update to fool the client into "thinking" it is still
> > connecting to the same database it always was. I recognize this is *a*
> > solution but I'm looking for something more elegant that is inline with
> > what
> > Oracle describes in the documentation.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 25 2018 - 21:10:37 CEST