Tag Archives: Database

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.

Oracle releases NoSQL Database, Enterprise Edition 1.1.100

While the NoSQL Database Community Edition still waits for final license approval, Oracle has released it’s NoSQL Database, Enterprise Edition 1.1.100. The download is available on OTN.

The Oracle NoSQL Database is a distributed key-value database. It is designed to provide highly reliable, scalable and available data storage across a configurable set of systems that function as storage nodes.

The documentation page is still rather empty. However Technical Overview and a Data Sheet are already available.

Oracle NoSQL is a key offering in Oracle’s portfolio of Big Data products. The product is based on the Java version of Berkeley DB, an open source database used in embedded systems.


Create Data Source in WebLogic Server

While I had to document these steps for a customer that is new to WebLogic Server, I thought why not share these relatively easy steps in a blog post as well. So here they are. The screen shots that come with it are at the bottom.

Create Data Source

  1. Use a browser to go to the WLS Console, for example http://somehost:7001/console/login/LoginForm.jsp
  2. On the home page, click the Data Sources link. You can find it in the Domain Configurations, Services section;
  3. On the Summary of JDBC Data Sources page click the New button (to change current configuration click the link of the Data Sources you want to change);
  4. Choose the Name, JNDI Name, and select the Database Type (Oracle). Click Next to confirm and continue;
  5. Choose the Database Driver. This is depending on the selected Database Type. We used Thin XA for Instance connections for the Data Sources created in the project. Click Next;
  6. Click Next after you read the Transaction Options;
  7. Enter the Connection Properties and after that Next to confirm and continue:
    • Database Name
    • Host Name
    • Port
    • Database User Name
    • Password
  8. Click the Test Connection button, and if the test succeeded click next;
  9. On the Select Targets page check for example the AdminServer. Click finish.

More on configuring JDBC on WebLogic Server can be found here (and the PDF on JDBC admin).

VirtualBox Appliances for Developers

I’m a fan of ready to use appliances for development and demo purposes. Recently it was announced that additional developer Virtual Machines have been released by Oracle. These in addition to the SOA Suite and BPM appliance mentioned before.

Developers can simply download a few files, assemble them with a script , and then import and run the resulting pre-built VM in VirtualBox. This makes starting with these technologies even easier. Each appliance contains some Hands-On-Labs to start learning.

Java Developement

This VirtualBox Appliance contains:

  • Java SE (JDK) 1.6.0 u20 (note: Mac OS X 10.5 users must use Apple update)
  • Java FX 1.3.1 runtime
  • NetBeans IDE 6.9.1
  • GlassFish Server Open Source Edition 3.0.1 (installed with NetBeans)

The Aquarium says about this Java EE6 VM:

This is everything you need to get started with Java EE 6 development and you’ll even find three labs (with code and instructions) to start using JSF 2, JAX-RS 1.1, EJB 3.1, Servlet 3.0, JPA 2.0 and CDI 1.0!

For detailed instructions and downloads go here…

Oracle Database Development

This VirtualBox Appliance contains:

  • Oracle Enterprise Linux 5
  • Oracle Database 11g Release 2 Enterprise Edition
  • Oracle TimesTen In-Memory Database Cache
  • Oracle XML DB
  • Oracle SQL Developer
  • Oracle SQL Developer Data Modeler
  • Oracle Application Express 4.0
  • Oracle JDeveloper
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

For detailed instructions.

It is expected that more flavors will be available in 2011.

The future of MySQL with Oracle

MySQLFrom the time the news that Oracle was going to acquire Sun there has been much debate on what this would mean for MySQL. Today Edward Screven, Oracle’s chief corporate architect, revealed the future of MySQL in a keynote at The O’Reilly MySQL Conference & Expo 2010. The simple message was:

MySQL matters to Oracle.

Prior to this keynote mister Screven told Reuters:

We are increasing our investment in MySQL… on every front.

Oracle is already the biggest player in the database market. With Microsoft’s SQL Server as the target for MySQL, Oracle believes it can boost it’s sales. From this perspective MySQL gives Oracle an entry to other parts of the market. While on an other aspect Oracle has improve the relation with Microsoft, since it’s Operating System Windows is the number-one destination for MySQL downloads! While Linux is the number-one OS for deployments.

InfoWorld also had an interview with Edward Screven before the keynote. Here are some quotes from the InfoWorld interview:

MySQL has some properties that Oracle does not,It’s small, it’s easy to install. It’s easy for developers to get going with it.

And on the open source community edition:

I don’t see foresee any substantial changes from how MySQL AB or Sun made the distinction [between what was in the community and commercial editions]. I expect that core features will end up in community edition. There will be some value-add, like monitoring or backup, that make sense in the enterprise edition.


It would be a mistake for us to starve the community edition because that would impinge upon the ubiquity of MySQL.

The MySQL community now includes several forks of the MySQL core tool like MariaDB and Drizzle. Both produced by ex-MySQL employees. These are experimenting with different data storage engines and other enhancements.

I think it will be hard for those guys to create a forked product with the kind of commercial support that our customers need for production applications. We’re really focused on ensuring that MySQL becomes a better product and appeals to our customers. What we’re fundamentally selling here is support.

Sources: Reuters and InfoWorld

Other Sources

  • The Register: Oracle drops top architect into MySQL skeptic zone
  • The Wall Street Journal: Here’s proof we’ll improve MySQL

Tuning AQ for Oracle ESB

If you are using AQ within Oracle ESB there might be a point in time you want to tune AQ performance. In this post you’ll find the results of Metalink research, and our experience on a production system.

Queue compatibility

When creating the ORAESB schema (in version using the script $ORACLE_HOME/integration/esb/sql/oracle/create_esb_topics.sql, the queues are created with 8.1 compatibility. This is solved in To alter this find the statement

    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '8.1'

and change this to:

    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '10.2'

if you already created the queues, use this statement:

    ( queue_table => 'ESB_JAVA_DEFERRED'
    , compatible => '10.2'

If you’re not sure check the compatibility with this query:

SELECT queue_table
,      compatible
,      recipients
FROM   dba_QUEUE_tables
WHERE  owner = 'ORAESB'

Streams pool size

Verify the current stream_pool_size using the following query:

SELECT component
,      current_size/1024/1024 "CURRENT_SIZE"
,      min_size/1024/1024 "MIN_SIZE"
,      user_specified_size/1024/1024 "USER_SPECIFIED_SIZE"
,      last_oper_type "TYPE" 
FROM   v$sga_dynamic_components

look for the streams pool. There are several Metalink notes on this setting (including 316889.1, 102926.1 and 335516.1). The latter has a general recommandetion per RDBMS version:

  • 11g: set STREAMS_POOL_SIZE to be greater or equal to 100 MB;
  • 10gR2: set SGA_TARGET > 0 and STREAMS_POOL_SIZE=0 to enable autotuning of the Streams pool;
  • 10gR1: use the STREAMS_POOL_SIZE init.ora parameter to configure the Streams memory allocation;

And of course you could use V$STREAMS_POOL_ADVICE to get advice for your specific situation.

Upgrade the JDK

A described here upgrading the JDK can also give a performance boost.

Searching Oracle sample code

This week few initiatives to find sample code caught my eyes.

Oracle Sample Code Repository

Although there was already a sample code page on Oracle’s Technet. There probably were more… Oracle has created a Sample Code Repository. Like any repository this one should centralize and consolidate the large pile of samples that is available, both from Oracle product specialist, consultant, partners and customers.

Actually this could make it easier to find samples. To make this a success, there has to be a spirit of reuse. And as the major part of developers and architects out there can testify that’s not always easy part. It boils down to the same willingness for reuse of code that is required for a successful long term Service Oriented approach of software development within an organization.
Anyway some good examples are already available. A good introduction on samplecode at Oracle Technet can be found in this podcast (m4v) that features Duncan Mills. By the way, more podcasts can be found on the techcast page.

Google Code Search

Google Code Search is not exactly new, but it caught my attention because of some blogpost and comments I saw this week. To give it a try I did some queries with it and have mixed feelings so far. Asking around co-workers did not raise and enthusiastic crowd. Leaving me with the question whether any of you out there uses Google Code Search on a regular basis? And, do you have any examples for what kind of queries this works for you?
Thanks in advance for your comments.

Getting SOA Suite 10.1.3 to work with Oracle

Sometimes the speed of Oracle releases is hard to keep up with, as Antony Reynolds discovered and shared with us in his blog. Even when product combinations are certified with each other.

Oracle 11g database

The 11g release 1 of the database has been out long enough. It cannot be considered bleeding edge nowadays. So both from a features and longevity perspective it makes sense to deploy SOA Suite on a 11g R1 database. This blog post will show you the additional steps that are needed to install SOA Suite 10.1.3. on 11g R1.

Oracle Enterprise Linux

The SOA Suite installer checks whether the OS it is running on, is supported. At the time 10.1.3 was first released, Oracle Enterprise Linux 5 (OEL) wasn´t on the list. The result is that the installer fails. There is a patch (6339508) available and a blog post that describes the solution.

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