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.
