Changing the SOA Suite database

Author: Robin Zimmermann
Date: November 2006

Introduction

Several components of Oracle SOA Suite 10.1.3.1.0 require a connection to a database, namely BPEL Process Manager, Enterprise Service Bus (ESB), and Web Services Manager (OWSM). If you chose a basic installation type (the most common choice) then Oracle Lite was automatically installed and SOA Suite was automatically configured to point to it. The benefit of this is that you can be up and running quickly with minimal configuration, and Oracle Lite does not consume a lot of resources. There are two disadvantages, though:

Oracle Lite is not well suited to multiple installations. If you would like to have more than one installation of SOA Suite, you still only have a single Oracle Lite instance. And unless it is managed correctly (which is tedious and most people don't know how to do that), you will end up with a corrupt instance.
Oracle Lite is not intended for high performance. If you attempt any type of performance testing you will quickly hit the limitations of Oracle Lite.

Even if you don't have any issues with Oracle Lite, you may have some other reason to use a different database after you install Oracle SOA Suite.

This document will take you through the steps of configuring your Oracle SOA Suite instance to point to a different database.

Multiple Installations

Once you know how to remove your dependency on Oracle Lite, that frees you to have multiple installations of SOA Suite on the same machine. See Appendix B for the steps on how to do that.

Caveats

This document is not exhaustive and does not take you through all possibilities.

The instructions in this paper only discuss pointing your Oracle SOA Suite instance to an Oracle database (such as Oracle 10g or Oracle XE). It is possible to use other databases but it's beyond the scope of this document.
No instructions are given for migrating data from your existing database to the new database. After following these steps your database instance will be fresh, and you won't have any of your previous deployments, instances, and audit trails for BPEL Process Manager, ESB, and OWSM.

The steps in this paper don't actually delete any data from the original database instance, so it is still available if you wish to point back to that database, or extract the information in some way.
In following with the previous point, it makes sense to follow these steps as soon as possible after you install SOA Suite. The longer you leave it, the more data you will lose or extra configuration you will have to do. In particular, this can have an effect on OWSM. If you already had OWSM Gateways or Agents configured, you will first need to re-create them, which will possibly result in different Gateway and Agent IDs. Then you will need to update the services that use those Gateways and Agents with the new IDs.

Conventions

Paths

This document refers to various paths. When you see any of these variables, replace them with the corresponding values for your environment.

${SOA_HOME}
The location you installed SOA Suite to.
Example: If ${SOA_HOME} = C:\product\10.1.3.1\OracleAS_1 then ${SOA_HOME}\opmn\bin would be C:\product\10.1.3.1\OracleAS_1\opmn\bin.

Database

These instructions were tested using Oracle Express Edition (also known as Oracle XE). Oracle XE is a great choice because it based on the same code-line as the Oracle Enterprise Edition RDBMS, but has a small footprint and is more entry level. It also has a free license for development and deployment, and if you ever outgrow it, it's easy to upgrade to Oracle Enterprise Edition because it's the same code base.

Within this paper, the original database you are using (probably, but not necessarily, Oracle Lite) is referred to as the "original" database. The new database that you wish to use will be referred to as the "target" or "new" database.

Prompts

During the steps, you will be instructed to enter commands at various points. Commands for the command line will be preceded with %. For example:

% cd c:\oracle\soasuite
% dir

Commands against the in MSQL (for Oracle Lite) or SQL*Plus (for the Oracle database) will be preceded with SQL>. For example:

SQL> connect scott/tiger
SQL> select * from dual;

Do not type the prompt (i.e. %, SQL>, etc) when entering commands.

Steps

Broadly speaking there are two main tasks:

Install the SOA schemas into the target database.
Configure SOA Suite to point to the target database.

Ensure that you have a working version of Oracle SOA Suite 10.1.3.1.0 installed. If it is running, then stop it. See Appendix A if you're not sure how to do this.

Ensure that you have a target database (such as Oracle XE) installed and running.

Edit MyIrca.bat (supplied in the same directory as this document) using a text editor. Set the environment variables at the top of the file until you reach the "Do not modify anything else beyond here." comment:

ORACLE_HOME Location of target database - See note 1

SOA_HOME Location of SOA Suite installation - See note 1

BPEL_USER Username for the BPEL schema - See note 2

BPEL_PW Password for the BPEL schema

ESB_USER Username for the ESB schema - See note 2

ESB_PW Password for the ESB schema

OWSM_USER Username for the OWSM schema - See note 2

OWSM_PW Password for the OWSM schema

SYS_PW SYS password

CONNECT_STRING Database name/SID

Save the file and close it, you won't need to edit it again.

Note 1: You must use a path without spaces. If you have a path with spaces in the directory name or filename, then see Appendix C to instructions on using the shortname with no spaces.

Note 2: Don't use orabpel, oraesb, or oraowsm as the schema names. That's because some scripts and install types are hard-coded to use those names and they could inadvertantly destroy your existing data. Treat those schema names as reserved words and don't use them.

Super Tip: In fact, it would be a good idea to link the schema names to your installation. If you have several installations there is no way to tell which schema goes with which installation. So you either have to remember them, or use this tip, and incorporate the installation into the schema name. For example, if your SOA Suite installation is in C:\product\10.1.3.1\OracleAS_1, then make your schema names orabpel_OracleAS_1, oraesb_OracleAS_1, oraowsm_OracleAS_1. Since you'll rarely ever connect to these schemas, the inconveniently long name doesn't matter. And you'll always be able to tell which schema goes with which installation.

Run MyIrca.bat. This will install the SOA schemas for BPEL, ESB, and OWSM into the target database. You will see some errors which can be ignored, when the scripts delete objects that haven't been created yet. The script is re-runnable.

Note: You can also use what are known as the IRCA scripts to install the SOA schemas. The BPEL Installation Guide contains instructions on how to do that. The disadvantage of using the IRCA scripts is that they are only available in the installation ZIP file or CD. They are not saved into the installation itself. So if you don't have the CD handy or have removed your staging area you won't have them. The steps in this paper utilize scripts that are available in the installation itself and will therefore always work.

Open ${SOA_HOME}\j2ee\home\config\data-sources.xml in a text editor.

Find the <connection-pool> tag for BPEL:

<connection-pool name="BPELPM_CONNECTION_POOL">

Replace the <connection-factory> tag for the current original database with the one for the new target database. Here is an example:

<connection-pool name="BPELPM_CONNECTION_POOL">
  <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
                      user="orabpel_OracleAS_1"
                      password="orabpel"
                      url="jdbc:oracle:thin:@localhost:1521:xe"/>
</connection-pool>

Make sure that the following attributes are set correct for your environment:

For an Oracle database, factory-class should be oracle.jdbc.pool.OracleDataSource.
user and password should be whatever you specified in step 3 for BPEL_USER and BPEL_PW.
url should be the JDBC URL for your environment. The example shown, above, is for a database on the same machine, with the default database port of 1521, and a database name/SID of "xe".

Still in the same file, find the <connection-pool> tag for ESB:

<connection-pool name="ESBPool">

Replace the <connection-factory> tag for the current original database with the one for the new target database. Here is an example:

<connection-pool name="ESBPool">
  <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
                      user="oraesb_OracleAS_1"
                      password="oraesb"
                      url="jdbc:oracle:thin:@localhost:1521:xe"/>
</connection-pool>

Make sure that the following attributes are set correct for your environment:

For an Oracle database, factory-class should be oracle.jdbc.pool.OracleDataSource.
user and password should be whatever you specified in step 3 for ESB_USER and ESB_PW.
url should be the JDBC URL for your environment. The example shown, above, is for a database on the same machine, with the default database port of 1521, and a database name/SID of "xe".

Save and close data-sources.xml (OWSM is handled differently, it doesn't use the settings in this file and will be configured later).

One of the ESB tables needs to be populated with some information. We will extract that information from the orignal database in order to insert it into the new database.

Open an ESB Developer Prompt, using one of these methods:

From the Windows Start menu: Start > Programs > Oracle - OracleAS_1 > Oracle ESB > Developer Prompt
From a command line, run ${SOA_HOME}\integration\esb\bin\esbdevprompt.bat

10.

At the command line prompt, enter the following:

% set PATH=${SOA_HOME}\jdk\jre\bin\server;%PATH%
% ${SOA_HOME}\Mobile\Sdk\BIN\msql system/any@jdbc:polite4@localhost:1531:oraesb

(The first line adds jvm.dll to the path. The second connects you to the Oracle Lite database.)

11.

Now that you are in the database, enter the following commands:

SQL> spool params.sql
SQL> SELECT 'INSERT INTO esb_parameter VALUES (''' || param_name || ''', ''' || param_value || ''');' FROM esb_parameter;
SQL> spool off
SQL> quit

Make sure you get the correct number of quotes. Note that they are all single quotes, no double quotes. Your best option would be to copy and paste the SELECT statement to avoid typing errors.

The result of these statements will be that you exited Oracle Lite and there is an output file called params.sql. Keep the command line prompt open, you will need it again, or note the the directory that params.sql is in.

12.

Using a text editor, open params.sql. Remove all lines except for the INSERT lines. Save the file and exit. Your file should look something like this:

INSERT INTO esb_parameter VALUES ('PROP_NAME_MONITOR_TOPIC_JNDI', 'OracleASjms/ESBMonitorTopic');
INSERT INTO esb_parameter VALUES ('PROP_NAME_ERROR_XATCF_JNDI', 'OracleASjms/MyXATCF');
INSERT INTO esb_parameter VALUES ('PROP_NAME_ERROR_RETRY_JNDI', 'OracleASjms/ESBErrorRetryTopic');
INSERT INTO esb_parameter VALUES ('DT_OC4J_HTTP_PORT', '8888');
INSERT INTO esb_parameter VALUES ('PROP_NAME_CONTROL_TCF_JNDI', 'OracleASjms/MyXATCF');
INSERT INTO esb_parameter VALUES ('PROP_NAME_MONITOR_TCF_JNDI', 'OracleASjms/MyTCF');
INSERT INTO esb_parameter VALUES ('PROP_NAME_ERROR_TOPIC_JNDI', 'OracleASjms/ESBErrorTopic');
INSERT INTO esb_parameter VALUES ('PROP_NAME_ERROR_TCF_JNDI', 'OracleASjms/MyTCF');
INSERT INTO esb_parameter VALUES ('PROP_NAME_CONTROL_TOPIC_JNDI', 'OracleASjms/ESBControlTopic');
INSERT INTO esb_parameter VALUES ('PROP_NAME_DEFERRED_TCF_JNDI', 'OracleASjms/MyTCF');
INSERT INTO esb_parameter VALUES ('DT_OC4J_HOST', 'myhostname');
INSERT INTO esb_parameter VALUES ('PROP_NAME_DEFERRED_XATCF_JNDI', 'OracleASjms/MyXATCF');
INSERT INTO esb_parameter VALUES ('PROP_NAME_ERROR_RETRY_TCF_JNDI', 'OracleASjms/MyXATCF');

13.

Connect to the target database:

% sqlplus ESB_USER/ESB_USER@CONNECT_STRING

where ESB_USER, ESB_USER, and CONNECT_STRING are the values you specified in Step 3.

For example: % sqlplus oraesb_OracleAS_1/oraesb@xe

14.

Once in SQL*Plus enter this command, run the params.sql file to insert the values. You'll also need to commit.

SQL> @params.sql
SQL> commit;
SQL> exit

15.

BPEL and ESB are now configured to use the new target database. Next, OWSM needs to have its configuration done.

Start the SOA Suite server. See Appendix A if you're not sure how to do this. Rather than waiting until the server is fully running before continuing, you can keep going with the next steps immediately while the server starts up in the background.

16.

Using a text editor, open ${SOA_HOME}\owsm\bin\install_properties

17.

Scroll down to the section that contains the install.db.* properties. After backing up the existing settings, modify them for your environment:

install.db.type=oracle
install.db.driver.type=thin
install.db.host=localhost
install.db.port=1521
install.db.name=xe
install.db.userid=oraowsm_OracleAS_1
install.db.password=oraowsm

Make sure install.db.userid and install.db.password are whatever you specified in step 3 for ESB_USER and ESB_PW.

18.

Save and close install_properties.

19.

Open a command window (Start > Run > cmd on Windows).

20.

Wait until the server has fully started, from step 9. See Appendix A on how to check the status and see if the server is running.

21.

Run the following command:

${SOA_HOME}\owsm\bin\wsmadmin.bat install <oc4jAdminPassword> <databasePassword>

where <oc4jAdminPassword> is the password for oc4jadmin (on the application server), typically "welcome1", and <databasePassword> is the password for the OWSM schema (which you set in step 3).

For example:

${SOA_HOME}\owsm\bin\wsmadmin.bat install welcome1 oraowsm

("oraowsm" is the password for OWSM_PW in step 3.)

22.

At this point, the server is running and using the new target database. But there is still some housekeeping that needs to be done.

23.

Using a browser, navigate to the SOA Suite Welcome Page. For example: http://localhost:8888

24.

Click on the BPEL Control link (from the portlet in the upper right corner of the page) to open the BPEL Control. Notice that there are no BPEL processes deployed:

However there two system processes that are necessary, so we need to redeploy them.

25.

From the ${SOA_HOME}\bpel\install\extensions directory, copy the following files to ${SOA_HOME}\bpel\domains\default\deploy:

bpel_TaskActionHandler_1.0.jar
bpel_TaskManager_1.0.jar

Click on the Dashboard tab of the BPEL Control and verify that the tasks are there (as shown in the image below). If you don't see them immediately, click the Dashboard tab every few moments until they appear.

26.

If you already had OWSM Gateways or Agents configured, you will first need to re-create them, which will possibly result in different Gateway and Agent IDs. Then you will need to update the services that use those Gateways and Agents with the new IDs.

27.

The change is complete. You can now use SOA Suite against your new database. You can disable or delete the original database as it won't be used anymore.

If you have performance issues or intermittent datasource connectivity issues with misleading suggestions to check the username/pw at the datasource and connection pool definition levels, see Appendix D for some database tuning tips.

Appendix A - Starting and stopping Oracle SOA Suite

There are multiple ways to start and stop Oracle SOA Suite 10.1.3.1.0. Here is a recommended way:

Open a command window (Start > Run > cmd on Windows).
Change directory to ${SOA_HOME}\opmn\bin.
Type opmnctl startall to start SOA Suite.
Type opmnctl stopall to stop SOA Suite.

If you're not sure if it's running or not, type opmnctl status. Here is some example output:

     Processes in Instance: soa101310.myhost
     ---------------------------------+--------------------+---------+---------
     ias-component                    | process-type       |     pid | status
     ---------------------------------+--------------------+---------+---------
     OC4JGroup:default_group          | OC4J:home          |    2576 | Init
     ASG                              | ASG                |     N/A | Down

If the status says "Init" (highlighted in red in the example) then the server is still starting. It will say "Alive" once the server has fully started.

Appendix B - Multiple Oracle SOA Suite Installations

Sometimes you will want to have multiple installations of SOA Suite on the same computer. This is entirely feasible -- unless you do a Basic Install. In that case the installer installs and configures Oracle Lite as the database used by the SOA Suite schemas. The problem with that is that Oracle Lite can only have one instance on a machine. So all data from the previous installations is destroyed and replaced with the most recent installation.

The solution is to either not use the Basic Install, or to not use Oracle Lite. Since using the Basic Install is desirable for its simplicity, here is how to still use the Basic Install, but get around the Oracle Lite problem by using a different database:

Install SOA Suite using the Basic Install type.
Configure that SOA Suite installation to use a different database (as described in this paper).
Repeat from step a.

In essence, after each installation of SOA Suite, make the first thing you do to change the database configuration to an Oracle database. Then it doesn't matter what the next installation does to Oracle Lite.

Appendix C - Windows Shortnames

Sometimes paths with spaces in them cause problems for scripts. Look at the following example:

% sqlplus C:\My Documents\createTables.sql

This looks like there are two parameters to SQL*Plus: "C:\My" and "Documents\createTables.sql".

The best way to avoid this is to never have paths with spaces, such as My Documents or Program Files. But sometimes it's unavoidable. In those cases, you can refer to the "shortname" of a file or directory. Every file and directory and Windows gets allocated a shortname, which is a hangover from the Windows 8.3 file naming scheme. To find the shortname of a file or dirctory, do a dir /x from the parent directory.

Example

Imagine the path and filename for a file is C:\Program Files\OracleXE\database scripts\create tables.sql. To determine the shortname of this path, open a command line and enter the following:

% cd \
% dir /x
 Volume in drive C is Local Disk
 Volume Serial Number is 2937-1D24

 Directory of C:\

   08/21/2005  11:50 AM                 0              AUTOEXEC.BAT
   08/21/2005  11:50 AM                 0              CONFIG.SYS
   10/10/2005  01:32 PM    <DIR>          DOCUME~1     Documents and Settings
   10/10/2005  11:34 AM    <DIR>                       Inetpub
   11/19/2006  04:38 PM    <DIR>          PROGRA~1     Program Files
   11/19/2006  04:27 PM    <DIR>                       Temp
   12/18/2006  10:00 AM    <DIR>                       WINDOWS
                  2 File(s)         10,426 bytes
                  5 Dir(s)   9,979,473,920 bytes free

The shortname appears to the left of the file or directory. In the case of Program Files, the shortname is PROGRA~1.

% cd PROGRA~1\OracleXE
% dir /x
 Volume in drive C is Local Disk
 Volume Serial Number is 2937-1D24

 Directory of C:\Program Files\oraclexe

   11/19/2006  04:38 PM    <DIR>                       .
   11/19/2006  04:38 PM    <DIR>                       ..
   12/05/2005  07:09 PM    <DIR>                       app
   11/19/2006  04:38 PM    <DIR>          DATABA~1     database scripts
   12/05/2005  07:12 PM    <DIR>                       oradata
                  0 File(s)              0 bytes 
                  5 Dir(s)   9,979,330,560 bytes free

From this output, you can see that the shortname for database scripts is DATABA~1. Our path also had a filename with a space, so continuing once again:

% cd DATABA~1
% dir /x
 Volume in drive C is Local Disk
 Volume Serial Number is 2937-1D24

 Directory of C:\Program Files\oraclexe

   11/19/2006  04:38 PM    <DIR>                       .
   11/19/2006  04:38 PM    <DIR>                       ..
   11/19/2006  04:38 PM                 0 CREATE~1.SQL create tables.sql
                  1 File(s)              0 bytes
                  2 Dir(s)   9,979,330,560 bytes free

From this output, you can see that the shortname for our file is CREAT~1.SQL.

Putting this alogether, our original path is:

C:\Program Files\OracleXE\database scripts\create tables.sql

and the shortname version is:

C:\PROGRA~1\oraclexe\DATABA~1\CREATE~1.SQL

It contains no spaces, and is valid to use anywhere in Windows where you would use a path. As a test, the two commands should give the same output:

% dir C:\Program Files\OracleXE\database scripts\create tables.sql
% dir C:\PROGRA~1\oraclexe\DATABA~1\CREATE~1.SQL

(A far easier way to get the shortname for a path is to install the incredibly useful Ninotech Path Copy utility: http://home.worldonline.dk/ninotech)

Appendix D - Performance Tuning

(Thanks to George Navarro.)

By default, Oracle Database XE is tuned to be tiny. Specifically, the system parameter for the number of processes is set to 40 by default. With the number of connection pools used by the SOA Suite and demo, this settting is simply too low. Changing the PROCESSES setting to something larger corrects this bottleneck:

Connect to XE as a SYSDBA.
In SQL*Plus, enter the following command:

alter system set processes=100 scope=spfile;

Below are some additional parameters worth adjusting to increase performance:

alter system set session_cached_cursors=100 scope=spfile;
alter system set session_max_open_files=100 scope=spfile;
alter system set sessions=100 scope=spfile;
alter system set license_max_sessions=100 scope=spfile;
alter system set license_sessions_warning=100 scope=spfile;