Archive for May, 2010

Find blocking and blocked sessions

Sometimes a system may grind to a halt or a crawl waiting for database locks. Perhaps a user has left SQL*Plus, TOAD or a similar tool open with uncommitted changes or perhaps an error or oversight in the system itself is causing contention. Regardless the first step is to find out who is holding the locks. The following SQL shows the blocking sessions with a list of all the sessions they block. It works with RAC (the gv views are used rather than v views):

select s1.sid as blocker_sid,
       s1.username || '@' || s1.machine as blocker_user,
       wm_concat(s2.sid || ':' || s2.username || '@' || s2.machine) as blocked
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.block=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2
  group by s1.sid, s1.username, s1.machine;

The following SQL shows the most recent SQL executed by sessions holding blocking locks (if available):

select sid as blocker_sid, wm_concat(sql_text) as blocker_sql
  from (select s.sid, txt.sql_text
          from gv$sqltext txt, gv$session s, gv$lock l
          where txt.address = s.sql_address
          and s.sid = l.sid
          and l.block = 1
          order by s.sid, txt.piece)
  group by sid order by sid;

Finally the following SQL shows the SQL executed by the sessions that are waiting for blocking locks:

select sid as waiting_sid, wm_concat(sql_text) as waiting_sql
  from (select s.sid, txt.sql_text
          from gv$sqltext txt, gv$session s, gv$lock lb, gv$lock lw
          where txt.address = s.sql_address
          and s.sid = lw.sid
          and lw.id1 = lb.id1
          and lb.block = 1
          and lw.request > 0
          order by s.sid, txt.piece)
  group by sid order by sid;

Armed with this information the root cause is often obvious. The user can be told to commit or rollback, a session can be killed or perhaps an application issue resolved.

Categories: Database, Oracle

Logging OC4J errors to mail

A common problem customers face with ESB and BPEL in OC4J is how to find and react to low-level errors. Most errors can be handled and are visible in the consoles, but some types of fatal errors are only reported to the log files. For example, if an inbound FTP-adapter fails to delete a file it will log the error and shutdown. If the server is restarted it will process the file again, causing a duplicate.

One solution is to monitor the log files, either manually or with tools. However, without tool support the operators may have a hard time keeping up with the logs. Fortunately the logging framework can be configured to send mail for serious errors.

JavaMail comes with a logging handler starting with version 1.4.3. Replace the existing file $ORACLE_HOME/j2ee/home/lib/mail.jar with the one from JavaMail 1.4.3 if necessary, then edit $ORACLE_HOME/j2ee/oc4j_soa/config/j2ee-logging.xml (assuming that the OC4J-container is oc4j_soa).
The mail handler should be configured in a logging_properties section. Add a new log_handler using com.sun.mail.util.logging.MailHandler. Modify the existing logger for "oracle" to use both the old oc4j-handler and the new mail handler.


   <!-- Logging properties for non-ODL loggers -->
      <property name="com.sun.mail.util.logging.MailHandler.level"
      <property name="com.sun.mail.util.logging.MailHandler.pushLevel"
      <property name=""
      <property name=""
      <property name="com.sun.mail.util.logging.MailHandler.mail.from"
      <property name="com.sun.mail.util.logging.MailHandler.mail.sender"
      <property name="com.sun.mail.util.logging.MailHandler.subject"
         value="Suitable subject"/>
      <!-- Standard handlers here, for example oc4j-handler -->

      <log_handler name="mail-notification-handler"
      <logger name="oracle" level="INFO" useParentHandlers="false">
         <handler name="oc4j-handler"/>
         <handler name="mail-notification-handler"/>

      <!-- Other loggers here, no changes -->

With this configuration messages are sent for errors (level SEVERE), warnings and other messages are only logged to the standard log files. Obviously the mail server and all the addresses must be replaced.

Categories: SOA Suite

Debugging remote OC4J

This is a short howto for debugging Java code running in a remote OC4J in OAS 10. It only presents the basics, for more details refer to the JavaTM Platform Debugger Architecture.

To enable debugging, edit $ORACLE_HOME/opmn/conf/opmn.xml. Find the process-type tag for the OC4J container you want, for example oc4j_soa. Find the next data tag with id "java-options". Add the following:

-Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=y,address=4000

Reload opmn and restart the process, for example:

opmnctl reload
opmnctl restartproc gid=oc4j_soa

The JVM will suspend on startup and wait for a debugger to connect. To avoid that use suspend=n, but it is often useful to debug code that runs only during startup.

Time to connect with a debugger.

In jDeveloper select "Project Properties" for a Java project, find "Run/Debug" in the dialog and create a new or edit an existing configuration. In the tree view for the configuration, select "Remote". Select protocol "Attach to JPDA" and enter the host name for the application server. The port should match the address in the runjdwp option.

In Eclipse, select "Run/Debug Configurations...", select "Remote Java Application" and create a new configuration. Connection type should be "Standard (Socket Attach)" which is the default. Simply fill in the correct host and port.

Categories: SOA Suite

Using SQL Server from ESB or BPEL in OC4J

Download Microsoft SQL Server JDBC Driver 2.0. When prompted pick the Unix version, which is an archive. Extract the files and locate sqljdbc.jar.

There are several ways to make the classes in the driver available to the platform, for example a shared library can be created. However, the easiest approach is to simply copy sqljdbc.jar to $ORACLE_HOME/applib. Restart the server.

Open Enterprise Manager and select Administration for the oc4j container where the driver is needed. Select JDBC Resources and create a new Connection Pool. Fill in Name, Connection Factory Class, JDBC URL, Username and Password. The correct connection factory class is and the URL should be jdbc:sqlserver://host:1433;databaseName=somedb where host and somedb are the names of the database host and the database itself. The port (1433) may need to be changed as well. Test the connection with:


To use the connection factory with a database adapter, create two data sources as managed data sources using the connection pool, for example MSSQLDataSource and MSSQLDataSource-XA with JNDI locations jdbc/MSSQLDataSource and jdbc/MSSQLDataSource-XA (the names are arbitrary). Navigate to applications and click on default. In the Modules list, click on DbAdapter and select Connection Factories. Create a new one with dataSourceName=jdbc/MSSQLDataSource and xADataSourceName=jdbc/MSSQLDataSource-XA.

In jDeveloper, copy the jar file to j2ee\home\applib or create a shared library. Create a new connection. The driver class should be Test the connection. It should now be possible to use the normal adapter wizards with SQL Server and to deploy and run the finished code.

Categories: Database, SOA Suite