Simple setup of Oracle 11g Release 2 on CentOS 6.3, including pdksh and all dependencies, in VirtualBox

I’ve installed Oracle Database 11g Release 2 a few times on various Linux installs and apart from a few quirks it is a pretty similar process on most. The absolute bare bones default install, as described here, is easy to set up and doesn’t take that long. You can see more detail, including all the recommended steps if you follow the instructions in the Oracle install guide. I will describe installing 32bit Oracle Database 11g Release 2 on CentOS 6.3 32bit with the UI installed so we can use the Oracle installer directly. My computer’s name was “localhost.localdomain” as I was testing this in a development VirtualBox install.

First download Oracle 11g Release 2 from their website. For a linux install it comes as 2 zip files which you must first accept the license for before downloading. The exact version I downloaded was “Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86″.

Now you need to prepare your CentOS install by adding the required users and user groups for the install process. In my setup I am following oracle and running the following commands to add the “oinstall” and “dba” user groups:

groupadd oinstall
groupadd dba

Now add the “oracle” user, who we will be using to run the Oracle 11g install and give the user the correct group membership:

useradd -g oinstall -G dba oracle

Now create a directory and set the appropriate permissions where you are going to install Oracle. In my case I have installed it in the “oracle” user’s home directory under “/home/oracle/app”:

mkdir -p /home/oracle/app
chown -R oracle:oinstall /home/oracle/app/
chmod -R 775 /home/oracle/app/

Now extract the Oracle ZIP files downloaded earlier into somewhere sensible. I chose “/home/oracle/database”. Navigate to the directory and run the install script as your new oracle user:

su oracle

cd /home/oracle/database
./runInstaller

NOTE: In my case, because this CentOS install was a VirtualBox virtual machine I needed to explicitly set the $DISPLAY variable to the local machine before the UI for the installer would run. This is done by running the following command and restarting my shell:

export DISPLAY=:0.0

Now the installer will start up. You can ignore entering your email in the first step “Configure Security Updates” and leave the default setting of “Create and configure a database” in the second step “Installation Option”.

For the “System Class” step of the install I just left it as the default “Desktop Class” and in the “Typical Installation” step I left everything as default apart from setting the Administrative password. The default settings puts the oracle base in “/home/oracle/app/oracle” with a global database name of “orcl.localdomain”. For the “Create Inventory” step I left the default folder of “/home/oracle/app/oraInventory” and the group name “oinstall”.

Now we get on to the interesting part of the install, which is the “Prerequisite Checks” stage. If you are running the install on a brand new copy of CentOS you will need to set a few system variables and install a set of prerequisites.

NOTE: You may not need to, but I needed to add more swap space to my CentOS install this time around in order to meet the prerequisites. Run the following commands as root to create a 2048mb swap file called “/swapfile” on your harddrive and set CentOS to use it for swap space:

dd if=/dev/zero of=/swapfile bs=1024 count=2097152
mkswap /swapfile
swapon /swapfile

Now set CentOS to always use this swap space at boot by editing your “/etc/fstab” file using the command:

nano /etc/fstab

And add the following line:

/swapfile  swap  swap  defaults  0  0

So if you have passed the swap space test in the “Prerequisite Checks” in the Oracle install you can start to fix all those “Failed” messages. Click on the button “Fix & Check Again” and a window will pop up to tell you about the handy “runfixup.sh” script that will be placed in “/tmp/CVU_11.2.0.1.0_oracle/runfixup.sh”. So in your shell, navigate to the directory as root and run the script:

cd /tmp/CVU_11.2.0.1.0_oracle/
./runfixup.sh

The “runfixup.sh” script will fix all the system variables for you so you don’t need to set them manually. Now all that remains is to fix the dependencies, most of which can be installed using “yum” with the following command:

yum install gcc gcc-c++ compat-libstdc++-33 elfutils-libelf-devel libaio-devel libstdc++-devel unixODBC unixODBC-devel

Now the only remaining prerequisite that causes a “Failed” message is “pdksh-5.2.14″ which has been removed from the CentOS repositories after CentOS 5 (see here). The replacement is “ksh” but if you install this package using “yum install ksh” you will get the same dependency check “Failed” in the Oracle install for “pdksh-5.2.14″ and “ksh” will conflict with “pdksh” if you then go to install it.

The solution is to install “pdksh” manually from RPM, which can be found at a variety of mirrors. I used the following command to install the “pdksh” package:

rpm -q ftp://ftp.pbone.net/mirror/archive.download.redhat.com/pub/redhat/linux/6.1/en/os/i386/RedHat/RPMS/pdksh-5.2.14-1.i386.rpm

Now Oracle should pass all the prerequisite checks and you will see the “Summary” step of the install where you can click the “Finish” button. It may take a while but Oracle Database will install with all the required settings ready for you to use out of the box.

The final step is to execute the configuration scripts as root, which will pop up after you have unlocked any users you might need other than the defaults (you don’t need to though at this stage). The two scripts can be run as follows:

cd /home/oracle/app/oraInventory/
./orainstRoot.sh

cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/
./root.sh

To test your install worked you can log in to the web based management interface for your computer “localhost.localdomain” with the user name “SYS” connecting as “SYSDBA” and using the password you set during the install of Oracle. Remember to open port 1158 on your firewall if you need to:

https://localhost:1158/em/

Now you can start to use Oracle. I highly recommend looking through the documentation from Oracle themselves to help get yourself used to the Oracle way of doing things. There are loads of client applications that can help, like the command line based Oracle Instant Client and the Oracle SQL Developer UI program. Oracle have a lot of good walkthroughs for working with their tools which are available as part of their Learning LIbrary.

My Oracle and Solaris Experience Overview

This post is to be updated as I dig out more of my original notes and bookmarks. It was written for an overview document to be handed to the next person handling the Oracle project:

The Oracle ADF development guide gives a good overview of the entire Application Development Framework as provided by Oracle and is targeted at Oracle 10g. There is a short presentation on ADF available.
Migration to Oracle from Access is best done using the ODBC connection to Oracle and standard SQL commands. There are other alternatives but most migration tools didn’t work properly or were expensive. Some of the tools tried include Oracle SQL Developer, EMS Data import, Apatar, Oracle Migration Workbench (see this forum thread).

The installation process of Oracle on Solaris took some time to get to work but despite a few typos this guide is the best source of information on Oracle installation. More information is available here but this is a slightly different installation method. My blog at http://blog.jamesrossiter.co.uk has some of the steps I took to install Oracle as well as a few of the pitfalls in the documentation to watch out for.
There is some slightly outdated info on Oracle’s built in http server here and here which helped explain a few of the different components Oracle needs for http.

The Oracle web server of choice is OC4J, Oracle Containers for Java. The most useful tutorials for working with JDeveloper and OC4J were on deployment of applications as well as this, the most important tutorial in this list on JSF, EJB and Oracle’s preferred way of writing web apps with JDeveloper. OC4J is based on Apache and must be installed separately to Oracle. A cut down old version of OC4J is included with Oracle 10g but this is not to be used to develop apps. A lot of the documentation is confusing on this but after checking the forums it appears you need to install a standalone OC4J instance or install the Oracle Application Server. I installed the standalone OC4J as it seems much easier to use and the documentation I found is mainly focused on the standalone version.

I had a look at Oracle htmlDB, which has been updated and is now called Apex (Oracle Application Express). This is supposed to be a very quick way of prototyping database driven applications, see this unofficial overview. There is an unofficial wiki for Apex. I got Apex to work but the amount of flexibility provided was not enough and I was concerned that we would outgrow the abilities of Apex when writing custom apps.

The best way to write online apps looks to be the ADF way of doing things with Jdeveloper, Oracle 10g (or 11) and OC4J. For this you need to know Enterprise Java Beans, Java Server Faces and how to use Jdeveloper to deploy your applications to OC4J. I had problems deploying applications to my Solaris install of OC4J using Jdeveloper but have tested several small apps based on the tutorial (in bold above) using the built in OC4J server in Jdeveloper. My forum posts with the problems I faced following the deployment tutorial are here and here but I still didn’t manage to solve the problem. There is a third party forum devoted to developing for Oracle.

The key to Jdeveloper web application creation is Master-Detail relationships, made possible through foreign key relationships in the Oracle database and automatic object creation using Java Beans. Master-Detail relationships can be easily visualised using the templates available in Oracle ADF Server Faces (a more Oracle focused version of Java Server Faces) and there are plenty of tutorials available from Oracle like those in the official documentation.

Auto Incrementing Columns in Oracle

Unlike SQL server, MySQL etc. Oracle doesn’t have identity columns. This is a bit of a pain when you need an auto incrementing column for a unique ID, which I use all the time as a primary key in the “many” table of a “one to many” relationship. 

Oracle deals with this by using a sequence and a trigger attached to insert statements. This post from lifeaftercoffee describes the method I followed to get it to work:

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

If you don’t want to use a trigger you can manually add the next value in the sequence during your insert statement:

insert into test values(test_sequence.nextval, ‘my data!’);

Changing The SQLPlus Working Directory

I tend to run sqlplus from the command line where it is really easy to set the working directory before each session. You just navigate to where you want the working directory to be and start sqplus from there. When you run “sqlplus” it uses the current directory as the working directory, easy!

This is how I do it in Windows, I assume it is the same in every other OS..

Running Web Applications on Oracle OC4J and Solaris

I reached the stage where I can deploy the example web application from Oracle on my own standalone Oracle Containers for Java EE (OC4J) installation running on Solaris. There were a couple of hurdles before I got this far, one of which is a known bug with the Oracle Application Development Framework (ADF). Now that my Solaris install is set up I should be able to deploy applications written on my local machine (a Windows XP install) to my OC4J server using Oracle Jdeveloper.

First up I downloaded the latest version of the OC4J standalone install and extracted it to “/export/home/OC4J”. I set the “J2EE_HOME” environment variable to “/export/home/OC4J/j2ee/home” and made sure “JAVA_HOME” was set to “/usr/java”.

Next I tested the install by downloading the “Hello World” .jsp page from Oracle. After making sure this was working ok by placing it in “/export/home/OC4J/j2ee/home/default-web-app” and going to “http://serveraddress:8888/helloworld.jsp” I was ready to try installing an actual web application.

I followed the instructions from Oracle for deploying a web application. In short this requires you to enable the “oe” user in Oracle, set up a connection to your Oracle database and OC4J server in Oracle Jdeveloper and set up deployment projects for the example web application. This is all covered in the tutorial.

I tested the application on my local machine where it worked fine so I thought it could easily be deployed. I clicked “Deploy to OC4J” on the “OrderEntryApplication.deploy” .EAR file and the deployment failed. I tried using the web interface at “http://serveraddress:8888/em” to deploy the .EAR file manually and got the same error (sorry I didn’t record exactly what it was).

Next up I googled and found that you need the Oracle ADF installed on your OC4J server in order to run web applications. It isn’t installed by default in the OC4J standalone install so you need to install it manually. I read a few forum posts that said you could install ADF by just copying some .jar files across to your OC4J install but decided to do it the “proper” way as suggested by Oracle. I installed Jdeveloper on my Solaris box by downloading it and extracting to “/export/home/jdevstudiobase10134″ then started it by running “/export/home/jdevstudiobase10134/jdev/bin”. I shut down my OC4J server first as required and clicked “tools -> ADF Runtime Installer -> Standalone OC4J…” in Jdeveloper and went through the wizard to install the ADF runtime components.

This should have been fine but when I went to start the OC4J server up again I got an error:

Error initializing server: Shared library “oracle.ws.jaxrpc” could not be found.

According to this forum post this is a bug and the workaround has even been included in the 10.1.3.x release notes addendum. To fix it you need to go into your “J2EE_HOME/config/server.xml” and remove the two references to the offending library by deleting this line twice:

<import-shared-library name=”oracle.ws.jaxrpc”/>

Now when you start up the OC4J server again it will start and you can visit your web application. One final thing, if following the tutorial make sure you visit “http://servername:8888/OrderEntry/faces/index.jsp”. I missed out the “/faces/index.jsp”, if you miss out the “faces” you get a server 500 error (the server doesnt know to use Java Server Faces) and if you miss out the “index.jsp” the link on the first page doesnt go anywhere!

Next step is to write a custom application to display, edit and add data in an Oracle database schema. I will be using Enterprise Java Beans for data objects and Java Server Faces for the front end on top of JSP. Still a fair way to go but at least now we can create and deploy applications using Oracle Jdeveloper.

Don’t Use OC4J Bundled with Oracle 10g

I couldn’t believe it when I saw it on this random forum post at Oracle’s own website but apparently you are not supposed to use the OC4J install that comes with Oracle for your own applications. It is meant only for internal settings!?

Please do not use the internal OC4J from the DB for your application. Please install a standalone OC4J instance which is available from OTN. You will not have the same functionality as you will with the standalone OC4J. That instance is used for internal settings (it is also used for the console), therefore please download an installation.

Is this why my “helloworld.jsp” page from Oracle themselves throws this error when placed in the default web app directory?

500 Internal Server Error

Servlet error: java.lang.ClassNotFoundException: _helloworld

I can only hope so. Next step, try installing a standalone version of OC4J and test using the same “helloworld.jsp” file. Why is this not CLEARLY stated in the documentation? The OC4J default install with Oracle includes a help file “OC4Jstandaloneguide.pdf” that doesn’t mention this anywhere! This could also explain why all the help I can find online references a default port of 8888 not the 1158 port that the Oracle Enterprise Manager is located on at “http://hostname:1158/em”.

UPDATE:

I installed the OC4J standalone from Oracle available here by unzipping the downloaded file and setting the J2EE_HOME to the location of “<oc4j install dir>/j2ee/home”. It works, but it seems to really really slow down my machine after it is started. I need to look into this but at least I can view the test pages and my “helloworld.jsp” file works perfectly.

Using Webmin to Start Oracle Automatically on Solaris Boot

I tried a few different techniques to get my Oracle startup script to run at boot on Solaris. I couldn’t get it to work using the old style “init.d” and “rc_3.d” directory methods, which have now been depreciated in Solaris 10 and replaced with the “Service Management Facility (SMF)”. You can still use the old legacy methods but I just could not get them to work. I started to read up on the SMF and other methods for controlling bootup and found this page on informIT which is a chapter from a Solaris exam prep book. This chapter goes into a lot of detail and includes info on using the SMF as well as info on legacy service starting methods.

I was about halfway through this when it was suggested I use Webmin for Solaris to manage startup services etc. I hadn’t used webmin properly before but I was pretty amazed at how easy it makes quite a lot of system management.

My database startup script had to be started as the correct user (oracle) which I think was throwing off my previous attempts at starting the script as a service. I am positive there is a better way of running the script than using “su – oracle -c” then the command but I couldn’t find how else to make sure it was running as the right user..

Solution: Use Webmin to make a bootup action and let it take care of where the script is started from. I should really dig around and see exactly what it has changed but for the time being this will do fine.

  1. Install the Webmin package using the instructions on their site.
  2. Go to the Webmin page at “http://yoursite:10000″.
  3. Click “System” then “Bootup and Shutdown” then on the page that opens click “Create a new bootup and shutdown action”.
  4. Put in a name (has to be standard name, no spaces) and description of your script.
  5. Now put in the actual script in the “bootup commands” box. For me this was “su – oracle -c /export/home/app/oracle/oracle/product/10.2.0/db_1/startupdb”.
  6. Make sure it is set to start at boot time and click “Create”.

.

Now your script will start as a service at boot time. For me it made the boot a little longer than usual but it’s a price I’m willing to pay to not have to log in and start Oracle manually!

For reference my “startupdb” script is the following (which also includes environment variables I thought I might need):

#!/bin/sh

ORACLE_HOSTNAME=myhostname;
ORACLE_SID=orcl;
ORACLE_BASE=/export/home/app/oracle;
ORACLE_HOME=/export/home/app/oracle/oracle/product/10.2.0/db_1;
PATH=/usr/sbin:/usr/bin:/usr/openwin/bin:/usr/ucb/:/export/home/app/oracle/oracle/product/10.2.0/db_1/bin;

export ORACLE_HOSTNAME ORACLE_SID ORACLE_BASE ORACLE_HOME PATH;

sqlplus sys/password as sysdba <<ENDOFSQL
startup;
exit;
ENDOFSQL
emctl stop dbconsole; emctl start dbconsole; lsnrctl start;

SQLPlus, SQL, Starting & Controlling Oracle Using Shell Scripts

I found a post by dbamac that describes how to run SQLPlus commands and SQL commands themselves from a shell script. I now use a script to start up my Oracle database and bypass my growing frustration with “dbstart” which seems to partially work now that I have set up the correct permissions on the startup.log and shutdown.log files.

As I have said before the way I boot oracle up is by running “sqlplus sys/password as sysdba” then typing “startup” and finally “exit” when it is all booted. I then run a nice long command “emctl stop dbconsole; emctl start dbconsole; lsnrctl start;” to restart the database web console and start the listener service up.

Thanks to dbamac I have now created a short script that does this in one go:

#!/bin/sh
sqlplus sys/password as sysdba <<ENDOFSQL
startup;
exit;
ENDOFSQL
emctl stop dbconsole; emctl start dbconsole; lsnrctl start;

I have to run the script as “oracle” as this is my user created during installation of the Oracle database who has all the right permissions etc. I stored the script as “startupdb” in my $ORACLE_HOME directory, in my case:

$ORACLE_HOME = /export/home/app/oracle/oracle/product/10.2.0/db_1/

So the command I use when I am logged in as root to start everything up is:

su – oracle -c $ORACLE_HOME/startupdb

The power of calling SQLPlus commands from the shell is awesome. It means you can set up cron jobs to automatically email reports, automatically backup data etc etc. Now I just need to get the script to start at boot, which means I am currently digging through the Solaris boot process using websites like this one from Admin’s Choice.

The final goal is to have Oracle automatically start when Solaris boots and then email me to tell me everything is up and running ok. I need to figure out a few things before but it would be an incredibly handy feature to have.

UPDATE:

See this post for how I got Oracle to automatically start on Solaris boot.

Setting Up & Starting Apache and Tomcat on Solaris 10 x86

Apache, Java and Tomcat are all installed by default in my copy of Solaris 10 x86, I assume this is the same for all versions of Solaris. I was dreading compiling Apache and Tomcat from source and was even considering installing the package from Blastwave.org. Luckily I googled a bit more and soon realised everything was installed by default, which is actually a bit obvious when you think about it.

Thanks to a random bug report page on opensolaris.org requesting an addition in documentation I got straight to the answer. I had to add an extra step in to add the required environment variables (which are now sitting in my “profile” file to be loaded each time). I’ve compressed the information a bit here but these are the steps I took to start Apache and Tomcat:

First add the two environment variables JAVA_HOME and CATALINA_BASE (for Tomcat) to your “/etc/profile” file or current session using usual “export” command:

export JAVA_HOME=/usr/java

and

export CATALINA_BASE=/var/apache/tomcat

Now you need to make sure the config files for both Apache and Tomcat exist with the right filenames. Luckily there are example config files included with both so you can just rename/copy these config files:

cp /etc/apache2/httpd.conf-example /etc/apache2/httpd.conf

cp /var/apache/tomcat/conf/server.xml-example /var/apache/tomcat/conf/server.xml

Now you can start up Apache:

svcadm enable apache

Now make a symbolic link to point the Tomcat “conf” directory to the Apache Tomcat “conf” directory (just to make sure config files come from the right place):

cd /usr/apache/tomcat

ln -s /var/apache/tomcat/conf conf

Now last of all, start up the Tomcat server using the startup.sh script in the Tomcat “bin” directory:

/usr/apache/tomcat/bin/startup.sh

Now you have both Apache and Tomcat running (on ports 80 and 8080 respectively). The home directory for Apache, where the html documents etc are stored, is located at “/var/apache2/htdocs/”. The home directory for Tomcat is at “/var/apache/tomcat/webapps/ROOT”.

Interestingly the test page for Tomcat at “http://localhost:8080″ states the environment variable as “$CATALINA_HOME” and not “$CATALINA_BASE” as I used. My Tomcat seems to be perfectly happy and I can run the sample applications so I guess you don’t need to worry about this.

UPDATE:

If you have installed Oracle you can use Oracle’s own web application server (OC4J) at “http://localhost:1158″. There is a default page here with links to documentation etc. I’ll write more on using this once I figure out how to do dev work in Windows and push it to our Solaris installs.