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.

Using the Oracle Developer Day pre-installed VM

Oracle provides pre-built virtual machine images with Oracle Database 12c Release 2 Enterprise Edition (12.2.0.1.0) 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.

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.

1. Download the OVA file from https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and import it into VirtualBox (File -> Import Appliance). You'll have to accept an extensive license agreement.

2. By default, the VM will be configured to have one NAT network interface and active port forwarding on port 1521. The database server itself starts automatically and listens on port 1521 (which is the default port for the Oracle listener), therefore you can connect to Oracle server using 127.0.0.1:1521. VM image uses orcl12c as Oracle SID and orcl as pluggable DB.

3. The server comes with more than thirty user accounts created beforehand. Password for all accounts is oracle. Crucial users are sys and system because both of them have "sysdba" privilege (which means full control over DB and privileges to do everything). You can use them to create new test accounts in orcl container.

   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.

4. Install instantclient on your host system (the one where you'll be running the test suite). On Debian or Ubuntu you can download the RPM packages ("Instant Client Package - Basic" and "Instant Client Package - SQL*Plus" for 12.2.0.1.0) from https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html and install using alien:

   $ sudo alien -i oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
   $ sudo alien -i oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
   

   After installing the client, you need to add links to Oracle shared libraries and sqlplus:

   $ sudo -- bash -c 'echo "/usr/lib/oracle/12.2/client64/lib/" > /etc/ld.so.conf.d/oracle.conf'
   $ sudo ldconfig
   $ sudo ln -s /usr/bin/sqlplus64 /usr/bin/sqlplus
   

5. Next install cx_Oracle in your testing virtualenv:

   $ pip install cx_Oracle
   

6. Create main user:

   $ sqlplus sys/oracle@localhost/orcl as sysdba
   SQL> ALTER SESSION SET CONTAINER=orcl;
   SQL> CREATE USER django IDENTIFIED BY django;
   SQL> GRANT DBA TO django;
   SQL> QUIT
   

7. Fill DATABASES in settings file:

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.oracle',
           'NAME': '127.0.0.1:1521/orcl',
           'USER': 'django',
           'PASSWORD': 'django',
           'TEST': {
               'USER': 'default_test',
               'TBLSPACE': 'default_test_tbls',
               'TBLSPACE_TMP': 'default_test_tbls_tmp',
           },
       },
       'other': {
           'ENGINE': 'django.db.backends.oracle',
           'NAME': '127.0.0.1:1521/orcl',
           'USER': 'django',
           'PASSWORD': 'django',
           'TEST': {
               'USER': 'other_test',
               'TBLSPACE': 'other_test_tbls',
               'TBLSPACE_TMP': 'other_test_tbls_tmp',
           },
       },
   }
   

8. Run tests:

   $ ./manage.py test
   Creating test database for alias 'default'...
   Creating test user...
   Creating test database for alias 'other'...
   Creating test user...
   ...
   ----------------------------------------------------------------------
   Ran XXX tests in XXXs
   
   OK
   Destroying test database for alias 'default'...
   Destroying test user...
   Destroying test database tables...
   Destroying test database for alias 'other'...
   Destroying test user...
   Destroying test database tables...
   

Using the Oracle Docker image

Oracle provides a Docker image for its Database via Docker Hub. Once you "purchased" (it's free of charge) the Docker image, you can start the server as followed:

docker run -d -it -p 1521:1521 store/oracle/database-enterprise:12.2.0.1

Next you will need to create the django database user. This requires you to have sqlplus. You simply follow the instructions above to install it.

Connect to the database and execute the following SQL commands:

sqlplus sys/Oradoc_db1@localhost/ORCLCDB.localdomain as sysdba
SQL> ALTER SESSION SET CONTAINER=ORCLPDB1;
SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;
SQL> CREATE USER django IDENTIFIED BY django;
SQL> GRANT DBA TO django;
SQL> QUIT

Next update your DATABASE setting as followed:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'localhost/ORCLPDB1.localdomain',
        'USER': 'django',
        'PASSWORD': 'django',
        'TEST': {
            'USER': 'default_test',
            'TBLSPACE': 'default_test_tbls',
            'TBLSPACE_TMP': 'default_test_tbls_tmp',
        },
    },
    'other': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'localhost/ORCLPDB1.localdomain',
        'USER': 'django',
        'PASSWORD': 'django',
        'TEST': {
            'USER': 'other_test',
            'TBLSPACE': 'other_test_tbls',
            'TBLSPACE_TMP': 'other_test_tbls_tmp',
        },
    },
}