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
Ingredients needed:
- 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>cd apex F:\oracle\product\10.2.0\db_1\apex>set ORACLE_HOME=F:\oracle\product\10.2.0\db_1 F:\oracle\product\10.2.0\db_1\apex>set ORACLE_SID=LAB |
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>sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production ON Mon DEC 18 14:31:36 2006 Copyright (c) 1982, 2006, Oracle. ALL rights reserved. SQL> conn / AS sysdba Connected. SQL> CALL dbms_xdb.setHttpPort(80); CALL completed. SQL> CALL dbms_xdb.setFtpPort(21); CALL completed. SQL> ALTER system register; System altered. |
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>lsnrctl status LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 18-DEC-2006 14:28:05 Copyright (c) 1991, 2006, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=loopback.amis.nl)(PORT=1521))) STATUS of the LISTENER Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ ction Start Date 18-DEC-2006 13:24:27 Uptime 0 days 1 hr. 3 min. 38 sec Trace Level off Security ON: Password or Local OS Authentication SNMP OFF Listener Parameter File F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.o ra Listener Log File F:\oracle\product\10.2.0\db_1\network\log\listener.log Listening Endpoints Summary (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=80))(Presentation=HT TP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VAMISNT02)(PORT=21))(Presentation=FT P)(Session=RAW)) Services Summary Service "LAB" has 2 instance(s). Instance "LAB", status UNKNOWN, has 1 handler(s) for this service Instance "lab", status READY, has 1 handler(s) for this service Service "labXDB" has 1 instance(s). Instance "lab", status READY, has 1 handler(s) for this service Service "lab_XPT" has 1 instance(s). Instance "lab", status READY, has 1 handler(s) for this service The command completed successfully |
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. Rem Rem NAME Rem apexins.SQL Rem Rem DESCRIPTION Rem Rem NOTES Rem Assumes the SYS USER IS connected. Rem Rem REQUIRENTS Rem - Oracle 10g Rem Rem Arguments: Rem POSITION 1: Password FOR APEX Admin account, application DB USER, AND files DB USER Rem POSITION 2: Name OF tablespace FOR HTMLD DB application USER Rem POSITION 3: Name OF tablespace FOR HTMLD DB files USER Rem POSITION 4: Name OF TEMPORARY tablespace Rem POSITION 5: Virtual directory FOR APEX images Rem POSITION 6: The TNS CONNECT string TO the DATABASE, IF LOCAL install, USE NONE OR NONE Rem Rem Example: Rem Rem 1)LOCAL Rem sqlplus "sys/syspass as sysdba" @apexins password SYSAUX SYSAUX TEMP /i/ NONE Rem Rem 2)WITH CONNECT string Rem sqlplus "sys/syspass@10g as sysdba" @apexins password SYSAUX SYSAUX TEMP /i/ 10g Rem |
Now login and execute the install script:
F:\oracle\product\10.2.0\db_1\apex>sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production ON Mon DEC 18 14:31:36 2006 Copyright (c) 1982, 2006, Oracle. ALL rights reserved. SQL> conn / AS sysdba Connected. SQL> SQL> @apexins.SQL admin SYSAUX SYSAUX TEMP /i/ NONE .. . Application Express Installation. ... PL/SQL PROCEDURE successfully completed. .. .. .. .. .. .. .. VII. L O A D E N G L I S H D I C T I O N A R Y TRIGGER altered. 10000 ROWS 20000 ROWS 30000 ROWS 40000 ROWS 50000 ROWS 60000 ROWS 70000 ROWS timing FOR: English Dictionary Elapsed: 00:06:06.25 Thank you FOR installing Oracle Application Express. Oracle Application Express IS installed IN the FLOWS_020200 schema. The STRUCTURE OF the link TO the Application Express administration services IS AS follows: http://host:port/pls/apex/apex_admin The STRUCTURE OF the link TO the Application Express development interface IS AS follows: http://host:port/pls/apex timing FOR: Upgrade Elapsed: 00:00:00.10 JOB_QUEUE_PROCESSES: 10 Completing registration process. Validating installation. timing FOR: Validate Installation Elapsed: 00:01:38.23 timing FOR: Complete Installation Elapsed: 00:20:29.32 Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - Pr oduction WITH the Partitioning, OLAP AND DATA Mining options |
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 Rem Rem htmldb_epg_config.SQL Rem Rem Copyright (c) 2004,2006, Oracle Corporation. ALL rights reserved. Rem Rem NAME Rem apex_epg_config.SQL - Application Express Embedded PL/SQL Gateway Configuration Rem Rem DESCRIPTION Rem This script should be run AS SYS AND takes one argument, the path Rem TO the Oracle Home. The script will LOAD the images INTO XDB AND THEN configure Rem a DAD FOR USE BY Application Express IN the Embedded PL/SQL Gateway. |
Login and execute the apex_epg_config.sql script
F:\oracle\product\10.2.0\db_1\apex>sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production ON Mon DEC 18 14:55:48 2006 Copyright (c) 1982, 2006, Oracle. ALL rights reserved. SQL> conn / AS sysdba Connected. SQL> @apex_epg_config.SQL F:\oracle\product\10.2.0\db_1 PL/SQL PROCEDURE successfully completed. PL/SQL PROCEDURE successfully completed. OLD 1: CREATE directory APEX_IMAGES AS '/apex/images' NEW 1: CREATE directory APEX_IMAGES AS 'F:\oracle\product\10.2.0\db_1/apex/images' Directory created. PL/SQL PROCEDURE successfully completed. Commit complete. timing FOR: LOAD Images Elapsed: 00:01:29.32 SESSION altered. PL/SQL PROCEDURE successfully completed. PL/SQL PROCEDURE successfully completed. PL/SQL PROCEDURE successfully completed. Commit complete. SESSION altered. Directory dropped. |
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>sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production ON Mon DEC 18 14:55:48 2006 Copyright (c) 1982, 2006, Oracle. ALL rights reserved. SQL> conn / AS sysdba Connected. SQL> ALTER USER anonymous account UNLOCK; USER altered. SQL> ALTER system SET shared_servers = 5; System altered. SQL> ALTER system register; System altered. |
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.
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="31098 trace name context forever, level 2" |
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
10 comments
1 ping
Skip to comment form ↓
Marco Gralike
2 July, 2007 at 14:04 (UTC 1) Link to this comment
Read also: http://forums.oracle.com/forums/thread.jspa?threadID=486649&start=0&tstart=0
Marco Gralike
4 July, 2007 at 11:17 (UTC 1) Link to this comment
Reset of the admin password is easy via:
SQL> conn / as sysdba
Connected.
SQL> @?/apex/apxxepwd.sql admin
Session altered.
…changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.
ash
20 June, 2008 at 12:06 (UTC 1) Link to this comment
i installed Apex as above on Oracle 10g . However, when i restart the http server , i get ora-604 when i try and access the apex pages. Any one have any ideas how to resolve this and how to track down what causes it ?
Marco Gralike
20 June, 2008 at 12:23 (UTC 1) Link to this comment
You encountered a mayor problem. Have a look at http://ora-00604.ora-code.com. Have a look in the udump and bdump directories and check you “alert_$SID.log”-log database file.
If needed contact Oracle support.
Marco Gralike
21 June, 2008 at 11:01 (UTC 1) Link to this comment
Ash replied via e-mail:
found the solution.
When i turned on the PlsDebugEnable switch in the plsql.conf then I could see the error in more detail.
Its to do with the NLS_LANG setting in the console that I was launching the opmnctl startall command.
You have to set it to the same lang setting as in the dads.conf file .
So I set mine to American_America… ( i.e export NLS_LANG=American_….) and then did opmnctl startall and all just works.
Its all due to that.
Marco Gralike
21 June, 2008 at 11:05 (UTC 1) Link to this comment
For me this is once again proof how important / how big an impact NLS settings can have. In this case it was easily fixed, although some times this can cause serious data corruption (seen a lot off those for instance in conjunction with something like ‘exp/imp’) or, only unnecessary CPU overhead.
BTW this post was about APEX installation while making use of the local XMLDB Protocol Server and not as applied by you via Apache and the mod pl/sql. Be aware that also other daemons, like Apache, are dependable on environment settings like NLS_LANG setting.
But I am glad you found the solution to your problem
aishu
16 November, 2011 at 16:33 (UTC 1) Link to this comment
i have installed oracle 10g express edition..I have also installed oracle database XE client software.Then what i have to do..I am in big confusion.Help me
Marco Gralike
17 November, 2011 at 21:52 (UTC 1) Link to this comment
Why not install Oracle 11g XE? Everything will be installed as it should by default…
us
1 August, 2012 at 8:26 (UTC 1) Link to this comment
how can i get apex virtual path /i/?
Marco Gralike
1 August, 2012 at 9:16 (UTC 1) Link to this comment
Path /i/ is an entry in the xdb repository configuration file xdbconfig.xml in location http://localhost:8080/xdbconfig.xml (8080 can be different).
The APEX team sets those virtual paths via DBMS_EPG. Have a look at the the code in $ORACLE_HOME/apex/apex_epg_config_core.sql.
Setting manually only the /i/ path, which is called in XMLDB land a servlet mapping in xdbconfig.xml, is done via using PL/SQL package DBMS_XDB (addservletmapping).
See the following:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xdb.htm#CHDCGCHA
http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/appaman.htm#i641205
Part of the Puzzle: Oracle XMLDB NFS Functionality « AMIS Technology blog
27 February, 2010 at 22:14 (UTC 1) Link to this comment
[...] the WebDAV definitions) like HTTP, FTP, Native Database Web Services (NDWS), PL/SQL Gateways like local APEX support, security, etc., [...]