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 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@xxxxxx> 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/******@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 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.
Attachment:
signature.asc
Description: This is a digitally signed message part.
