Consolidation of SOA databases

Author: Christian Saaler –

This article briefly describes the consolidation of multiple Oracle databases that belong to an existing Oracle SOA Suite environment. In case you are running multiple SOA environments that are spread over multiple database and application servers, it enhances the maintainability and reduces licensing costs to consolidate underlying databases on just one single server. Especially when it comes to licensing costs this can save a huge amount of money. In what follows we will cover all relevant steps that you should consider when you plan to perform such a shift. This also covers the process of just migrating a database to another server with changing the hostname.

System architecture

At first you have to regard that SID and hostname of your databases are changing. So it is a good start to get an overview of your systems and plan the assignments of database and application servers.

In our use case we move the database of a SOA Suite 10g instance onto a new server to consolidate it. The hostnames of source and target database servers are called ‘ODB1’ and ‘ODB2’. The SIDs of the source and target database instances are ‘SOAO’ and ‘SOAN’.


Of course it is important that you accurately plan the whole operation. Figure 1 gives you a rough idea of how we are going to perform this. At first we prepare the database. Preparing particularly means to install and configure a new database server or create an additional instance on which the former databases should be moved. After the installation you have to consider a few more things to ensure an easy migration. This is also necessary when your target database already exists. When the new database is ready we move on to the main part. After stopping the relevant source application server you have to migrate the data from the old to the new database and adjust the configuration. When this is done you can restart the environment. It is highly recommended to accomplish the whole process inside a test environment at first. Additionally you should do some initial testing after performing it on your live system.

Figure 1: The process of consolidation


The following will give a quick demonstration of what to do within the single steps shown in Figure 1.

Step 1: Database preparation

As mentioned above you may need to install a database as the new target database. In our use case we prefer Oracle Database 11g as the target dbms. Since there are lots of tutorials about how to set it up (e.g. Database Installation Guide) we will skip this part.

When you create your database instance you have to check that it supports the AL32UTF8 character set. Besides you should check that the processes parameter is set to at least 300. This is necessary to properly prepare it for a SOA deployment. If your target instance is already in use you should additionally check that the schema names you used on the source system are available.

When the database is finally ready we create the tablespace for the migrated data:
‘/opt/oracle/SOAN/soa10g.dbf’ SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 10G,

On the source database we create a directory for the data to export. It is convenient to use shared storage. So you can easily mount the directory on the target system and import the dump without the need of actually transferring data.


Please note: If you use an NFS share you may receive ORA-27054 which means that your share is not mounted properly. Oracle published a document to solve this error (ID 781349.1).

Step 2: Shut down SOA

Stop your SOA environment with all its components.
Please note: Remember to schedule enough downtime.

Step 3.1: Migrate data

To dump the data of the source database we use Data Pump Export (expdp). With expdp it is possible to either export schemas, tables, tablespaces or the whole database. In our use case we export multiple schemas which are located in separated tablespaces.


Switching over to the new database server we mount the storage where the data has been exported to, change the permissions and copy it to the local DATA_PUMP_DIR.

chown oracle:dba /mnt/repo/Oracle/ODB1/SOAO.*
cp /mnt/repo/Oracle/ODB1/SOAO.* /opt/oracle/admin/SOAN/dpdump/

Afterwards we import the data and remap the tablespaces with the following statement.


Step 3.2: Adjust application

When we finished migrating the data onto the new database server we adjust the application. For this purpose we need to load the SOA environment on the application server and replace all references to hostname and SID of the old database server. To do so we have to adjust the data sources and adapter configuration files.

  • To adjust the database connection properties for ORABPEL and ORAESB replace the hostname of the source database (ODB1) with the target one (ODB2) and the old SID (SOAO) with the new one (SOAN) in the following file
  • To adjust the database connection properties for IWAY again replace source with target database hostname and old with new SID in the following two files


  • Lastly adjust the OWSM configuration again by replacing source with target database hostname and old with new SID in the following file


After completing this we use the wsmadmin script to load the changed values from the last file. This is necessary for the deployment command in the next step.
$ORACLE_HOME/owsm/bin/ copyDBConfig

Step 4: Restart SOA

Now we can start the application server again. To complete the adjustment process we need to execute the wsmadmin script with the ‘deploy all’ option:
$ORACLE_HOME/owsm/bin/ deploy all

Finally we restart the environment and are good to go.

Please note: Don’t forget to also change the references to any custom applications hosted in your SOA database as well (e.g. in the data source tab of enterprise Manager if access happens in that way).

Check database sessions

After we are done with the shift to the consolidated database server we can check the database sessions to ensure that we adjusted everything correctly so that the application does not try to access the old database. For this purpose we need to activate the session auditing on the old database.
SQL> alter system set audit_trail=DB scope=SPFILE;
SQL> audit session;

Afterwards we need to restart the old database and the SOA environment. With the following statement we can then check the sessions.

SQL> select username, to_char(TIMESTAMP, ‘DD-MM-YYYY HH24:MI:SS’) from dba_audit_session where username not like ‘DBSNMP’ order by 2;

If everything worked as expected, no new sessions should appear in the audit of the old database. Then you can finally shut down the old database.