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.
- I can gently take my database instances in and out of the cluster such that clients are mostly unaffected (shutdown transactional).
- I can do a switchover back and forth from the primary to the standby and vice versa.
- I'm trying to add a switchover to a secondary site *such that clients continue to be unaffected* (or at least as minimal as possible).
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