Version 13 (modified by Ramiro Morales, 13 years ago) ( diff )

--

==============================
Preparing an Oracle test setup
==============================

**This document is work in progress**

This document attempt to ease the task of running the Django (or your own app's) test
suite against Oracle by:

* Providing a step by step setup guide to achieve that.
* Hopefully collecting information (best practices, tuning tips) to do that
  as efficiently as possible.

Contributions from seasoned Oracle users are welcome!.

Chosen components are:

* Oracle XE (10g)
* GNU/Debian Linux x86. Things should also work with Ubuntu Linux without too much tweaking.

The system isn't going to be dedicated exclusively to run Oracle so we will
leave things set up so it is necessary to start it manually before a test-debug
session.

Obtain and install Oracle XE
============================

We will be roughly following the `Oracle installation documentation`_.

#. Download the **oracle-xe-universal_10.2.0.1-1.0_i386.deb**  package file from
   http://www.oracle.com/technetwork/database/express-edition/downloads/index.html
   (*Oracle Database 10g Express Edition for Linux x86*). You need to have an
   *Oracle Developer Network* account and to accept the licence agreement.

   Contrarily to what is suggested in some places_ I've found the newer **oracle-xe-universal_10.2.0.1-1.1_i386.deb** package
   you can get here_ (as of Nov 2010) and installable by using high level Debian/Ubuntu package management
   tools (APT, Aptitude, ...) didn't install things like the ``/etc/init.d/oracle-xe`` init
   script and some files under the ``/usr/lib/oracle`` hierarchy so I went with the older
   package plus the low-level `dpkg` package manager and taking care of dependencies manually
   (see next step).

#. Install the prerequisite packages, if you fail to do so the installation won't be successful
   but won't abort either::

       $ sudo apt-get install bc libaio1

#. Make sure you have enough swap space. If you fail to do this, the Oracle
   package installation will abort with an error message about this unmet
   condition.

   ================ ======================
   **RAM [MiB]**    **Swap size required**
   ================ ======================
   0 > RAM >= 256                  3 * RAM
   256 > RAM > 512                 2 * RAM
   RAM >= 512                        1 GiB
   ================ ======================

#. Install the package you downloaded::

       $ sudo dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb

#. Configure the DB engine::

       $ sudo /etc/init.d/oracle-xe configure

   It will ask you a number of questions, namely:

   * A TCP port for the Oracle Database XE graphical user interface (default: 8080)
   * A TCP port for the Oracle database listener (default: 1521)
   * A password for the SYS and SYSTEM administrative user accounts. Take note of the value you choose.
   * Whether you want the database to start automatically when the computer starts -- I chose **NO** here, see next step.

#. **Optional** -- Create an alternate init script -- If you've answered NO to the question
   about running the Oracle DB engine automatically on system start then
   it won't be possible to start it manually because that flag is stored
   (among others) in the ``/etc/default/oracle-xe`` configuration file and we
   would be using the same script as the one executed when the system boots
   (the SyV ``/etc/init.d/oracle-xe`` script) that always examines these values.

   What we can do is to create a slightly modified ``/etc/init.d/xe`` script
   that ignores that flag and allows us to control the Oracle process at will::

       $ cd /etc/init.d
       $ sudo cp -a oracle-xe xe
       $ sudo patch < /home/myuser/oracle-xe-script.diff

   This is the ``oracle-xe-script.diff`` patch file::

    --- xe   2006-02-24 17:23:15.000000000 -0300
    +++ xe.new  2010-11-03 07:58:43.000000000 -0300
    @@ -596,13 +596,8 @@
     # See how we were called
     case "$1" in
       start)
    -       if test -f "$CONFIGURATION"
    +       if test ! -f "$CONFIGURATION"
            then
    -               if test "$ORACLE_DBENABLED" != "true"
    -               then
    -                       exit 0
    -               fi
    -       else
            echo "Oracle Database 10g Express Edition is not configured.  You must run
    '/etc/init.d/oracle-xe configure' as the root user to configure the database."
                    exit 0
    @@ -613,13 +608,8 @@
            configure
            ;;
       stop)
    -       if test -f "$CONFIGURATION"
    +       if test ! -f "$CONFIGURATION"
            then
    -               if test "$ORACLE_DBENABLED" != "true"
    -               then
    -                       exit 0
    -               fi
    -       else
            echo "Oracle Database 10g Express Edition is not configured.  You must run
    '/etc/init.d/oracle-xe configure' as the root user to configure the database."
                    exit 0

   Now we can start/stop Oracle manually using it::

       $ sudo /etc/init.d/xe start
       Starting Oracle Net Listener.
       Starting Oracle Database 10g Express Edition Instance.

       $ sudo /etc/init.d/xe stop
       Shutting down Oracle Database 10g Express Edition Instance.
       Stopping Oracle Net Listener.

   (in newer versions of Debian/Ubuntu we can use the shorter version
   ``sudo service xe start``)


#. Make sure the Oracle environment vars needed by its client libraries are set. You need to do this in all systems
   where you will be running client apps (e.g. the django test runner, the Oracle ''sqlplus'' CLI admin tool, etc.):
   the system where the DB engine will run and/or other systems connected to it through the network (see next item).

   I use the Bash shell, see the Oracle docs for instructions for other shells::

    $ echo "source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh" >> ~/.bashrc
    $ source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

#. Decide if you will perform the following two tasks:

   * Access the administrative web app
   * Run the Django tests

   from the same system where the DB engine is running or from another system through the network. If you chose the
   first option you can skip to the next step, if you chose the second option for any of the two kinds of access then you
   need to solve the following two issues first (see the `Oracle installation documentation`_ for
   detailed instructions):

   * You will need to install the Oracle client stack on your remote system.
   * By default no access of any type (SQL sessions, admin web app) is allowed through the network to the DB engine, you need to change that by using the administrative web interface or possibly using the `sqlplus` tool.

#. Access the DB engine administration web app by pointing your Web browser to ``http://localhost:8080/apex`` (or ``http://server:8080/apex``)
   and using the ``SYSTEM`` user plus the password you chose above.

#. Create an user to be used to connect to the DB when running the tests. (e.g. ``djangotest``)
   -- Go to *Home > Administration > Database Users > CREATE*, assign it a password (e.g. ``tehsekret``)

#. Give the user the needed privileges.

   * Roles: ``CONNECT``, ``RESOURCE`` and ``DBA``

.. _Oracle installation documentation: http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm
.. _places: http://blog.schmehl.info/Debian/oracle-xe
.. _here: http://oss.oracle.com/debian/dists/unstable/non-free/binary-i386/

Install cx_Oracle
=================
Do this in the system where you will run your tests::

    $ sudo apt-get install python-dev

::

    $ sudo pip install cx_Oracle

or::

    $ sudo easy_install cx_Oracle

etc.

Create the Django settings file
===============================
::

    $ cat oracle_settings.py

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.oracle',
            'NAME': 'xe',
            'USER': 'djangotest',
            'PASSWORD': 'tehsekret',
            'TEST_USER': 'django_test_default',
            'TEST_TBLSPACE': 'django_test_default',
            'TEST_TBLSPACE_TMP': 'django_test_default_temp',
        },

        'other': {
            'ENGINE': 'django.db.backends.oracle',
            'NAME': 'xe',
            'USER': 'djangotest',
            'PASSWORD': 'tehsekret',
            'TEST_USER': 'django_test_other',
            'TEST_TBLSPACE': 'django_test_other',
            'TEST_TBLSPACE_TMP': 'django_test_other_temp',
        },
    }

The key is that ``NAME`` should be the same (``'xe'``) for both entries, since you're really establishing two connections to the same database (see *Understanding the database test setup* below for details about this). The ``TEST_USER``, ``TEST_TBLSPACE``, and ``TEST_TBLSPACE_TMP`` entries must be different, however.

Thanks Ian Kelly for providing the correct settings file.

Test things
===========
::

    $ sudo /etc/init.d/xe start

    $ ./runtests.py --settings=oracle_settings.py basic

    Creating test database 'default'...
    Creating test user...
    Creating test database 'other'...
    Creating test user...
    .......s...
    ----------------------------------------------------------------------
    Ran 11 tests in 2.279s

    OK (skipped=1)
    Destroying test database 'default'...
    Destroying test user...
    Destroying test database tables...
    Destroying test database 'other'...
    Destroying test user...
    Destroying test database tables...

Understanding the database test setup
=====================================

An Oracle instance allows the existence of only one database. This is somewhat different from what we could be accustomed from working with other RDBMS. For more details see the `relevant Oracle documentation`_.

*Oracle XE* allows the existence of only one database instance (named ``'xe'``). So, when the Django ORM testing code needs to test multi-DB functionality added in version 1.2, the Django Oracle backend implements that by creating what is know as *tablespaces*. The overall scheme of things is like this:

For every DB alias defined in ``DATABASES``:

* The ``'NAME'`` variable value specifies the name of the DB we ask the Oracle engine for ('xe' in the case of XE). AFAIK This can be further controlled by using the ``tnsnames`` infrastructure.
* Both a data tablespace and a temporary data tablespace are created.
* The names of such tablespaces are derived from the value of the ``NAME`` var (the data tablespace gets a name prefixed by ``'test_'`` and the temporary tablespace one prefixed by ``'test_'`` and suffixed by ``'_tmp'``.
* But their names can also be overridden by the ``'TEST_TBLSPACE'`` and ``'TEST_TBLSPACE_TMP'`` vars, respectively.
* For these tablespaces, a temporary user is created beforehand that has full ownership and control over them.
* The username of such user isn't the same as the ``'USER'`` var, is derived from it by adding a ``'test_'`` prefix. It can be overridden with the value of the ``'TEST_USER'`` var.
* The password assigned to that user is hardcoded, but can be overridden by the value of the ``'TEST_PASSWD'`` var.
* The tablespaces and the user are removed when test execution finishes.
* Some variables are provided to control parts of this process:

  * ``'TEST_CREATE'`` -- Skips creation and deletion of the tablespaces.
  * ``'TEST_USER_CREATE'`` -- Skip creation and deletion of the temporary user.

In summary, Oracle has the following extra ``DATABASES`` vars when compared with other Django DB backends: ``'TEST_TBLSPACE'``, ``'TEST_TBLSPACE_TMP'``, ``'TEST_USER'``, ``'TEST_PASSWD'``, ``'TEST_CREATE'`` and ``'TEST_USER_CREATE'``.

.. _relevant Oracle documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/intro.htm#i60798

Notes
=====

In my particular case I implemented this setup by using a KVM virtual machine 
(host system is a workstation running Debian unstable *Sid*). The VM got:

* One CPU (Oracle XE won't use any additional CPU).
* 512 MiB of RAM (initially it was 1GiB but the Django test suite execution doesn't push memory usage above that at all).
* 30 GiB hard disk.

Platform is GNU/Debian Linux 5.0 aka *Lenny* (stable as of Nov 2010) because it still is in
its support period. As a bonus contains Python 2.4 in pre-packaged form.

Things to review:

* How much does the Oracle installation pollutes the system?. If it result to be confined and easy to undo/cleanup, maybe this setup doesn't need to be done inside a VM.

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.
Back to Top