Sunday, December 21, 2008

Adventures with Apex, part three: Setting up Apex with Tomcat and the DBPrism plugin

It is common knowledge that you can run Apex using either the Apache-based Oracle HTTP Server (with mod_plsql) or the Embedded PL/SQL Gateway (on 10g XE and 11g), but did you also know that there is a third alternative?

The DBPrism project is a free, open-source plugin for the Tomcat web server that, among other things, can function as a replacement for mod_plsql.

Side Note 1: Interestingly, Oracle itself is also developing the "Apex Listener", what I assume is based on similar [Java] technology. So by Apex 4.0 we will have even more deployment choices.

Side Note 2: My wish is that someone familiar with Microsoft's IIS should write a plugin to mimic mod_plsql on that platform as well, to make it easier to start using Apex in companies that have standardized on Microsoft technology (sneaking it in the back door, so to speak :-). UPDATE (AUGUST 2009): I decided to implement a free, open-source PL/SQL gateway for IIS myself. The Thoth Gateway runs all mod_plsql applications (including Apex applications) on Microsoft Internet Information Server (IIS) 6.0 or later.

Anyway, here are instructions for using Apex with Tomcat and DBPrism:

ON THE DATABASE SERVER

1. Download the latest version of Application Express from oracle.com

- http://www.oracle.com/technology/products/database/application_express/download.html

2. Install Apex into the database according to the instructions

- do not worry about configuring either the Embedded PL/SQL Gateway (DBMS_EPG) or Oracle HTTP Server,
- but make sure to unlock the apex_public_user schema and make a note of the password given to apex_public_user (you will need it to configure the JDBC connection on the web server)


ON THE WEB SERVER

Note: This could be the same physical machine as the database, if desired.

1. Download Java Runtime Engine (JRE) from sun.com

- install by running setup program
- create an environment variable called JRE_HOME and set it to the location where JRE was installed

2. Download Tomcat from tomcat.apache.org

- unzip to a folder, for example c:\program files\apache-tomcat-xxx (where xxx is version number)
- follow instructions in RUNNING.txt to start Tomcat and verify installation (http://localhost:8080)

3. Download the Oracle JDBC Thin driver from Oracle.com

- http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
- get the "ojdbc5.jar" file

4. Download DBPrism from http://sourceforge.net/projects/dbprism/

- choose the "dbprism" package (not the "cms" package)
- unzip and get the "dpls.war" file
- put the dpls.war file into the "webapps" folder in Tomcat
- shutdown and restart Tomcat
- this will create a "dpls" folder under "webapps"
- put the "ojdbc5.jar" file from step 3 in the dpls\WEB-INF\lib folder
- edit the prism.xconf file
- under "variables", change the value of the "demo.db" to a valid jdbc connectionstring to the database
- under under category named "DAD_apex", change the "dbpassword" to the correct password of the "apex_public_user" (note that this was set during/after the installation of Apex)

- shutdown and restart Tomcat again

5. Configure Apex images/javascript for Tomcat

- Create a folder called "i" under "webapps" in Tomcat
- shutdown and restart Tomcat
- Extract the files in the "images" folder in the Apex installation zip file and copy it to the "i" folder

6. Verify that everything works

- Go to http://localhost:8080/dpls/apex (this should display the login page of the Apex environment)


Pretty cool, isn't it? :-)

Saturday, April 12, 2008

Adventures with Apex, part two: Setting up the stand-alone Oracle HTTP Server as a Windows service

The latest stand-alone version of Oracle HTTP Server (OHS) for Windows is available on OTN as the file "iAS_101330_Apache2_Modplsql2_win32.zip".

I ran the default setup on a Windows 2003 server (a separate machine from the database server) and encountered no errors during installation. The setup process created a few Start Menu items to start and stop the Apache process (through the Oracle Process Manager, OPMN, and the "opmnctl startall" command, which in turn starts the HTTP Server).

After configuring OHS and mod_plsql on the webserver to connect to Apex on the database server (as per the Apex installation docs), everything seemed to work fine. However, when I logged out of the server, the Apex application (and OHS) stopped responding.

Investigating, I found that the Apache process (apache.exe) is started and runs under the current logged-in user when using the Start Menu item to start it. So once I logged out of the server, the Apache process shuts down...!

As far as I could tell, there should have been a Windows service called OracleProcessManager that runs the OPMN process in the background, without requiring a logged-on user. However, there is no such service created after installation.

I tried creating the service manually myself using the "sc create" command. The service was created successfully but gave an error message ["...did not respond in a timely fashion..."] when trying to start it.)

Fortunately, I had a quick look in the Knowledgebase on Metalink and found the solution there. According to Note:459474.1, entitled "Howto Create a Windows Services Entry for Starting Oracle Application Server Processes", this behavior is by design (!)...:

Most Oracle Application Server installations automatically create a Windows Services entry to allow the OracleAS processes to start up on server startup. By design, certain OracleAS releases such as the SOA Suite 10.1.3.1 do not create the Windows Services entry. In such installations, the user starts OracleAS processes from the Windows Start Menu. When the user logs out of the Windows session, the OracleAS processes will terminate as well. Resulting in an unexpected Application Server shutdown.

This note provides the steps to create a Windows Services entry for starting up OracleAS processes upon reboot/startup of the server. Processes started via Windows Services entries are not affect by a user logging out of the Windows session.

I'll leave the question as to why on earth the setup program doesn't create the necessary Windows services by default, for what is clearly a server-type product?!? up in the air for now.

Instead, I will provide the necessary details here for those who do not have access to Metalink:

Turns out I was right about having to create a Windows service manually using the "sc" command, but there is also a few registry entries that need to be present for it to work.

First, find the "key" value from the file \opmn\bin\oracle.key on the server. In my case, the value of this key was:

SOFTWARE\ORACLE\KEY_oracleas1

Run regedit and add the following two entries (in my case, the first entry already existed):


HKLM / Software / ORACLE / Key_oracleas1 / ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_oracleas1


and


HKLM / Software / ORACLE / oracleas1 / ORACLE_OPMN_SERVICE = OracleHTTPServerProcessManager


Then run the following command to create the service:


sc create OracleHTTPServerProcessManager binPath= "c:\OraHome_1\opmn\bin\opmn.exe -s" DisplayName= "OracleHTTPServerProcessManager" start= "auto"


Note that there must be an empty space between the equal sign and the value in the command above. Apparently some kind of quirk with the sc command. By the way, the sc command is part of the Windows server resource kit (but it was already present on my Windows 2003 server).

After creating the service, go to Services and verify that the service starts up. Also verify using Task Manager that apache.exe runs under the SYSTEM user, rather than the logged-in user.

Adventures with Apex, part one: Boosting performance when using DBMS_EPG

We are currently developing a web application using Apex 3.1. There is a handful of developers on the team and we all work in the Apex Application Builder against a shared Oracle 11g database server, which has the Embedded PL/SQL Gateway (DBMS_EPG), rather than a full Apache (Oracle HTTP Server) setup, as per the default database install.

It did not take long before we noticed a significant variation in the time it took to serve Apex pages from the database. Most of the time, pages were served quickly enough, but at uneven intervals pages would just take forever to load. It seemed as if the underlying database or webserver/XDB session was timing out.

After some head-scratching, googling, and experimentation, the following two configuration changes fixed the problem. Page performance is now consistently excellent, and we do not experience timeouts anymore.

  1. Increase the XDB session-timeout parameter (I changed it from the default of 6000 [1 minute] to a new value of 30000 [5 minutes].
  2. Increase the shared_servers database parameter (I changed it from the default of 1 to a new value of 5).

Note: I changed both these settings at the same time, so I did not measure the effect of each individually. I suspect the shared_servers database setting is the most important of the two.

Detailed instructions follow:

Changing the session-timeout parameter in XDB

Connect as SYSDBA and run the following:

declare
  l_newconfig xmltype;
begin
  select updatexml (dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout/text()', 30000)
  into l_newconfig
  from dual;

  dbms_xdb.cfg_update (l_newconfig);

  commit;
end;
/


Note: The database must be restarted for the change to take effect. Issue "alter system register" for the new changes to take effect without restarting the database.

Changing the shared_servers database parameter

Connect as SYSDBA and run the following:

SQL> alter system set shared_servers = 5 scope=both;

System altered.

SQL> alter system register;

System altered.


This will create more background sessions to handle the Apex page requests served through XDB.

Tuesday, March 25, 2008

Implementing table inheritance in the database

Over at sqlteam.com, there is an interesting article about "Implementing table inheritance in SQL Server". But the concept is just as applicable to Oracle, of course:

http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

Friday, March 21, 2008

Yet another Oracle blog!

I was not really planning to start a blog, but then I realized the subdomain ORA-00001 was available on Blogger, so I thought "why not"? At least now I have a place to ramble about various Oracle-related stuff.