B
Troubleshooting

OAS Compatibility

Using the mod_plsql without the /pls in the URL

You can use the mod_plsql without the /pls in the URL (similar to the Oracle Application Server [OAS] 4.0.8).

Note: The default installation of mod_plsql is mapped to /pls. The code in the plsql.conf is similar to: [...] <Location /pls> SetHandler pls_handler ... </Location> [...]

There are several methods, outlined below.

• Rewrite directive (without an external redirect)
  Since using a redirect or a rewrite engine both cause an external redirect, which adds additional workload, consider using this version of the rewrite directive:

  [...] 
  RewriteEngine on 
  RewriteRule ^/myapp/(.*)$ /pls/[DAD]/$1 [PT] 
  [...] 
  

The passthru (PT) flag is a way to post-process the output of RewriteRule directives, but in this case prevents a redirect. Another advantage is that you can hide your URL behind the alias, meaning that the URL does not get rewritten in the browser location window. Therefore this method is the preferred way to set alias names for your application URL.

For more information on rewrites, refer to the documentation at http://www.apache.org/docs/mod/mod_rewrite.html.

• Redirect the request
  The PL/SQL Gateway does not map to a filesystem but as a module handler it cannot use an alias or similar mod_alias directives. You can, however, redirect the request. The syntax in the plsql.conf is similar to:

  Redirect /myapp http://[hostname]:[port]/pls/[DAD] 
  

  This redirects all requests http://[hostname]:[port]/myapp/... to http://[hostname]:[port]/pls/[DAD]/...

  For more information on rewrites, refer to the documentation at http://www.apache.org for more information on the redirect directive.

• Use the Rewrite Engine
  Refer to http://www.apache.org/docs/mod/mod_rewrite.html. The commands offered by this complex module allow rule-based URL manipulations dynamically. The sample rewrites any URL /myapp/... to /pls/[DAD]/ and forces a redirect. The syntax for using the rewrite engine is similar to:

  [...] 
  RewriteEngine on 
  RewriteRule ^/myapp/(.*)$ /pls/[DAD]/$1 [R,L] 
  [...] 
  

Using Flexible Parameters and the Exclamation Mark

The Flexible Parameter passing mode in Oracle9i Application Server expects the PL/SQL procedure to have the exclamation mark before the procedure name. Due to performance implications of the auto-detect method used in OAS, the exclamation mark is now required for flexible parameter passing in Oracle9i Application Server.

In OAS, each procedure is described completely before being executed. The Procedure Describe call figures out the signature of the procedure and requires a round-trip to the database. The PL/SQL Gateway in Oracle9i Application Server avoids this round trip by having end-users explicitly indicate the flexible parameter passing convention by adding the exclamation mark before the procedure. Refer to "Flexible Parameter Passing" for more information.

Logging

To turn on logging for mod_plsql, do the following:

1. Stop the Apache Listener.

   Note: When sending log file to Oracle support, start with a clean set of log files to expedite the process. You can either archive or delete the older logs after Step 1.

2. Edit the file <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app.

3. Change the line containing debugModules to debugModules=all.

4. Restart your listener.

When you get an error, stop the Apache listener. The mod_plsql debug log files can be found in <ORACLE_HOME>/Apache/modplsql/log directory. Additionally, look at the following log files for errors:

     <ORACLE_HOME>/Apache/Apache/logs/httpd_access_log 
     <ORACLE_HOME>/Apache/Apache/logs/httpd_error_log 
     <ORACLE_HOME>/Apache/Jserv/logs/jserv.log 
     <ORACLE_HOME>/network/admin/sqlnet.log 
     <ORACLE_HOME>/admin/<database>/bdump/alert 

When you are finished debugging, revert the change in the configuration file (wdbsvr.app) by typing a semi-colon before the line to comment it out.Enabling logging degrades your web server's performance.

Controlling Database Processes for Each mod_plsql Request

The database connection pool in mod_plsql is not shared across Apache processes, which means that each process maintains its own pool. The total number of database connections pooled in Apache mod_plsql is directly related to the number of Apache processes that are spawned and the number of DAD's used to access different PL/SQL applications. The mod_plsql pools one database session per DAD per Apache process. So, the maximum number of database sessions that will be pooled by mod_plsql will be (NumberOfApacheProcesses*NumberOfDADs).

On NT, where Apache is multi-threaded, all threads share the same database connection pool. The maximum number of database sessions that will be pooled by mod_plsql is (MaximumNumberOfApacheThreadsConcurrentlyActive
ForEachDAD). This is the ideal case scenario where every thread can take advantage of a database session created by another thread.

On platforms where Apache is not multi-threaded, it is important that the following parameters be tuned.

• Tune the Apache process configuration so that processes are not started or shutdown heavily (each process takes down its connection pool with it, and a new process will need to replenish its pool). This tuning is governed by the load on the Web Server. Set the following: Set MaxRequests = MaxSpareServers, and MaxRequestsPerchild=HighNumber and MinSpareServers=0. This configuration ensures that Apache processes are very rarely shutdown and the overhead of creating an Apache process or new database connection is greatly reduced.

• Setup the maximum number of database sessions according to the maximum number of Apache processes expected.

• For heavily loaded sites, turn KeepAlive off (Apache's recommendation). Otherwise, one Apache process gets blocked for the duration of "KeepAliveTimeOut" setup in httpd.conf.

If your database is unable to handle the load, do one of the following:

• Set up a new Listener for handling PL/SQL requests. The main Apache Listener can redirect all PL/SQL requests to the new Listener. For the new Listener, you can set up the Apache processes parameter to a low number (since it will not be doing any other request except PL/SQL). This will keep the database session number to a minimum.

• Consider using Oracle Multi-Threaded Server (MTS). This should reduce the load on the database.

• Consider using the Calypso/iCache/mod_plsql OWA based-caching approaches.

The mod_plsql in Oracle9i Application Server v1.0.2 has a cleanup thread which periodically cleans up database sessions which are inactive for more than 15 minutes. So, you need not be concerned about database related resources since mod_plsql does the cleanup automatically at idle time.

Debugging

The following series of steps can help you isolate your particular problem. Verify you can do each step before proceeding to the next one.

1. If you are getting Login failures, confirm that your DAD configuration in <ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app has the proper user name, password and connect string. Use SQL*Plus to verify that you have connectivity to the database.

2. If you have multiple Oracle homes, verify that the New8 alias is present in the correct tnsnames.ora file. Synchronize all versions of tnsnames.ora to have the same content. Merge the files instead of directly copying one over the other.

3. If you can logon, try accessing a simple procedure, for example:

   http://host:port/pls/DAD/owa_util.print_cgi_env
   
   

4. If you still have problems, contact Oracle Support and provide the log files. Refer to the "Logging" section for instructions on obtaining a log.

Error Code 503 (Service Temporarily Unavailable)

If you are getting error code "503 Service Temporarily Unavailable" when your web server is under some load, determine if the mod_plsql cannot connect to the database because the maximum number of database sessions has been reached.

Check the maximum number of sessions parameter in your database configuration file. This parameter is called processes and is in your database configuration file init$SID.ora. This number must be at least equal to the maximum number of Apache processes configured in httpd.conf (StartServers+MaxSpareServers).

Verify this is the issue by doing the following. As soon as you get this error, connect as SYS thru SQL*Plus, and issue "select username from v$session". If the count of the number of rows is almost the same as what you have in init$SID.ora, then you are having this problem.