For my current assignment I need to get 3 SOA/B2B environments running. I'm going to try-out the multi-hop functionality, where the middle B2B environment should work as a dispatcher. The idea is that in Dev, Test and Pre Prod environments the dev environment can send messages to the remote trading partner's test environment, through the in-between-environment. To the remote trading partner, that in-between-environment should act as the local test environment, but then should be able to dispatch the message to the actual dev, test or pre-prod environment.

I envision  a solution, where this in-between B2B environment should act as this dispatching B2B hop. So I need to have 3 VMs running with their own database (although I could have them share one database), and Fusion Middleware domain.

The Vagrant project that I wrote about earlier this week, creates a database and then provisions all the FMW installations and a domain. That database is a 12cR1 database (that I could upgrade) that is installed default. In my setup it takes about 1.8GB of memory. My laptop is 16GB, so to have 2 VMs running on it, and let Windows have some memory too, I want to have a VM of at most 6,5 GB.
I need to run an AdminServer and a SOAServer, that I gave 1GB and GB respectively. And since they're not Docker containers, they both run an Oracle Linux 7 OS too.

So, one of the main steps is to downsize the database to "very small".

My starting point is an article I wrote years ago about shrinking an 11g database to XE proportions.
As described in that article I created an pfile as follows:

create pfile from spfile;

This creates an initorcl.ora in the $ORACLE_HOME/dbs folder.

I copied that file to initorcl.ora.small and editted it:

orcl.__data_transfer_cache_size=0
#orcl.__db_cache_size=1291845632
orcl.__db_cache_size=222298112
#orcl.__java_pool_size=16777216
orcl.__java_pool_size=10M
#orcl.__large_pool_size=33554432
orcl.__large_pool_size=4194304
orcl.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
#orcl.__pga_aggregate_target=620756992
orcl.__pga_aggregate_target=70M
#orcl.__sga_target=1828716544
orcl.__sga_target=210M
#orcl.__shared_io_pool_size=83886080
orcl.__shared_io_pool_size=0
#orcl.__shared_pool_size=385875968
orcl.__shared_pool_size=100M
orcl.__streams_pool_size=0
*.audit_file_dest='/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/app/oracle/oradata/orcl/control01.ctl','/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=578m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
#*.sga_target=1734m
*.sga_target=350M
*.undo_tablespace='UNDOTBS1'

The lines that I changed are copied with the original values commented out. So I downsized the db_cache_size, java_pool, large_pool and pga_aggregate_target. Also the sga_target, shared_io_pool(have it auto-managed) and shared_pool. I needed to set the sga_target to at least 350M, to get it started.
SOASuite needs at least  300 processes and open_cursors.

Now the script, checks if the database running. It is actually a copy of the startDB.sh script also in my Vagrant project.

If it is running, it shutdowns the database. It then creates a pfile for backup. If the database isn't running, it only creates the pfile.

Then it I copied that file to initorcl.ora.small and creates a spfile from it. And then it starts the database again.

#!/bin/bash
SCRIPTPATH=$(dirname $0)
#
. $SCRIPTPATH/../../install_env.sh
. $SCRIPTPATH/db12c_env.sh
#
db_num=`ps -ef|grep pmon |grep -v grep |awk 'END{print NR}'`

if [ $db_num -gt 0 ]
then 
  echo "Database Already RUNNING."
  $ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
shutdown immediate;
prompt create new initorcl.ora.
create pfile from spfile;
exit;
EOF
  #
  # With use of a plugable database the following line needs to be added after the startup command
  # startup pluggable database pdborcl; 
  #
  sleep 10
  echo "Database Services Successfully Stopped. "
else
  echo "Database Not yet RUNNING."
  $ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
prompt create new initorcl.ora.
create pfile from spfile;
exit;
EOF
  sleep 10
fi
#
echo Copy initorcl.ora.small to $ORACLE_HOME/dbs/initorcl.ora, with backup to $ORACLE_HOME/dbs/initorcl.ora.org
mv $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorcl.ora.org
cp $SCRIPTPATH/initorcl.ora.small $ORACLE_HOME/dbs/initorcl.ora
#
echo "Starting Oracle Database again..."
$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
create spfile from pfile;
startup;
exit;
EOF

The scripts can be found here.

Oh, by the way: I must state here that I'm not a DBA. I'm not sure if those settings make sense all together. (Should have someone review it). So you should not rely on them for a serious environment. Not even a development one. My motto is that a development environment is a developer's production environment. For me this is to be able to try something out. And to show the mechanism to you.

A very simple quick post today. I'm re-engineering a few Java based Mock Webservices into a SOA Suite/BPEL service.

Some of those generate a soap fault when a message id contains "8888" for instance.
I'd like to generate a GUID based message id, that is formatted with groups of four digits.

Of course there are loads of methods to do that. For instance, the Oracle database has a sys_guid() function for years. It generates a guid like: '824F95ECCB1C0EB7E053120B260A2D0F'.

But, I'd like it in a form '824F-95EC-CB1F-0EB7-E053-120B-260A-2D0F'. It can easily done by concatenating substr() calls. But you do not want to re-generate the guid with every 4 digit substr().

So, I put it into the following select:

with get_guid as (select sys_guid() guid
from dual)
select guid
, substr(guid, 1, 4)||'-'||substr(guid, 5, 4)||'-'||substr(guid, 9, 4)||'-'||substr(guid, 13, 4)||'-'||substr(guid, 17, 4)||'-'||substr(guid, 21, 4)||'-'||substr(guid, 25, 4) ||'-'||substr(guid, 29, 4)guid_formatted
, length(guid) guid_length
from get_guid;

What might be less obvious for the regular SQL developer is the with class. It is explained excelently by Tim Hall (and although around since Oracle 9.2 already, only recently put in my personal skill-box). This allows me in this query to call the sysguid() and reuse the value in the three columns.

Although this is a very simple query, it might come in handy more often. And since I'll be around this customer for a longer period, I expect, I want to save it as a snippet.

A feature around in SQLDeveloper for years are the snippets. You can make them visible through the View menu:
I tabbed-it away to the left gutter, to have it out of my way, but still in reach:
Create and edit snippets through the indicated icons. You can create your own categories, by just enter a new category name. Name it, provide a tool tip and paste the snippet. Easy-piecy.

You'll find quite a number of predefined snippets categorized neatly.

If you have gathered several of those snippets like me, and maybe want to take them to other assignments, you might feel the need to backup them.

To reuse a snippet just drag and drop them from the list into your worksheet.

The Snippets are stored in the UserSnippets.xml in the roaming user profile of SQL Developer:
In Windows like 'c:\Users\makker\AppData\Roaming\SQL Developer\'. Just backup/copy the file. Here you see the CodeTemplate.xml file as well, that contains the shorthand acronyms/aliases to much typed pieces of code that you can create too.

By the way, googling "That Jeff Smith Snippets" brought me this archived article (yes, snippets are that old) and with a link to this nice still active library of snippets.

At my current customer we use queues a lot. They're JMS queues, but in stead of Weblogic JMS, they're served by the Oracle database.

This is not new, in fact the Oracle database supports this since 8i through Advanced Queueing. Advanced Queueing is Oracle's Queueing implementation based on tables and views. That means you can query the queue table to get to the content of the queue. But you might know this already.

What I find few people know is that you shouldn't query the queue table directly but the accompanying AQ$ view instead. So, if your queue table is called MY_QUEUE_TAB, then you should query AQ$MY_QUEUE_TAB. So simply prefix the table name with  AQ$. Why? The AQ$ view is created automatically for you and joins the queue table with accompanying IOT tables to give you a proper and convenient representation of the state, subscriptions and other info of the messages. It is actually the supported wat of query the queue tables.

A JMS queue in AQ is implemented by creating them in queue tables based on the Oracle type
sys.aq$_jms_text_message type.

That is in fact a quite complex type definition that implements common JMS Text Message based queues. There are a few other types to support other JMS message types. But let's leave that.

Although the payload of the queue table is a complex type, you can get to its attributes in the query using the dot notation. But for that it is mandatory to have a table shortname and prefix the view columns with the table shortname.

The sys.aq$_jms_text_message has a few main attributes, such as text_lob for the content and header for the JMS header attributes. The header is based on the type sys.aq$_jms_header. You'll find the JMS type there. But also the properties attribute based on sys.aq$_jms_userproparray. That in its turn  is a varray based on aq$_jms_userproperty. Now, that makes it a bit complex, because we would like to know the values of the JMS properties, right?

We use those queues using the JMS adapter of SOA Suite and that adds properties containing the composite instance ID, ECID, etcetera. And if I happen to have a message that isn't picked up, it would be nice to know which Composite Instance enqueued this message, wouldn't it?

Luckily, a Varray can be considered as a collection of Oracle types. And do you know you  can query those? Simply provide it to the table() function and Oracle threats it as a table. When you know which properties you may expect, and their types, you can select them in the select clause of your query.  I found the properties that are set by SOA Suite and added them to my query. But you could find others as well.

Putting all this knowledge together, I came up with the following  query:

select qtb.queue
, qtb.msg_id
, qtb.msg_state
,qtb.enq_timestamp
--,qtb.user_data.header.replyto
,qtb.user_data.header.type type
,qtb.user_data.header.userid userid
,qtb.user_data.header.appid appid
,qtb.user_data.header.groupid groupid
,qtb.user_data.header.groupseq groupseq
--, qtb.user_data.header.properties properties
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_compositeInstanceId') tracking_compositeInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleDeliveryMode') JMS_OracleDeliveryMode
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_ecid') tracking_ecid
, (select num_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleTimestamp') JMS_OracleTimestamp
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_parentComponentInstanceId') tracking_prtCptInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_conversationId') tracking_conversationId
,qtb.user_data.header
,qtb.user_data.text_lob text
from AQ$MY_QUEUE_TAB qtb
where qtb.queue = 'MY_QUEUE'
order by enq_timestamp desc;

This delivered me an actual message that was not picked up by my process. And I could use  the property tracking_compositeInstanceId to find my soa composite instance in EM.

Very helpful if you are able to pause the consumption of your messages.

This also shows you how to query tables with complex nested tables.

In my previous blog posts (here and here), I wrote about how to create a base box and a create and start a virtual machine out of it. I started with provisioning, to have the vagrant user adapt the kernel settings, add a install user/owner and create a filesystem on an added disk.

Now let's make the provisioning a bit more interesting and install actual software in it.

Prepare new Vagrant project 

For this article I copied the project created from the previous blog. I called it ol75_db12c, since the goal is database 12c. But we'll also add java.
Now edit the Vagrantfile, since we want a new VM with another name:
So adapt the VM_NAME variable to something like "OL75U5_DB12c". You see how convenient it is to have those properties set as a global variable?

You could already try to do vagrant up to try it out. Remember, you can just do vagrant destroy to recreate it.

Also remove (or don't copy) the .vagrant subfolder, otherwise Vagrant would probably assume the box is already provisioned.  If that is the case, then either do a vagrant destroy to destroy the VM altogether, or vagrant provision to just re-provision the box.

Java

In the copied project, lets start with Java. There are several possibilities to install java, you could download the RPM from OTN. But one of the recommended practice I found in installing Java on a server is to put it in a path that hasn't got the java version (especially  the update) in it. When using it to install Weblogic, for instance, this path ends up in several places in scripts. Although it's a handfull, it's more than once.
Upgrading java is then just bringing down the servers/services using it, backup the version and unzip/untar the new version in the same folder. And there you have it: I like a java distribution that comes in an archive.

In Oracle Support you can find it by searching for the document All Java SE Downloads on MOS (Doc ID 1439822.1). There you find the current versions of the Java SE pacakges. For this article I used the public version JDK 8 Update 172, that you can download as patch 27412872:
 This contains the rpm as well as a .tar.gz package, that we'll use. Make sure that you download the x86_64 version:
And copy the download in the Stages folder in your vagrant main project folder (see previous blog).

To install it, I have the following script installJava.sh:

#!/bin/bash
#
#Download a zip with tar.gz containing complete JDK
#On MOS: Search for Doc ID 1439822.1
#Download latest 1.8 (public) patch, eg.:
#27412872  Oracle JDK 8 Update 172 (complete JDK, incl. jmc, jvisualvm)
#
SCRIPTPATH=$(dirname $0)
#
. $SCRIPTPATH/fmw12c_env.sh
#
TMP_DIR=/tmp
STAGE_HOME=/media/sf_Stage
EXTRACT_HOME=$STAGE_HOME/Extracted
JAVA_ZIP_HOME=$STAGE_HOME/Java
JAVA_INSTALL_HOME=$EXTRACT_HOME/Java
JAVA_INSTALL_TMP=$EXTRACT_HOME/jdk
JAVA_INSTALL_ZIP=p27412872_180172_Linux-x86-64.zip
JAVA_INSTALL_TAR=jdk-8u172-linux-x64.tar.gz
JAVA_INSTALL_NAME=jdk1.8.0_172

#
echo "Checking Java Home: "$JAVA_HOME
if [ ! -f "$JAVA_HOME/bin/java" ]; then
 #
  #Unzip Java
if [ ! -f "$JAVA_INSTALL_HOME/$JAVA_INSTALL_TAR" ]; then
    if [ -f "$JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP" ]; then
      echo Unzip $JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP  to $JAVA_INSTALL_HOME/$JAVA_INSTALL_TAR
      mkdir -p $JAVA_INSTALL_HOME
      unzip -o $JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP -d $JAVA_INSTALL_HOME
    else
      echo JAVA Zip File $JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP does not exist!
    fi  
  else
    echo $JAVA_INSTALL_TAR already unzipped
  fi
  # Install jdk
  echo Install jdk 
  echo create folder $JAVA_INSTALL_TMP
  mkdir -p $JAVA_INSTALL_TMP
  echo create JAVA_HOME $JAVA_HOME
  mkdir -p $JAVA_HOME
  echo Untar $JAVA_ZIP_HOME/$JAVA_INSTALL_TAR to $JAVA_INSTALL_TMP
  tar -xf $JAVA_INSTALL_HOME/$JAVA_INSTALL_TAR -C $JAVA_INSTALL_TMP
  echo Move $JAVA_INSTALL_TMP/$JAVA_INSTALL_NAME/* to $JAVA_HOME
  mv  $JAVA_INSTALL_TMP/$JAVA_INSTALL_NAME/* $JAVA_HOME
  #cp -R $JAVA_INSTALL_RPM/* $JAVA_HOME
  #sudo rpm -ihv $JAVA_INSTALL_HOME/$JAVA_INSTALL_RPM  
else
  echo jdk 1.8 already installed
fi

That uses the fmw12c_env.sh script:

#!/bin/bash
echo set Fusion MiddleWare 12cR2 environment
export ORACLE_BASE=/app/oracle
export INVENTORY_DIRECTORY=/app/oraInventory
export JAVA_HOME=$ORACLE_BASE/product/jdk

The script checks java already exists. If not then it checks if the tar or zip file exist in /media/sf_Stage/Java. If the tar file does not exist it will unzip the zip file. If the tar file does exist, then it will create a temp folder to extract the tar file into. Then it will create the java-home folder and move the extracted jdk folder to it.

I put these files in the scripts/fmw folder of my project:

Call script from provisioning

Now we're at the point that took me a lot of time to figure out last winter. How do I call this scripts form the provisioning. Simple question, but let me try to explain the difficulty.
The provisioning is done using the vagrant user. The vagrant user is in the sudoers list, so it's able to run a script using the permissions of another user, usually the super user (root). But it is still the running vagrant user who owns the resulting files and folders. I could do something like sudo su - oracle -c "script"... However, it still results in all the files owned by vagrant. So, if I run the Java install script, the complete java tree is owned by vagrant. But I want oracle to own it. Now,  I could create another base box and replace vagrant by oracle as the install user. But that is not the idea.

It took me some time to finally find the great utility runuser. This allows me to run the script as another substitute user. The runuser utility must be run as root, but that's no problem since vagrant is in the sudoers list.

So add the following lines to your provisioning part of the Vagrantfile:

    echo _______________________________________________________________________________
    echo 3. Java SDK 8
    sudo runuser -l oracle -c '/vagrant/scripts/fmw/installJava.sh'

With the -l argument I denote the user and with -c the command to run.

First try

Having done that, you could do a first try of the provisioning by upping the box.
Open a command window and start it the first time with vagrant up. Then if all goes well the provisioning ends with:

    darwin: _______________________________________________________________________________
    darwin: 3. Java SDK 8
    darwin: set Fusion MiddleWare 12cR2 environment
    darwin: Checking Java Home: /app/oracle/product/jdk
    darwin: Unzip /media/sf_Stage/Java/p27412872_180172_Linux-x86-64.zip to /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.tar.gz
    darwin: Archive:  /media/sf_Stage/Java/p27412872_180172_Linux-x86-64.zip
    darwin:   inflating: /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.rpm
    darwin:   inflating: /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.tar.gz
    darwin:   inflating: /media/sf_Stage/Extracted/Java/readme.txt
    darwin: Install jdk
    darwin: create folder /media/sf_Stage/Extracted/jdk
    darwin: create JAVA_HOME /app/oracle/product/jdk
    darwin: Untar /media/sf_Stage/Java/jdk-8u172-linux-x64.tar.gz to /media/sf_Stage/Extracted/jdk
    darwin: tar: jdk1.8.0_172/bin/ControlPanel: Cannot create symlink to `jcontrol': Protocol error
    darwin: tar: jdk1.8.0_172/man/ja: Cannot create symlink to `ja_JP.UTF-8': Protocol error
    darwin: tar: jdk1.8.0_172/jre/bin/ControlPanel: Cannot create symlink to `jcontrol': Protocol error
    darwin: tar: jdk1.8.0_172/jre/lib/amd64/server/libjsig.so: Cannot create symlink to `../libjsig.so': Protocol error
    darwin: tar: Exiting with failure status due to previous errors
    darwin: Move /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/bin /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/COPYRIGHT /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/db /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/include /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/javafx-src.zip /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/jre /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/lib /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/LICENSE /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/man /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/README.html /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/release /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/src.zip /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/THIRDPARTYLICENSEREADME-JAVAFX.txt /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/THIRDPARTYLICENSEREADME.txt to /app/oracle/product/jdk

It it all went well you can just destroy it:

d:\Projects\vagrant\ol75_db12c>vagrant destroy
    darwin: Are you sure you want to destroy the 'darwin' VM? [y/N] y
==> darwin: Forcing shutdown of VM...
==> darwin: Destroying VM and associated drives...

d:\Projects\vagrant\ol75_db12c>

Install database

Installing the database is a bit more complex. I'll not post every file, the code can be found here in github, as in fact the rest of my project files.
It expects the database installation as two zip files, V46095-01_2of2.zip and V46095-01_1of2.zip in the folder /media/sf_Stage/DBInstallation/12.1.0.2/x86_64/Zipped. Like for the rest it works much like my earlier install Fusion Middleware scripts. It unzips the files, installs the database based on the template response files. And it runs the database creation utility.

I also added scripts to install/unzip sqldeveloper and sql commandline.

To install the database and possibly SQLDeveloper and/or SQLcl, just add the following lines to your vagrant file:

    echo _______________________________________________________________________________
    echo 4. Database 12c
    sudo runuser -l oracle -c '/vagrant/scripts/database/installDB.sh'
    echo _______________________________________________________________________________
    echo 5.1 SQLCL and SQLDeveloper
    sudo runuser -l oracle -c '/vagrant/scripts/database/installSqlcl.sh'
    echo _______________________________________________________________________________
    echo 5.2 SQLDeveloper
    sudo runuser -l oracle -c '/vagrant/scripts/database/installSqlDeveloper.sh'

And run vagrant up again.

Conclusion

I didn't get much into the install of the database. I feel that I wrote quite a lot in the past on installing Oracle software. In the upcoming period I'll wrap these lasts blogs into a presentation on creating boxes with Vagrant. This an input to my talk on this subject at the NLOUG Tech Experience 2018.

By now you should be able to replicate my findings and create other boxes as well. In the next blogs I might write about transforming my other FMW install scripts into vagrant project. We should now be able to create Vagrant projects to install OSB, SOA/BPM Suite, SOA/BPM Quickstart, etc.

Lately I did a Docker install in an Ubuntu 16 base box. Ubuntu has some peculiarities in creating users and logical volume management as opposed to RedHat/Oracle Linux. I might also write about that. But feel free to leave a comment if you have particular wishes. However, please have a bit of patience with me, since I first need to get my talk on the Tech Experience ready.

It was so fun: yesterday I wrote  a small article on creating a query on Foreign Keys refering a certain table. A post with content that I made up dozens of times in my Oracle carreer. And right away I got 2 good comments. One was on the blog itself.

And of course Anonymous is absolutely right. So I added 'U' as a constraint type option.

The other comment was from my much appreciated colleague Erik. He brought this to another level, by pointing me out how to add this as a User Defined Extension in SQL Developer.

I must say I was already quite pleased with the Snippets in SQLDeveloper. So I already added the query as a snippet:
But the tip of Erik is much cooler.
He refered to a tip by Sue Harper that explains this (What, it's been in there since 2007?!).

Now what to do? First create an xml file, for instance referred_by_fks.xml,  with the following content:

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
    <query>
        <sql>
            <![CDATA[select fk.owner,
fk.table_name,
fk.constraint_name,
fk.status
from all_constraints  fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name 
where fk.constraint_type='R'
and rpk.constraint_type in('P','U')
and rpk.table_name = :OBJECT_NAME
and rpk.owner = :OBJECT_OWNER
order by fk.table_name, fk.constraint_name;]]>
        </sql>
    </query>
    </item>
</items>

Note that I updated my query a bit.


Then to add the extension to SQL Developer:

• Open the prefereces via: Tools > Preferences
• Navigate to Database > User Defined Extensions
• Click "Add Row" button
• In Type choose "EDITOR", Location is where you saved the xml file above
• Click "Ok" then restart SQL Developer

Now, if you click on a table in the navigater, you will have an extra tab on your table editor:

Cool stuff! And it's been there for ages!

Ok, this time a quick not so exciting post. Actually, I find my self recreating a query again, that I created many times in my carreer. So, why not post it?

Last year, I published my Darwin Object Type Accelerator (Dotacc). It allows you to generate objects from a datamodel. What it also does is create collection types for tables that refer to the tabel you want to generate an object for. For some you want that, but for others you don't. Simply because you don't need them to be queried along. Therefor, I added functionality to disable those.

But then comes the question: which are the tables with their foreignkey constraints that refer to this particular table?

The answer is in the ALL_CONSTRAINTS view (with the variants of DBA_% and USER_%).
There are several types of constraints:

• C: Check constraints
• R: Referential -> the particular foreign keys
• P: Primary Key
• U: Unique Key

I'm interested in the Foreign keys, thus those where constraint_type='R'. But those refer not to a table but to another constraint. So, I need to get the primary key, constraint_type='P', of the table that I want to query and join those together.

That get's me:

select fk.* 
from all_constraints  fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name 
where fk.constraint_type='R'
and rpk.constraint_type in ('P', 'U')
and rpk.table_name = 'DWN_MY_TABLE';

Years ago, I created a set of XSL templates and queries to create object types out of queries on the datadictionary of the Oracle Database. It only did basic types on tables, and selects on those. I wrote an article on it that still can be downloaded here. Later, I extended the framework to also do inserts and updates and follow foreign keys. The thing with foreignkeys is that you can handle them a as a detail-tables like Order Lines with an Order. Or lookup, like a place of birth, or country of origin, etc.

I must say I was quite pleased with what it could do.

Lately I stumbled on a question on community.oracle.com that ran about updating multiple tables using the database adapter. Normally you would need to do multiple invokes of a Database Adapter definitions, at least one per table, to do inserts and or updates.  I created thus because Oracle Types are a very powerful means to get data from a datamodel with multiple tables in just one invoke. Or insert data into it. You just create a pl/sql procedure with a parameter based on the root object. The database adapter wizard creates the accompanying XSD's and you only need to do a proper mapping in to the input variable and do the invoke. In the Pl/Sql procedure you call the particular method (sel, ins, upd) of the root object, that propagates into all the child and lookups. Most of the times that is enough. In more complex models, you might enhance the calling pl/sql.

I'm happy to announce that a moment ago I put my source on Github. I renamed it to Darwin Oracle Type accelerator. I did not have the change to create elaborate documentation. But in short:

• In $GitHub/Dotacc/Source/Dotacc/ddl/owner\ you'll find setup scripts for the framework.
• With setupTables.sql and setupPlsql.sql you create a bunch of tables and pl/sql with XXX_ as a prefix to support the generation of types.
• insertXslNL2.0.sql or insertXslEN2.0.sql creates the xsl in the xxx_xmldocuments table.
• $GitHub/Dotacc/Source/Dotacc/Config contains a set of insert scripts to define what tables, foreignkeys etc. to handle: 
• XXX_TABLES: defines the tables for which you want to generate the types. The column generation_order defines in which order the types are created or dropped at recreation. 
• XXX_FK_DEFINITIONS: defines the foreignkeys to consider. The FK_TYPE column defines if it should be considered as a child table ('DETAIL') or Lookup ('LOOKUP'). 
• XXX_DERIVED_COLUMNS: defines virtual columns that can be looked up from another table. You can define a method that is added to do the actual lookup based on a lookup value from a key column. 
• XXX_CUSTOM_METHODS: can be used to add custom methods to the object type.
• I added two datamodels (Doe_owner and hbc_owner under ddl) as a sample model. It would be nice to come up with a sample filling for named tables.

To do a recreate of the types, perform:

execute  xxx_gen_objects.recreate_objects;

 If you have remarks, post a comment on the blog and/or do a request to contribute on github or send me a PM on community.oracle.com.

A quick one. In the past I used to generate ddl based on queries, like the following. But I find myself to re-invent them again. So to have it saved for my offspring: here's one on deleting obsolete columns as generated on importing an excel sheet in SQLDeveloper:

declare
  l_schema_name varchar2(30) := 'MY_SCHEMA';
  l_table_name varchar2(30) := 'A_TABLE';
  cursor c_cols is 
    select column_name 
    from all_tab_columns col 
    where col.table_name = l_table_name 
    and col.owner = l_schema_name
    and col.column_name like 'COLUMN%';
begin
  for r_cols in c_cols loop
    execute immediate 'alter table '||l_schema_name||'.'||l_table_name||' drop column '||r_cols.column_name;
  end loop;
end;
/

And here's one to generate a check constraint on all index colunns of a table:

declare
  l_schema_name varchar2(30) := 'MY_SCHEMA';
  l_table_name varchar2(30) := 'A_TABLE';
  l_constraint_name_pfx varchar2(30) := 'XXX_ALIAS_CHK';
  l_idx pls_integer := 1;
  cursor c_cols is 
    select column_name 
    from all_tab_columns col 
    where col.table_name = l_table_name 
    and col.owner = l_schema_name
    and col.column_name like 'IND_%'; 
begin
  for r_col in c_col loop
    execute immediate 'ALTER TABLE '||l_schema_name||'.'||l_table_name||' ADD CONSTRAINT '||l_constraint_name_pfx||l_idx||' CHECK ('||r_col.column_name||' in (''J'',''N''))ENABLE';
    l_idx := l_idx+1;
  end loop;
end;
/

Last few days I spent with describing a Throttle mechanism using the DB Adapter. Today the 'Distributed Polling' functionality of the DB Adapter was mentioned to me, which uses the SKIP LOCKED clausule of the database.

On one of the pages you'll get to check the 'Distributed Polling' option:
Leave it like it is, since it adds the 'SKIP LOCKED' option in the 'FOR UPDATE' clausule.

In my example screendump I set the Database Rows per Transaction, but you might want to set in a sensible higher value with regards to the 'RowsPerPollingInterval' that you need to set yourself in the JCA file:

The 'RowsPerPollingInterval' is not an option in the UI, unfortunately. You might want to set this as a multiple to the MaxTransactionSize (in the UI denoted as 'Database Rows per Transaction').

A great explanation for this functionality is this A-Team blogpost. Unfortunately the link to the documentation about 'SKIP LOCKED' in that post is broken. I found this one. Nice thing is that it suggests using AQ as preferred solution in stead of SKIP LOCKED.

Maybe a better way for throttling is using the AQ Adapter together with the properties

• adapter.aq.dequeue.threads in the JCA file; see AQ Adapter tuning 
• minimumDelayBetweenMessages; see this forum thread or note 465747.1 in support.oracle.com.

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data as xml, but then: how about the foreign key relations?

It turns out pretty straightforward, that you can probably figure out yourself. But hey, I'm not a bad guy, so how about sharing it to you?

The query selects employees with their addresses and goes as follows:

select xmlelement("emp:employees"
  , xmlattributes( 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns:emp"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/address' as "xmlns:ads")
  , xmlagg
  ( xmlelement
    ( "emp:employee"
    , xmlforest
      ( emp.title as "emp:title"
      , emp.firstname as "emp:firstName"
      , emp.last_name as "emp:lastName"
      , emp.gender as "emp:gender"
      , emp.birth_date as "emp:birthDate"
      )
    , ( select xmlelement( "emp:addresses"
        , xmlagg(
             xmlelement("ads:address"
             , xmlattributes( ate.code as "type"
                            , ate.description as "description")
             , xmlforest
               ( ads.adress_line1 as "ads:addressLine1"
               , ads.adress_line2 as "ads:addressLine2"
               , ads.adress_line3 as "ads:addressLine3"
               , ads.postal_code as "ads:postalCode"
               , ads.city as "ads:city"
               , ads.country as "ads:country"
               )
             )
          )
        )
        from doe_party_addresses pae
        join doe_addresses ads on ads.id = pae.ads_id
        join doe_address_types ate on ate.id= pae.ate_id
        where pae.emp_id = emp.id
        )
      )
    )
  )  xml
from doe_employees emp;

The doe_addresses table is joined with the doe_employees table via a couple-table named doe_party_addresses. This is because an employee can have multiple addresses, but with different types. An employee can have a Business address and a Home address. Like a customer can have a shipping, billing and visiting addresses. Also an address can be used by multiple parties. You see here that the addresses are selected as a subselect. The output of the sub-select is an XMLType that can be embedded in the xmlelement (and other xml-) functions. What you also see is that I added namespace declarations as xmlattributes on the top-level element. The element and attribute names are prefixed with the corresponding 'emp:' or 'ads:' namespace-prefixes. Oh, and the output of the query is something like:

<?xml version="1.0" encoding="UTF-8" ?>
<emp:employees xmlns="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:emp="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:ads="http://xmlnls.darwin-it.nl/doe/xsd/v1/address">
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Ed</emp:firstName>
    <emp:lastName>Bushes</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1968-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Maasstraat 19</ads:addressLine1>
        <ads:postalCode>3812HS</ads:postalCode>
        <ads:city>Amersfoort</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rocky Road 2</ads:addressLine1>
        <ads:postalCode>20001</ads:postalCode>
        <ads:city>StoneHench</ads:city>
        <ads:country>UNITED KINGDOM</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Hofvijver 12</ads:addressLine1>
        <ads:postalCode>2000XX</ads:postalCode>
        <ads:city>Den Haag</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Pat</emp:firstName>
    <emp:lastName>Darwin</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1980-03-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rijnzathe 6</ads:addressLine1>
        <ads:postalCode>3140ZP</ads:postalCode>
        <ads:city>De Meern</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>T.</emp:firstName>
    <emp:lastName>Barakus</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1970-02-11</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Erasmusstraat 312</ads:addressLine1>
        <ads:postalCode>1234GK</ads:postalCode>
        <ads:city>Rotterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Debby</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1982-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="WORK" description="Work address">
        <ads:addressLine1>Darwinplein 11</ads:addressLine1>
        <ads:postalCode>4321PS</ads:postalCode>
        <ads:city>Amsterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Newtonweg 41</ads:addressLine1>
        <ads:postalCode>6543AB</ads:postalCode>
        <ads:city>Maasland</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Wally</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1963-06-13</emp:birthDate>
    <emp:addresses></emp:addresses>
  </emp:employee>
</emp:employees>

2014-04-25, update: I changed the namespaces and I found a curiosity in my database content: all birthdates were in the future...

Sometimes I want to duplicate or reuse a Virtual Machine for another purpose. This week I had to start with a setup for a B2B communication for a client. So I pick one, duplicate them and change the particular hostname and corresponding network settings (/etc/hosts) to reflect the particular situation. So that I can communicate to the instance with its own host-alias.

It is not exactly necessary, but it is neat to have the hostname of the server changed to the particular situation. But if you have installed an Oracle 11g database, you'll encounter that you can't login at it anymore.
Some how the database checks the hostname and with the ip-address of the server its installed on.
I found the solution here in the doc, see paragraph 3.3.2.2. Network.

You'll have to make sure that the name of your host (see cat /etc/sysconfig/network) is in the /etc/hosts file with the ip address of the machine.
So my first network adapter is a host-only with a fixed address of 10.0.0.1. As such I installed the database. If I change my hostname to 'darwin-vce-soa', for instance then in the /etc/hosts there should be a line like:

10.0.0.1          darwin-vce-soa.darwin-it.local    darwin-vce-soa

Also make sure that you change the listner.ora and tnsnames.ora in $ORACLE_HOME/network/admin.

Oh, and if you are on a demo/development server, you may want to have password expiry turned off. See my blog-entry here.

This week I started with Webcenter 11g, using the Oracle VirtualBox VM that can be downloaded here.
One of the tips upfront was that the NAT network adapter should be "Internal Network" or "Host Only". Because otherwise the UCM part of the hands-on would not work.

I also changed the memory settings to 3GB at first. But that caused my Windows 7 to stutter. Aparently Windows has became very memory consumptive. On my Linux (Open Suse) it would not have be too much of a problem to raise the VM to 3GB. An 8GB laptop would be nice. So I brought the settings back to a more modest 2,5 GB.

But then I encountered that the install of Oracle DB 11g was pretty basic. And that means  a memory consumption of 700GB only the lonely for the database. I remembered my earlier post to tune Oracle DB11g together with SoaSuite10g on a OEL5 VM.

It was basically about resizing the memory. What I did was to startup an XE database. There I looked at the basic memory settings. For convenience I created a plain init.ora.

For the non-dba's amongst you, you can do that by loging on as internal with:

sqlplus "/ as sysdba"

having set the ORACLE_HOME and ORACLE_SID:

ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_SID=orcl

When you logged on as internal you can create an init.ora (also called a pfile) with:

create pfile from spfile;

Then you'll find an init.ora in the $ORACLE_HOME/dbs folder.

For an Oracle XE database the most interesting settings I found were:

java_pool_size=4194304
    large_pool_size=4194304
    shared_pool_size=67108864
    open_cursors=300
    sessions=20
    pga_aggregate_target=70M
    sga_target=210M

The sga_max_size was not set.
So I changed the 11g database with these settings, created a spfile from the pfile again (create spfile from pfile) started it again.

My initorcl.ora:

#orcl.__db_cache_size=222298112
#orcl.__java_pool_size=12582912
orcl.__java_pool_size=10M
orcl.__large_pool_size=4194304
....
#orcl.__pga_aggregate_target=159383552
orcl.__pga_aggregate_target=70M
#orcl.__sga_target=478150656
orcl.__sga_target=210M
orcl.__shared_io_pool_size=0
#orcl.__shared_pool_size=234881024
orcl.__shared_pool_size=100M
orcl.__streams_pool_size=0
...
#*.memory_target=635437056
*.open_cursors=300
#*.processes=150
*.sessions=20
...
*.sga_max_size=250
...

Mark that I unset the db_cache_size and memory_target. I also replaced the processes parameter with the sessions parameter being 20. These two parameters relate to eachother, one computed from the other.

I found that I had a database of 145MB!But that's some what too small, especially the shared-poolsize being about 64M, while the sga_max_size was 145M.

I changed my sga_max_size to explicitly 250M and the large_pool_size to 100M:

SQL> alter system set sga_max_size=250M scope=spfile;
System altered.
SQL> alter system set shared_pool_size=100M scope=spfile;
System altered.

Then restarting the database resulted in a database of 250M:

Total System Global Area 263639040 bytes
Fixed Size 1299284 bytes
Variable Size 209718444 bytes
Database Buffers 50331648 bytes
Redo Buffers 2289664 bytes

That looks better to me. And having it posted again refreshes it for me.

Update: I now see that in the initorcl.ora I had a sga_max_size of 250. But that should be 250M... Maybe that caused the shared_pool_size and sga_max_size too small.

Lately I was asked to help with routing messages from a object-type based AQ-queue to JMS.
The thing is that JMS works with text. So you have to transform the objecttype to text.
When the Object type is one of your own, you could extend it with a method "toXML" to give a XML message based on the attributes of the object.

In this case it was about a Oracle Integratin B2B AQ Queue, which is based on the B2B object "IP_MESSAGE_TYPE".

It turns out not too hard to translate the object type to JMS. I created a package for it which I provided for download here.

You can test it with the following code:

declare
  -- Non-scalar parameters require additional processing
  result     sys.aq$_jms_text_message;
  ip_message ip_message_type;
  payload    clob;
begin
  payload         := def_b2b.varchar_to_clob('Jet, Teun, Vuur, Schapen');
  ip_message := ip_message_type(MSG_ID           => 'Aap'
                                    ,INREPLYTO_MSG_ID => 'noot'
                                    ,FROM_PARTY       => 'Mies'
                                    ,TO_PARTY         => 'Zus'
                                    ,ACTION_NAME      => 'Lezen'
                                    ,DOCTYPE_NAME     => 'Leesplankje'
                                    ,DOCTYPE_REVISION => '1.0'
                                    ,MSG_TYPE         => 1
                                    ,PAYLOAD          => payload
                                    ,ATTACHMENT       => null);
  -- Call the function
  result := def_b2b.ip_message2jms_msg(ip_message => ip_message);
   result.get_text( :msg);
end;

As you can see a jms-accesible AQ-queue has a special system-Object-type: 'sys.aq$_jms_text_message'. There are several others, for different kinds of jms queues or topics. Also markt that the object types differ between Oracle 10g or 11g Enterprise Edition or equivalent and Oracle XE. In XE you wouldn't find a 'construct' method. You could try the solution of Peter Ebell for this.

Another thing is that the guys that asked me for help, had to do the enqueue of the message on the JMS-queue based on the enqueue on the source B2B-queue.
From Oracle they got permission to use a trigger on the Queue-table. To begin with they used a Before Row Insert trigger. Besides triggers on queue-tables are not support and certainly not the way to go, they encountered a problem with it. And that lays in the fact that the payload attribute is a CLOB. I allways found the way Oracle handled CLOBs at least "a little remarkable". On insert you create a row with an empty CLOB and then query it for update. In the queried row you upload the content to the CLOB-column. Since an AQ queue is based on a table it works essentially the same way. So on Before Row Insert the payload attribute is still empty. They solved it to use an After Delete trigger (when the message is consumed by the subscribing-process).

The way to go is actually to register a notification service on the queue using code like:

declare
  lc_reg_info      SYS.AQ$_REG_INFO;
  lc_reg_info_list SYS.AQ$_REG_INFO_LIST;
begin
  lc_reg_info := SYS.AQ$_REG_INFO('B2B.IP_IN_QUEUE:'
                                 ,DBMS_AQ.NAMESPACE_AQ
                                 ,'plsql://b2b.def_b2b.handle_inbound_notification?PR=1'
                                 ,HEXTORAW('FF'));

  lc_reg_info_list := SYS.AQ$_REG_INFO_LIST(lc_reg_info);
  dbms_aq.register(lc_reg_info_list, 1);
end;

Such a plsql notification function is a function that is required to have a particular "authograph":

PROCEDURE handle_inbound_notification(context  RAW
                                       ,reginfo  sys.aq$_reg_info
                                       ,descr    sys.aq$_descriptor
                                       ,payload  RAW
                                       ,payloadl NUMBER)

These parameters provide you with the data to fetch/dequeue the message this procedure is called for. You won't get the message itself, you have to dequeue-it explicitly. See the package for an example to implement this procedure.

You should perform the register for every queue-consumer that you want to reroute the messages for. But it is not too hard to put this in a parameterized-procedure and call it based on a query that fetches the consumer from either the dictionary or (better) the B2B repository. In fact this code is extracted from such a construct. But it was a little too much (I already put a reasonable amount of time in the package) to anonymize it and make it more generic.
If you need more help with it, I could of course provide some consultancy.

It's been a while that I wrote an article. This week I struggled with creating insert scripts based on data from DB2 to be used in my local test database (Oracle XE) at my customer.

We use Siebel and have to integrate here and there by querying data from the Siebel DB2 database. It turns out that my local database adapter has trouble with connecting to the DB2 database. Could not find out what's wrong, so I decided that I would query the data from the Siebel tables and insert it into my local XE database. It's faster anyway (in my case) and it also allows me to manipulate the data for test-case purposes.
But querying db2 to generate insert statements isn't as obvious as I would do it in Oracle.

Here is an example script.

select
'Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,'
|| 'PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,'
|| 'EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL'
||',TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM'
||',BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,'
||'TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,'
|| 'DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER)'
||' values ('
|| ''''|| coalesce(ctt.PARTYROWID,'') ||''','
|| ''''|| coalesce(ctt.KLANTID,'') ||''','
|| ''''|| coalesce(ctt.KLANTTYPE,'') ||''','
|| case when ctt.AANGEMAAKTOP is not null then 'to_date('''||varchar_format( ctt.AANGEMAAKTOP,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.BANKCODE,'') ||''','
|| ''''|| coalesce(ctt.BANKLOCATIE,'') ||''','
|| ''''|| coalesce(ctt.KLANTSTATUS,'') ||''','
|| ''''|| coalesce(ctt.CORRESPONSDENTIETAAL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONNUMMER,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONTYPE,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAIL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAILFORMAAT,'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONPRIVE),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONOVERIG),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONMOBIEL),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONWERK),'') ||''','
|| ''''|| coalesce(varchar(ctt.FAX),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAIL),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAILFORMAAT),'') ||''','
|| case when ctt.EMAILDATUM is not null then 'to_date('''||varchar_format( ctt.EMAILDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(EMAILBRON,'') ||''','
|| ''''|| coalesce(ctt.INGEZETENEVAN,'') ||''','
|| ''''|| coalesce(ctt.NATIONALITEIT,'') ||''','
|| ''''|| coalesce(ctt.ACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.ROEPNAAM,'') ||''','
|| ''''|| coalesce(ctt.GESLACHTSNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOORLETTERS,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEVOORNAMEN,'') ||''','
|| ''''|| coalesce(ctt.ACADEMISCHETITEL,'') ||''','
|| ''''|| coalesce(ctt.TUSSENTITEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERTITEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSELGESLACHTSNAAM,'') ||''','
|| case when ctt.GEBOORTDATUM is not null then 'to_date('''||varchar_format( ctt.GEBOORTDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.GESLACHT,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTELAND,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTEPLAATS,'') ||''','
|| ''''|| coalesce(ctt.EIGENHUIS,'') ||''','
|| ''''|| coalesce(ctt.FAILLIET,'') ||''','
|| ''''|| coalesce(ctt.SAMENLEVINGSVORM,'') ||''','
|| ''''|| coalesce( ctt.BURGERLIJKSTAAT,'') ||''','
|| ''''|| coalesce( ctt.HUW_VOORWAARDEN,'') ||''','
|| ''''|| coalesce( ctt.PERSONEEL,'') ||''','
|| ''''|| coalesce( ctt.TYPEKLANT,'') ||''','
|| ''''|| coalesce( ctt.MAATSCHAPPELIJKESTATUS,'') ||''','
|| ''''|| coalesce( ctt.LOKALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.CENTRALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.KLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.LOKAALINGEDEELD,'') ||''','
|| ''''|| coalesce( ctt.BEHOEFTEPROFIEL,'') ||''','
|| ''''|| coalesce( ctt.TAXIDENTIFICATIONNR,'') ||''','
|| ''''|| coalesce( ctt.WOONPLAATSVERKLARING,'') ||''','
|| ''''|| coalesce( ctt.SOFINUMMER,'') ||''','
|| ''''|| coalesce( ctt.REDENGEENSOFINUMER,'') ||''','
|| case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.OVERLEDEN,'') ||''','
|| ''''|| coalesce( ctt.AARDIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMLEGITIMATIE is not null then 'to_date('''||varchar_format( ctt.DATUMLEGITIMATIE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.NRIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMUITGIFTE is not null then 'to_date('''||varchar_format( ctt.DATUMUITGIFTE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.LANDUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PLAASTUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PERTELEFOONBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PEREMAILBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERPOSTBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERSMSBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERFAXBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.INSOLVENCYSTATUS,'') ||''','
|| ''''|| coalesce( ctt.IKBNUMBER,'') ||''');'
from siebel.contact ctt
where klantid='12345';

The first 'not so obvious' is the NVL-function. This is a typical Oracle function. For most purposes this can be translated to the coalesce function above. In most cases when the column is empty I want to have an "empty value". In some cases just
giving "coalesce( column-reference,'')" does not suffice. I had to cast the column explicitly to char with the varchar() function:

coalesce(varchar(ctt.TELEFOONPRIVE),'')

Here TELEFOONPRIVE is apparently a number column. The function coalesce() assumes the number datatype and can't accept an empty string as default string.

For dates it is a little more complicated. If there is a date I want to transform it to an Oracle to_date function. But then I have to be sure that the format comming from DB2 is of a standard format. I choose "YYYY-MM-DD HH24:MI:SS". If the date is empty I just want to return an empty string again. I couldn't come up with a simple construct using coalesce(). So I used the CASE WHEN-construct:

case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end

It took me a while to find out that where in Oracle you can provide a date-format to the to_char(<date-value>, <date-format>) function, in DB2 you need the varchar_format(<date-value>, <date-format>) function for that. Luckily the accepted date-formats are the same in my case. So here I transform the date-value from DB2 to the required date-format and concatenate it with the Oracle to_date() function with the same format.

The generated insert statement(s) will look like (enter at values clause added manually for readability):

Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL,TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM,BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER) 
values ('1-100BM-100','000000105727750','Person',to_date('2006-05-09 19:59:21','YYYY-MM-DD HH24:MI:SS'),'3365','336515','C','NL','','','','','','','','','','','',to_date('2008-09-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),'FULFILMENT','01','',to_date('2009-07-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','NL','Name','Name','','Name','I.R.S.','Iris Ronald Simon','','','','','','',to_date('1966-11-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),'M','NL','Tool Town','Y','N','3','1','9','03','01','08','','1','1','Y','','','X','123456789','',null,'N','03',to_date('2005-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'IC4631943',to_date('2004-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','Tool Village','N','N','Y','Y','N','','1-22A-3344');

Today I had to change my java project in a way that it uses Tnsnames for database resolving instead of a jdbc-url.

I googled around and found my answer here. This solution works with the jdbc-drivers from Oracle 10.2 onwards.

The solution basically is that you set the java system property "TNS_ADMIN". This can be done in two ways:

1. By giving it as a java command line paramater: -Doracle.net.tns_admin=<tns_admin_home> eg.-Doracle.net.tns_admin=c:\\oracle\\OracleXE\\app\\oracle\\product\\10.2.0\\server\\NETWORK\\ADMIN
2. Or setting from the code with the statement: System.setProperty("oracle.net.tns_admin", "<tns_admin_home>");
   

Example for the second option:

public final static String ORA_NET_TNS_ADMIN = "oracle.net.tns_admin";
...
// fetch tnsAdmin from a property file. Here hard-coded as an example:
public String tnsAdmin = "c:\\oracle\\OracleXE\\app\\oracle\\product\\10.2.0\\server\\NETWORK\\ADMIN";
...
System.setProperty(ORA_NET_TNS_ADMIN,tnsAdmin);

For an XE database for example the jdbc-url will be: jdbc:oracle:thin:@xe

The TNS_ADMIN property has to be set with the location/folder where the tnsnames.ora can be found. Not with the full-path to the tnsnames.ora.
I tried this with the database drivers from jdeveloper10.1.3.4 (ojdbc14.jar). But apparently they are from the 10.1 era. It does work with the one delivered with SqlDeveloper 1.5.x (ojdbc5.jar).

There are many myths on the Oracle database. Many of those myths were true statements versions ago, but became myths because the Oracle database developers outsmarted the limitations. There was a myth that said that you should put non-null columns in the beginning of the table and null columns at the end. This was because in earlier versions a null column in a row did not take space until there was a filled column after it. In that case the database preallocated space for the null column to save on re-allocation efforts. But I learned that the storing-techniques of the database were improved in a way that this statement became a myth. Although it is still a good practice to have a standard on organizing your tables.

I know quite a lot of the Oracle database. But most of it is from using the modern ones and carrying the lugage of courses in the older ones. I know how a car and it's engine theoretically work, but I have no up-to-date knowlegde of the internals of the modern car. In the same way I have to rely on what I hear from other experts and co-workers about statements as above. So I keep my ears open and carefull about spreading myths.

This week a valued co-worker came with a DDL-script to create a table with Char-columns. My first reaction was that he should replace the CHARs with VARCHAR2s. Since a Varchar, as suggested by it's name, should occupy space related to it's fillings, while a CHAR occupies space according to it's maximum size. Though this was true, I was not sure if this statement was passed over by the reality of the modern database's storage techniques.
But according to Tom Kyte, the statement still holds (article started in 2001, but is updated recently). However, browsing to the end of the article's list of comments, I read: if a char or a varchar2 column is null, than it does not take more room then is needed to hold the null-value.

So myth confirmed...

I did not pay attention to it until today, but since Oracle 11g accounts have by default a limited valid period for password. By default password expire causing a password change upon logon. This is very inconvenient for databases running as a repository for applications like for example the SoaSuite.
I found after a little googling a solution answer for example here. The solution is by simply disabling the password expiration on the default profile:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Oracle SQL Datamodeler (OSDM) is a new tool that Oracle acquired a few months ago from a belgium company called cdw4all. This tool will be bundled with sql developer and could be the new Oracle tool for datamodeling.
There is no production version for the tool yet (thats scheduled for calendar year 2009), but there are early adopter versions available. The current early adopter version is EA2.
It is possible to import data from a designer environment and in this post I will describe how this works. I must say, the process of importing designer objects is straight forward.
I'll import from an Oracle Designer 10gR2 with versioning disabled. When you want to import from a versioned repository make sure that your objects are checked-in. I installed a new Designer repository, created a new application system with two entities (emp and dept with a relationship) and used the database design transformer to create two tables with a corresponding foreign key.




After that I started OSDM and chose File --> Import --> Oracle Designer model.
First you have to make a database connection:



Select the correct workarea



Select the application system(s) with the workarea



Select the objects you want to import (in this case entities and tables)


and the last step: Generate Design



We are finished. Let's look and the results. In the logical model (Entity Relational Model) I got my two entities and in the Relational Model (data diagram) I got my two tables:





Wow that was easy!

This little demonstration was made with Early Adopter version 1. I tried it with EA2 but in this version I could only import entities...

In the past I installed DB9i several times under Windows. I might have done it earlier under Linux, but I can't remember how I did it. I also installed 10g several times. But recently I had to work with Oracle Streams under 9.2.0.8. It was a very large database that could not be upgraded that easily in a short notice.

Unfortunately we experienced some problems, on Logminer and on performance. So I wanted to have a clean DB install on a VM ware image so that I could play around a little with it to see if I could get it to work properly on a clean database install.

Installing DB 9i turned out not that simple, when done in the train having just the install-disks. But when having your friends Google and Metalink around the task turns out not too hard.

I used two inputs:

• Installing Oracle9i 32-bit on Red Hat Enterprise Linux Advanced Server 4, written by Werner Puschitz (thanks Werner)
• Metalink note 303859.1

Below I'll set out the steps I took to install 9.2.0.8 on Red Hat Enterprise Linux Advanced Server 4.0. If you have another taste of linux, try the document of Werner (it contains also info about other Red Hat flavours) or Google a little further.
Packages
First check out if you have the required packages. The following are required:

• compat-db-4.1.25-9
• compat-gcc-32-3.2.3-47.3
• compat-gcc-32-c++-3.2.3-47.3
• compat-oracle-rhel4-1.0-3
• compat-libcwait-2.0-1
• compat-libgcc-296-2.96-132.7.2
• compat-libstdc++-296-2.96-132.7.2
• compat-libstdc++-33-3.2.3-47.3
• gnome-libs-1.4.1.2.90-44
• gnome-libs-devel-1.4.1.2.90-44
• libaio-devel-0.3.102-1
• libaio-0.3.102-1
• make-3.80-5
• openmotif21-2.1.30-11
• xorg-x11-deprecated-libs-devel-6.8.1-23.EL
• xorg-x11-deprecated-libs-6.8.1-23.EL

This can easily be checked by issueing:

rpm -q make                           \
compat-db                      \
compat-gcc-32                  \
compat-gcc-32-c++              \
compat-oracle-rhel4            \
compat-libcwait                \
compat-libgcc-296              \
compat-libstdc++-296           \
compat-libstdc++-33            \
gcc                            \
gcc-c++                        \
gnome-libs                     \
gnome-libs-devel               \
libaio-devel                   \
libaio                         \
make                           \
openmotif21                    \
xorg-x11-deprecated-libs-devel \
xorg-x11-deprecated-libs

In my case I lacked the libraries:
package compat-oracle-rhel4 is not installed
package compat-libcwait is not installed
package gnome-libs-devel is not installed
package libaio-devel is not installed
package xorg-x11-deprecated-libs-devel is not installed

For the X11 stuff I had several dependencies that I resolved with:

rpm -Uhv fontconfig-devel-2.2.3-7.i386.rpm \
pkgconfig-0.15.0-3.i386.rpm \
xorg-x11-libs-6.8.2-1.EL.13.37.i386.rpm \
freetype-devel-2.1.9-1.i386.rpm \
zlib-devel-1.2.1.2-1.2.i386.rpm \
xorg-x11-xfs-6.8.2-1.EL.13.37.i386.rpm \
xorg-x11-6.8.2-1.EL.13.37.i386.rpm

rpm -Uhv xorg-x11-deprecated-libs-devel-6.8.2-1.EL.13.37.i386.rpm \
xorg-x11-devel-6.8.2-1.EL.13.37.i386.rpm

Then Libaio-devel:

rpm -ihv libaio-devel-0.3.105-2.i386.rpm

For compat-oracle-rhel4 you need an Oracle patch: 4198954 from metalink.
This one installs:

• rpm -ihv compat-oracle-rhel4-1.0-5.i386.rpm
• rpm -ihv compat-libcwait-2.1-1.i386.rpm

The compat-oracle-rhel4 libary also checks for xorg-x11-deprecated-libs and
xorg-x11-deprecated-libs-devel.

For the gnome library I also had some depencies, that I resolved by:

rpm -ihv gnome-libs-devel-1.4.1.2.90-44.2.i386.rpm \
ORBit-devel-0.5.17-14.i386.rpm \
esound-devel-0.2.35-2.i386.rpm \
gtk+-devel-1.2.10-33.i386.rpm  \
imlib-devel-1.9.13-23.i386.rpm \
glib-devel-1.2.10-15.i386.rpm \
indent-2.2.9-6.i386.rpm \
alsa-lib-devel-1.0.6-5.RHEL4.i386.rpm \
audiofile-devel-0.2.6-1.el4.1.i386.rpm \
glib-devel-1.2.10-15.i386.rpm \
libjpeg-devel-6b-33.i386.rpm \
libtiff-devel-3.6.1-10.i386.rpm \
libungif-devel-4.1.3-1.el4.2.i386.rpm

What I did was just doing the rpm -ihv gnome-libs-devel-1.4.1.2.90-44.2.i386.rpm (that was the one I had on my dvd) and then added all the dependent rpms that it mentioned. In your case you might not need the alsa and audio libraries.
Change Sysctl.conf
There are a few settings on kernel level to set. Below my sysctl.conf:

# Kernel sysctl configuration file for Red Hat Linux
#
kernel.hostname = rhel4vm.darwin-it.local
kernel.domainname = darwin-it.local

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1
kernel.sem = 256 32000 100 142
kernel.shmmax = 4294967295
kernel.shmmni = 100
kernel.shmall = 2097152
#fs.file-max = 206173
fs.file-max = 327679
net.ipv4.ip_local_port_range = 1024 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Pay attention to the kernel.shmmax, shmmni, shmall (shared memory), fs.file-max (max filehandles), kernel.sem (min, max semaphores), and kernel.hostname + domainname.

Swap space
You need at least the double of your machines memory as a swapspace. To check your memory you can do:

grep MemTotal /proc/meminfo

To check your swapspace:

cat /proc/swaps

You can add an extra drive and format it as swapspace. To add temporary swapspace you can use the following procedure to add for example 1GB swapspace:

su - root
dd if=/dev/zero of=/u01/swapfile01 bs=1k count=1000000
chmod 600 /u01/swapfile01
mkswap /u01/swapfile01
swapon /u01/swapfile01

To remove it again:

su - root
swapoff /u01/swapfile01
rm /u01/swapfile01

Temp space

For the Temp space, if /tmp does not have enough space you can do:

export TEMP=/           # used by Oracle
export TMPDIR=/         # used by Linux programs like the linker "ld"

Create Users
I had a Virtual Machine with RHEL4 already installed and an pre-existing Oracle user. If you haven't then use the following procedure to add the oracle user:

su - root
groupadd dba          # group of users to be granted with SYSDBA system privilege
groupadd oinstall     # group owner of Oracle files
useradd -c "Oracle software owner" -g oinstall -G dba oracle
passwd oracle

Create Oracle Directories


The following directories are needed for the install, with the specified rights. Check if your filesystems have enough space. A complete installation with a starter database will need about 2,5GB. With the addition of some temp space I would be on the save side and reserve at least 5GB.

su - root
mkdir -p /u01/app/oracle/product/9.2.0
chown -R oracle.oinstall /u01

mkdir /var/opt/oracle
chown oracle.dba /var/opt/oracle
chmod 755 /var/opt/oracle

Setting Oracle Environment variables
There are few settings important to install the database. Especially the LD_ASSUME_KERNEL variable that needs to be on 2.4.19.
So I created a little environment script oraenv.sh:

export LD_ASSUME_KERNEL=2.4.19   # for RHEL AS 4
export TMP=/u01/oracle/tmp
export TMPDIR=/u01/oracle/tmp
# Oracle Environment
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=ORCL
export NLS_LANG=AMERICAN;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

# Set shell search paths
export PATH=$PATH:$ORACLE_HOME/bin

The ORACLE_HOME depenent variables are merely for being able to use the database after installing it.
You can run the script by:

. ./oraenv.sh

Do not forget the extra dot '.' in front of it, this will cause the set parameters exported to the calling shell.

Install the database
With this I could install the 9.2.0.4 database with the cd's I got from: http://www.oracle.com/technology/software/products/oracle9i/index.html

Then do not forget to upgrade it to 9.2.0.8. Look for the patch 4547809 in Metalink.
During the run of catpatch, I got time outerrors on the sys.XMLType and sys.XMLTypePI objects. But checking afterwards they turned out to be created and valid.

In earlier posts I wrote about how to install the Oracle DB11g and SoaSuite 10133 in an Oracle Enterprise Linux based VM. Earlier this year I also wrote how to rename your SoaSuite installation when having renamed your host.

I did in fact a more or less default installation of both 11gDB and a SoaSuite 10133. But since the VM was to run on a 2GB laptop for courses I gave the VM only 1.6GB of memory. The database was sized so that it claimed about 640MB and the default J2EE+Webserver+soasuite installtion of the midtier resulted in two OC4J instances that both had a minimum heapsize of 512MB and a max of 1024. So at starting both database and soasuite I ran out of memory what results in a guest-os that gets very befriended with the harddrive (swapping all over the place).

So I took some time in getting the system tuned.

The database
First step was getting the database downsized. Earlier I shrinked the sga_max_size to about 470MB. So that was allready an improvement of about 170MB.

But that was not enough for me. So what I did was to startup an XE database. There I looked at the basic memory settings. For convenience I created a plain init.ora.
For the non-dba's amongst you, you can do that by loging on as internal with:
sqlplus "/ as sysdba"
having set the ORACLE_HOME and ORACLE_SID:
ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_SID=orcl
When you logged on as internal you can create an init.ora (also called a pfile) with:
create pfile from spfile;
Then you'll find an init.ora in the $ORACLE_HOME/dbs folder.

For an Oracle XE database the most interesting settings I found were:

• java_pool_size=4194304
• large_pool_size=4194304
• shared_pool_size=67108864
• open_cursors=300
• sessions=20
• pga_aggregate_target=70M
• sga_target=210M

The sga_max_size was not set.
So I changed the 11g database with these settings, created a spfile from the pfile again (create spfile from pfile) started it again.

My initorcl.ora:

#orcl.__db_cache_size=222298112
#orcl.__java_pool_size=12582912
orcl.__java_pool_size=10M
orcl.__large_pool_size=4194304
....
#orcl.__pga_aggregate_target=159383552
orcl.__pga_aggregate_target=70M
#orcl.__sga_target=478150656
orcl.__sga_target=210M
orcl.__shared_io_pool_size=0
#orcl.__shared_pool_size=234881024
orcl.__shared_pool_size=100M
orcl.__streams_pool_size=0
...
#*.memory_target=635437056
*.open_cursors=300
#*.processes=150
*.sessions=20
...
*.sga_max_size=250
...

Mark that I unset the db_cache_size and memory_target. I also replaced the processes parameter with the sessions parameter being 20. These two parameters relate to eachother, one computed from the other.

I found that I had a database of 145MB! I could start the middletier, but then I could nog logon myself because of the shared-poolsize being to small. This turned out to be about 64M, while the sga_max_size (that I did not set) was 145M.

I changed my sga_max_size to explicitly 250M and the large_pool_size to 100M:
SQL> alter system set sga_max_size=250M scope=spfile;
System altered.
SQL> alter system set shared_pool_size=100M scope=spfile;
System altered.
Then restarting the database resulted in a database of 250M:
Total System Global Area 263639040 bytes
Fixed Size 1299284 bytes
Variable Size 209718444 bytes
Database Buffers 50331648 bytes
Redo Buffers 2289664 bytes

That looks better to me.

Total System Global Area 263639040 bytes
Fixed Size 1299284 bytes
Variable Size 209718444 bytes
Database Buffers 50331648 bytes
Redo Buffers 2289664 bytes

That looks better to me.

The MidTier
The changes in the middle tier are a little less complicated. In fact you have to change two settings in opmn. So go to the $ORACLE_HOME/opmn/conf directory of the middle tier.
There you'll find a file called opmn.xml.

In that file look for:
process-type id="home" module-id="OC4J" status="enabled"
Below that you'll find a node with start-parameters, having a data sub-node with "java-options". In the value-attribute of that node change -ms512M -mx1024M into -ms128M -mx128M. These are the minimum and maximum heapsizes. The home oc4j only needs 128M. It's recommended to give the OC4J at startup the max heapsize right away. Then it need not to grow.

Look again for:
process-type id="oc4j_soa" module-id="OC4J" status="enabled"
Find the same start-parameters, and do the same change but then give it heapsizes of 384M: -ms384M -mx384M.

Conclusion
This gave me aVM with a soasuite and 11g database that runs quite fine in a 1.6GB VM.
These settings are just "wet-thumb"-values. I must strictly say that these are not valid values for a production environment and even might not be valid for a regular development environment with a significant number of developers.

But in my case it all fits, having even 40MB of memory left. According to "top" my VM is not swapping!