APEX installation via the Oracle Protocol Server (listener)

23

From time to time, people on the OTN XMLDB forum, ask if it is possible to connect via the Oracle Protocol Server (aka the Oracle Listener), for instance in combination with APEX and DBMS_EPG. I am not sure if it is an already official supported environment, but the following works as demonstrated in this step-by-step manual:

Ingredients

  • Oracle RDBMS 10.2.0.3.0 (Enterprise Edition) and/of higher version
  • APEX 2.2.1
  • Windows 2003 / XP

Shortlist

The following list is the shortlist regarding installing APEX via the Oracle Protocol Server ....

  1. Install the Oracle database software and apply the 10.2.0.3 patch
  2. Create a database with (among others) XML DB installed
  3. Download the APEX software (http://www.oracle.com/technology/products/database/application_express/download.html)
  4. Copy the APEX directory into the %ORACLE_HOME% software path
  5. Enable the HTTP (or HTTPS and/or FTP) Oracle Protocol Server (aka the Oracle Listener)
  6. Install the APEX packages in the database
  7. Install/enable the DBMS_EPG and DAD procedures
  8. Allow anonymous access on the database HTTP port
  9. Create extra shared server processes
Be-aware that if you enable access via port numbers beneath port 1024; that the user under which the process is started, needs superuser privileges. In my case the software is installed under a Oracle Windows OS user, that also has Windows local administrator privileges. If this is not the case (or you don’t want this because of security reasons) then you will have to connect on a port higher then 1024 (for instance the default 8080 HTTP XML DB port).

Step-by-step example

Create your database instance (mine has been called "LAB") and start database and the listener service. As said, download the APEX 2.2.1 software via http://www.oracle.com/technology/products/database/application_express/download.html and unzip the file to a temporary directory. This temporary directory now holds a "core" directory which contains the APEX software.

Copy this software to the ORACLE_HOME directory (in my case %ORACLE_HOME%=F:\oracle\product\10.2.0\db_1).

Go to the created F:\oracle\product\10.2.0\db_1\core directory and set your ORACLE_HOME and ORACLE_SID (in my case this ORACLE_SID=LAB) in a command window (start=>run=>CMD)

 

F:\oracle\product\10.2.0\db_1&gt;cd apex<br /><br />F:\oracle\product\10.2.0\db_1\apex&gt;<strong>set ORACLE_HOME=F:\oracle\product\10.2.0\db_1</strong><br /><br />F:\oracle\product\10.2.0\db_1\apex&gt;s<strong>et ORACLE_SID=LAB</strong><br />

 

Open a SQL*Plus session and set the HTTP (and/or FTP) port you want for the APEX software (for instance default port 80 for HTTP data traffic and default port 21 for FTP data traffic).

F:\oracle\product\10.2.0\db_1\apex&gt;sqlplus /nolog<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 18 14:31:36 2006<br /><br />Copyright (c) 1982, 2006, Oracle.  All rights reserved.<br /><br />SQL&gt; conn / as sysdba<br />Connected.<br /><br />SQL&gt; <strong>call dbms_xdb.setHttpPort(80);</strong><br /><br />Call completed.<br /><br />SQL&gt; <strong>call dbms_xdb.setFtpPort(21);</strong><br /><br />Call completed.<br /><br />SQL&gt; alter system register;<br /><br />System altered.<br />

 

You can check if the listener has picked up the HTTP and FTP settings by executing the following in a windows command box.

 

C:\Documents and Settings\oracle&gt;<strong>lsnrctl status</strong><br /><br />LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 18-DEC-2006 14:28<br />:05<br /><br />Copyright (c) 1991, 2006, Oracle.  All rights reserved.<br /><br />Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=loopback.amis.nl)(PORT=<br />1521)))<br />STATUS of the LISTENER<br />------------------------<br />Alias                     LISTENER<br />Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ<br />ction<br />Start Date                18-DEC-2006 13:24:27<br />Uptime                    0 days 1 hr. 3 min. 38 sec<br />Trace Level               off<br />Security                  ON: Password or Local OS Authentication<br />SNMP                      OFF<br />Listener Parameter File   F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.o<br />ra<br />Listener Log File         F:\oracle\product\10.2.0\db_1\network\log\listener.log<br /><br />Listening Endpoints Summary...<br />  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=1521)))<br /><strong>  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=80))(Presentation=HT<br />TP)(Session=RAW))<br />  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=21))(Presentation=FT<br />P)(Session=RAW))</strong><br />Services Summary...<br />Service &quot;LAB&quot; has 2 instance(s).<br />  Instance &quot;LAB&quot;, status UNKNOWN, has 1 handler(s) for this service...<br />  Instance &quot;lab&quot;, status READY, has 1 handler(s) for this service...<br />Service &quot;labXDB&quot; has 1 instance(s).<br />  Instance &quot;lab&quot;, status READY, has 1 handler(s) for this service...<br />Service &quot;lab_XPT&quot; has 1 instance(s).<br />  Instance &quot;lab&quot;, status READY, has 1 handler(s) for this service...<br />The command completed successfully<br /><br />

Install the APEX software by (while starting from the APEX directory) executing the following statements in SQL*Plus and executing the APEX installation script apexins.sql.

The apexins.sql has to be executed via the SYS database account and it has the following parameters:

 

Rem  Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.<br />Rem<br />Rem    NAME<br />Rem      apexins.sql<br />Rem<br />Rem    DESCRIPTION<br />Rem<br />Rem    NOTES<br />Rem      Assumes the SYS user is connected.  <br />Rem <br />Rem    REQUIRENTS<br />Rem      - Oracle 10g<br />Rem<br />Rem    Arguments:<br />Rem     Position 1: Password for APEX Admin account, application DB user, and files DB user<br />Rem     Position 2: Name of tablespace for HTMLD DB application user<br />Rem     Position 3: Name of tablespace for HTMLD DB files user<br />Rem     Position 4: Name of temporary tablespace<br />Rem     Position 5: Virtual directory for APEX images<br />Rem     Position 6: The TNS connect string to the database, if local install, use none or NONE<br />Rem<br />Rem    Example:<br />Rem <br />Rem    1)Local<br />Rem      sqlplus &quot;sys/syspass as sysdba&quot; @apexins password SYSAUX SYSAUX TEMP /i/ none<br />Rem<br />Rem    2)With connect string<br />Rem      sqlplus &quot;sys/syspass@10g as sysdba&quot; @apexins password SYSAUX SYSAUX TEMP /i/ 10g<br />Rem<br />

 

Now login and execute the install script:

F:\oracle\product\10.2.0\db_1\apex&gt;sqlplus /nolog<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 18 14:31:36 2006<br /><br />Copyright (c) 1982, 2006, Oracle.  All rights reserved.<br /><br />SQL&gt; conn / as sysdba<br />Connected.<br />SQL&gt;<br /><br />SQL&gt; <strong>@apexins.sql admin SYSAUX SYSAUX TEMP /i/ NONE</strong><br />..<br />. Application Express Installation.<br />...................................<br />PL/SQL procedure successfully completed.<br />..<br />..<br />..<br />..<br />..<br />..<br />..<br />VII.   L O A D    E N G L I S H   D I C T I O N A R Y<br /><br />Trigger altered.<br /><br />...10000 rows<br />...20000 rows<br />...
30000 rows<br />...40000 row
s<br />...50000 rows<br />...60000 rows<br />...70000 rows<br />timing for: English Dictionary<br />Elapsed: 00:06:06.25<br /><br /><br /><br />Thank you for installing Oracle Application Express.<br /><br />Oracle Application Express is installed in the FLOWS_020200 schema.<br /><br />The structure of the link to the Application Express administration services is<br />as follows:<br /><strong>http://host:port/pls/apex/apex_admin</strong><br /><br />The structure of the link to the Application Express development interface is as<br /> follows:<br /><strong>http://host:port/pls/apex</strong><br /><br />timing for: Upgrade<br />Elapsed: 00:00:00.10<br />JOB_QUEUE_PROCESSES: 10<br />Completing registration process.<br />Validating installation.<br />timing for: Validate Installation<br />Elapsed: 00:01:38.23<br />timing for: Complete Installation<br />Elapsed: 00:20:29.32<br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr<br />oduction<br />With the Partitioning, OLAP and Data Mining options<br /><br />

Configure the DAD and virtual path to the installed database images via configuring DBMS_EPG (by calling script apex_epg_config.sql). The apex_epg_config.sql script has to be executed via the SYS database account and it needs your ORACLE_HOME setting as variable.

 

Rem<br />Rem<br />Rem htmldb_epg_config.sql<br />Rem<br />Rem Copyright (c) 2004,2006, Oracle Corporation.  All rights reserved.  <br />Rem<br />Rem    NAME<br />Rem      apex_epg_config.sql - Application Express Embedded PL/SQL Gateway Configuration<br />Rem<br />Rem    DESCRIPTION<br />Rem      This script should be run as SYS and takes one argument, the path <br />Rem      to the Oracle Home. The script will load the images into XDB and then configure <br />Rem      a DAD for use by Application Express in the Embedded PL/SQL Gateway.<br /><br />

Login and execute the apex_epg_config.sql script

F:\oracle\product\10.2.0\db_1\apex&gt;sqlplus /nolog<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 18 14:55:48 2006<br /><br />Copyright (c) 1982, 2006, Oracle.  All rights reserved.<br /><br />SQL&gt; conn / as sysdba<br />Connected.<br />SQL&gt; @apex_epg_config.sql F:\oracle\product\10.2.0\db_1<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br />old   1: create directory APEX_IMAGES as '&amp;1/apex/images'<br />new   1: create directory APEX_IMAGES as 'F:\oracle\product\10.2.0\db_1/apex/ima<br />ges'<br /><br />Directory created.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Commit complete.<br /><br />timing for: Load Images<br />Elapsed: 00:01:29.32<br /><br />Session altered.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Commit complete.<br /><br /><br />Session altered.<br /><br /><br />Directory dropped.<br />

 

Grant anonymous access on the database / HTTP port by unlocking the anonymous account and (regarding performance reasons) increase the amount of share servers.

 

F:\oracle\product\10.2.0\db_1\apex&gt;sqlplus /nolog<br /><br />SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 18 14:55:48 2006<br /><br />Copyright (c) 1982, 2006, Oracle.  All rights reserved.<br /><br />SQL&gt; conn / as sysdba<br />Connected.<br /><br />SQL&gt; alter user anonymous account unlock;<br /><br />User altered.<br /><br />SQL&gt; alter system set shared_servers = 5;<br /><br />System altered.<br /><br />SQL&gt; alter system register;<br /><br />System altered.<br /><br /><br />

Now you can connect to APEX via http://yourserver:port/apex and http://yourserver:port/apex/apex_admin.

In my case this will be http://vamisnt02/apex/ and http://vamisnt02/apex/apex_admin. Because I used "admin" as a password during the apexins.sql script you can now login into the http://vamisnt02/apex/apex_admin page with the username/password combination: admin/admin

 

I hope I have been of help.

Smiley

 

Marco.

 

PS.

Extra info on the XML DB Protocol Server can be found in the Oracle XML DB Developers Guide, among others here: http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb22pro.htm#sthref2258 

In certain cases, it may be useful to log the requests received and
responses sent by a protocol server. This can be achieved by setting
event number 31098 to level 2. To set this event, add the following line to your init.ora file and restart the database:

event=&quot;31098 trace name context forever, level 2&quot;<br />

To use HTTP(S) on the standard port, such as 80, your DBA must chown (on linux, unix) the TNS listener to setuid ROOT rather than setuid ORACLE, and configure the port number in the Oracle XML DB configuration file /xdbconfig.xml.

Extra very useful information and examples about DBMS_EPG can be found here: http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

23 Comments

  1. Sounds like or you didn’t have all the privileges granted to execute and/or select DBMS_EPQ or you didn’t install all the needed functionality in the database or you database has invalid packages.

  2. I got following thing at the end of of Oracle Apex installation.

    Performing Application Express component validation – please wait…

    validate_apex;
    *
    ERROR at line 13:
    ORA-06550: line 13, column 5:
    PLS-00201: identifier ‘VALIDATE_APEX’ must be declared
    ORA-06550: line 13, column 5:
    PL/SQL: Statement ignored

    timing for: Validate Installation
    Elapsed: 00:00:00.00
    timing for: Complete Installation
    Elapsed: 00:12:03.68

  3. I got following error, hot to resolve it ?
    ————————–
    SQL> @apex_epg_config.sql “c:\10gEM\Oracle APEX\apex_3.0.1″
    select insertChildXML(cfg,
    *
    ERROR at line 24:
    ORA-06550: line 24, column 16:
    PL/SQL: ORA-00904: “INSERTCHILDXML”: invalid identifier
    ORA-06550: line 24, column 9:
    PL/SQL: SQL Statement ignored

    PL/SQL procedure successfully completed.

    old 1: create directory APEX_IMAGES as ‘&1/apex/images’
    new 1: create directory APEX_IMAGES as ‘c:\10gEM\Oracle APEX\apex_3.0.
    mages’

    Directory created.

    PL/SQL procedure successfully completed.

    Commit complete.

    PL/SQL procedure successfully completed.

    timing for: Load Images
    Elapsed: 00:01:31.72

    Session altered.

    dbms_epg.create_dad(‘APEX’,’/apex/*’);
    *
    ERROR at line 2:
    ORA-06550: line 2, column 5:
    PLS-00201: identifier ‘DBMS_EPG.CREATE_DAD’ must be declared
    ORA-06550: line 2, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 3, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 3, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 4, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 4, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 5, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 5, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 6, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 6, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 7, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 7, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 8, column 5:
    PLS-00201: identifier ‘DBMS_EPG.SET_DAD_ATTRIBUTE’ must be declared
    ORA-06550: line 8, co

    Commit complete.

    Session altered.

    Directory dropped.

    SQL>

  4. I had the same problems that many here had. Specifically, the problems with logging into to APEX using an Oracle 10.2.0.3 database with the PL/SQL gateway and subsequently NOT being able to login into the http://host:port/pls/apex/apex_admin…..

    This looks as though it applies.

    Metalink Note 422680.1
    Is it possible to have Apex 3.0 installation on 10g databases with Embeded PL/SQL Gateway?

    Short Story is that it wasn’t meant to.

    Long Story:

    Applies to:
    Oracle Application Express (formerly HTML DB) – Version: 3.0
    Information in this document applies to any platform.

    Goal
    Can we install the Apex 3.0 using the Embedded PL/SQL Gateway on 10g Databases as described on the installation guide ?

    Reference 4.2 Configuring the Embedded PL/SQL Gateway of Apex 3.0 Install Documentation.

    http://download-east.oracle.com/docs/cd/B32472_01/doc/install.300/b32468/post_inst.htm#CHDEIGGI

    Solution
    Embedded PL/SQL Gateway implementation of the Apex installation will be introduced when the 11g
    databases are rolled out for productions.
    Currently it is not possible for installation of the Embedded PL/SQL Gateway on 10g databases.

    You need to install the Apex following the 4.3 Configuring Oracle HTTP Server of the installation guide.

    http://download-east.oracle.com/docs/cd/B32472_01/doc/install.300/b32468/post_inst.htm#BHAGICFJ

    The HTTP server can be installed either from a Database companion CD or if an Application server already exists then that can be used for configuring.

    At the time of writing the Note the 11g database wasnt released for Production and also there wasnt any information on using 10g database for the same problem.

  5. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  6. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  7. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  8. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  9. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  10. Rebecca Goh on

    Hi,

    I refer to my email posted on 24 May. Just to add that I tried typing in password given during APEX installation, passwords for Oracle superuser account sys and system but all failed. I also unlocked anonymous account. Can anyone tell me what else whould I try ?

    Thanks in advance for any help.

    Kind regards,
    Rebecca Goh

  11. Peter vd Neut on

    In reply to Tim Vernon.
    I had the same problem, what worked for me was the unlocking of the anyonymous account (as mentioned in the article).
    alter user anonymous account unlock;

  12. My problem… access to the images that are loaded onto the XML DB. i.e. “XDB requires userid and password”.
    I have typed system/******** and remember password enough to get everything working. However, when I imported in an application… I now get “XDB requires userid and password” on all the images for the new application.
    Has anyone sucessfully setup the ACL security for the /i/ or /images/ folder on the XML DB?

  13. Rebecca Goh on

    Hi there,

    I am also getting the same problem that some of you have encountered ie. APEX Administration page won’t let me go
    thru’ although I typed in correct password. Has anyone managed to resolve this issue ?

    Thanks
    Rebecca

  14. Rebecca Goh on

    Hi there,

    I am also getting the same problem that some of you have encountered ie. APEX Administration page won’t let me go
    thru’ although I typed in correct password. Has anyone managed to resolve this issue ?

    Thanks
    Rebecca

  15. juancarlosreyesp on

    following the steps in this guide (because this solution didn’t work when I ran the 11g script.
    When I was asked for the xdb password I enter the password of an Oracle dba user, it logged on and it worked ok.
    Some times appears for the apex password and I didn’t found a solution.
    The other problem I foudn is always I went to admin logon, but logging out I found I could log in a specific workspace.
    Hope this help.

  16. Jaideep Sarkar on

    Hello, I am getting exactly the same problem as Liam and cool_techie ie. a Windows dialog box is not letting me proceed. Has anyone got a resolution for this? Thanks, Jaideep

  17. Hi,

    I would like to know how can I implement windows authentication in APPEX (HTMLDB). I mean once the user is logged in windows and when he starts HTMLDB, he should be authenticated with the login-password used for windows.

    Thanks
    ARvind

  18. Hi cool_techie,
    I had the same issue regaring path issue. I followed the install guide for Window which asked me to run the following @apex_epg_config11 C:\TEMP , I did this but forgot to replace the path to the path were I extracted the files. I got an error, I ran again with the correct path (below)and it worked fine. Now I am having a problem logging in with the admin username and the password created during installation. Did you manage to resolve this?

    @apex_epg_config11 C:\oracle\TMP

  19. A very helpful step by step guide for installing APEX.
    I have been trying to install the same… but am stuck for last few hours. It would be great if you can help me point where I might be getting wrong:-
    1. Installed APEX.
    One error while running the apex_epg_config11: ORA-22288: file or LOB operation failed. The system cannot find the path specified.
    ORA-06512: at sys.DBMS_LOB,line 523
    ORA-06512: at sys.xmltyp,line 287

    2. After a not-so-clean install trying to open the apex_admin page, but a windows username/password dialgbox appears saying the server at XDB reuires the username and password. Am giving the username as admin and the apex password given during the installtion, but not getting thru. Am i doing the right thing.
    I seem to have been stuck (maybe stupid) and would really appreciate any help.

    Thanx.
    L

  20. Two things come to mind, protecting the root folder.

    - One could place a index.htm(l) file which refreshes immediately to the wanted URL.via the META tag (http://www.w3.org/QA/Tips/reback). But this is more or less a workaround.
    - One could protect the folders via ACL’s. See the XMLDB Developers Guide how to implement this.

  21. Hi,

    I have followed your procedure…but when i go to http://hostname:8080/ (root page) i see the page “Index of/”

    Index of /
    Name Last modified Size

    home/ Sun, 18 Feb 2007 16:31:42 GMT
    i/ Sun, 18 Feb 2007 21:12:55 GMT
    public/ Tue, 30 Aug 2005 14:28:30 GMT
    sys/ Sun, 18 Feb 2007 16:31:41 GMT

    Is there any way to hide this page or redirect it to another?. I am worried about that if i exposes it to internet.