Oracle Database XE Configuration

From TierSeven

Jump to: navigation, search

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).

Contents

Pre-Installation

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.

Installation

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 /etc/apt/sources.list:

## The repository for Oracle products
deb http://oss.oracle.com/debian unstable main non-free

Then do:

$ 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.

Troubleshooting

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.

One can 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 su to oracle.

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 sqlplus:

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

The 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:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/stopdb.sh
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/startdb.sh


ORA-12520: TNS:listener could not find available handler for requested type of server

See #ORA-12519: TNS:no appropriate service handler found

TNS-12535 TNS:operation timed out

The following error appeared in $ORACLE_HOME/network/log/sqlnet.log:

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 $ORACLE_HOME/network/admin/listener.ora:

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
Personal tools