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.

This week I encountered a little, nasty, problem with my renamed/rehosted application server under VMWare.
When I registered esb-services I was not able to create a BPEL Partnerlink on them since it contained an import-url to the old host name ("localhost.localdomain"). It took me quite a while to find out, and solved it by changing the appropriate parameter in the esb_parameter table in oraesb.
I edited my original posting with this new knowlegde to have all the steps in one document.

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!

I previously described how to install 11g Database. I found that it took a long time when connecting to the database from outside the VM, using sqldeveloper, sqlplus, Pl/Sql Developer.

I use a host-only and a bridged adapter in the VM. It took me a while but I found that it has something to do with a DNS-lookup that the database does during the connection process. In my /etc/resolv.conf a reference to the host for the name server is registered. But on my host I don't have a DNS server. It should get it from the physical/bridged network or not at all.

I resolved it by uncommenting the lines in the /etc/resolv.conf (place a semi-colon before each line). Also you change your networksettings in the network-devices. in Oracle Enterprise Linux. On one of the tabs you'll find an entry to the prefered dns-server. And a domain. You should clear those lines.

In my previous post I mentioned that running Pl/Sql developer under wine goes fine, but it does not show the icons on the buttons. Indeed it was the case with me. But somehow they appeared magically.

However, the other "minusses" still stand.

This week I installed Pl/Sql Developer under wine. It was pretty easy. To have it working you need to install an Oracle Instant client. Probably you could install a complete Oracle Client, but the Instant Client will do and it just gives you enough to run Pl/Sql developer.

I unzipped the 10gR2 instant client (Windows 32-bit) into /home/makker/.wine/drive_c/oracle/product/instantclient_10_2.
I also put the sql-plus addendum there, but that did not work.
Then you place a valid tnsnames.ora in the subdirectory /home/makker/.wine/drive_c/oracle/product/instantclient_10_2/Network/Admin.

Install Pl/Sql developer (I just ran the installer under wine). When starting Pl/Sql developer you first have to go to the preferences and then the connection part. (menu=> tools => preferences). There you have to point Pl/Sql developer to your instant client in a windows way: C:\oracle\product\instantclient_10_2\oci.dll. After doing that, restart Pl/Sql Developer. Then it will load the oci.dll.

Then, provided that you have a database running and a valid tnsnames.ora you can connect to your database. In my case the connection to my 11g database in the VM is very, very slow. I haven't figured out yet what causes it. But I got the same behaviour using SqlDeveloper.

I'm very pleased having Pl/Sql developer running under Linux. There are however a few points to figure out and/or improve:

• Buttons in the button bar are not shown.
• Some features just don't work, like the macro-recorder.
• Sometimes when switching applications, the Pl/Sql developer pane is not repainted correctly or at all. I have to play with switch "shade" (right-click in the taskbar) on and of, to get Pl/Sql Developer shown again.

But for me Pl/sql developer is most productive tool for the job. So I accept these "instabilities" under wine.

My current customer is implementing Hyperion DRM. In DRM Organizational hierarchies are stored. These hierarchies have to be exported to several output formats for several client-systems.

We advised to use Oracle SoaSuite for the integration in stead of building exports for every single target-system. But how do you get the exports out of DRM into SoaSuite? The original idea was to have a schedular call DRM to run the export to a (XML-) file and have SoaSuite polling to that file.

I've looked into the integration possibilities of DRM. DRM has been said to have WebServices but we could, upfront, not find out if the webservices are just Soap-Services (without WSDL's) or "real" Webservices described with WSDL's. It turns out that DRM has WSDL-described webservices. The url to the WSDL should be something like:
http://--drm-server--/mdm_ntier/--service--.asmx?WSDL
Where --drm-server-- is the host where the DRM server with the webbrowser is running, and --service-- is the particular service. So something like:
http://winxp.darwin-it.local/mdm_ntier/SessionMgr.asmx?WSDL

It turns out that the DRM webservices have multipart message-types. The ESB of Oracle SoaSuite 10133 does not like them. BPEL PM seems not having a problem with them. But the wsdl's use imported schemas from:

<s:import namespace="http://schemas.xmlsoap.org/soap/encoding/" >
<s:import namespace="http://schemas.xmlsoap.org/wsdl/" >

These need an internet connection to be validated and be used in JDeveloper. What you could do is put them on a local webserver and modify the wsdl's accordingly.

But having solved that it also turns out that BPEL gets a response message that does not seem to conform the WSDL. The message I got was:
"trailing block elements must have an id attribute"
So I created a webservice-proxy on a WSDL and that works fine. Using the HTTP-Analyser of JDeveloper I intercepted the response and although the webservice proxy did accept the response, and apparently is confident with the wsdl, it seems to mee that the response does not match the wsdl. And BPEL PM agrees with me. Or better, I agree with BPEL PM.

So that did not get us any further. I've learned from a contact at Oracle Development that the Webservices from DRM indeed are not supported by BPEL PM. In DRM 11 there are changes made to the webservices in a way that some simpler ones should be accepted by BPEL PM. But apperently others still aren't.

One could wonder how this could be? Aren't webservices just invented to have technology agnostic and flexible integration? I read somewhere that the way the wsdl's of DRM are created are quite regular in the .Net world. DRM is build in Delphi (I was really surprised to see such a high-end Bussiness Application being build with Delphi, since Turbo Pascal was my favorite programming environment on college/university).

DRM also delivers java-API's. To use them you need an sdk from DRM, that can be downloaded here.

We created a java-class using the examples in the api-documentation that is delivered with DRM. Look for mdm_ntier_api_932.pdf. Unfortunately I could not find this information on OTN for you.
This Java class connects with the master-data-management server. Then it looks-up an export, starts a job and then gets the output into a string. This string is then returned. Our businessguys defined some standard exports that deliver the data into an XML message.

On this java class we created a webservice, using the webservice generation wizard from JDeveloper. Actually, since the api's are a layer on the DRM webservices, in fact they are webservice proxies, we created a webservice on several DRM webservices.
This webservice is then callable from BPEL PM.
When deploying the webservice to the AS, you should deploy the jar's from the mdm_ntier_apis-sdk also, with your deployment-descriptor. I tried to upload them as separate shared libraries in OC4J, but that didn't work.

The exported XML message is parsed in BPEL PM using the parse-xml ora:parseEscapedXML function. To be able to transfer it we had to add a namespace in the root element, using
concat(substring-before(bpws:getVariableData('Receive_Export_onResult_InputVariable','payload','/ns1:RunExportProcessResponse/ns1:result'),'<MDMMetadata'),'<MDMMetadata xmlns="http://xmlns.customer.com/drm" ',substring-after(bpws:getVariableData('Receive_Export_onResult_InputVariable','payload','/ns1:RunExportProcessResponse/ns1:result'),'<MDMMetadata'))


In the Workflow Development Kit that can be downloaded using the link above, the same approach is used. So apparently Oracle also found that BPEL PM does not support the DRM Webservices and state that this is the way to go.

I did not put in any code in this blog-entry. But most of the java-code I got from the examples. Except for transferring the export-output into a string. But that is also quite straight-forward. And generating the webservice is just playing the wizard with the defaults.