Python 64bit, Oracle instantclient, and cx_Oracle on Mac

Since I had a considerable amount of ‘fun’ figuring this out, I thought I’d share.  I do some operational graphing (mount size over time, backup duration over time) in a bottle/mako/bootstrap/highcharts based portal, and develop locally before pushing it out with git.  Locally I use virtualenv with virtualenvwrapper (highly recommended). Connecting to Oracle from python is relatively simple using cx_Oracle, however if you’re still using the 32bit InstantClient and you’re using 64bit python, you won’t be able to use cx_Oracle.  The easy answer is to update your instantclient install to the 64bit version that came out January 2013 (huge thread by folks who were waiting for it).  There is a workaround (namely, using lipo to remove the 64bit version from the python-universal you’ll find in your virtualenv’s python binaries folder), but that way is far more painful.  Most of the info out there in various forums still tells you that using 64bit python and cx_Oracle is impossible, but with the latest instantclient that is no longer the case.

64bit client instructions:

  1. Download 11.2.0.3 64bit instantclient.  You need BASIC + sdk.  NOTE: Do not get the lite instantclient, or you won’t be able to connect to databases using certain character sets.  It will fail with a really descriptive error (not) … “cx_Oracle.DatabaseError: ORA-28547: connection to server failed, probable Oracle Net admin error.”  Sqlplus from your machine will fail the same way.
  2. Put it in a dir (as root). I put mine here:
    /usr/local/lib/instantclient64-11203
  3. Set paths:
    export ORACLE_HOME=/usr/local/lib/instantclient64-11203
    export DYLD_LIBRARY_PATH=/usr/local/lib/instantclient64-11203

    … and add $ORACLE_HOME to your path.

  4. Create these two softlinks in the instantclient dir:
    lrwxr-xr-x root ... lib -> /usr/local/lib/instantclient64-11203
    lrwxr-xr-x root ... libclntsh.dylib -> libclntsh.dylib.11.1
  5. Now you should be able to install cx_Oracle.  Make sure you’re in the correct virtualenv (if you have virtualenvwrapper, type “workon” and the name of your virtualenv).  Then check whether you’re using 32 or 64bit python right now:
    python -c 'import struct;print( 8 * struct.calcsize("P"))'

    64   <– shows we’re using 64bit python

  6. Do pip uninstall cx_Oracle first if you had installed previously when in 32bit mode … or just to be sure.  Then:
    pip install cx_Oracle
  7. Test:
    import cx_Oracle
    try:
     con = cx_Oracle.connect('username/blah@dvcadb09.capella.edu/NOTHERE')
    finally:
     try:
       con.close()
     except:
       pass
  8.  Good error (it got to the listener):
    (uops-dbas-sysportal)uops-dbas-sysportal> python test.pyTraceback (most recent call last):File "test.py", line 2, in <module>
    con = cx_Oracle.connect('username/blah@myserver/NOTHERE')
    cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  9. If you plan to use tnsnames.ora make sure to also add:
    export TNS_ADMIN=/my/path/to/tnsnames.ora

If you can’t make this work, you can run everything in 32bit mode; many people have explained that process well, try this: using lipo to remove the 64bit version from the python binary.  Here’s roughly what it looks like:

(uops-dbas-sysportal)bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
64
(uops-dbas-sysportal)bin> echo $WORKON_HOME
/Users/username/.virtualenvs
(uops-dbas-sysportal)bin> pwd
/Users/username/.virtualenvs/uops-dbas-sysportal/bin
(uops-dbas-sysportal)bin> pwd
/Users/username/.virtualenvs/uops-dbas-sysportal/bin
(uops-dbas-sysportal)bin> file python
python-universal: Mach-O universal binary with 2 architectures
python-universal (for architecture x86_64):Mach-O 64-bit executable x86_64
python-universal (for architecture i386):Mach-O executable i386
(uops-dbas-sysportal)bin> cp python python-universal
(uops-dbas-sysportal)bin> lipo python-universal -thin i386 -output python
(uops-dbas-sysportal)bin> file python
python: Mach-O executable i386
(uops-dbas-sysportal)bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
32
(uops-dbas-sysportal)bin> deactivate
bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
64
Advertisements

My Oracle Support article

SearchOracle.com has a new article up on the ongoing issues with My Oracle Support, the replacement for Metalink.  I know I have spent 30+ minute writing up a ticket only to lose it when one out of many Draft saves failed, as well as gotten stuck in “you haven’t filled out required fields” loops that were impossible to exit.  I did try to submit a ticket on one of my issues, when there were clearly error messages from a botched site fix or upgrade showing on my page, but despite the errors clearly showing, they kept telling me to try new browsers and the like so I eventually gave up.  So their attempts to do technical support on their support site has also been spotty.  Now I just avoid submitting tickets from Firefox on my Mac now; IE8 on PC seems to be more stable.

I’m all for forward progress, but I’m still bemused by the architectural choice to make the site Flash based.  The basic actions of a support site are simple – find articles, submit and track tickets.  These activities can be enhanced by Flash but by no means require it, and the KISS principle is always best with web sites.  Oracle of course wants to create a support portal that does more than the basics, and acts more like a heads-up display, receiving info from your systems, etc etc.  But user uptake of that sort of feature was misjudged or worse, paternalistically viewed by the design team.  A support portal is a bad place to get fancy, because 80% of the time when I hit that site I’m already stressed, possibly panicked, and every second may count.  Adding features that I have to wait for to load or that make it harder to do the basics quickly and in a stable fashion is a bad idea.

Overall, Oracle seems to have lost sight of what a support portal is for … any site overhaul should start with the question, “What are our key services and how can we maintain and enhance them without degrading their user experience?”  That should be your guiding principle.  Then start tacking on new stuff.