Oracle Database XE Configuration
Oracle Database Express Edition (XE) is a free, entry-level, version of Oracle Database and is available for GNU/Linux and Windows. This page exists to summarise the installation and configuration of XE on Ubuntu Linux (8.04).
Although XE is Oracle's small-footprint offering, it is by no means a light-weight product when compared to MySQL, PostgreSQL, Apache Derby, and even Microsoft's SQL Server. If the machine on which XE is being installed as less than 1GB RAM, it will need at least 1GB of swap. Where memory is scarce, but disk space plentiful (as in a virtual environment on a desktop workstation) 2GB swap may be advisable.
Kudos to Oracle for stream-lining the installation process; on Debian-based GNU/Linux systems XE is available as a .deb. To make the package eligable for installation, add the following to
## The repository for Oracle products deb http://oss.oracle.com/debian unstable main non-free
$ sudo apt-get update $ sudo apt-get install oracle-xe
Once installed, XE must be configured as root by running
$ sudo /etc/init.d/oracle-xe configure
The default configuration options are sensible, but be sure to use (and remember) a secure password for the XE system user.
Running XE in a memory-constrained environment requires a few tweaks to compensate for the inevitable system load and processing delays. Following are errors that have been encountered, and the solutions that were used to resolve them.
ORA-03136: inbound connection timed out
According to Burleson Consulting's page about this error the 3 typical causes are:
- The server gets a connection request from a malicious client which is not supposed to connect to the database, in which case the error thrown is the correct behavior (retrieve the client address for which the error was thrown via sqlnet log file).
- The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
- The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
In my case the 2nd and third options where the most likely and blog and forum postings suggested changing the listener
INBOUND_CONNECTION_TIMEOUT parameter. The default is 0, which indicates not
UNLIMITED, but default; the default is 30 seconds.
lsnrctl to increase the settings, but it must be run as the
oracle user as only this user as the authority to view and change this parameter. Running it as another user (even one in the
dba group will result in the following error:
marc@oizys:~$ groups denizens adm dialout cdrom floppy audio dip video plugdev fuse lpadmin admin dba marc@oizys:~$ lsnrctl show inbound_connect_timeout LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 28-NOV-2008 09:13:20 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) TNS-01190: The user is not authorized to execute the requested listener command
On Ubuntu, the best way to do this seems to be get an interactive
sudo session, and then to
marc@oizys:~$ sudo -i root@oizys:~# su - oracle oracle@oizys:~$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found oracle@oizys:~$ lsnrctl LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 28-NOV-2008 08:58:16 Copyright (c) 1991, 2005, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> show inbound_connect_timeout Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) LISTENER parameter "inbound_connect_timeout" set to 0 The command completed successfully LSNRCTL> set inbound_connect_timeout 300 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) LISTENER parameter "inbound_connect_timeout" set to 300 The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) Saved LISTENER configuration parameters. Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora Old Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.bak The command completed successfully LSNRCTL> reload Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) The command completed successfully LSNRCTL> show inbound_connect_timeout Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) LISTENER parameter "inbound_connect_timeout" set to 300 The command completed successfully LSNRCTL> quit
ORA-12519: TNS:no appropriate service handler found
This problem first manifested when starting up and application that opens up a large number of connections to the database. After confirming that both both the XE instance and the listener are up (using
lsnrctl status), and that it was possible to connect using
sqlplus, some Googling identified the problem as one that can occur if the number of processes that XE will allow for handling incoming requests is exceeded. The installation default is 40, which seems to allow for up to 20 concurrent connections. The current value can be checked using the view
v$parameter; for example, using
SQL> select NAME, VALUE from V$PARAMETER where NAME='processes'; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- processes 40
Change this to suit your needs using an
ALTER statement of the form:
alter system set PROCESSES=150 SCOPE=spfile
SCOPE=spfile ensures that the change is persisted by writing it to the XE configuration file. Restart the instance to bring the change into effect:
ORA-12520: TNS:listener could not find available handler for requested type of server
TNS-12535 TNS:operation timed out
The following error appeared in
Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 10.2.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production Time: 10-DEC-2008 07:28:34 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out ns secondary err code: 12606 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.204.128)(PORT=48593))
It was raised because of the way in which connections are being handled by some Java code: multiple connections are requested at the start of a potentially long running operation, and may have been "timed-out" by the time that they are actually required. This manifested as an exception being raised in Java:
java.sql.SQLException: Io exception: Connection reset at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:494) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:465) at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:275) at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:206) at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:156)
The correct solution is to fix the Java code to only request a connect when it is actually required (there's no real performance implication in this specific application), but it was educational to figure out how to configure XE so as to avoid running into this.
Add the following to
CONNECT_TIMEOUT = 0
And add the following to
$ORACLE_HOME/network/admin/sqlnet.ora (for XE you may need to create this file):
SQLNET.EXPIRE_TIME = 0 SQLNET.INBOUND_CONNECT_TIMEOUT = 0 SQLNET.RECV_TIMEOUT = 0 SQLNET.SEND_TIMEOUT = 0
After these changes have been made it may be sufficient to bounce the listener:
$ lsnrctl reload
but it is probably best to stop and stop the database:
$ lsnrctl stop $ $ORACLE_HOME/config/scripts/stopdb.sh $ $ORACLE_HOME/config/scripts/startdb.sh $ lsnrctl start