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.
