This post was originally published on this site

With Oracle database version 19.7 we got Standard Edition High Availability as a kind of replacement for Standard Edition RACs which cannot be used as of 19c any more. See https://blogs.oracle.com/maa/standard-edition-high-availability-officially-released

In a recent discussion I wondered whether FAN events and Fast Connection Failover might work with such a setup. See https://twitter.com/MathiasZarick/status/1365266901639843844 .

This short blog post is about the results of my tests with it.

I have a SEHA setup in place. Installed a 19.10 Standard Edition on one node of a 19c cluster. Make sure that you install DB RU (32218454) and 19.10 OCW part (32222571) for database.

./runInstaller -applyRU /u01/patches/32226239/32218454 
  -applyOneOffs /u01/patches/32226239/32222571

Then I copied the home to the other node as requested in the docs:

$ORACLE_HOME/addnode/addnode.sh -silent CLUSTER_NEW_NODES=yellow

Then I created a database with dbca. I also removed “local_listener” parameter to allow a dynamic configuration by agent which you can see quite good in alert log, whenever you restart or relocate such a database.
Finally I added the second node to the resource configuration and created a service:

srvctl modify database -db seha -node blue,yellow
srvctl add service -db seha -service seha_svc

To check the FAN events created by this configuration I setup 2 things.

  1. a simple FAN call out script in /racg/usrco
    with following contents:
#!/bin/sh
echo $(date) >> /u00/app/grid/product/19/racg/log/fan_logger.log
echo ${*} >> /u00/app/grid/product/19/racg/log/fan_logger.log
  1. I downloaded and configured FANwatcher as proposed by Markus Michalewicz.
    See https://www.oracle.com/technetwork/database/options/clustering/overview/fastapplicationnotification12c-2538999.pdf
    and https://blogs.oracle.com/weblogicserver/monitoring-fan-events
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME/opmn/lib/ons.jar
$ORACLE_HOME/jdk/bin/javac fanWatcher.java
export user=system
export password=ItIsConfidential4Sure
# sweden is the name of the cluster and also name for SCAN, db_domain is trivadistraining.com
export url="jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp)(host=sweden)(port=1521)))(connect_data=(service_name=seha_svc.trivadistraining.com)))"
$ORACLE_HOME/jdk/bin/java fanWatcher autoons

And this is what happens when I run a relocate of my database resource using

srvctl relocate database -db seha -node yellow
  1. FAN call out script results
    on first node “blue” 4 down events for service member, service, database and instance:
Sun Feb 28 14:34:02 CET 2021
SERVICEMEMBER VERSION=1.0 service=seha_svc.trivadistraining.com database=seha instance=SEHA host=blue status=down reason=USER timestamp=2021-02-28 14:34:02 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:02 CET 2021
SERVICE VERSION=1.0 service=seha_svc.trivadistraining.com database=seha instance=SEHA host=blue status=down reason=USER timestamp=2021-02-28 14:34:02 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:22 CET 2021
DATABASE VERSION=1.0 service=seha.trivadistraining.com database=seha instance= host=blue status=down reason=USER timestamp=2021-02-28 14:34:22 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:22 CET 2021
INSTANCE VERSION=1.0 service=seha.trivadistraining.com database=seha instance=SEHA host=blue status=down reason=USER timestamp=2021-02-28 14:34:22 timezone=+01:00 db_domain=trivadistraining.com

on second node “yellow” for instance, database, service member and service:

Sun Feb 28 14:34:48 CET 2021
INSTANCE VERSION=1.0 service=seha.trivadistraining.com database=seha instance=SEHA host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:48 CET 2021
DATABASE VERSION=1.0 service=seha.trivadistraining.com database=seha instance= host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:49 CET 2021
SERVICEMEMBER VERSION=1.0 service=seha_svc.trivadistraining.com database=seha instance=SEHA host=yellow status=up reason=USER card=1 timestamp=2021-02-28 14:34:48 timezone=+01:00 db_domain=trivadistraining.com
Sun Feb 28 14:34:49 CET 2021
SERVICE VERSION=1.0 service=seha_svc.trivadistraining.com database=seha instance=SEHA host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00 db_domain=trivadistraining.com

2. Output of fanWatcher java program reveals the same 8 events:

oracle@blue:~/ [SEHA] $ORACLE_HOME/jdk/bin/java fanWatcher autoons
Subscribing to events of type:
Auto-ONS configuration=maxconnections.0001=0003
nodes.0001=SWEDEN:6200
Opening FAN Subscriber Window ...




** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:02 CET 2021
Creation Time: Sun Feb 28 14:34:02 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=SERVICEMEMBER service=seha_svc.trivadistraining.com instance=SEHA database=seha db_domain=trivadistraining.com host=blue status=down reason=USER timestamp=2021-02-28 14:34:02 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:02 CET 2021
Creation Time: Sun Feb 28 14:34:02 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=SERVICE service=seha_svc.trivadistraining.com database=seha db_domain=trivadistraining.com host=blue status=down reason=USER timestamp=2021-02-28 14:34:02 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:22 CET 2021
Creation Time: Sun Feb 28 14:34:22 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=INSTANCE service=seha.trivadistraining.com instance=SEHA database=seha db_domain=trivadistraining.com host=blue status=down reason=USER timestamp=2021-02-28 14:34:22 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:22 CET 2021
Creation Time: Sun Feb 28 14:34:22 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=DATABASE service=seha.trivadistraining.com database=seha db_domain=trivadistraining.com host=blue status=down reason=USER timestamp=2021-02-28 14:34:22 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:48 CET 2021
Creation Time: Sun Feb 28 14:34:48 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=INSTANCE service=seha.trivadistraining.com instance=SEHA database=seha db_domain=trivadistraining.com host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:48 CET 2021
Creation Time: Sun Feb 28 14:34:48 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=DATABASE service=seha.trivadistraining.com database=seha db_domain=trivadistraining.com host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:49 CET 2021
Creation Time: Sun Feb 28 14:34:49 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=SERVICEMEMBER service=seha_svc.trivadistraining.com instance=SEHA database=seha db_domain=trivadistraining.com host=yellow status=up card=1 reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00

** Event Header **
Notification Type: database/event/service
Delivery Time: Sun Feb 28 14:34:49 CET 2021
Creation Time: Sun Feb 28 14:34:49 CET 2021
Generating Node: yellow.trivadistraining.com
Event payload:
VERSION=1.0 event_type=SERVICE service=seha_svc.trivadistraining.com database=seha db_domain=trivadistraining.com host=yellow status=up reason=USER timestamp=2021-02-28 14:34:48 timezone=+01:00

So yes FAN and ONS work perfectly with this new SEHA setup. Hence we can also enable Fast Connection Failover / FCF in our applications with Universal Connection Pool / UCP.

Thanks for reading
Mathias