12c Application Continuity with Data Guard in action

Introduction

Application Continuity enables replay, in a non-disruptive and rapid manner, of a database request when a recoverable error makes the database session unavailable. Application Continuity uses Transaction Guard. You can find more details on those features here.

With RAC, Application Continuity can be used to launch the replay on another Instance. It can also be used with Data Guard under some circumstances and conditions (Extract from here):

Screen Shot 2015-05-07 at 08.52.52

Well, I just want to see this feature in action during a Data Guard switchover. Let’s give it a try.

Test preparation

The test environment is made of:

  • NBDT database (12.1.0.2 and initially primary).
  • SNBDT database (12.1.0.2 and initially standby).

On the primary database, connected as user bdt, let’s create the following tables:

SQL> create table BDT_CONT as select rownum num,rpad('U',20) val from dual connect by level <= 500000;

Table created.

SQL> create table BDT_SPY (d date, instance_name varchar2(16), num number);

Table created.

And the following trigger:

create or replace trigger BDT_CONT_SPY_T
before update
on BDT_CONT
for each row
begin
insert into BDT_SPY values(sysdate,sys_context('USERENV', 'INSTANCE_NAME'), :old.num);
end;
/

Trigger created.

The test application will update the 500000 rows of the BDT_CONT table in one transaction. The aim of the trigger is to track on which Instance the update has been successfully executed .

Now let’s create a service on the primary and standby databases that way:

# Primary
$ srvctl add service -d NBDT -s appco -preferred NBDT1,NBDT2 -l primary -policy automatic -j SHORT -B SERVICE_TIME -z 30 -w 10 -commit_outcome TRUE -e TRANSACTION -replay_init_time 1800 -retention 86400 -notification TRUE

# Standby
$ srvctl add service -d SNBDT -s appco -preferred SNBDT1,SNBDT2  -l primary -policy automatic -j SHORT -B SERVICE_TIME -z 30 -w 10 -commit_outcome TRUE -e TRANSACTION -replay_init_time 1800 -retention 86400 -notification TRUE

So that the appco service:

  • Is automatically started on the standby if it becomes primary.
  • Is defined for Application Continuity (see more details here on how to configure a service for Application Continuity).

Now let’s create a JAVA application that will be used to test the Application Continuity:

import java.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
import oracle.jdbc.replay.*;

public class AppCont {
    public String getInstanceName(Connection conn) throws SQLException {
        PreparedStatement preStatement = conn.prepareStatement("select instance_name from v$instance");
        String r=new String();
        ResultSet result = preStatement.executeQuery();

        while (result.next()){
            r=result.getString("instance_name");
        }
        return r;
    }

    public static void main(String args[]) throws SQLException {
         Connection conn = null;
         Statement stmt = null;
         try {
              // OracleDataSourceImpl instead of OracleDataSource.
              OracleDataSourceImpl ocpds = new OracleDataSourceImpl();
              // Create the database URL
              String dbURL =
              "jdbc:oracle:thin:@(DESCRIPTION_LIST="+
              "(LOAD_BALANCE=off)"+
              "(FAILOVER=on)"+
              "(DESCRIPTION=(CONNECT_TIMEOUT=90) (RETRY_COUNT=10)(RETRY_DELAY=3)"+
              "(ADDRESS_LIST="+
              "(LOAD_BALANCE=on)"+
              "(ADDRESS=(PROTOCOL=TCP) (HOST=rac-cluster-scan)(PORT=1521)))"+
              "(CONNECT_DATA=(SERVICE_NAME=appco)))"+
              "(DESCRIPTION=(CONNECT_TIMEOUT=90) (RETRY_COUNT=10)(RETRY_DELAY=10)"+
              "(ADDRESS_LIST="+
              "(LOAD_BALANCE=on)"+
	      "(ADDRESS=(PROTOCOL=TCP)(HOST=srac-cluster-scan)(PORT=1521)))"+
              "(CONNECT_DATA=(SERVICE_NAME=appco))))";

              ocpds.setURL(dbURL);
              ocpds.setUser("bdt");
              ocpds.setPassword("bdt");

              // Get a connection
              conn = ocpds.getConnection();

              // Instantiation
              AppCont ex = new AppCont();

              // On which Instance are we connected?
              System.out.println("Instance Name = "+ex.getInstanceName(conn));

              System.out.println("Performing transaction");
              /* Don't forget to disable autocommit
	       * And launch the transaction
	       */
              ((oracle.jdbc.replay.ReplayableConnection)conn).beginRequest();
              conn.setAutoCommit(false);
              stmt = conn.createStatement();
              String updsql = "UPDATE BDT_CONT " +
                              "SET val=UPPER(val)";
              stmt.executeUpdate(updsql);
              conn.commit();
              // End
              ((oracle.jdbc.replay.ReplayableConnection)conn).endRequest();

	      stmt.close();

              System.out.println("Instance Name = "+ex.getInstanceName(conn));

              // On which Instance are we connected?
              conn.close();
         }
         catch (Exception e) {
              e.printStackTrace();
        }
    }
}

The important parts are:

  • The use of OracleDataSourceImpl instead of OracleDataSource.
  • The database URL definition that includes the access to the appco service for both the primary and standby databases (NBDT is hosted on “rac-cluster” and SNBDT is hosted on “srac-cluster”).
  • Autocommit is disabled (Read this if you need more details).

We are ready to launch the application and test the Application Continuity in a Data Guard context.

Run it

First let’s check the status of the data guard configuration and that it is ready for the switchover:

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/toto@NBDT
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - nbdt_dr

  Protection Mode: MaxPerformance
  Members:
  nbdt  - Primary database
    snbdt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 24 seconds ago)

DGMGRL> validate database SNBDT;

  Database Role:     Physical standby database
  Primary Database:  nbdt

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    nbdt:   Off
    snbdt:  Off

Launch the application

$ cat launch.ksh
export CLASSPATH=/u01/app/oracle/product/12.1.0.2/jdbc/lib/ojdbc7.jar:.
time java -cp $CLASSPATH:. AppCont
$ ksh ./launch.ksh
Instance Name = NBDT1
Performing transaction

At this stage, launch the switchover (from another terminal):

DGMGRL> switchover to snbdt;
Performing switchover NOW, please wait...
Operation requires a connection to instance "SNBDT1" on database "snbdt"
Connecting to instance "SNBDT1"...
Connected as SYSDBA.
New primary database "snbdt" is opening...
Oracle Clusterware is restarting database "nbdt" ...
Switchover succeeded, new primary is "snbdt"

And then wait until the application ends:

$ ksh ./launch.ksh
Instance Name = NBDT1
Performing transaction
.
.   <= Switchover occurred here
.
Instance Name = SNBDT1

real	1m46.19s
user	0m1.33s
sys	0m0.13s

As we can see:

  • No errors have been reported.
  • The application terminated on the SNBDT1 instance (Then on the new primary database).

Let’s check (thanks to the spy table) on which Instance did the transaction commit:

$ sqlplus bdt/bdt@SNBDT

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 7 10:04:54 2015

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

Last Successful login time: Thu May 07 2015 09:59:05 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select distinct(instance_name) from bdt_spy;

INSTANCE_NAME
----------------
SNBDT1

As we can see the transaction has been replayed and did commit on the SNBDT database (The new primary database).

Remarks

  1. Application Continuity is supported for Oracle Data Guard switchovers to physical standby databases. It is also supported for fast-start failover to physical standbys in maximum availability data protection mode.
  2. Don’t forget that the primary and standby databases must be licensed for Oracle RAC or Oracle Active Data Guard in order to use Application Continuity.
  3. The bdt oracle user that has been used during the test, has grant execute on DBMS_APP_CONT.
  4. This Java Application has been created and shared by Laurent Leturgez during the Paris Oracle Meetup. User groups are always good, as you can share your work with others. Thanks again Laurent!

Conclusion

We have seen the Application Continuity feature in Action in a Data Guard context.

Advertisements

3 thoughts on “12c Application Continuity with Data Guard in action

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s