Category Archives: Database

OTech Magazine – Winter 2014

OTech Magazine winter 2014The winter edition of OTech Magazine, the independent magazine for Oracle professionals, is available. In OTech Magazine – Winter 2014 subjects include:

  • Time Series Forecasting in SQL
  • Flashback – Empowering Power Users
  • Starting WebLogic
  • DevOps and Continuous Delivery for Oracle
  • The Rapid Reaction Force – real time business monitoring

Download OTech Magazine Winter 2014 here.

OTech Magazine – Summer 2014

OTech magazineThe fourth issue of OTech Magazine, the independent magazine for Oracle professionals, is available. For the summer 2014 issue there are some more personal articles included. Titles vary from The Spiritual Programmer to The Next Generation: Oracle SOA Suite 12c and back via How to protect your sensitive data using Oracle Data Vault.

Download OTech Magazine – Summer 2014 here.

Upgrading to SOA Suite PS5 on Oracle XE

Installing PS5 ( can cause some problems when using Oracle XE as the infrastucture database. The solution is described in this SOA Suite / BPM blog by Niall Commiskey.

More tips on upgrading OFM 11g to patch set 5

My colleague Laurens van der Starre published a serie of tips and tricks on upgrading Oracle Fusion Middleware to PS5 on the Whitehorses blog.

More considerations on upgrading to OFM

There is an additional article on upgrading to OFM on the Whitehorses blog. It goes into more detail on JDK 7 support, OWSM in SOA Suite, the combination of OSB en OWSM and for example the PSA (Patch Set Assistant) performance.

Java memory arguments in OFM

More on setting Java memory arguments in SOA Suite

Good practises for SOA Database Adapter

Sharing some things we learned while working with SOA Suite 10g database adapters.

  • Restrict the number of rows that will be returned by the query that is used in the database adapter. If you do not restrict the number of rows that is returned that could lead to high CPU and memory usage and time outs due to extreme processing time. These conditions can appear when a query results in severla thousand of rows returned.
  • Changes the this_toplink_mappings.xml file. If a toplink:read-all-query is used, change it to toplink:max-rows like
  • <toplink:max-rows>100</toplink:max-rows>
  • In case you forgot… if the audit level of the BPEL domain is still on Development level change it to the production level.

For links to File and FTP adapter stuff.

Maintaining SOA Suite infrastructure database

While trying to keep my post on purging the BPEL and ESB/Mediator database up to date with the latest documents and experiences, I found these whitepapers and references:

  • SOA 11G Database (pdf) Growth Management Strategy
  • An Oracle SOA 11G installation presents a few challenges for administrators and one of these is managing the growth of the SOA database. The advice facilitates better dialog between SOA and Database administrators when planning and managing database requirements.

  • On Oracle support: SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide [ID 1384379.1]
  • This document covers everything you need to know about the SOA Infrastructure Database for Oracle SOA Suite 11g. Here we bring together content from sources such as OTN, Product Documentation and our Knowledge Base in an effort to provide you with a single comprehensive resource.

Running SOA Suite on Amazon EC2

SOA Suite on EC2

SOA Suite on EC2

One of the things on my To Do list was to move my local SOA Suite 11g R1 to The Cloud. It seemed a good idea to save my laptop some resources (to spare some for JDeveloper) with only a limited investment. Besides that it can be a good way to demo applications, and work together with my colleagues on these demos.

During the last months I noticed that there are several good blogpost on the subject. In this post I’ll show you the ones I used and provide some additions to them.

Setting up Amazon Web Services (EC2 and S3)

This arcticle on OTN guided me while signing up for:

  • Amazon AWS
  • Amazon S3 – Simple Storage Service
  • Amazon EC2 – Elastic Compute Cloud

and to setup PuTTY. The only hick-up here was that I’m using the PortableApps version of PuTTY that doesn’t come with the puttygen – Key Generator.

Provisioning a SOA Server on Amazon EC2

This blogpost guided me in the provisioning of the AMI (Amazon Machine Image).

  • AMIs are per region: The Amazon Machine Instance (AMI) for SOA Suite (id = ami-acb557c5) is only available in the US East (Northern Virginia) Region.
  • Don’t bother to setup the Elastic Block Store (EBS) Volume. It is scripted in the latest version of the AMI, as described in step 5 of “SSH to your image and accept license”. The EBS Volume is seeded using a snapshot (id = snap-dd980db4) that is provided. This volume will be used to persist your data across sessions and AMI start/stop.
  • When launching the image (during the Configure Firewall step) set the SecurityGroup to accept HTTP traffic on port 7001 in case you want to use the SOA Suite from outside the Image.

Monitoring AQ

Although there were already several posts on querying AQ, like “in the queue” and “enqueue“, the next one is great when monitoring AQ. We are using this to monitor AQ, especially for Oracle ESB (hence the commented addition):

,      aq.queue_table
,      aq.queue_type
,      v$aq.* 
FROM   v$aq
,      all_queues aq
WHERE  aq.qid = v$aq.qid
-- use if looking for ESB AQ: and    aq.queue_table = 'ESB_JAVA_DEFERRED';

The query will result in something like:

Monitoring AQ query results

It contains the following metrics:

  • Waiting – Number of waiting message;
  • Ready – Number of messages with status ready;
  • Expired – Number of expired messages;
  • Total Wait – Total waiting time per queue;
  • Average Wait – Average waiting time per queue.

A describtion of the AQ and Streams views can be found here.

Will AQ enqueue and dequeue messages

Whether or not your Oracle Advanced Queue (AQ) will enqueue and dequeue messages can be determined with a simple query:

,      queue_table
,      enqueue_enabled
,      dequeue_enabled
,      max_retries
,      retry_delay
FROM   user_queues

The query example is based on user_queues and in that case it has to be performed as the owner of the queue. The result wil be something like:

AQ enqueu dequeue

AQ enqueu dequeue

What is in the (Oracle Advanced) queue? – Monitoring AQ

As a quick follow up on the previous post… If you are using a JMS queue with Oracle´s Advanced Queueing or Streams (as they have been extended to) the previously described tool can´t help you out. You need something else for monitoring AQ.

For this case a blog by a former colleague helps us out. This approach to monitoring AQ boils down to…

Find the queue you want to monitor AQ.

SELECT owner
,      queue_table
,      TYPE
,      user_comment
FROM   all_queue_tables

Perform a query.

SELECT msgid
,      enq_time
,      enq_uid
,      qt.user_data.text_vc
FROM   my_queue_table qt  --alter the table name
WHERE  q_name = 'MY_QUEUE' --alter the queue name