How to install Oracle Application Express

These are notes on the installation and configuration of Oracle 11g and Oracle Application Express, in November 2009.

The installation of App Ex is pretty straightforward.  The configuration gets complicated.  Originally for development purposes on the cmf2.it.uwosh.edu box we'd set up App Ex to use the built-in PL/SQL gateway because it was easier to set up.  For production purposes on acct.it.uwosh.edu, we wanted to use the most robust setup possible, so instead of the PL/SQL gateway we wanted to use a separate Oracle HTTP Server, as recommended in the documentation.

We installed Oracle 11g into here

 178  cd /u01/oracle/product/11gR2/db/

To get the installer for Oracle HTTP Server, I grabbed the Web Tier Utilities from otn.oracle.com, and installed OHS into

 256  cd /u01/oracle/product/11gR2/ofm/

The App Ex instructions say to edit dads.conf.  What's misleading is that the instructions imply that you are changing the contents of dads.conf, whereas what I found was only comments in dads.conf.  Also, there are three dads.conf files in the directory tree, and this is the one you want to edit:

 266  vi ./instances/instance1/config/OHS/ohs1/mod_plsql/dads.conf

Here is what we put in it:

Alias /i/ "/u01/oracle/product/11gR2/ofm/ohs/images/"
AddType text/xml       xbl
AddType text/x-component       htc

<Location /pls/apex>
 Order deny,allow
 PlsqlDocumentPath docs
 AllowOverride None
 PlsqlDocumentProcedure         wwv_flow_file_mgr.process_download
 PlsqlDatabaseConnectString     acct.it.uwosh.edu:1521:accounting ServiceNameFormat
 PlsqlNLSLanguage               AMERICAN_AMERICA.AL32UTF8
 PlsqlAuthenticationMode        Basic
 SetHandler                     pls_handler
 PlsqlDocumentTablename         wwv_flow_file_objects$
 PlsqlDatabaseUsername          APEX_PUBLIC_USER
 PlsqlDefaultPage               apex
 PlsqlDatabasePassword          yourpasswordgoeshere
 PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
 Allow from all
</Location>

Here is how you start or stop or look at the status of OHS.  The docs say to use this:

 320  ./opmnctl stopproc ias-component=HTTP_Server

but of course this is the correct command line:

 325  bin/opmnctl stopproc ias-component=ohs1
 326  bin/opmnctl startproc ias-component=ohs1
 327  bin/opmnctl status -l

At various times you need to use SQL/Plus as SYS:

 331  ./sqlplus /nolog

then 

connect sys@accounting as sysdba

When I'd launch firefox to visit

https://acct.it.uwosh.edu:4443/pls/apex

it would work only partially.  Images, CSS, and Javascript files were not found.  The problem was that the /i/ alias was not set correctly in OHS.  The /u01/oracle/product/11gR2/ofm/instances/instance1/config/OHS/ohs1/mod_plsql/dads.conf file has a line

Alias /i/ "/u01/oracle/product/11gR2/ofm/ohs/images/"

 381  /u01/oracle/product/11gR2/db/bin/sqlplus /nolog
 393  alias d='dirs -v'
 394  alias pd=pushd

I also made the mistake of running this reset_image_prefix.sql and specifying the full filesystem path for where the images were, but in fact I should have let it run with the default "/i/" value.

 400  vi reset_image_prefix.sql 
 401  /u01/oracle/product/11gR2/db/bin/sqlplus /nolog

 453  export ORACLE_HTTPSERVER_HOME=/u01/oracle/product/11gR2/ofm/ohs
 454  export ORACLE_INSTANCE=/u01/oracle/product/11gR2/ofm/instances/instance1

Here are full paths to opmnctl:

 475  $ORACLE_INSTANCE/bin/opmnctl status -l
 476  $ORACLE_INSTANCE/bin/opmnctl stopproc ias-component=ohs1
 477  $ORACLE_INSTANCE/bin/opmnctl status -l

 481  $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=ohs1

Here is where you can see the log for Oracle HTTP Server:

 482  less ../../../diagnostics/logs/OHS/ohs1/console~OHS~1.log 

That's how I found the image path error messages that led me to the fix.

 486  less ../../../diagnostics/logs/OHS/ohs1/ohs1.log 

Alternate ways of shutting down or starting OHS:

 505  $ORACLE_INSTANCE/bin/opmnctl stopall
 506  $ORACLE_INSTANCE/bin/opmnctl startall

 546  /u01/oracle/product/11gR2/db/bin/sqlplus /nolog
 547  $ORACLE_INSTANCE/bin/opmnctl stopall
 548  $ORACLE_INSTANCE/bin/opmnctl startall

It all worked after I had reset the image prefix to "/i/" and had put the trailing '/' in the Alias command in dads.conf, then restarted OHS via the opmnctl command.

Now to open up the firewall for port 4443 (the SSL port):

 549  vi /etc/sysconfig/SuSEfirewall2
 550  SuSEfirewall2 start

The other URL of interest is

https://acct.it.uwosh.edu:4443/pls/apex/apex_admin

Then we used Oracle Enterprise Manager to add a new user COBXACTION.  Make sure to set unlimited quota for that user in the USERS tablespace.

Then in App Ex Admin, created a new workspace COBACCT, with user COBACCTADMIN.