High Availability in PHP OCI8 with Oracle Database Application Continuity

This is a demonstration of how Oracle Database’s Application Continuity feature helps PHP OCI8 applications continue running smoothly during unexpected connection and database outages. Users remain unaware of issues. No complex application error handling logic is needed.

Christopher Jones

Published in

Oracle Developers

5 min read6 days ago

Introduction

Oracle Database’s Application Continuity feature reduces the incidence of application errors by automatically reconnecting and replaying interrupted, in-flight transactions after a database connectivity outage. It restores application state seamlessly. AC masks hardware, software, network, storage errors, and timeouts. Applications continue running and users are unaware of outages. The application’s code doesn’t need unnecessary, complex recovery logic. AC, and its sibling Transparent Application Continuity, are usable with various configurations of Oracle Database, such as RAC and Oracle Autonomous Database.

AC and TAC are supported by many language drivers including PHP OCI8.

Application Continuity is recommended for OLTP applications that use an Oracle Database driver pool (such as the Oracle Call Interface session pool), or for apps that that provide explicit request boundaries. Transparent Application Continuity (TAC) is a functional mode of Application Continuity that doesn’t need the application to use an Oracle session pool. It transparently tracks, and records, sessions and transactional states.

For applications that do use an Oracle Database driver pool, it is your choice whether to use AC or TAC.

To understand all the differences between AC and TAC, and see the best practice information and fine print, check the references at the end of this post.

Demo

Following my earlier blog post Oracle Application Continuity — for continuous availability, I enabled TAC on the “high” service of my Oracle Autonomous Database:

$ sqlplus -l admin@'tcps://adb.melb.oraclecloud.com:1522/abc_cjmtls_high.adb.oraclecloud.com?wallet_location=/Users/cjones/alwaysfree/CJMTLS'

SQL> execute dbms_app_cont_admin.enable_tac('abc_cjmtls_high.adb.oraclecloud.com', 'AUTO', 600);

And checked it was enabled:

SQL> set pagesize 1000 linesize 150
SQL> col name format a60
SQL> col failover_type format a15
SQL> select name, failover_type from dba_services;

NAME                                                         FAILOVER_TYPE
------------------------------------------------------------ ---------------
ABC_CJMTLS_tp.adb.oraclecloud.com
ABC_CJMTLS_high.adb.oraclecloud.com                          AUTO
ABC_CJMTLS_medium.adb.oraclecloud.com
ABC_CJMTLS_low.adb.oraclecloud.com
ABC_CJMTLS_tpurgent.adb.oraclecloud.com
ABC_CJMTLS

In real life you may prefer to enable it on your ‘TP’ service, per the documentation.

The PHP OCI8 Application

The full PHP app I used is available as a GitHub gist here.

It first prints out the connection’s unique session ID and serial number as a handy “kill” statement that a DBA can use to destroy the connection. This interruption to the running application is how the demo simulates an unplanned connectivity outage:

// Display the SQL that an administrator can run to kill the connection
$killsql = "select unique 'alter system kill session '''||sid||','||serial#||''';'||'' from v\$session_connect_info where sid = sys_context('USERENV', 'SID')";
$s = oci_parse($c, $killsql);
oci_execute($s);
$r = oci_fetch_row($s);
print("While this script is running, use SQL*Plus to execute:\n   ".$r[0]. "\n");

For example this might print:

While this script is running, use SQL*Plus to execute:
  alter system kill session '16198,41975';

The main code loop inserts some data, shows the connection’s current unique session ID and serial number, and then sleeps for a couple of seconds before committing:

for ($i = 1; $i <= 10; $i++) {

    $data = "a" . $i;
    $s1 = oci_parse($c, "insert into demo (username) values(:un)");
    oci_bind_by_name($s1, ":un", $data);
    oci_execute($s1, OCI_NO_AUTO_COMMIT);

    // Show the unique session ID and serial number that identify this connection
    $sidsql = "select unique sid || '-' || serial# from v\$session_connect_info where sid = sys_context('USERENV','SID')";
    $s2 = oci_parse($c, $sidsql);
    oci_execute($s2);
    $r = oci_fetch_row($s2);
    print("SID-serial#: ". $r[0]. " inserted data ". $i . "\n");

    sleep(2);

    oci_commit($c);
}

The table had been created as:

create table demo (id number generated by default as identity,
                   username varchar2(40));

With TAC

I first edited the app to use the database service that has TAC enabled:

$service = 'high'; // TAC

I then ran the app:

$ php tac-php.php

This displayed the kill SQL mentioned above, and then it started inserting data:

Using service: high
While this script is running, use SQL*Plus to execute:
  alter system kill session '16198,41975';
SID-serial#: 16198-41975 inserted data a1
SID-serial#: 16198-41975 inserted data a2
SID-serial#: 16198-41975 inserted data a3
SID-serial#: 16198-41975 inserted data a4
SID-serial#: 16198-41975 inserted data a5

I let it continue for a few insertions as shown. Then in a separate window running SQL*Plus as the privileged user ADMIN, I executed the displayed SQL statement to kill the PHP connection in an attempt to cause the app to fail:

SQL> alter system kill session '16198,41975';

But back in the terminal where PHP was running, all I saw was the SID and serial number of the connection change from 16198–41975 to 21130–3914 while the app continued to run just fine. There was no error thrown. Eventually the app completed correctly by showing the query output to prove all 10 rows had been inserted:

SID-serial#: 21130-3914 inserted data a6
SID-serial#: 21130-3914 inserted data a7
SID-serial#: 21130-3914 inserted data a8
SID-serial#: 21130-3914 inserted data a9
SID-serial#: 21130-3914 inserted data a10
The data inserted was:
a1
a2
a3
a4
a5
a6
a7
a8
a9
a10

This showed TAC successfully and gracefully handled the database “failure”. Oracle TAC had been recording the SQL statements being executed and it was then able to internally reconnect and replay any outstanding database operations after the first connection was killed. The user wasn’t aware there was a problem. The application didn’t need to have any special reconnection and data retry logic.

Without AC or TAC

To double check the application, I edited the file to use the ‘low’ service that did not have AC or TAC enabled:

$service = 'low'; // No AC or TAC

I re-ran the app:

$ php tac-php.php

Using service: low
While this script is running, use SQL*Plus to execute:
  alter system kill session '11981,61556';
SID-serial#: 11981-61556 inserted data a1
SID-serial#: 11981-61556 inserted data a2
SID-serial#: 11981-61556 inserted data a3
SID-serial#: 11981-61556 inserted data a4
SID-serial#: 11981-61556 inserted data a5

After a few iterations, I ran the kill statement in SQL*Plus:

SQL> alter system kill session '11981,61556';

And this time the PHP app immediately terminated with various errors due to the dead connection:

Warning: oci_commit(): ORA-03113: end-of-file on communication channel
Process ID: 64045
Session ID: 11981 Serial number: 61556
Help: https://docs.oracle.com/error-help/db/ora-03113/ in tac-php.php on line 166

Game over! Unhappy users!

Summary

AC and TAC help protect applications from unplanned database node or connection failures. Users are unaware of the issues that they handle. Developers don’t need to code data retry logic to redo incomplete transactions. Since PHP OCI8 uses the Oracle Call Interface session pool, it supports AC and TAC.

The following references should be consulted for more information about Oracle High Availability solutions.