Installing Oracle for use with Alfresco

oracle-11g-express-edition

This article is about installing Alfresco with Oracle database. In getting Oracle installed I followed most of the information at this link. The article mentioned here is mainly about setting up an Oracle RAC — which is out of the scope of our article really — but has some good steps to getting Oracle 11g installed on a RHEL 5 server.

Where do you get Oracle 11g? Right here. How much does it cost? Nothing but of course you get no support.

My Oracle “server” is a RHEL 5 virtual machine. Keep in mind if you weren’t aware of the ramifications already but Oracle is serious enterprise software. It has a lot of system requirements that you’ll have to fulfill before it will allow you to install it without complaining. I followed these steps to getting 11g installed here:

Install these packages using yum:

yum install binutils elfutils-libelf glibc glibc-common libaio \
 libgcc libstdc++ make compat-libstdc++-33 elfutils-libelf-devel \
 glibc-headers glibc-devel libgomp gcc gcc-c++ libaio-devel \
 libstdc++-devel unixODBC unixODBC-devel sysstat

Add these lines to /etc/sysctl.conf:

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# 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
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1
# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536
# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
# Additional and amended parameters suggested by Kevin Closson
#net.core.rmem_default = 524288
#net.core.wmem_default = 524288
#net.core.rmem_max = 16777216
#net.core.wmem_max = 16777216
net.ipv4.ipfrag_high_thresh=524288
net.ipv4.ipfrag_low_thresh=393216
net.ipv4.tcp_rmem=4096 524288 16777216
net.ipv4.tcp_wmem=4096 524288 16777216
net.ipv4.tcp_timestamps=0
net.ipv4.tcp_sack=0
net.ipv4.tcp_window_scaling=1
net.core.optmem_max=524287
net.core.netdev_max_backlog=2500
sunrpc.tcp_slot_table_entries=128
sunrpc.udp_slot_table_entries=128
net.ipv4.tcp_mem=16384 16384 16384
fs.file-max=6815744
fs.aio-max-nr=1048676

Note that some of these lines are different than what’s mentioned in article. I had to add a couple of missing settings and increase the values for a few of them contrary to the article. After these are added to sysctl.conf run the following to have the system accept them:

# sysctl -p

Add these lines to /etc/security/limits.conf:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add this line to /etc/security/limits.conf:

session required pam_limits.so

You’ll also want to disable SELinux (Redhat’s advanced security suite) by changing to the setting below in /etc/selinux/config file:

SELINUX=disabled

You’ll need to restart the server so that this setting takes before installing Oracle. Actually you can disable this after adding that line without a server restart. You can do this now or wait until you’re ready for the actual install.

Next, create the groups and users needed for Oracle to work properly:

# groupadd oinstall
# groupadd dba
# groupadd oper
# groupadd asmadmin

Add the Oracle user:

# useradd -u 500 -g oinstall -G dba,oper,asmadmin oracle

Set the password for the “oracle” user:

# passwd oracle

I did add these settings below to the .bash_profile file in user oracle’s home directory:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=rac1.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
ORACLE_SID=RAC1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
    if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
    ulimit -u 16384 -n 65536
    fi
fi

But, after installing Oracle I went back and made changes to paths and other settings to reflect the installed environment. For yours, remember to set the correct values for the ORACLE_SID and ORACLE_HOSTNAME here.

Now, you can run the Oracle installer. Hopefully the prerequisite checks should clear but you may have to work out whichever issues you may come across. Generally for any unexpected issues I found, I was able to Google a solution for it.

If you’re not familiar with the way Oracle manages its database, know that it makes use of schemas as traditional databases (unlike DB2, MySQL or Microsoft SQL Server). The Oracle database is akin to a database server instance. When you create and manage a traditional database in Oracle, know that this is referred to as a schema. For more on how this works, have a look at: http://forums.devshed.com/mysql-help-4/schema-vs-database-388038.html

This one has a crude answer but explains it simply enough. Think of it this way:

MySQL:
tables -> database -> database server or instance (commonly called a MySQL instance)

DB2:
tables -> schema -> database -> database server or instance (commonly called a DB2 instance)

Oracle:
tables -> schema -> database server or instance (commonly called an Oracle database)

The schema when created will have a user owner with the same name. Make sure you use this schema when setting up your database for use with Alfresco. My database settings within alfresco-global.properties look like this:

db.driver=oracle.jdbc.OracleDriver
db.username=alf400
db.password=alfresco
db.name=alf400
db.url=jdbc:oracle:thin:@localhost:1521:orcl
db.port=1521

Know that it’s entirely possible to have a schema created but use a different username other than the one generated on schema creation to connect with Alfresco. In this case db.username is the username created for the schema. The db.name is the schema. A lot of times they are the same but not necessarily especially in high security environments. With the jdbc url know that “orcl” refers to the used SID or instance installed.

Lastly before starting up Alfresco, you’ll need to make sure that the jdbc driver file, ojdbc6.jar, is inside tomcat/lib directory. Make sure you use the ojdbc6.jar found at the Oracle website instead of the one that comes with the Oracle install. With all that when Alfresco starts up, your database should be recognized and all tables Alfresco needs will be set up and populated with initial data.

Cheers! -H. S.