Will,
Using the acronym TAF is fine. There are various client and server side
connection switchover/failover mechanisms but most database folks will
understand what you mean.
Yes, it can be done without dns changes, without tnsnames changes, and with
minimal disruption to end users.
Yes, you need to create a custom service as described in the MAA document.
Yes, this will work with or without RAC and can be combined with instance
failover.
srvctl will never show the default service so it is normal that you don't
see anything from those commands.
The document should give you everything you need to set this up correctly.
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.
On Tue, Sep 25, 2018 at 2:12 PM Will Beldman <wbeldma@xxxxxx> wrote:
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.
1. I can gently take my database instances in and out of the cluster such
that
clients are mostly unaffected (shutdown transactional).
2. I can do a switchover back and forth from the primary to the standby
and
vice versa.
3. 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).
Unfortunately today, the only way I can achieve this is to fudge the name
through a DNS update (I have to involve the NOC) or to instruct the users
to
update their TNS entries and restart. Both are VERY disruptive!
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 Willha
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@xxxxxx>
that
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
later)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
environment? I
As expected, my connection to STBY is perfect:
===============
sqlplus system/******@STBY
...
SQL>select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
===============
However, as *not* expected, my connection to PRIM fails immediately:
===============
sqlplus system/******@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
updatehave 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
stillor a
quick tnsnames.ora update to fool the client into "thinking" it is
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.
