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.
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
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_configuration> <!-- Logging properties for non-ODL loggers --> <logging_properties> <property name="com.sun.mail.util.logging.MailHandler.level" value="SEVERE"/> <property name="com.sun.mail.util.logging.MailHandler.pushLevel" value="SEVERE"/> <property name="com.sun.mail.util.logging.MailHandler.mail.smtp.host" value="mailserver-host-or-ip"/> <property name="com.sun.mail.util.logging.MailHandler.mail.to" value="firstname.lastname@example.org"/> <property name="com.sun.mail.util.logging.MailHandler.mail.from" value="email@example.com"/> <property name="com.sun.mail.util.logging.MailHandler.mail.sender" value="firstname.lastname@example.org"/> <property name="com.sun.mail.util.logging.MailHandler.subject" value="Suitable subject"/> </logging_properties> <log_handlers> <!-- Standard handlers here, for example oc4j-handler --> <log_handler name="mail-notification-handler" class="com.sun.mail.util.logging.MailHandler"/> </log_handlers> <loggers> <logger name="oracle" level="INFO" useParentHandlers="false"> <handler name="oc4j-handler"/> <handler name="mail-notification-handler"/> </logger> <!-- Other loggers here, no changes --> </loggers> </logging_configuration>
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.
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:
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.
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
com.microsoft.sqlserver.jdbc.SQLServerDataSource 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-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
In jDeveloper, copy the jar file to
j2ee\home\applib or create a shared library. Create a new connection. The driver class should be
com.microsoft.sqlserver.jdbc.SQLServerDriver. 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.