{{{ #!text/x-rst ============================== Preparing an Oracle test setup ============================== This document attempts to ease the task of running Django's (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. This is a free version of Oracle targeted at developers. * Debian GNU/Linux or Ubuntu. Oracle provides 32 bit binaries of Oracle 10g and 64 bit binaries of Oracle 11g. If you're using an x86 kernel, you have to install Oracle 10g, and Oracle 11g if you're using an amd64 kernel. There are no other free versions available. The 64 bit version is currently in beta and only available as a RPM packages; this document describes how to convert them to Debian packages. This procedure was tested with Debian 6 i686 + Oracle 10g, and with Ubuntu 11.04 x86_64 + Oracle 11g. There were no difference between Debian and Ubuntu. In case the system isn't going to be dedicated exclusively to run Oracle, we will describe how to set things up so it doesn't start automatically. In this case, it will be necessary to start it manually before a test-debug session. How to install Oracle XE 10g ============================ As explained in the introduction, this will only work on a 32 bit distro. 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 license 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 install with 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 took care of dependencies manually (see next step). #. Install the prerequisite packages. If you don't do this, the Oracle package will attempt to install anyway, will fail, and you will end up with a half-installed package:: $ 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 ``/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. Download [[attachment:oracle-xe-script.diff]] then:: $ cd /etc/init.d $ sudo cp -a oracle-xe xe $ sudo patch < /home/myuser/oracle-xe-script.diff 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/ How to install Oracle XE 11g ============================ As explained in the introduction, this will only work on a 64 bit distro. This is a simplified version of the installation procedure described at: http://forums.oracle.com/forums/thread.jspa?threadID=2227554 The procedure is very similar to Oracle 10g, we focus on the differences, please read the previous section for more information. #. Create a Debian / Ubuntu package. This can be done on another machine, you just need the resulting files, ``oracle-xe_11.2.0-1.5_amd64.deb`` and ``oracle-instantclient11.2-basiclite_11.2.0.2.0-2_amd64.deb``. Download Oracle XE and Oracle InstantClient for Red Hat from Oracle's website. Oracle XE is delivered as a zip file that contains the RPM file we will use. You need to have an *Oracle Developer Network* account and to accept the license agreement. (free but registration required). Run these commands:: $ sudo apt-get install alien $ alien --scripts oracle-xe-11.2.0-0.5.x86_64.rpm $ alien --scripts oracle-instantclient11.2-basiclite-11.2.0.2.0.x86_64.rpm #. Install dependencies Run this command:: $ sudo apt-get install bc libaio1 The RPM contains install scripts that use ``chkconfig`` to add Oracle to the services started at boot. Debian and Ubuntu use ``update-rc.d``. In order to avoid modifying the install scripts, we create a fake ``chkconfig``. Create a file called ``/sbin/chkconfig`` with this content:: #!/bin/sh if [ "$1" = '--add' ]; then update-rc.d "$2" defaults elif [ "$1" = '--del' ]; then update-rc.d -f "$2" remove fi Make it executable:: $ sudo chmod +x /sbin/chkconfig With this hack, there's a harmless warning during installation because the init script isn't tailored for Debian / Ubuntu:: update-rc.d: warning: /etc/init.d/oracle-xe missing LSB information update-rc.d: see #. Install the package you generated:: $ sudo dpkg -i oracle-xe_11.2.0-1.5_amd64.deb #. Configure the DB engine:: $ sudo /etc/init.d/oracle-xe configure #. Make sure the Oracle environment vars needed by its client libraries are set at least for the Oracle user:: $ sudo su oracle $ echo '. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh' >> ~/.bashrc $ exit #. Remove the fake ``chkconfig`` script:: $ sudo rm /sbin/chkconfig #. Test the server:: $ sudo su oracle $ sqlplus / as sysdba SQL> quit $ exit APEX is reachable at http://localhost:8080/ but I can't figure out how to login. Since we don't need it for the purposes of running Django's test suite, we disable it:: SQL> EXEC DBMS_XDB.SETHTTPPORT(0); SQL> COMMIT; If we wanted to enable it again:: SQL> EXEC DBMS_XDB.SETHTTPPORT(8080); SQL> COMMIT; #. Create a user and give the needed privileges:: $ sudo su oracle $ sqlplus / as sysdba SQL> CREATE USER djangotest IDENTIFIED BY djangotest; SQL> GRANT DBA TO djangotest; SQL> quit $ exit #. Install the client libraries This is necessary to compile ``cx_Oracle``:: # dpkg -i oracle-instantclient11.2-basiclite_11.2.0.2.0-2_amd64.deb Since the libraries are installed in a non-standard directory, declare it:: # echo /usr/lib/oracle/11.2/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf # ldconfig Uninstall Oracle 11g ==================== To uninstall the server:: # /etc/init.d/oracle-xe stop # dpkg -P oracle-xe The ``prerm`` script doesn't work on Debian / Ubuntu; here's how to clean up your system entirely:: # rm -r /u01 # update-rc.d oracle-xe remove # rm /etc/default/oracle-xe /etc/oratab /var/lib/update-rc.d/oracle-xe # rm -r /etc/kde/xdg/menus/OracleXE # rmdir -p /etc/kde/xdg/menus To uninstall the client:: # dpkg -P oracle-instantclient11.2-basiclite # rm /etc/ld.so.conf.d/oracle-instantclient.conf # ldconfig Install cx_Oracle ================= Before compiling ``cx_Oracle`` make sure Oracle's environment variables are set (``. path/to/oracle_env.sh``, the path depends on the version you intalled). You need Python's headers too. Do this in the system where you will run your tests:: $ sudo apt-get install python-dev $ sudo pip install cx_Oracle To test the installation (as usual, you need the environment variables):: $ python >>> import cx_Oracle 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... If you encounter the following error many times:: DatabaseError: ORA-12520: TNS:listener could not find available handler for requested type of server you may need to increase the value of the `processes` parameter. By default, it's 40; I've increased it to 100 and this has fixed this error — but I can't guarantee it's the "correct" solution. To change the value: * start a SQL shell with ``sqlplus`` * (optional) check the current value with ``SHOW PARAMETER processes;`` * run ``ALTER SYSTEM SET processes = 100 scope=spfile;`` * exit the SQL shell * restart the server: ``/etc/init.d/xe restart``. 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 to 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 always the same 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, it 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 boolean 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 Using the Oracle Developer Day pre-installed VM =============================================== Oracle provides pre-built virtual machine images with Oracle Database 11g Release 2 Enterprise Edition for developers in form of an OVA archive which can be imported by VirtualBox. The archive can be downloaded `here`__ (or alternately a different one can be chosen from `this list`__) after signing up for an *Oracle Developer Network* account. .. __: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html .. __: http://www.oracle.com/technetwork/community/developer-vm/index.html The server will probably be somewhat slower than if you install it directly on the host machine, but using a VM has a slightly simpler setup, there's a somewhat smaller chance you'll pollute your host system completely and in case something goes wrong, you can always just remove the virtual machine, import a fresh copy and start again. #. Download the OVA file from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and import it into VirtualBox. You'll have to accept an extensive license agreement. #. By default, the VM will be configured to have one NAT network interface. The database server itself starts automatically and listens on port 1521. You'll have to set up port forwarding in VirtualBox in order to access the server. Open the settings of the emulated network adapter and under *Advanced* open *Port Forwarding*. Insert a new rule with host IP set to 127.0.0.1, host port and destination port 1521 and an empty guest IP. #. The server comes with a number of user accounts created beforehand but I only found out how to access two of them. There's ``sys``, which is a "sysdba" account (whatever that means), and ``system``. The password for both of these is ``oracle`` and both have full privileges. Since the ``sys`` account is "sysdba", I'm not sure it can be used to run the tests (it spits out an error message on login attempt) but ``system`` seems to work just fine. As for other credentials on the virtual machine, if you want to login as a regular user, just use ``oracle``/``oracle``, the root password is ``oracle`` as well. However, in order to run the test suite, you just need to boot the machine up, you don't need to start anything manually. #. Install *instantclient* on your host system (the one where you'll be running the test suite). On Gentoo there is an ebuild, which means it is just a matter of ``emerge -av oracle-instantclient-basic`` and fetching the appropriate zip archives from the Oracle download site as instructed. On Debian or Ubuntu you can download the RPM package ("Instant Client Package - Basic"), convert it to a DEB package and install using alien:: $ sudo alien -i oracle-instantclient-basic*.rpm After installing the client, you will need to install the "Instant Client Package - SDK". After downloading, unzip and copy it to the client install directory:: $ unzip instantclient-sdk-linux.x64-*.zip $ sudo cp -R instantclient_11_2/* /usr/lib/oracle/11.2/client64/ #. Start a new shell and install cx_Oracle in your testing virtualenv:: pip install cx_Oracle The new shell is necessary because the *instantclient* package sets various environment variables required to build the adapter. On Debian/Ubuntu, you'll need to set the variables manually:: $ export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ If you get an error installing cx_oracle (``/usr/bin/ld: cannot find -lclntsh``), you may also need to create this link:: $ sudo ln -s /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 /usr/lib/oracle/11.2/client64/lib/libclntsh.so #. Create a settings file, the following example seems to work for me:: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.oracle', 'HOST': '127.0.0.1', 'PORT': '1521', 'NAME': 'orcl', 'USER': 'system', 'PASSWORD': 'oracle', 'TEST_USER': 'django', 'TEST_TBLSPACE': 'django_test', 'TEST_TBLSPACE_TMP': 'django_test_tmp', }, 'other': { 'ENGINE': 'django.db.backends.oracle', 'HOST': '127.0.0.1', 'PORT': '1521', 'NAME': 'orcl', 'USER': 'system', 'PASSWORD': 'oracle', 'TEST_USER': 'other', 'TEST_TBLSPACE': 'other_test', 'TEST_TBLSPACE_TMP': 'other_test_tmp', } } SECRET_KEY = "django_tests_secret_key" As opposed to the XE server whose installation is outlined above, the server provided on the VM image uses ``orcl`` as its SID. #. Profit:: $ # may be need on Debian/Ubuntu $ export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib $ # for error "libaio.so.1: cannot open shared object file" $ sudo apt-get install libaio1 (env)johnny64@equus ~/GSoC/2011/koniiiik-django/tests $ ./runtests.py --settings=test_oracle Creating test database for alias 'default'... Creating test user... Creating test database for alias 'other'... ..............................................................................................................................................................................................................................................................................................................................x...........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................s..s.s....ss.s...ss...........................................................................................................................................................................................................................................................................x...........xx..................s.........................................................................................................................s.........................................................................................................................................................................................x..............................................................................................................................................................................................s..................................................................................ss............s.....................s................s...................................................................................................................................................................................s.......x.xx....................x.............x................................................................................................................................................................................................................................................................s.........s.........................................................sss......s..........................................................................................................................................................................................................................................................................x...............................................................................................sssssss....s..............s......s..........................................................................................................................................s.....................................................................................................................................................s..................................s................s...........................................................................s.............................................................................................x.s..s..................................................................................ss.......................................................................................................s....................................................................................................x.........................................................................................ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss.......................ss..........................................................................................................................................................................................................................................ssssss.......s.........................................................................................................................................................................................................................................................................................................s.......s..........sss..........................................................................................................................................................................................s.............................................................................sss...............................ssss........ss................................................................................................................................................................................................................................................................................................................................ss..........................................................................................................................................................................................................................................................................................................ssssssssssssssssssssssssssssssssssssss............................ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss........................sssssssssssssssssss...................... ---------------------------------------------------------------------- Ran 5826 tests in 1290.532s OK (skipped=360, expected failures=13) Destroying test database for alias 'default'... Destroying test user... Destroying test database tables... Destroying test database for alias 'other'... }}}