Thursday, February 11, 2010

Creating a Highly Available Environment for APEX Application in an Oracle Real Application Clusters

In Oracle 11g Oracle Application Express (APEX) is now a standard component in the Oracle 11g RDBMS.
The APEX component is intergration with the Oracle Database and ensures that the application builts with Oracle APEX
benefit from the reliability, security, scalability and availability offered by the Oracle database.
Oracle Real Application Cluster (RAC) is recommended for applications that require High Availability (HA), scalability,
and workload management. How can we intergrate the High Availability (HA), scalability,
and workload management functionality to the APEX module in a RAC environment
In this blog I descibe, how to use and configure APEX module in a RAC database environment.
In a normal High Availability for database enviroment is the Oracle HTTP server an unprotected feature by the Oracle Clusterware.

Installing Oracle Application Express (APEX) command line in a RAC database environment

Login on one of the RAC instances as sysdba user.
Verify that the instance is part of the RAC.
select instance from v$thread;
Start the installation of Oracle Application Express the script is located in the $ORACLE_HOME/apex directory.
@apexins sysaux sysaux temp /i/ 
Connect via second instance and verify the FLOWS_030100 user was created.
SELECT username FROM dba_users WHERE username='FLOW_030100';

Configure the Oracle RAC Service APX_RAC

Create a database service "APX_RAC" for our database "RAC_DB" on nodes "wh_server1" and "wh_server2"; srvctl add service -d RAC_DB -s APX_RAC -r wh_server1,wh_server2 -P BASIC
Start the database service "APX_RAC"; srvctl start service -d RAC_DB -s APX_RAC
Validate the service on the database; srvctl status service -d RAC_DB; Service APX_RAC is running on instance(s) wh_server1, wh_server2
and validate with a select statement;
select name,service_id from dba_services where name = 'APX_RAC';
NAME            SERVICE_ID
--------------- ----------
APX_RAC                  9

RAC configuration with the service APX_RAC; select SERVICE_ID, NAME, FAILOVER_METHOD, FAILOVER_TYPE, CLB_GOAL from dba_services where name = 'APX_RAC';

---------- ----------- ------------------- -------------------- -----
         9 APX_RAC                                               LONG

Set the failover attributes for node side TAF policies APX_RAC; execute dbms_service.modify_service ( service_name => 'APX_RAC', goal => DBMS_SERVICE.GOAL_SERVICE_TIME, clb_goal => dbms_service.CLB_GOAL_SHORT, failover_method => dbms_service.FAILOVER_METHOD_BASIC, failover_type => dbms_service.FAILOVER_TYPE_SESSION, failover_retries => 3, failover_delay => 5, aq_ha_notifications => true );

Validate the updated service; select SERVICE_ID, NAME, FAILOVER_METHOD, FAILOVER_TYPE, CLB_GOAL from dba_services where name = 'APX_RAC';

---------- ----------- ------------------- -------------------- -----
         9 APX_RAC     BASIC               SESSION              SHORT

Oracle MOD_PLSQL Extension

The mod_plsql extension maintains a pool of connections to the database.
If there is no reponse from the database connection in the connection pool, mod_plsql detect this,
discard the dead connection, and creates a fresh database connection for requests.
By default, when an Oracle RAC node or database Instance goes down and mod_plsql had previously pooled connections to the node
The failure "HTTP-503" error is then used by mod_plsql to trigger the detection and removal all dead connections in its pool.
There is no configuration for the enviroment as this extension is shipped with the Oracle HTTP Server

The DAD configuration in the Oracle HTTP Server

In this example we choose to install the Oracle HTTP server on either one of the RAC Nodes.
Conform the RAC database the HTTP server must also be configured for High-Availably.
We do this by installing two or more Oracle HTTP Service on two or more physical servers, with a traffic load balanced between the nodes by a load balancing router (LBR).

How to Configurate the DAD for Oracle HTTP Server (dads.conf)

  Order                                      deny, allow
  PlsqlDocumentPath                          docs
  AllowOverride                              None
  PlsqlDocumentProcedure                     wwv_flow_file_mgr.process_download
  PlsqlDatabaseConnectString                 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1-vip)(PORT = 1526))
                                                          (ADDRESS=(PROTOCOL=TCP)(HOST=server2-vip)(PORT = 1526))
                                                          (CONNECT_DATA=(SERVER = DEDICATED)
                ( TNSNamesFormat
  PlsqlNLSLanguage                           AMERICAN_AMERICA.AL32UTF8
  PlsqlAuthenticationMode                    Basic
  SetHandler                                 pls_handler
  PlsqlDocumentTablename                     wwv_flow_file_objects$
  PlsqlDatabaseUsername                      APEX_PUBLIC_USER
  PlsqlDefaultPage                           apex
  PlsqlDatabasePassword                      welcome1
  PlsqlRequestValidationFunction             wwv_flow_epg_include_modules.authorize
  Allow from all

If used SCAN then replace the PlsqlDatabaseConnectString string by:

                                                          (CONNECT_DATA=(SERVER = DEDICATED)
                ( TNSNamesFormat
In addition to configuring the dads.conf entries required to allow each HTTP Server to communicate with each RAC node,
the Oracle HTTP Servers should be configured to propagate the correct logical host name, for example
If you install the Oracle HTTP Server on machines named and
the Oracle HTTP Servers will install with those names. Adding a VirtualHost entry with the httpd.conf file
of each Oracle HTTP Server installation will accomplish this.

Add a VirtualHost for each Oracle HTTP Server

Stop the Oracle HTTP server; Host$> opmnctl stopproc ias-component=HTTP_Server
Add the following lines to the bottom of httpd.conf file, replace 80 with the port your load balancer is listening on and (www.) with the logical name of your site.

PlsqlDatabaseConnectString (DESCRIPTION =
  NameVirtualHost *:80
After the changes of the httpd.conf and dads.conf, start the HTTP server on de nodes.

start the Oracle HTTP Server; opmnctl startproc ias-component=HTTP_Server

Configuring Multiple HTTP Servers on the Oracle RAC environment

The Oracle RAC environment reduce the risk of failure due to loss of service.
A single Oracle HTTP Server, is a single point of failure component that enables processing of client request to the Oracle APEX engine

The Oracle Clusterware managed the Oracle RAC Databases. The Oracle HTTP Server can be installed and configured onto
the Oracle Clusterware conform a Oracle RAC Instance.

Installing and configuring the Oracle clusterware to manged the Oracle HTTP Server

Copy the following action script to the $CLUSTERWARE_HOME/crs/public directory on all cluster nodes.
Test the http action script before creating the http resource.
With the http server running verify the script will stop the http server: $CLUSTERWARE_HOME/crs/public/ stop
When the http server is down, verify the script will check, and start the http server if it is not running: $CLUSTERWARE_HOME/crs/public/ check
If the action script is correct in your environment the action will start the http server automatically.
If the action script is able to check and start the http server. Now create the HTTP Cluterware resources.
If the http server is not started, DO NOT create the http resource in the clusterware before solving the problem.
Create the http resources for each http server node of the cluster.

crs_profile -create APEX_http1 -t application –a /scratch/11.1.0/crs/crs/public/ -o ci=20,ra=5
crs_profile -create APEX_http2 -t application -a /scratch/11.1.0/crs/crs/public/ -o ci=20,ra=5
Register and start the http resource on each of the cluster nodes.

crs_register APEX_http1; crs_start -c wh_server1 APEX_http1 
crs_register APEX_http2; crs_start -c wh_server APEX_http2

Oracle Embedded PL/SQL Gateway (EPG):

The Oracle Embedded PL/SQL Gateway (EPG), installed with the 11g Database,
is an alternate to the Oracle HTTP Server Configuration.
It provides the Oracle database with a Web server and the necessary infrastructure to create dynamic web applications.
The EPG runs in the Oracle XML DB HTTP server in the Oracle database and includes the core features of mod_plsql.

Configure the embedded PL/SQL gateway

Connect to one of the RAC Nodes
Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role: sqlplus /nolog; connect sys as sysdba
When prompted, enter the appropriate password.
Run apex_epg_config.sql script; @apex_epg_config /tmp
Enter the following statement to unlock the ANONYMOUS account:  ALTER USER ANONYMOUS ACCOUNT UNLOCK;
Verify the port number where the Oracle XML DB HTTP Server is running: SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
If the port number returns 0, the Oracle XML DB HTTP Server is disabled.
To enable the Oracle XML DB HTTP Server, enter the following; EXEC DBMS_XDB.SETHTTPPORT(port);
For example:
In an Oracle RAC environment, the above configuration steps would have to be configured on all the Database cluster nodes.

Note: Port numbers less than 1024 are reserved for use by privileged processes on many operating systems


Installation and Configuration of Oracle Database 11g,
Installation and Configuration of Oracle Application Express,
Understanding of Oracle RAC concepts and terminologies,
High Availability Guide,
Oracle Maximum Availability Architecture White Paper,
Oracle Internet Director, Please refer to the following link,
Using Oracle Clusterware to Protect A Single Instance Oracle Database 11g white paper is on OTN

# $Header: 05-apr-2007.14:14:24 rvenkate Exp $
# Copyright (c) 2007, Oracle. All rights reserved.
# - action script for the http server resource 
# This perl script is the action script for start / stop / check
# the Oracle HTTP Server in a restart configuration.
# Edit the perl installation directory as appropriate.
# Place this file in /crs/public/
# dismith 03/18/08 - Creation
# The ORACLE_HTTP_SERVER_HOME must be set to point to the HTTP Server Home directory
$ORACLE_HTTP_SERVER_HOME = "/scratch/product/11.1.0/ohs";  #This should point to the ORACLE_HTTP_SERVER_HOME
if ($#ARGV != 0 ) {
print "usage: start stop check required \n";
$command = $ARGV[0];
# start http server
if ($command eq "start") {
system ("
$ORACLE_HTTP_SERVER_HOME/opmn/bin/opmnctl startall");
# stop http server
if ($command eq "stop") {
system ("
$ORACLE_HTTP_SERVER_HOME/opmn/bin/opmnctl stopall");
# check http server
if ($command eq "check") {
sub check_http {
my($check_proc_http,$process_http) = @_;
$process_http = "$ORACLE_HTTP_SERVER_HOME/Apache/Apache/bin/httpd";
$check_proc_http = qx(ps -ef | grep $process_http | grep -v grep | wc -l);
if ($check_proc_http gt 1) {
exit 0;
} else {
system ("$ORACLE_HTTP_SERVER_HOME/opmn/bin/opmnctl startall");