Configuring a client TNS entry for TAF

From: Will Beldman <wbeldma_at_uwo.ca>
Date: Mon, 24 Sep 2018 17:06:21 -0400
Message-ID: <40472809.c5PddAgSFX_at_wbeldma>

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-l

Received on Mon Sep 24 2018 - 23:06:21 CEST