Monday Jan 19, 2015

The Easiest Way to Enable Oracle Database Application Development on Mac OS X

tl;dr Your OS X applications can connect to Oracle Database as if it was running natively on OS X if you simply run Oracle Database in a VirtualBox VM with port forwarding enabled (easy).

To work backwards through the installation process: in the Network window of the VirtualBox GUI, I enable a NAT Network adapter.

Then, under Networking -> Advanced -> Port Forwarding, I create a TCP rule with Host IP 127.0.0.1 and both the Host and Guest Port fields set to the port number that the Oracle network listener in the VM is using: the Oracle default is 1521. I leave the Guest IP field blank.

I click OK twice, dismissing the "The current port forwarding rules are not valid. None of the host or guest port values may be set to zero." dialog

Start the VM. If the DB is set to start during OS boot, you don't even need to log in.

Update: If you have Oracle Database 12c you can use the new EM Express console for DB management and monitoring. Just enable port forwarding for port 5500 and then browse (from OS X) to https://localhost:5500/em. See this article by Gerald Venzl.

The Client

Connecting to the database from OS X tools and clients uses the same forms of Oracle connect string as if the database was native on OS X. For example, using the "Easy Connect" hostname/service_name form, I can simply use 'localhost' as if the database was on OS X:

cjones@mac:~$ sqlplus cj/welcome@localhost/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 19 09:20:38 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

Here I was using SQL*Plus from the free, easy to install Instant Client bundle. Download the Oracle Instant Client Basic, SDK and SQL*Plus ZIP files. Unzip them, create the two symbolic links given in the install instructions, and set DYLD_LIBRARY_PATH to the Instant Client directory.

The Instant Client can be used, among other things, for building PHP, Python, Node.js and other language drivers. With each of these you would use exactly the same connect string to connect to the database.

Prebuilt VMs

There are prebuilt VMs with Oracle Database already installed, such as the Database App Development VM. Download and import it into VirtualBox.

The Database

It's easy to install your own Oracle Database "XE" Express Edition database for development. After the XE RPM is installed on Oracle Linux 5 and 6, run its simple configuration script to set up the administration passwords, the listener port (default is 1521), and whether to autostart during boot. Install XE by following the ten steps in Chapter 4 of The Underground PHP and Oracle Manual or check the official instructions.

Alternately you could install the Enterprise Edition for free ("only for the purpose of developing, testing, prototyping, and demonstrating" - read the click-through license). There are various scripts on the web to autostart at machine boot.

The OS

If you need an OS, Oracle Linux is free from our public yum server. Download the ISO, create a new VirtualBox VM and tell it where to find the ISO. Boot and follow the install prompts.

VirtualBox

VirtualBox is free from VirtualBox.org and runs on OS X, Windows, Linux and Solaris.

The only "trick" to using VirtualBox is to remember the magic cursor-releasing key, e.g. the Left Command Key on OS X. This key is useful if/when you haven't installed the VirtualBox "Guest Additions" into the VM and clicking into the VM window captures the cursor. The specific key combination is shown as a reminder on the bottom right of the containing VirtualBox VM window. Pressing it returns cursor control to the host OS. Luckily, once you install the "Guest Additions" the cursor is automatically released when you mouse out of the VM window.

Monday Dec 08, 2014

Video: Best Practices for Application Performance, Scalability, and Availability

Nancy Ikeda nails it in a great Oracle OpenWorld recording of her Best Practices for Application Performance, Scalability, and Availability session now viewable on the Oracle Call Interface page

The session covered:

Best practice coding samples and techniques show how to resolve connection management, statement execution, and data fetching inefficiencies in applications using APIs such as JDBC, OCI, ODBC, ODP.Net, or higher-level scripting languages. This session shows how the Automatic Workload Repository feature of Oracle Database and Automatic Database Diagnostic Monitor profiling tools help diagnose application design and coding issues. Specific solutions show how to resolve these and other issues to enhance applications for scalability and resilience. Among the solutions discussed are Oracle Database 12c’s new client configuration file. Developers or DBAs can use it to tune and configure applications without modifying code. Examples use JDBC and OCI but are applicable to all APIs.

Nancy is one of Oracle's senior developers working in the call interface group.

Monday Nov 17, 2014

Configuring Python cx_Oracle and mod_wsgi on Oracle Linux

The Web Server Gateway Interface (WSGI) is a standardized interface between web servers and Python web frameworks or applications. Many frameworks including Django support WSGI.

This post is a brief how-to about configuring Apache's mod_wsgi with Python's cx_Oracle driver for Oracle Database. The steps are for Oracle Linux.

  1. Download Instant Client Basic & SDK ZIP files from OTN. For cx_Oracle 5.1, use the ZIPs, not the RPMs.

  2. As root, unzip the files to the same directory, e.g. /opt/oracle/instantclient_12_1:

    mkdir /opt/oracle
    cd /opt/oracle
    unzip /tmp/instantclient-basic-linux.x64-12.1.0.2.0.zip
    unzip /tmp/instantclient-sdk-linux.x64-12.1.0.2.0.zip
    
  3. Configure Instant Client:

    cd /opt/oracle/instantclient_12_1
    ln -s libclntsh.so.12.1 libclntsh.so
    
  4. Install the pip package management tool for Python by following pip.readthedocs.org/en/latest/installing.html and downloading get-pip.py. Then run:

    python get-pip.py
    
  5. Install cx_Oracle:

    export LD_RUN_PATH=/opt/oracle/instantclient_12_1
    export ORACLE_HOME=/opt/oracle/instantclient_12_1
    pip install cx_Oracle
    

    The key here is the use of LD_RUN_PATH. This obviates the need to later set LD_LIBRARY_PATH or configure ldconfig for cx_Oracle to find the Instant Client libraries. There is a cx_Oracle-specific variable FORCE_RPATH which has the same effect.

    Note the cx_Oracle installer overloads the meaning of ORACLE_HOME. This variable is not normally used with Instant Client.

    Neither ORACLE_HOME or LD_RUN_PATH need to be set at runtime.

    If you don't use LD_RUN_PATH or FORCE_RPATH during installation, you will need to make LD_LIBRARY_PATH available to the Apache process or use ldconfig to add Instant Client to the system wide library search path.

    Configuring ldconfig is an effective and commonly used solution. However it has a potential problem that if multiple Oracle products exist, with possibly differing versions of Oracle libraries on the same machine, then there might be library clashes. If you wish to use it, create a file /etc/ld.conf.so.d/oracle-instant-client.conf containing:

    /opt/oracle/instantclient_12_1
    

    Then update the linker cache by running:

    ldconfig

    Alternatively set LD_LIBRARY_PATH in Apache's environment file, /etc/sysconfig/httpd. In Oracle Linux 6 use:

    export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1
    

    In Oracle Linux 7 use:

    LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1
    

    In Oracle Linux 7, don't reference variables on the right-hand side of the equals sign since they won't be expanded.

    [The Apache environment configuration file location varies between Linux distributions. On OpenSUSE see /etc/sysconfig/apache2. On Debian-based distributions look at /etc/apache2/envvars].

  6. Set any other Oracle environment variables in the Apache environment configuration file /etc/sysconfig/httpd. For example:

    NLS_LANG=GERMAN_GERMANY.AL32UTF8
    

    (Prefix any variable setting with export in Oracle Linux 6)

  7. Install mod_wsgi:

    yum install mod_wsgi
    
  8. Add this line to /etc/httpd/conf/httpd.conf:

    WSGIScriptAlias /wsgi_test /var/www/html/wsgi_test.py
    
  9. On Oracle Linux 6, start the web server with:

    service httpd start
    

    On Oracle Linux 7 use:

    systemctl start httpd.service
    
  10. Create a test file /var/www/html/wsgi_test.py that connects to your database:

    #-*- coding: utf-8 -*-
    
    def query():
        import cx_Oracle
        db = cx_Oracle.connect("hr", "welcome", "localhost/orcl")
        cursor = db.cursor()
        cursor.execute("select city from locations where location_id = 2200")
        return cursor.fetchone()[0]
    
    def wsgi_test(environ, start_response):
        output = query()
    
        status = '200 OK'
        headers = [('Content-type', 'text/plain'),
    	       ('Content-Length', str(len(output)))]
        start_response(status, headers)
        yield output
    
    application = wsgi_test
    
  11. Load http://localhost/wsgi_test in a browser. The city of the queried location id will be displayed.

That's it. Let me know how it works for you.

Information on cx_Oracle can be found here.

Information on Oracle Linux can be found here.

Information on Oracle Database can be found here.

Saturday Sep 21, 2013

Come and Join us in San Francisco this Week

Come and Join us in San Francisco this Week

It's the week of the huge Oracle OpenWorld, JavaOne and MySQL Connect conferences in San Francisco. The week will be a blast with so many things happening.

The easiest way to find sessions is to browse the Content Catalog

Followers of this blog might want to think about these sessions:

There are lots of interesting language talks at JavaOne worth considering - search the Content Catalog.

Finally, drop by the Oracle Database "Database Access APIs" demo pod 3591 in the Moscone South exhibition hall and say Hi.

Thursday Mar 21, 2013

Python cx_Oracle and Oracle 11g DRCP Connection Pooling

The topic of Oracle 11g DRCP connection pooling in Python cx_Oracle came up twice this week for me. DRCP is a database tier connection pooling solution which is great for applications run in multiple processes. There is a whitepaper on DRCP that covers a lot of background and talks about configuration. This whitepaper is ostensibly about PHP but is good reading for all DRCP users.

The first DRCP and cx_Oracle scenario I dealt with was a question about mod_python.

To cut a long story short, I created a handler and requested it 1000 times via Apache's 'ab' tool. In my first script, and despite having increased the default pool parameters, there were a high number of NUM_WAITS. Also NUM_AUTHENTICATIONS was high. Performance wasn't the best. Querying V$CPOOL_CC_STATS showed:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        992          8        478                1000

At least the session information in each DRCP server was reused (shown via a high NUM_HITS).

Results were better after fixing the script mptest.py to look like:

from mod_python import apache
import cx_Oracle
import datetime

# mptest.py
# Example: Oracle 11g DRCP with cx_Oracle and mod_python

# These pool params are suitable for Apache Pre-fork MPM
mypool = cx_Oracle.SessionPool(user='hr', password='welcome',
         dsn='localhost/orcl:pooled', min=1, max=2, increment=1)

def handler(req):
    global mypool

    req.content_type = 'text/html'
    n = datetime.datetime.now()
    req.write (str(n) + "<br>");

    db = cx_Oracle.connect(user='hr', password='welcome',
            dsn='localhost/orcl:pooled', pool=mypool, cclass="CJDEMO1",
            purity=cx_Oracle.ATTR_PURITY_SELF)

    cur = db.cursor()
    cur.execute('select * from locations')
    resultset = cur.fetchall()
    for result in resultset:
        for item in result:
            req.write (str(item) + " ")
        req.write ("<br>")
    cur.close()
    mypool.release(db)

    return apache.OK

The 'ab' benchmark on this script ran much faster and the stats from V$CPOOL_CC_STATS looked much better. The number of authentications was right down about to about 1 per Apache (ie. mod_python) process:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        977         23         13                  26

The NUM_HITS was high again, because the DRCP purity was ATTR_PURITY_SELF. If I hadn't wanted session information to be reused each time the handler was executed, I could have set the purity to ATTR_PURITY_NEW. If I'd done this then NUM_HITS would have been low and NUM_MISSES would have been high.

If you're testing this yourself, before restarting the DRCP pool don't forget to shutdown Apache to close all DB connections. Otherwise restarting the pool will block. Also, if you're interpreting your own V$CPOOL_CC_STATS stats don't forget to account for the DRCP "dedicated optimization" that retains an association between clients (mod_python processes) and the DB. The whitepaper previously mentioned discusses this.

The second place where DRCP and python came up this week was on the cx_Oracle mail list. David Stanek posed a question. He was seeing application processes blocking while waiting for a DRCP pooled server to execute a query. My variant of David's script is drcp2.py:

import os
import time
import cx_Oracle

# drcp2.py
# Example: Sub-optimal connection pooling with Oracle 11g DRCP and cx_Oracle

def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    con = cx_Oracle.connect(user=user, password=pw, dsn=dsn, cclass="CJDEMO2",
           purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    con.close()
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())
 
user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

This script forks off a bunch of processes - more than the number of pooled DRCP servers (see MAXSIZE in the DBA_CPOOL_INFO view). The first few processes grab a DRCP server from the pool and do their query. But they don't release the DRCP server back to the DRCP pool until after the sleep() when the process ends. The other forked processes are blocked waiting for those DRCP servers to become available. This isn't optimal pool sharing.

My suggestion was to use an explicit cx_Oracle session pool like drcp3.py:

import os
import time
import cx_Oracle

# drcp3.py
# Example: Connection pooling with Oracle 11g DRCP and cx_Oracle
 
def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    mypool = cx_Oracle.SessionPool(user=user,password=pw,dsn=dsn,min=1,max=2,increment=1)
    con = cx_Oracle.connect(user=user, password=pw,
          dsn=dsn, pool = mypool, cclass="CJDEMO3", purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    mypool.release(con)
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())

user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

The mypool.release(con) call releases the DRCP server back to the DRCP pool prior to the sleep. When this second script is run, there is a smoothness to the output. The queries happen sequentially without noticeably being blocked.

Like with any shared resource, it is recommended to release DRCP pooled servers back to the pool when they are no longer needed by the application.

Update August 2014: Oracle submitted a cx_Oracle patch which Anthony has merged but not yet released. This patch now allows drcp2.py to work efficiently. For the fork scenario the non-explicit session pool solution in drcp2.py is likely to be better than drcp3.py, since it does fewer DRCP requests. For the mod_python example, continuing to use a session pool as shown in mptest.py can minimize the number of authentications, which helps scalability.

Monday Sep 26, 2011

Scripting Language Related Sessions at Oracle OpenWorld and JavaOne, October 2011

Oracle OpenWorld and JavaOne conferences are happening in San Francisco next week. It will be a busy and exciting time.

First, here's a shout out: For me the conference kicks off on Sunday morning. Marcelle Kratochvil from Piction (heavy users of PHP and Oracle DB) is hosting the inaugural Unstructured Data with Multimedia SIG for Oracle Database and MySQL database (32440) Sunday 9:00 am in Moscone West room 2011.

Below are some of the scripting and related sessions happening during the week.

Exhibition Hall

During the Exhibition Hall hours, come and talk to us at the Database Access Services and APIs booth. This year we're in Moscone South, Left SL-067. The Tuxedo application server booth is Moscone South, Right - SR-202. At JavaOne look out for the NetBeans booth, Hilton San Francisco - HHJ-023.

Scripting Sessions, Birds-of-a-Feather Meetings, and Hands-on-Labs at OOW

  • The Oracle Tuxedo team has scripting language support in their powerful application server environment:
    High-Performance Web Applications with C/C++/PHP/Python (15705)
    Monday, 05:00 PM, Moscone South - 300
  • This year we are running introductory Hands-on Lab sessions for three languages concurrently. Come along and choose which language you'd like to dip your toes into:
    Develop and Deploy High-Performance Web 2.0 PHP, Ruby, or Python Applications (30082)
    Monday, 05:00 PM, Marriott Marquis - Salon 10/11
  • Come and ask questions at the round table Birds-of-a-Feather session:
    Meet the Oracle Database Clients Developers: C, C++, PHP, Python, Ruby, and Perl (26240)
    Monday, 07:30 PM, Marriott Marquis - Salon 8
  • My overview and state-of-the-nation session is:
    PHP, Ruby, Python, and Perl: Develop and Deploy Mission-Critical Apps with Oracle Database 11g (14704)
    Wednesday, 11:45 AM, Marriott Marquis - Salon 8
  • The Tuxedo team Hands-on-Lab lets you code in C/C++/PHP/Python/Ruby:
    Develop High-Performance, Service-Oriented C/C++ Applications for Oracle Exalogic (31120)
    Thursday, 12:00 PM, Marriott Marquis - Salon 3/4
  • Raimonds Simanovskis, maintainer of the Rails adapter for Oracle is giving a session:
    Extending Oracle E-Business Suite with Ruby on Rails (8604)
    Thursday, 03:00 PM, Moscone West - 2002/2004

Several other sessions discuss topics that scripting language devotees will find invaluable:

  • Build, Deploy, and Troubleshoot Highly Performant, Highly Available Apps with Oracle Database (14703)
    Wednesday, 05:00 PM, Moscone South - 303
  • Net Services: Best Practices for Performance, Scalability, and High Availability (14345)
    Wednesday, 01:15 PM, Moscone South - 303

Also check out the full Oracle Tuxedo application server schedule here.

Scripting at JavaOne

Over in the concurrent JavaOne conference there are several scripting sessions driven by San Francisco's EngineYard. This year they have JRuby sessions but with their recent aquisition of PHP technnology, I hope they'll have more on PHP in one of the OOW streams next year:

  • Accelerate Your Business and Aim for the Cloud with Java and JRuby (25284)
    Wednesday, 03:00 PM, Parc 55 - Embarcadero
  • From Java to Rails: Techniques for Adding Ruby Agility to Your Java Web Stack (24582)
    Monday, 05:30 PM, Parc 55 - Market Street
  • Real-World JRuby (23600)
    Wednesday, 04:30 PM, Parc 55 - Market Street
  • Script Bowl 2011: A Scripting Languages Shootout (22060)
    Wednesday, 08:30 AM, Hilton San Francisco - Grand Ballroom B

Also keep an eye out for the various NetBeans IDE sessions and demo booth.

Linux

Check out the four pages of Focus on Linux sessions and events.

MySQL

There is a veritable plethora of MySQL content - four pages of sessions and activites are listed in the Oracle Focus on MySQL. Don't forget the MySQL Community Reception Tuesday 7:00pm - 9:00pm in the Marriott Marquis - Foothill G.

Having started this post with a shout out, let me end with one to Bill Karwin, who was instrumental in getting PHP's Zend Framework off the ground. He is talking about MaatKit and SQL Injection.

Update: Ligaya Turmelle, well known in the PHP community, confirmed overnight that despite a recent job move she is still on track to present her MySQL Performance Tuning talk (16040).

You can search the OOW session catalog here and the JavaOne session catalog here.

Tuesday Mar 29, 2011

cx_Oracle 5.1 for Python is Available

[Read More]

Wednesday Dec 15, 2010

Learn to use PHP and Python with Oracle Database

[Read More]

Saturday Sep 18, 2010

Newly Released Stuff: Perl Driver, Python Tools and Berkeley DB 5.1

[Read More]

Thursday Jul 29, 2010

Scripting Languages at the Oracle Tuxedo Virtual Developer Day: Review

[Read More]

Wednesday Jul 28, 2010

cx_Oracle 5.0.4 Python driver for Oracle Database Released

[Read More]

Tuesday May 18, 2010

Python and Ruby in Oracle Tuxedo

[Read More]

Saturday Oct 10, 2009

Oracle OpenWorld starts today!

[Read More]

Tuesday Jul 14, 2009

PyOhio - Python in Ohio, 25 & 26 July 2009

[Read More]
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
27
28
29
30
31
      
Today