[ZODB-Dev] RelStorage on Oracle RAC (doing now)

[Darryl Dixon - Winterhouse Consulting]

Folks,

We have a client with an existing Oracle RAC infrastructure (4 node RAC).
We are going to using RelStorage + cx_Oracle to connect to this from
RedHat Linux servers, running Plone 3.3.x. This note is to say "we're
here" and to provide some technical notes on our experience for the
benefit of other implementers.

First, we have created a buildout recipe which knows how to install the
necessary oracle instantclient libraries as instructed by the cx_Oracle
package - hopefully we will pypi-ify this recipe shortly. It sets things
up such that the cx_Oracle egg can then be built and installed by buildout
simply by specifying eggs = cx_Oracle.

Second, the recipe exports the necessary values for ORACLE_HOME and
LD_LIBRARY_PATH in its options so that other buildout parts (eg,
[instance]) can take these and put them in to zope.conf's <environment>
section.

Third, it accepts tnsnames entries and puts them in to an appropriate,
instance-specific tnsnames.ora file ready for Zope to find and use.

>From that point, all that remains is for the user to specify the usual
RelStorage zope.conf options (%import relstorage, etc) and use the
<oracle> tag with user/password/dsn.

This works. We are going to be testing RAC failover etc shortly to gauge
the failure characteristics of the client connector library. Our
expectation is that it should probably be seamless.

One (aesthetic?) problem is that the string for the relstorage_util
package is hard-coded to SYS; eg, in
relstorage.adapters.locker.OracleLocker.hold_commit_lock is the string
"sys.relstorage_util.request_lock". Our DBA finds this distasteful and
would prefer to keep the relstorage_util package in the Zope schema that
he has created to partition this stuff off. This all works fine, except
that we must currently manually hotfix locker.py to remove 'sys.' from the
front of that string. Options for handling this differently might be:
1) Hardcode the string as "relstorage_util.request_lock" and advise
users/provide the necessary SQL to add a synonym into their schema for
sys.relstorage_util
2) Make this a configurable parameter inside the <oracle> zope.conf tags
that would override the default string of 'sys.relstorage_util'; along the
lines of 'utilpackage relstorage_util' or 'utilpackage
sys.relstorage_util'
3) ??? Better database/oracle people than me can comment here :)


Aside from that everything has been relatively smooth. We are going to be
running this up on both 32bit and 64bit Linux clients. It is a shame that
the Oracle client libraries downloads are hidden behind both a
login/registration mechanism and a hokey javascript 'Accept this licence'
scheme, which prevents them from being automatically fetched and installed
a-la-buildout. We are keeping the necessary copies on our internal
buildout index server to make deployment to our various environments
smooth here.

We were surprised to discover that with cx_Oracle and the
oracle-instantclient libraries, that connection passwords must be supplied
in lower case in zope.conf, regardless of whether upper case was used when
specifying the password initially.

I will keep the community posted on some basic performance metrics and
failover tests as we perform them.

Footnote - the SQL the we used to initialise the zope user and create the
relstorage_util package is:
------------------8<------------------[cut]
As SYS execute:

CREATE USER zope IDENTIFIED BY XXXXXXXX
DEFAULT TABLESPACE XXtablespaceXX
TEMPORARY TABLESPACE sys_temp
QUOTA UNLIMITED ON XXtablespaceXX
QUOTA UNLIMITED ON XXindexXX

GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO zope;

GRANT EXECUTE ON DBMS_LOCK TO zope;


As ZOPE execute:

CREATE OR REPLACE PACKAGE relstorage_util AS
    FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
        RETURN NUMBER;
END relstorage_util;
/

CREATE OR REPLACE PACKAGE BODY relstorage_util AS
    FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        RETURN DBMS_LOCK.REQUEST(
            id => id,
            lockmode => DBMS_LOCK.X_MODE,
            timeout => timeout,
            release_on_commit => TRUE);
    END request_lock;
END relstorage_util;
------------------8<------------------[cut]


many regards,
Darryl Dixon
Winterhouse Consulting Ltd
http://www.winterhouseconsulting.com