Oracle Application Express – A DBA Perspective


Oracle Application Express (APEX) is a product provided by Oracle that is
used for rapid application development to create web enabled applications to
run against an Oracle database. Being primarily a developer’s tool, the
question arises…should DBAs be overly concerned about APEX? And the short
answer to this question is…YES!

The three main reasons that DBAs do need to be concerned are first, it must
be installed into the database, http server access to the database must be
enabled for APEX based applications to run and there are several privileges
granted to the workspace administrator accounts that the DBA should be aware
of. Additionally there are always space requirements for each of the workspaces
that the DBA needs to be aware of if managing a database that is going to
support applications designed using Oracle Application Express.

Choosing the HTTP Server Access

The first decision that should be made is to determine which kind of HTTP
Server Access. There are slightly different detailed installation steps
depending on which access method is chosen.

Application Express has three options (if installed into an Oracle 11g
database) for configuring the required HTTP Server Access to the database; the
Oracle HTTP Server with mod_plsql, the Oracle Application Express Listener or
the Embedded PL/SQL Gateway. All of these methods are used to establish
communications between the web server and the APEX objects in the database
using a SQL*Net connection.

Application Express Listener

This is a java based web server and is a multi tier option that allows for
separation of the listener tier from the database tier. This is downloaded
separately from Oracle and must be unzipped and then separately installed and
configured.

HTTP Server with mod_plsql

Using the Oracle HTTP Server requires the mod_plsql plug-in and the proper
configuration of the dads.conf file for the HTTP Server.

The huge advantage of the HTTP Server with mod_plsql is that it allows for
separation of the mid tier from the database tier. If your application is going
to be placed outside of a firewall (for example, external web based applications)
the DBA should always go with the HTTP Server with mod_plsql option rather than
using the Oracle Application Express Listener.

Embedded PL/SQL Gateway

This is installed in the database as part of the Oracle XML DB HTTP Server.

However, it should be noted, that there are two distinct disadvantages to
using the embedded gateway option. First, there is no way to separate the
listener from the database, which would be a security risk for internet based
applications. Second, it is not as feature rich, when it comes to configuration
and logging, as the Oracle HTTP server with mod_plsql.

Installing Application Express

System Requirements

Before beginning the installation (Application Express 4.0), the following
system requirements must be met:

a) Database Version 10.2.0.3 or higher
b) Shared pool size at least 100M (or use Automatic Shared Memory Management or
Automatic Memory Management)
c) Free space in the APEX tablespace 185M (plus 75M for each additional
language required)
d) Free space in the SYSTEM tablespace 100M
e) Oracle XML DB must be installed, and the associated anonymous user must not
be dropped
f) Oracle Text is required (but this is installed by default)
g) PL/SQL Web Toolkit version 10.1.2.0.6 or later

Running the Installation Scripts

Application Express can be downloaded from Oracle ( Application
Express Download Site
). There are two versions of Application Express, the
full development version and a runtime version. After downloading and staging
the software, running the following command (must be run while connected as
sysdba) installs the full version:

@apexins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/ 
<apex_tbs> is the tablespace for the APEX application user
<apex_files_tbs> is the tablespace for the APEX files user
<tmp_tbs> is the temporary tablespace
/i/ the virtual directory for APEX images - using /i/ is recommended for future upgrade support

The following script has the same parameters, but installs the runtime
version of Application Express.

@apexrtins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/

The following three accounts will be created in the database – APEX_040000
(owner of the application express schema and meta data), FLOWS_FILES (owner of
any uploaded files by application express) and APEX_PUBLIC_USER (a low
privilege account used by the HTTP Server with mod_plsql)

Changing the Application Express Administrator Password

A default ADMIN account is set up to be used when first logging into the
Application Express Administration site. This is the primary set of tools used
by the Application Express Administrator to create and manage workspaces in the
Oracle database. This is not the account that is set up for each workspace to
be the workspace administrator. There is generally only one Application Express
Administrator while there is at least one workspace administrator account for
each workspace created.

This password should always be changed using the provided script.

@apxchpwd

Configure APEX_PUBLIC_USER

If using the HTTP Server with mod_plsql (recommended), then the
APEX_PUBLIC_USER must be configured properly. Log in as sysdba and set the
password for the account.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY password

It is also important to make sure the password for the APEX_PUBLIC_USER does
not expire because your application will become unusable if the password
changes and the dads.conf file is not updated properly.

The best method is to create a password profile that does not expire the
password and assign that profile to the APEX_PUBLIC_USER account.

CREATE PROFILE apex_user_prfl
LIMIT PASSWORD_LIFE_TIME unlimited;
ALTER USER APEX_PUBLIC_USER PROFILE apex_user_prfl;

Copy the Images

The images for Apex must be copied from the apeximages directory to the home
for the Oracle HTTP Server. The following example could be used on a unix
platform

cp -rf $ORACLE_HOME/apex/images HTTPSERVERHOME/Apache

Configure the dads.conf file

Configure the dads.conf file (if using the HTTP Server with mod_plsql) – the
following is a typical dads.conf entry for Application Express:

Alias /i/ "HTTPSERVER_HOME/Apache/images/"
<Location /pls/apex
Order deny, allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure www_flow_file_mgr.process_download
PlssqlDatabaseConnectString host:listenerport:service
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword <password set earlier>
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

Enable Network Services (for 11g databases)

By default, in Oracle 11g (both R1 and R2) the ability to interact with
network services is disabled. The DBMS_NETWORK_ACL_ADMIN package must be used
to grant connect privileges to any host for the APEX_040000 database user
account.

For more details on using the package, refer to the online installation
documentation for Application Express (Application
Express Installation Guide
)

Setting JOB_QUEUE_PROCESSES

In order for Application Express to run successfully, it submits jobs, and
requires JOB_QUEUE_PROCESSES to be set. A recommended starting number is 20.

Privileges Granted to the Workspace Administrator Accounts

Each workspace that is created is associated with a workspace administrator
database account in the database. By default, there are a number of privileges assigned
to this account, and the workspace administrator is able to connect via tools
other than just Application Express.

The following is a list of the privileges that are assigned to the workspace
administrator:

CONNECT
CREATE ANY CONTEXT
CREATE CLUSTER
CREATE DIMENSION
CREATE INDEXTYPE
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

With the popularity of Oracle Application Express on the rise, database
administrators should be aware of the features, installation options,
listener/communication options and privileges, and space associated with the
workspaces and user accounts associated with Oracle Application Express.

»


See All Articles by Columnist

Karen Reliford

Karen Reliford
Karen Reliford
Karen Reliford is an IT professional who has been in the industry for over 25 years. Karen's experience ranges from programming, to database administration, to Information Systems Auditing, to consulting and now primarily to sharing her knowledge as an Oracle Certified Instructor in the Oracle University Partner Network. Karen currently works for TransAmerica Training Management, one of the foremost Oracle Authorized Education Centers (OAEC) in the Oracle University North America region. TransAmerica Training Management offers official Oracle and Peoplesoft Training in Coral Gables FL, Fayetteville AR, Albuquerque NM, Providence RI and San Juan PR. Karen has now been teaching Oracle for Oracle University for more than 15 years. Karen has attained her Certified Technical Trainer designation along with several Oracle certifications including OCP-DBA, OCP-Internet Developer, Oracle Expert - Oracle 10g RAC and Oracle Expert - Oracle Application Express (3.2). Additionally, Karen achieved her Oracle 10g Oracle Certified Master (OCM) in 2008. Karen was raised in Canada, and in November 2009 became a US Citizen. Karen resides in Columbus OH with her husband, Ron along with their 20 pets, affectionately referred to as the "Reliford Zoo".

Latest Articles