Why tnsnames doesn't failover to standby database correctly?

Question

I have this tnsnames:

JUPITER =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (LOAD_BALANCE = OFF)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.mercurio.sp)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.85)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jupiter)
    )
  )

)

rac-scan.mercurio.sp is the scan for prod test db and 192.168.0.85 is the standby.

When I stop the prod db and try connect, it still direct me to prod db rac-scan.mercurio.sp

My steps:

Testing connection with prod database using tns alias:

C:\Users\Danilo>sqlplus sys/xxxxxx@jupiter as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 10:55:09 2022
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Everything ok, now I will stop this database:

Stopping the primary database

(rac1)(oracle@jupiter1):~> srvctl stop database -d jupiter -o immediate

Now I try to connect again using the same tns alias

C:\Users\Danilo>sqlplus sys/xxxxxx@jupiter as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 10:58:47 2022
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

using a non-sysdba account

C:\Users\Danilo>sqlplus user_test/xxxxx@jupiter

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 11:12:27 2022
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -1145261241
Process ID: 0
Session ID: 0 Serial number: 0

Why tns don't try 192.168.0.85?

NOTE: I have a static listener, could be it? how to resolve?

Answer 1

Because you have a static listener entry for the "jupiter" service.

A connection failover happens when the requested service is not found in the listener. But your static registration breaks this feature because the service will be found always, even if there is no instance behind it.

Remove your static entry and use role-based services for client connections.

Answer 2

There are various errors that make Oracle client to consider to try another host. SYS user when connected "AS SYSDBA" can connect even into MOUNTED instance so it is no reason to failover onto another host. So other errors are not considered so critical. The "right" error is: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor. When client sees this, it tries to connect to another host specified in connection string.

The easiest approach (in your case) is to have RAC on one side and Oracle Restart on another side.

Then create a SERVICE - managed by clusterware - which will be available on primary side only.

srvctl add service -db db_unique_name -service service_name -role PRIMARY
srvctl start service -db db_unique_name -service service_name 

Important to note is that the chosen service name should not be one of the default names. Pick a name like dbname_app so it is clear that this service name is intended for the app connection.

Then try to initiate switchover using dgmrgl and execute lsnrctl status. The newly created service should be online on one site only.

If you do not use CRS, then there are ways how to achieve the same approach using TRIGGER ON DATABASE STARTUP. The whole trick is to have a particular service to be available on one side only.

You should also add these fields into your connection string (values added as an example)

TRANSPORT_CONNECT_TIMEOUT (5sec)
TIMEOUT (10s)
RETRY_COUNT ( 60x)

So the client will keep jumping between two sides, trying to connect, until one of side finally becomes PRIMARY.

PS: JDBC drivers should behave exactly has OCI drivers in this case. Unfortunately it is not true. I really recommend using latest 19c JDBC drivers with Dataguard.

Answer 3

Your problem is that the Service that you want to connect to, regardless of which database is offering it, has the same name as one of your databases, and database name always takes precedence!
Even though your "jupiter" Service is being offered by the standby, the primary, "jupiter" database is still there and will try to accept connections.

Your Service must be identifiable separately from either of the two database names.

For a Database Service "SVC", I typically use databases named "SVCa" and "SVCb".