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