Installing Oracle XE on Debian - David Pashley.comDavid Pashley.com

Oracle recently published Debian packages, so I thought I’d have a play to see what they were like. In this article I explain how to install a database server and set up a client to connect to it, and the gotchas to watch out for along the way.

Installing the server

Oracle is not a light database and needs some fairly hefty hardware to run on. You will need at least 256MB of memory and preferably 512MB and at least 1GB of swap space. As well as having minimum requirements, XE also is restricted to 1GB of memory, 4GB of user data and will only use one CPU on a multi-processor box. If you need more, then you will need to buy an upgrade from Oracle.

To install Oracle XE, you need to add the following line to your /etc/apt/sources.list file:

deb http://oss.oracle.com/debian unstable main non-free

We can now install the Debian packages using your favourite package management tool.

Note

There is a small bug in the Debian packaging, in that it doesn’t depend on the bc package, so you will need to make sure this is installed first. Also, if, like me, you are using a chroot to install Oracle, you need to make sure proc is mounted.

For the server you can install the oracle-xe package, or, if you need non-latin language support, you can install oracle-xe-universal. The only difference is the addition of the language support. You can also install the oracle-xe-client package, although it isn’t needed on the server. The only dependency Oracle has is for libaio. If you are installing on sarge, Oracle provide a suitable version, although the version in etch and sid is fine.

db-server:/# aptitude install oracle-xe oracle-xe-client
Reading package lists... Done
Building dependency tree... Done
Reading extended state information
Initializing package states... Done
Reading task descriptions... Done
Building tag database... Done
The following NEW packages will be automatically installed:
  libaio
The following NEW packages will be installed:
  libaio oracle-xe oracle-xe-client
0 packages upgraded, 3 newly installed, 0 to remove and 0 not upgraded.
Need to get 0B/247MB of archives. After unpacking 476MB will be used.
Do you want to continue? [Y/n/?]
WARNING: untrusted versions of the following packages will be installed!

Untrusted packages could compromise your system's security.
You should only proceed with the installation if you are certain that
this is what you want to do.

  oracle-xe-client oracle-xe

Do you want to ignore this warning and proceed anyway?
To continue, enter "Yes"; to abort, enter "No": yes
Writing extended state information... Done
Selecting previously deselected package libaio.
(Reading database ... 8777 files and directories currently installed.)
Unpacking libaio (from .../libaio_0.3.104-1_i386.deb) ...
Selecting previously deselected package oracle-xe.
Unpacking oracle-xe (from .../oracle-xe_10.2.0.1-1.0_i386.deb) ...
Selecting previously deselected package oracle-xe-client.
Unpacking oracle-xe-client (from .../oracle-xe-client_10.2.0.1-1.0_i386.deb) ...
Setting up libaio (0.3.104-1) ...

Setting up oracle-xe (10.2.0.1-1.0) ...
Executing Post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure 
the database.

Setting up oracle-xe-client (10.2.0.1-1.0) ...
Executing Post-install steps..........

The installation performs a number of steps including creating an oracle user and a dba group. It also modifies your /etc/sysctl.conf to set a couple of options. On my setup it added:

# Oracle XE Recommended Values
kernel.sem = 250 32000 100 128
kernel.shmmax = 536870912
fs.file-max = 65536

The complete list of parameters the installation may change are:

/proc/sys/kernel/sem
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmmni
/proc/sys/kernel/shmall
/proc/sys/fs/file-max
/proc/sys/net/ipv4/ip_local_port_range

We should now do what the install script said and run /etc/init.d/oracle-xe configure to do the final set up of the database.

db-server:/# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:<enter>

Specify a port that will be used for the database listener [1521]:<enter>

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:password
Confirm the password:password

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:<enter>

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"

Be warned, this stage can take some minutes. We can check to make sure that everything is running okay by checking the/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log log file.

TNSLSNR for Linux: Version 10.2.0.1.0 - Production on 17-APR-2006 16:49:47

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

System parameter file is /usr/lib/oracle/xe/app/oracle/product/10.2.0/
   server/network/admin/listener.ora
Log messages written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/
   server/network/log/listener.log
Trace information written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/
   server/network/trace/listener.trc
Trace level is currently 0

Started with pid=3407
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-server.example.com)
(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
17-APR-2006 16:49:48 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=db-server)(USER=oracle))
(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
17-APR-2006 16:50:38 * service_register * XE * 0
17-APR-2006 16:50:40 * service_update * XE * 0
17-APR-2006 16:55:34 * service_update * XE * 0
17-APR-2006 16:55:40 * service_update * XE * 0
17-APR-2006 16:55:42 * service_update * XE * 0
17-APR-2006 16:55:45 * service_died * XE * 12537
17-APR-2006 16:55:59 * service_register * XE * 0
17-APR-2006 16:56:34 * service_update * XE * 0
17-APR-2006 16:58:14 * service_update * XE * 0
17-APR-2006 16:58:32 * service_update * XE * 0
17-APR-2006 16:58:36 * service_update * XE * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))
(Presentation=HTTP)(Session=RAW))
17-APR-2006 16:58:39 * service_update * XE * 0
17-APR-2006 16:59:03 * service_update * XE * 0
17-APR-2006 16:59:12 * service_update * XE * 0
17-APR-2006 16:59:15 * service_update * XE * 0
17-APR-2006 16:59:25 * service_update * XE * 0
17-APR-2006 16:59:28 * service_update * XE * 0
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)
(PORT=8080))(Presentation=HTTP)(Session=RAW))
17-APR-2006 16:59:37 * service_update * XE * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-server.example.com)
(PORT=8080))(Presentation=HTTP)(Session=RAW))
17-APR-2006 16:59:37 * service_update * XE * 0
17-APR-2006 16:59:40 * service_update * XE * 0
17-APR-2006 16:59:41 * service_update * XE * 0
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=db-server.example.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
17-APR-2006 16:59:45 * service_died * XE * 12537
17-APR-2006 16:59:49 * service_register * XE * 0
17-APR-2006 16:59:50 * service_update * XE * 0
17-APR-2006 16:59:55 * service_update * XE * 0
17-APR-2006 16:59:58 * service_update * XE * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))
(Presentation=HTTP)(Session=RAW))
17-APR-2006 17:00:01 * service_update * XE * 0
17-APR-2006 17:00:10 * service_update * XE * 0
17-APR-2006 17:00:28 * service_update * XE * 0
17-APR-2006 17:01:01 * service_update * XE * 0
17-APR-2006 17:03:46 * service_update * XE * 0

Note

It’s worth pointing out that the first time I did this step everything on the terminal said it completed successfully, but the http server hadn’t started on port 8080. Checking the log file I discovered:

Error listening on: (Description=(Address=(Protocol=tcp)(Port=8080)(Host=localhost))
   (Presentation=HTTP)(Session=RAW))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist

Checking the wonderfully useful http://ora-12545.ora-code.com/ I discovered it was a failing DNS lookup. This confused me for a while as I had working DNS, until I realised the host it was trying to look up was localhost and that I had failed to copy across an /etc/hostsfile. Once I copied this into the chroot and restarted the server, everything worked.

Connecting to the server

We can now log on to the web administration pages. If you are not installing Oracle on the local computer, you will need to set up a ssh tunnel as the web administration pages is bound only to localhost and therefore unavailable from any other host,

ssh db-server -L 8080:localhost:8080

You can now visit http://localhost:8080/apex

Let’s now try connecting using sqlplus. It’s probably a good idea to do this as a non-root user, so change to the oracle user. Also, connecting as the oracle user would allow us to connect as sysdba.

 su - oracle

Oracle needs a couple of environment variables configured for the client to work. You need to alter your PATH, too, as the packages do not install them in /usr/bin. Fortunately, the packages include a couple of scripts to configure everything properly. As the client package only includes the sqlplus client and ott (Object Type Translator), when you have the server package installed, you will probably want to use the server package binaries when you have them available.

if [ -e /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh ]; 
then
   source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
elif [ -e /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin/oracle_env.sh ]; 
then
   source /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin/oracle_env.sh
fi

You can put those lines in your ~/.bash_profile and the next time you log in you’ll have your environment configured correctly. To save you logging out and in again you can run source ~/.bash_profile. We’ll start by connecting as the system user using sqlplus / as sysdba.

oracle@db-server:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 17 19:20:41 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>exit

You may be wondering what is stopping you connecting as the sysdba as any other user on the database server. It turns out that connecting as sysdba requires some file permissions, which other users won’t have. If you try you’ll receive the following error:

david@db-server:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 18 19:26:45 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 13: Permission denied
Additional information: 9925
ORA-01031: insufficient privileges

Enter user-name:

Now we know that our database server is up and running and accepting connections, lets try creating a user.

SQL> create user david identified by "password";

User created.

SQL> Disconnected from Oracle Database 10g Express Edition Release
10.2.0.1.0 - Production
oracle@db-server:~$ sqlplus david

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 17 19:20:32 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: password
ERROR:
ORA-01045: user DAVID lacks CREATE SESSION privilege; logon denied

Enter user-name:

What the hell? Okay, so new users don’t have permission to create sessions. That kind of makes them hard to use. What we need to do is to create a user, create a role, grant privileges to the role and then grant that role to the user. We only need to create the role once and then use it on all users.

CREATE USER david IDENTIFIED BY password
       DEFAULT TABLESPACE users  
       TEMPORARY TABLESPACE temp
       QUOTA UNLIMITED ON users;

CREATE ROLE conn;

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure, CREATE synonym
      TO conn;

GRANT conn TO david;

Okay, so now we can reconnect as our new user:

oracle@db-server:~$ sqlplus david

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 18 00:11:10 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: password

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table foo ( bar int);

Table created.

SQL> alter table foo rename column bar to baz;

Table altered.

SQL> drop table foo;

Table dropped.

Installing the client

Install oracle-xe-client and try connecting to the database. We need to connect using username/password@SID. As we are using XE, we can only have one instance and it’s called XE.

david@db-client:~$ sqlplus david/password@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 23 10:11:10 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

The reason why this failed is because the client doesn’t know where to find the XE instance. We need to tell the Oracle client how to map the Oracle SID to the database server. We do this by writing a /etc/tnsnames.ora.

XE =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = db-server.example.com)(PORT = 1521))
      )
      (CONNECT_DATA =
         (SERVICE_NAME = xe)
      )
   )

The first line is the name of our Oracle SID. We define the address of our database server, by setting the HOST option. You can leave the port assuming you didn’t change it when you set up the server. The other part you’ll need to change is the SERVICE_NAME value, which should be the name of the database on the server. The default is XE.

This time when we connect we get an SQL prompt.

david@db-client:~$ sqlplus david/password@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 23 14:06:55 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

If we look on the server, in the listener log, we will see:

23-APR-2006 17:21:06 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=
sqlplus@db-client)(HOST=db-client)(USER=david))) * (ADDRESS=(PROTOCOL=tcp)
(HOST=192.168.0.1)(PORT=42792)) * * establish * XE * 0

Policy bugs in Oracle’s packages

I’ve noticed several things in the Debian packaging of Oracle that don’t comply with parts of Debian Policy that you probably should be aware of.

Packages fail to depend on bc
Package maintainer scripts modified conffiles, namely /etc/sysctl.conf.
Log files are not in /var/log
Binary files are not in /usr/bin
The manpage for sqlplus is called client-sqlplus.

In addition, while they aren’t policy bugs, the packages ship with their own copy of several binaries like zip and unzip. Also, the client package doesn’t include some Oracle programs that might have been useful like tnsping, which is only available in the server package.