Databases | database dumping ground

Over the years I’ve had exposure to various popular DBMSes; mainly PostgreSQL, MySQL, Microsoft SQL server and a little bit of PervasiveSQL/Btrieve. One DBMS that I’ve never had to touch is Oracle, and perhaps that’s a good thing, but it can’t harm to get a little bit of insight into a technology that is used so widely.

Being well versed with PostgreSQL should be a good start. Despite the various grammatical, functional and architectural differences there are a lot of similarities, and the PostgreSQL wiki has an excellent guide intended for Oracle DBAs which works just as well in reverse for PostgreSQL DBAs.

One thing Oracle doesn’t have in common however, is the wide choice of “supported” host operating systems that PostgreSQL does (Check out the PostgreSQL build farm); and my personal preference of Debian GNU Linux isn’t one of them! (At least according to Oracle FAQ’s)

Despite Debian not being supported it shouldn’t be too difficult to get it to work if it works on other Linux flavours, and this post summarises my attempt. A lot of the process comes from a great guide I found on Debian Administration, so to a great degree I’m regurgitating that adding in a little along the way.

To begin with we start out with a vanilla install of Debian Squeeze 6.0 from the netinst CD image, on top of that there’s a few libraries that Oracle depends on we need to install:

# apt-get install gcc make binutils libmotif4 lesstif2 rpm libaio1 libdb4.6 unzip libstdc++5 libstdc++6 g++

And we want bash as our default shell:

# dpkg-reconfigure dash

Next we need to make some parts of the directory structure look more like Red Hat, we can do that with a few soft links as follows:

# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename
# ln -s /etc /etc/rc.d

Next we set up a group and user for the Oracle install:

# groupadd oinstall
# groupadd dba
# useradd -m -g oinstall -G dba -p passwd -s /bin/bash -d /home/oracle oracle
# passwd oracle

I’ve read that it’s typical to use a prefix of /u01 for the Oracle binaries, and optionally put the data directories on their own mount points following the same pattern /u0n etc. If this was a production system those data directory mount points would be on well tested and reliable raid arrays, but for this test I’m going to keep everything in /u01 which just symlinks into the home directory:

# mkdir /home/u01
# ln -s /home/u01 /
# chown -R oracle.oinstall /home/u01 /u01
# chmod -R 775 /home/u01 /u01

Create a directory for the installer, place the files in there and make sure the installer has priviliges:

# mkdir /usr/oracle_installer
# chown -R oracle.oinstall /usr/oracle_installer

Next we setup some of Oracles preferred kernel parameters, certainly if you’re used to other linux databases this will be familiar to you; shared memory, semaphores, max open files, huge pages etc. My test VM has 4Gb of memory, and I’ve set my maximum shared memory segment size to a conservative 248Mb, and put in the group id of the Oracle dba group to use hugepages. Place these in /etc/sysctl.conf and reload with sysctl -p:

kernel.sem = 250 32000 100 128
kernel.shmmax = 260513792
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
fs.file-max = 6815744
vm/hugetlb_shm_group = 1002

Increase the system wide user limits for open files and running processes in /etc/security/limits.conf as follows:

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

You can check these as by running ulimit with any of the -Hu/-Su/-Hn/-Sn flags as the oracle user.

We also need to make sure the above limits are used, so make sure pam_limits.so is not commented out in the following files:

/etc/pam.d/su
/etc/pam.d/login
/etc/pam.d/sshd

Now edit /etc/profile to set the process and file limits for the oracle user, I’m sure this could also be done in the “oracle” users .profile too.

umask 022
if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
            ulimit -p 16384
            ulimit -n 65536
      else
            ulimit -u 16384 -n 65536
      fi
fi

Edit /home/oracle/.profile and set the environment variables required for the installer

ORACLE_BASE=/u01/app/oracle
ORACLE_SID=instancename
export ORACLE_BASE ORACLE_SID
unset ORACLE_HOME
unset TNS_ADMIN

The Oracle installer is a graphical one, so unless we’ve already generated a “response” file to do a non-interactive installation we need X windows. To setup a dummy xserver and use vnc:

# apt-get install xserver-xorg-video-dummy vnc4server x11-xserver-utils xterm wm2

Run vnc4server as the oracle user and then kill it. This should make sure that /home/oracle/.vnc/xstartup exists, edit it and add the following 2 lines to the bottom. This makes sure a window manager and terminal will be started correctly:

# su oracle -
$ vnc4server
$ vi /home/oracle/.vnc/xstartup
	x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" & 
	wm2 &
$ exit

Now run vnc4server again as user oracle

$ vnc4server

Now we connect to the server with vnc and run the installer from a terminal in X windows:

$ cd /usr/oracle_installer
$ ./runInstaller

Once the installation is complete, we need to change /home/oracle/.profile again ready for running Oracle rather than the installer:

ORACLE_BASE=/u01/app/oracle
ORACLE_SID=orcl				#(instancename)
ORATAB=/etc/oratab
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin/
export ORACLE_BASE ORACLE_SID ORATAB
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
unset TNS_ADMIN

Here’s the init script for starting the server:

#!/bin/bash
#
# Run-level Startup script for the Oracle Instance and Listener
#
# chkconfig: 345 91 19
# description: Startup/Shutdown Oracle listener and instance

### BEGIN INIT INFO
# Provides:          oracle
# Required-Start:    $remote_fs $syslog $all
# Required-Stop:
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Oracle database server
# Description:       Oracle database server
### END INIT INFO

ORA_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
ORA_OWNR="oracle"

# if the executables do not exist -- display error

if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ]
then
        echo "Oracle startup: cannot start"
        exit 1
fi

# depending on parameter -- startup, shutdown, restart
# of the instance and listener or usage display

case "$1" in
    start)
        # Oracle listener and instance startup
        echo -n "Starting Oracle: "
        ## I think dbstart automatically starts the listener if it's not running
        ## previously the listener was started first.
        su - $ORA_OWNR -c "$ORA_HOME/bin/dbstart $ORA_HOME"
        su - $ORA_OWNR -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNR -c "$ORA_HOME/bin/emctl start dbconsole"
        touch /var/lock/oracle
        echo "OK"
        ;;
    stop)
        # Oracle listener and instance shutdown
        echo -n "Shutdown Oracle: "
        su - $ORA_OWNR -c "$ORA_HOME/bin/lsnrctl stop"
        su - $ORA_OWNR -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        su - $ORA_OWNR -c "$ORA_HOME/bin/emctl stop dbconsole"
        rm -f /var/lock/oracle
        echo "OK"
        ;;
    reload|restart)
        $0 stop
        $0 start
        ;;
    *)  
        echo "Usage: $0 start|stop|restart|reload"
        exit 1
esac
exit 0

Put this in /etc/init.d/oracle, and set it to run at boot:

# chmod 755 /etc/init.d/oracle 
# update-rc.d oracle defaults

For the default database instance to start automatically it should be set in /etc/oratab, for the above setup this just means changing:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

To:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

Lastly check that the listener address is set to the current host ip/domain in /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora, and if it is wrong amend it appropriately.

We should now be able to start Oracle with:

# invoke-rc.d oracle start

If we get a message stating “listener supports no services” on startup, we just need to register the server with the listener, and we can do that from sqlplus with:

# su oracle -
oracle$ sqlplus SYSTEM

[oracle11]
SQL> ALTER SYSTEM REGISTER;
[/oracle11]

So that’s it, the instance of Oracle should now be all set up and running, the Enterprise Manager should be available at https://your-ip-or-domain:1158/em/console/logon/logon, and we can get to the database command line / shell as superuser running “sqlplus SYSTEM” as the oracle user.

Update 16/03/2013 – Upgrade from Squeeze to Wheezy gives us the error: “ORA-00845: MEMORY_TARGET not supported on this system”

This is because /dev/shm has now moved to /run/shm, Debian creates a softlink from /dev/shm to /run/shm, however this is not enough for Oracle and it’s Automatic Memory Management (AMM).

The easiest solution to this is to create a startup script to do the rebinding with mount -B
(Reference: https://forums.oracle.com/forums/thread.jspa?threadID=2301639)

Create an init script /etc/init.d/oracle-shm as follows:

#! /bin/sh

### BEGIN INIT INFO
# Provides:          oracle-shm
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6 
# Short-Description: Bind /run/shm to /dev/shm at system startup.
# Description:       Fix to allow Oracle 11g use AMM.
### END INIT INFO

# /etc/init.d/oracle-shm
#
#
case $1 in
  start)
    echo "Starting script /etc/init.d/oracle-shm"
    # Run only once at system startup
    if [ -e /dev/shm/.oracle-shm ]; then
      echo "/dev/shm is already mounted, nothing to do"
    else
      rm -f /dev/shm
      mkdir /dev/shm
      mount -B /run/shm /dev/shm
      touch /dev/shm/.oracle-shm
    fi
    ;;
  stop)
    echo "Stopping script /etc/init.d/oracle-shm"
    echo "Nothing to do"
    ;;
  *)
    echo "Usage: /etc/init.d/oracle-shm {start|stop} passed $1"
    exit 1
    ;;
esac

Make the script executable and run at startup before oracle starts:

# chmod 755 /etc/init.d/oracle-shm
# update-rc.d oracle-shm defaults 01 99

database dumping ground

… getting the wrong answer fast is not an improvement over the right answer slow

Category Archives: Databases

Installing Oracle 11g on Debian Squeeze (and Wheezey)