Changes between Version 23 and Version 24 of OracleTestSetup


Ignore:
Timestamp:
May 7, 2015, 12:02:39 PM (9 years ago)
Author:
Tim Graham
Comment:

Removed outdated Oracle instructions

Legend:

Unmodified
Added
Removed
Modified

  • OracleTestSetup

    v23 v24  
    55==============================
    66
    7 This document attempts to ease the task of running Django's (or your own app's) test suite against Oracle by:
    8 
    9 * Providing a step by step setup guide to achieve that.
    10 * Hopefully collecting information (best practices, tuning tips) to do that as efficiently as possible.
    11 
    12 Contributions from seasoned Oracle users are welcome!
    13 
    14 Chosen components are:
    15 
    16 * Oracle XE. This is a free version of Oracle targeted at developers.
    17 * Debian GNU/Linux or Ubuntu.
    18 
    19 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.
    20 
    21 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.
    22 
    23 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.
    24 
    25 How to install Oracle XE 10g
    26 ============================
    27 
    28 As explained in the introduction, this will only work on a 32 bit distro.
    29 
    30 We will be roughly following the `Oracle installation documentation`_.
    31 
    32 #. Download the **oracle-xe-universal_10.2.0.1-1.0_i386.deb**  package file from
    33    http://www.oracle.com/technetwork/database/express-edition/downloads/index.html
    34    (*Oracle Database 10g Express Edition for Linux x86*). You need to have an
    35    *Oracle Developer Network* account and to accept the license agreement.
    36 
    37    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
    38    you can get here_ (as of Nov 2010) and install with high level Debian/Ubuntu package management
    39    tools (APT, Aptitude, ...) didn't install things like the ``/etc/init.d/oracle-xe`` init
    40    script and some files under the ``/usr/lib/oracle`` hierarchy. So I went with the older
    41    package plus the low-level `dpkg` package manager and took care of dependencies manually
    42    (see next step).
    43 
    44 #. Install the prerequisite packages. If you don't do this, the Oracle package will attempt
    45    to install anyway, will fail, and you will end up with a half-installed package::
    46 
    47        $ sudo apt-get install bc libaio1
    48 
    49 #. Make sure you have enough swap space. If you fail to do this, the Oracle
    50    package installation will abort with an error message about this unmet
    51    condition.
    52 
    53    ================ ======================
    54    **RAM [MiB]**    **Swap size required**
    55    ================ ======================
    56    0 > RAM >= 256                  3 * RAM
    57    256 > RAM > 512                 2 * RAM
    58    RAM >= 512                        1 GiB
    59    ================ ======================
    60 
    61 #. Install the package you downloaded::
    62 
    63        $ sudo dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb
    64 
    65 #. Configure the DB engine::
    66 
    67        $ sudo /etc/init.d/oracle-xe configure
    68 
    69    It will ask you a number of questions, namely:
    70 
    71    * A TCP port for the Oracle Database XE graphical user interface (default: 8080)
    72    * A TCP port for the Oracle database listener (default: 1521)
    73    * A password for the SYS and SYSTEM administrative user accounts. Take note of the value you choose.
    74    * Whether you want the database to start automatically when the computer starts -- I chose **NO** here, see next step.
    75 
    76 #. **Optional** -- Create an alternate init script -- If you've answered NO to the question
    77    about running the Oracle DB engine automatically on system start then
    78    it won't be possible to start it manually because that flag is stored
    79    (among others) in the ``/etc/default/oracle-xe`` configuration file and we
    80    would be using the same script as the one executed when the system boots
    81    (the ``/etc/init.d/oracle-xe`` script) that always examines these values.
    82 
    83    What we can do is to create a slightly modified ``/etc/init.d/xe`` script
    84    that ignores that flag and allows us to control the Oracle process at will.
    85    Download [[attachment:oracle-xe-script.diff]] then::
    86 
    87        $ cd /etc/init.d
    88        $ sudo cp -a oracle-xe xe
    89        $ sudo patch < /home/myuser/oracle-xe-script.diff
    90 
    91    Now we can start/stop Oracle manually using it::
    92 
    93        $ sudo /etc/init.d/xe start
    94        Starting Oracle Net Listener.
    95        Starting Oracle Database 10g Express Edition Instance.
    96 
    97        $ sudo /etc/init.d/xe stop
    98        Shutting down Oracle Database 10g Express Edition Instance.
    99        Stopping Oracle Net Listener.
    100 
    101    (in newer versions of Debian/Ubuntu we can use the shorter version
    102    ``sudo service xe start``)
    103 
    104 
    105 #. Make sure the Oracle environment vars needed by its client libraries are set. You need to do this in all systems
    106    where you will be running client apps (e.g. the django test runner, the Oracle ''sqlplus'' CLI admin tool, etc.):
    107    the system where the DB engine will run and/or other systems connected to it through the network (see next item).
    108 
    109    I use the Bash shell, see the Oracle docs for instructions for other shells::
    110 
    111     $ echo "source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh" >> ~/.bashrc
    112     $ source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
    113 
    114 #. Decide if you will perform the following two tasks:
    115 
    116    * Access the administrative web app
    117    * Run the Django tests
    118 
    119    from the same system where the DB engine is running or from another system through the network. If you chose the
    120    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
    121    need to solve the following two issues first (see the `Oracle installation documentation`_ for
    122    detailed instructions):
    123 
    124    * You will need to install the Oracle client stack on your remote system.
    125    * 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.
    126 
    127 #. Access the DB engine administration web app by pointing your Web browser to ``http://localhost:8080/apex`` (or ``http://server:8080/apex``)
    128    and using the ``SYSTEM`` user plus the password you chose above.
    129 
    130 #. Create an user to be used to connect to the DB when running the tests. (e.g. ``djangotest``)
    131    -- Go to *Home > Administration > Database Users > CREATE*, assign it a password (e.g. ``tehsekret``)
    132 
    133 #. Give the user the needed privileges.
    134 
    135    * Roles: ``CONNECT``, ``RESOURCE`` and ``DBA``
    136 
    137 .. _Oracle installation documentation: http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm
    138 .. _places: http://blog.schmehl.info/Debian/oracle-xe
    139 .. _here: http://oss.oracle.com/debian/dists/unstable/non-free/binary-i386/
    140 
    141 How to install Oracle XE 11g
    142 ============================
    143 
    144 As explained in the introduction, this will only work on a 64 bit distro.
    145 
    146 This is a simplified version of the installation procedure described at:
    147 http://forums.oracle.com/forums/thread.jspa?threadID=2227554
    148 
    149 The procedure is very similar to Oracle 10g, we focus on the differences, please read the previous section for more information.
    150 
    151 #. Create a Debian / Ubuntu package.
    152 
    153    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``.
    154 
    155    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.
    156 
    157    You need to have an *Oracle Developer Network* account and to accept the license agreement. (free but registration required).
    158 
    159    Run these commands::
    160 
    161     $ sudo apt-get install alien
    162     $ alien --scripts oracle-xe-11.2.0-0.5.x86_64.rpm
    163     $ alien --scripts oracle-instantclient11.2-basiclite-11.2.0.2.0.x86_64.rpm
    164 
    165 
    166 #. Install dependencies
    167 
    168    Run this command::
    169 
    170     $ sudo apt-get install bc libaio1
    171 
    172    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::
    173 
    174 
    175     #!/bin/sh
    176     if [ "$1" = '--add' ]; then
    177         update-rc.d "$2" defaults
    178     elif [ "$1" = '--del' ]; then
    179         update-rc.d -f "$2" remove
    180     fi
    181 
    182    Make it executable::
    183 
    184    $ sudo chmod +x /sbin/chkconfig
    185 
    186    With this hack, there's a harmless warning during installation because the init script isn't tailored for Debian / Ubuntu::
    187 
    188     update-rc.d: warning: /etc/init.d/oracle-xe missing LSB information
    189     update-rc.d: see <http://wiki.debian.org/LSBInitScripts>
    190 
    191 #. Install the package you generated::
    192 
    193     $ sudo dpkg -i oracle-xe_11.2.0-1.5_amd64.deb
    194 
    195 #. Configure the DB engine::
    196 
    197     $ sudo /etc/init.d/oracle-xe configure
    198 
    199 #. Make sure the Oracle environment vars needed by its client libraries are set at least for the Oracle user::
    200 
    201     $ sudo su oracle
    202     $ echo '. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh' >> ~/.bashrc
    203     $ exit
    204 
    205 #. Remove the fake ``chkconfig`` script::
    206 
    207     $ sudo rm /sbin/chkconfig
    208 
    209 #. Test the server::
    210 
    211     $ sudo su oracle
    212     $ sqlplus / as sysdba
    213     SQL> quit
    214     $ exit
    215 
    216    APEX is reachable at http://localhost:8080/ but I can't figure out how to login.
    217    Since we don't need it for the purposes of running Django's test suite, we disable it::
    218 
    219     SQL> EXEC DBMS_XDB.SETHTTPPORT(0);
    220     SQL> COMMIT;
    221 
    222    If we wanted to enable it again::
    223 
    224     SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);
    225     SQL> COMMIT;
    226 
    227 #. Create a user and give the needed privileges::
    228 
    229     $ sudo su oracle
    230     $ sqlplus / as sysdba
    231     SQL> CREATE USER djangotest IDENTIFIED BY djangotest;
    232     SQL> GRANT DBA TO djangotest;
    233     SQL> quit
    234     $ exit
    235 
    236 
    237 #. Install the client libraries
    238 
    239    This is necessary to compile ``cx_Oracle``::
    240 
    241     # dpkg -i oracle-instantclient11.2-basiclite_11.2.0.2.0-2_amd64.deb
    242 
    243    Since the libraries are installed in a non-standard directory, declare it::
    244 
    245     # echo /usr/lib/oracle/11.2/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf
    246     # ldconfig
    247 
    248 
    249 Uninstall Oracle 11g
    250 ====================
    251 
    252 To uninstall the server::
    253 
    254     # /etc/init.d/oracle-xe stop
    255     # dpkg -P oracle-xe
    256 
    257 The ``prerm`` script doesn't work on Debian / Ubuntu; here's how to clean up your system entirely::
    258 
    259     # rm -r /u01
    260     # update-rc.d oracle-xe remove
    261     # rm /etc/default/oracle-xe /etc/oratab /var/lib/update-rc.d/oracle-xe
    262     # rm -r /etc/kde/xdg/menus/OracleXE
    263     # rmdir -p /etc/kde/xdg/menus
    264 
    265 To uninstall the client::
    266 
    267     # dpkg -P oracle-instantclient11.2-basiclite
    268     # rm /etc/ld.so.conf.d/oracle-instantclient.conf
    269     # ldconfig
    270 
    271 
    272 Install cx_Oracle
    273 =================
    274 
    275 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.
    276 
    277 Do this in the system where you will run your tests::
    278 
    279     $ sudo apt-get install python-dev
    280     $ sudo pip install cx_Oracle
    281 
    282 To test the installation (as usual, you need the environment variables)::
    283 
    284     $ python
    285     >>> import cx_Oracle
    286 
    287 Create the Django settings file
    288 ===============================
    289 ::
    290 
    291     $ cat oracle_settings.py
    292 
    293     DATABASES = {
    294         'default': {
    295             'ENGINE': 'django.db.backends.oracle',
    296             'NAME': 'xe',
    297             'USER': 'djangotest',
    298             'PASSWORD': 'tehsekret',
    299             'TEST_USER': 'django_test_default',
    300             'TEST_TBLSPACE': 'django_test_default',
    301             'TEST_TBLSPACE_TMP': 'django_test_default_temp',
    302         },
    303 
    304         'other': {
    305             'ENGINE': 'django.db.backends.oracle',
    306             'NAME': 'xe',
    307             'USER': 'djangotest',
    308             'PASSWORD': 'tehsekret',
    309             'TEST_USER': 'django_test_other',
    310             'TEST_TBLSPACE': 'django_test_other',
    311             'TEST_TBLSPACE_TMP': 'django_test_other_temp',
    312         },
    313     }
    314 
    315 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.
    316 
    317 Thanks Ian Kelly for providing the correct settings file.
    318 
    319 Test things
    320 ===========
    321 ::
    322 
    323     $ sudo /etc/init.d/xe start
    324 
    325     $ ./runtests.py --settings=oracle_settings.py basic
    326 
    327     Creating test database 'default'...
    328     Creating test user...
    329     Creating test database 'other'...
    330     Creating test user...
    331     .......s...
    332     ----------------------------------------------------------------------
    333     Ran 11 tests in 2.279s
    334 
    335     OK (skipped=1)
    336     Destroying test database 'default'...
    337     Destroying test user...
    338     Destroying test database tables...
    339     Destroying test database 'other'...
    340     Destroying test user...
    341     Destroying test database tables...
    342 
    343 If you encounter the following error many times::
    344 
    345     DatabaseError: ORA-12520: TNS:listener could not find available handler for requested type of server
    346 
    347 you may need to increase the value of the `processes` parameter.
    348 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.
    349 To change the value:
    350 
    351 * start a SQL shell with ``sqlplus``
    352 * (optional) check the current value with ``SHOW PARAMETER processes;``
    353 * run ``ALTER SYSTEM SET processes = 100 scope=spfile;``
    354 * exit the SQL shell
    355 * restart the server: ``/etc/init.d/xe restart``.
    356 
    357 Understanding the database test setup
    358 =====================================
    359 
    360 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`_.
    361 
    362 *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:
    363 
    364 For every DB alias defined in ``DATABASES``:
    365 
    366 * 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.
    367 * Both a data tablespace and a temporary data tablespace are created.
    368 * 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.)
    369 * For these tablespaces, a temporary user is created beforehand that has full ownership and control over them.
    370 * 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.
    371 * The password assigned to that user is hardcoded, but can be overridden by the value of the ``'TEST_PASSWD'`` var.
    372 * The tablespaces and the user are removed when test execution finishes.
    373 * Some boolean variables are provided to control parts of this process:
    374 
    375   * ``'TEST_CREATE'`` -- Skips creation and deletion of the tablespaces.
    376   * ``'TEST_USER_CREATE'`` -- Skip creation and deletion of the temporary user.
    377 
    378 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'``.
    379 
    380 .. _relevant Oracle documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/intro.htm#i60798
     7This 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.
    3818
    3829Using the Oracle Developer Day pre-installed VM
Back to Top