Fast Application Notification (FAN) – JDBC HA – part 3

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

Fast Application Notification (FAN) require you build a Real Application Cluster (RAC) environment. You cannot use the classical Operating System cluster active/passive. So a bit more work…

Fast Application Notification (FAN) testing

Even if I have found this in Oracle official documentation:

Although the Oracle JDBC drivers now support the FAN events, Oracle UCP provides more comprehensive support for all FAN events.

I have always wondered the usage of this simplefan.jar file available for download on Java download page. So testing JDBC with this JAR file should be the most basic FAN testing.

The complete reference of the API is available at Oracle® Database RAC FAN Events Java API Reference 12c Release 2 (12.2).

Service setup

I have struggled a lot trying to use the internal service that is created with every pluggable database (even if you do not have the multitenant paid option and so a single pluggable database). This is simply not working and you must create a new one with the Grid infrastructure part of your RAC installation.

This has triggered a new problem when I have stupidly created this service with the same name as the internal one (pdb1), unbelievable that there is no control on this please see issues encountered section to overcome the situation if you face it.

I have chosen to create my RAC cluster with now to be used server pool policy so creating a FAN aware service with (-pdb to specify the pluggable database to be used or you end up in container one and obviously name MUSt NOT be pdb1 so using pdb1srv):

[oracle@server3 ~]$ srvctl add service -db orcl -pdb pdb1 -service pdb1srv -notification TRUE -serverpool server_pool01 -failovertype SELECT
[oracle@server2 ~]$ crsctl stat res ora.orcl.pdb1srv.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.orcl.pdb1srv.svc
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@server2 ~]$ srvctl start service -db orcl -service pdb1srv
[oracle@server2 ~]$ crsctl stat res ora.orcl.pdb1srv.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.orcl.pdb1srv.svc
      1        ONLINE  ONLINE       server2                  STABLE
      2        ONLINE  ONLINE       server3                  STABLE
--------------------------------------------------------------------------------

You can also check to any SCAN listener that it is taken into account:

[oracle@server2 ~]$ lsnrctl status listener_scan1
 
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 22-FEB-2018 11:29:44
 
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                20-FEB-2018 12:57:51
Uptime                    1 days 22 hr. 31 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/server2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.121)(PORT=1531)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "65188d13fadb4a7be0536638a8c0aa34" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "651a2f85336128cde0536638a8c0ff54" has 2 instance(s).
  Instance "orcl_1", status READY, has 1 handler(s) for this service...
  Instance "orcl_2", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "gimr_dscrep_10" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl_1", status READY, has 1 handler(s) for this service...
  Instance "orcl_2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 2 instance(s).
  Instance "orcl_1", status READY, has 1 handler(s) for this service...
  Instance "orcl_2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
  Instance "orcl_1", status READY, has 1 handler(s) for this service...
  Instance "orcl_2", status READY, has 1 handler(s) for this service...
Service "pdb1srv" has 2 instance(s).
  Instance "orcl_1", status READY, has 1 handler(s) for this service...
  Instance "orcl_2", status READY, has 1 handler(s) for this service...
The command completed successfully

If you like to drop and recreate a new one with different option use:

[oracle@server2 ~]$ srvctl stop service -db orcl -service pdb1srv
[oracle@server3 ~]$ srvctl remove service -db orcl -service pdb1srv

Oracle Notification Service (ONS) setup

One of the prerequisite of FAN is Oracle Notification Service (ONS), the documentation says you must have it on node where JDBC is running but you can have it on each node of your RAC cluster using a feature called auto-ONS. Luckily with my fresh 12cR2 installation ONS is already configured and running (I just had to correct nodes value on second node where first server was missing. Don’t mess up with all config files located on $GRID_HOME/opmn/conf directory the one to use is the one displayed in ONS log file ($GRID_HOME/opmn/logs/ons.log.server2 for me). Means the file to modify for me IS NOT ons.config but ons.config.server2:

[2018-02-23T17:49:42+01:00] [ons] [TRACE:32] [] [ons-local] Reloading by request
[2018-02-23T17:49:45+01:00] [ons] [TRACE:32] [] [ons-local] Config file: /u01/app/12.2.0/grid/opmn/conf/ons.config.server2

After correction I end up with a file like:

[oracle@server2 ~]$ cat $ORACLE_HOME/opmn/conf/ons.config.server2
usesharedinstall=true
allowgroup=true
localport=6100          # line added by Agent
remoteport=6200         # line added by Agent
nodes=server2.domain.com:6200,server3.domain.com:6200
walletfile=/u01/app/grid/crsdata/server2/onswallet/             # line added by Agent
allowunsecuresubscriber=true            # line added by Agent

Check all is running fine with:

[oracle@server2 ~]$ onsctl ping
ons is running ...
[oracle@server2 ~]$ onsctl debug
[2018-02-28T12:31:12+01:00] [ons] [ERROR:1] [] [ons-local] /u01/app/12.2.0/grid/opmn/conf/ons.config.server2: 5: (warning) unkown key: ocrnodename
[2018-02-28T12:31:12+01:00] [ons] [ERROR:1] [] [ons-local] /u01/app/12.2.0/grid/opmn/conf/ons.config.server2: 5: (warning) unkown key: ocrnodename
HTTP/1.1 200 OK
Connection: close
Content-Type: text/html
Response:
 
== server2.domain.com:6200 9006 18/02/28 12:31:11 ==
Build: ONS_12.2.0.1.0_LINUX.X64_161121.1010 2016/11/21 19:53:59 UTC
Home: /u01/app/12.2.0/grid
 
======== ONS ========
 
           IP ADDRESS                   PORT    TIME   SEQUENCE  FLAGS
--------------------------------------- ----- -------- -------- --------
                         192.168.56.102  6200 5a9530ac 0000000d 00000008
 
Listener:
 
  TYPE                BIND ADDRESS               PORT  SOCKET
-------- --------------------------------------- ----- ------
Local                                        ::1  6100      6
Local                                  127.0.0.1  6100      7
Remote                                       any  6200      8
Remote                                       any  6200      -
 
Servers: (1)
 
            INSTANCE NAME                  TIME   SEQUENCE  FLAGS     DEFER
---------------------------------------- -------- -------- -------- ----------
dbInstance_server3.domain.com_6200       5a953161 00000003 00000002          0
                          192.168.56.103 6200
 
Connection Topology: (2)
 
                IP                      PORT   VERS  TIME
--------------------------------------- ----- ----- --------
                         192.168.56.103  6200     4 5a953161
                           **                          192.168.56.102 6200
                         192.168.56.102  6200     4 5a9530ac=
                           **                          192.168.56.103 6200
 
Server connections: (1)
 
   ID            CONNECTION ADDRESS              PORT  FLAGS  SNDQ REF PHA ACK
-------- --------------------------------------- ----- ------ ---- --- --- ---
       d                          192.168.56.103  6200 2004a6    0   1  IO   0
 
Client connections: (10)
 
   ID            CONNECTION ADDRESS              PORT  FLAGS  SNDQ REF PHA SUB
-------- --------------------------------------- ----- ------ ---- --- --- ---
       0                                internal     0 00044a    0   1  IO   1
       2                                     ::1 56365 20041a    0   1  IO   1
       3                                     ::1 56621 20041a    0   1  IO   1
       4                                     ::1 56877 20041a    0   1  IO   1
       5                                     ::1 57133 20041a    0   1  IO   1
       9                                     ::1 59949 20041a    0   1  IO   0
       a                                     ::1 60717 20041a    0   1  IO   1
     5f2                     ::ffff:192.168.56.1  1237 26042a    0   1  IO   2
     5f1                     ::ffff:192.168.56.1   725 26042a    0   1  IO   2
 request                                     ::1 29026 200e1a    0   1  IO   0
 
Events:
 
  Flags 00000000 Processed 17842
  Threads:
    Total 3 Idle 3
    Last started: 18/02/27 11:19:24
 
AIO:
 
  Sockets 9 Events 0 Waiters 3 Timers 2 Flags 00000000
  Threads:
    Total 3 Idle 3
    Last started: 18/02/27 11:19:24
 
Resources:
 
  Notifications:
    Received: Total 17 (local 13 internal 1)
              Queued 0 (threads 0, flags 00000000)
 
  Blocks:
    mLink   : 5000/5000 blocks 1
    subMatch: 10000/10000 blocks 1
    event   : 5000/5000 blocks 1

Java testing code

The Oracle official documentation is not super clear on this part and it is not possible to find a clear working example. The idea behind simplefan.jar library and classes is to register a listener that will listen on particular event. Currently only three are supported:

  • LoadAdvisoryEvent
  • NodeDownEvent
  • ServiceDownEvent

As I am not using a pool the LoadAdvisoryEvent has not raised for me. Then to be honest I did not know in advance which one would raise from the NodeDownEvent or ServiceDownEvent events in case I kill the used instance by my java program. At the end this is the ServiceDownEvent event that has raised…

You need to add to your project (classpath) ojdbc8.jar, ons.jar and simplefan.jar.

package fan01;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
import oracle.simplefan.FanEventListener;
import oracle.simplefan.FanManager;
import oracle.simplefan.FanSubscription;
import oracle.simplefan.LoadAdvisoryEvent;
import oracle.simplefan.NodeDownEvent;
import oracle.simplefan.ServiceDownEvent;
 
public class fan01 {
  static Integer connection_status = 0;
  // Make Oracle connection and return a connection object
  private static Connection oracle_connection() throws Exception {
    Connection connection1 = null;
    OracleDataSource ods1 = new OracleDataSource();
 
    try {
      ods1.setUser("yjaquier");
      ods1.setPassword("secure_password");
      ods1.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-cluster-scan.domain.com)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=pdb1srv)))");
      connection1 = ods1.getConnection();
    }
    catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      return null;
    }
    System.out.println("Connected to Oracle database...");
    return connection1;
  }
 
  public static void main(String[] args) throws Exception {
    Connection connection1 = null;
    String query1 = null;
    ResultSet resultset1 = null;
    Properties props = new Properties();
    Properties onsProps = new Properties();
    FanManager fanMngr = FanManager.getInstance();
 
 
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    }
    catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC driver ?");
      e.printStackTrace();
      System.exit(1);
    }
    System.out.println("Oracle JDBC Driver Registered!");
 
    connection1=oracle_connection();
    if (connection1==null) {
      System.exit(1);
    }
 
    props.put("serviceName", "pdb1srv");
    onsProps.setProperty("onsNodes", "server2.domain.com:6200,server3.domain.com:6200");
    fanMngr.configure(onsProps);
    FanSubscription sub = fanMngr.subscribe(props);
    sub.addListener(new FanEventListener() {
      public void handleEvent(ServiceDownEvent event) {
        System.out.println("Service down event registered !");
        connection_status=1;
      }
      public void handleEvent(NodeDownEvent event) {
        System.out.println("Node down event registered !");
        connection_status=2;
      }
      public void handleEvent(LoadAdvisoryEvent event) {
        System.out.println("Load advisory event registered !");
        connection_status=3;
      }
    });
 
    query1="select HOST_NAME from v$instance";
    while (true) {
      System.out.println("\nStatus at "+LocalDateTime.now().format(DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss"))+": ");
      if (connection1!=null) {
        try {
          resultset1 = connection1.createStatement().executeQuery(query1);
          while (resultset1.next()) {
            System.out.println("Server used: "+resultset1.getString(1));
          }
        }
        catch (SQLException e1) {
          System.out.println("Query has failed...");
          if (connection_status != 0) {
            connection1.close();
            connection1 = null;
            while (connection1 == null) {
              try {
                connection1=oracle_connection();
              } catch (Exception e2) {
                e2.printStackTrace();
              }
            }
            connection_status = 0;
          }
        }
      }
      Thread.sleep(2000);
    }
    //resultset1.close();
    //connection1.close();
  }
}

Once the program is connected I have killed the pmon of the instance where the program connected. We clearly the ServiceDownEvent event raised and the re-connection handle by my Java program with no applicative failure:

fan01
fan01

Issues encountered

Default pluggable database service destroyed

If by mistake or lack of knowledge (my case) you try to play with default pluggable database service (DBMS_SERVICE package) you end up with a very particular situation where you cannot start your pluggable database when it is down:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ALTER pluggable DATABASE pdb1 CLOSE IMMEDIATE;
 
Pluggable DATABASE altered.
 
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 ALTER pluggable DATABASE pdb1 OPEN
*
ERROR AT line 1:
ORA-44304: service  does NOT exist
ORA-44777: Pluggable DATABASE service cannot be started.
 
SQL> SELECT con_id, name FROM v$services;
 
    CON_ID NAME
---------- ----------------------------------------------------------------
         1 orclXDB
         1 orcl
         1 SYS$BACKGROUND
         1 SYS$USERS
 
SQL> SELECT name FROM dba_services;
 
NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
orclXDB
orcl

And if the pluggable database is up and running you cannot connect to it:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ALTER SESSION SET container=pdb1;
ERROR:
ORA-44787: Service cannot be switched INTO.
 
SQL> ALTER SESSION SET container=pdb1;
ALTER SESSION SET container=pdb1
*
ERROR AT line 1:
ORA-03113: end-of-FILE ON communication channel
Process ID: 11524
SESSION ID: 54 Serial NUMBER: 53333

Situation and way to recover it as been well explained in Mike Dietrich and William Sescu blogs:

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

3 thoughts on “Fast Application Notification (FAN) – JDBC HA – part 3”

  1. hyunsuk says:

    hi~~

    Could you teach me, how can execute ???

    —————————————-
    $ export PATH=.:/u01/app/orascm/product/19C/jdk/bin:$PATH
    $ export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/simplefan.jar:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME//opmn/lib/ons.jar:$CLASSPATH
    $ cd /home/orascm/hsnoh/fantest
    $ rm fan01.class
    $ javac fan01.java
    $ ls
    fan01$1.class fan01.class fan01.java

    —————————————-
    $ java fan01
    Error: Could not find or load main class fan01

    $ java fan01.fan01
    Error: Could not find or load main class fan01.fan01

    Thanks..

    • hyunsuk says:

      hi~~~ After compiling as follows, it ran without any problems.

      export PATH=.:/u01/app/orascm/product/19C/jdk/bin:$PATH
      export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/simplefan.jar:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME//opmn/lib/ons.jar:$CLASSPATH
      cd /home/orascm/hsnoh/fantest
      rm -rf fan01 *.class
      javac -d . fan01.java
      java fan01.fan01
      <— No error….

      Thanks…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>