Setting up Oracle Database 10g and PHP on Mac OS X
by Matt Rohrer

Mac OS X is now an officially supported platform for the Oracle Database; use this guide to set up your own development workstation

As announced in July, Oracle has made the Oracle Database 10g Early Adopter Release for Mac OS X available for download. The following guide will help you set up a development workstation with the Oracle Database, PHP compiled with OCI support, and a more productive wrapper for SQL*Plus.

Prerequisites

You will need a Mac running the latest version of Mac OS X "Panther" (10.3.4 at the time of this writing) with XCode 1.1 or higher installed, and an administrator account. The installer and database will run on the Client version, though Oracle only supports the Server version of the OS. The Mac should have at least 512MB of RAM and 5GB of free hard disk space.

You should be familiar with working in the Unix shell environment and running administrative commands via sudo. The instructions below will use the bash shell syntax as it is the default shell for new accounts in Panther.

If you have not updated XCode to version 1.2 you will also need to download and install an update to the gcc suite from the Apple Developer Connection site. Go to http://connect.apple.com/ and sign in or register for a new account. Once you have logged in, choose the "Download Software" link on the left-hand side of the page. Then choose "Developer Tools" and download the "Dec 2003 gccLongBranch Tools." Click on the downloaded file and follow the instructions to install the update.

Finally, you will need to download the Oracle Database and source code for PHP 4.3.8. Be aware that the Oracle Database file is quite large (575MB) and may take some time to download.

Getting Started

The 10g Early Adopters Release comes with helpful documentation and installs via the Oracle Universal Installer, so first unpack the file you downloaded. Open up a terminal window and go the directory where you downloaded the file (probably ~/Desktop or ~/Documents) and execute the following commands:

$ ls MAC_DB_SERVER*

If the file ends with a .cpio.gz extension, run the following command to unzip it. If it ends in .cpio your browser has already unzipped the file and you can skip the next step.

$ gunzip MAC_DB_SERVER.cpio.gz

Next extract the installation disk from the cpio archive (you can add v after -idm to see the list of files as they are extracted).

$ cpio -idm < MAC_DB_SERVER.cpio

You will be left with a directory called Disk1. To read the documentation in your default web browser, run the following command:

$ open Disk1/doc/unixdoc/index.htm

Click on the "Quick Installation Guide for Apple MAC OS X" link. Despite the name, this is actually a detailed guide to all of the steps involved in preparing the system and running the Oracle Universal Installer. If you have never installed Oracle before or run into problems with the instructions below, please take some time to read through this guide. I will also point you to sections in the guide for more details at various stages of the instructions below.

Although the guide recommends running a shell as the root user via su - root, the root user is not enabled by default on Mac OS X and the command will prompt you for a non-existent password. You can either run commands with root privileges via sudo (such as sudo command) or start a root shell via sudo using sudo su -.

Preparing the System

The database should be installed and run by a dedicated user account, usually named oracle. If you have installed the 9iR2 Developer's Release (see my previous article), you probably already have an oracle account on your system. Refer to the "Create Required Unix groups and user" section in the Quick Installation Guide to create or update the oracle user and the OSDBA and Oracle Inventory groups. You can check to make sure the user is properly configured by running the following command:

$ id oracle
uid=1521(oracle) gid=1523(oinstall) groups=1523(oinstall), 80(admin), 1521(dba) [[should be only one line]]

If you see a list of groups similar to the above your oracle user is ready to perform the installation. Note that you may see different numbers for uid, gid and in the list of groups.

Next you need to decide where to store the database application and data files. As this installation is for a developer system, I have opted to forgo Oracle's Optimal Flexible Architecture in the interest of simplicity. Open another terminal window and enter the following commands:

$ sudo su - oracle
$ mkdir 10gEAR2

This starts a shell as the oracle user and creates a /Users/oracle/10gEAR2 directory, which will be the $ORACLE_BASE for this installation. If the computer has multiple disks, you may also want to create a directory on another disk to hold the data files. Oracle recommends separating the application and data files for best performance.

Next you need to check several kernel parameters and ensure that they are set at or above Oracle's recommended values. Please refer to the "Configure Kernel Parameters" section of the Quick Install Guide for details. For example, on my system all of the parameters but two were already set to the recommended values, so I changed the two as follows after switching back to the terminal with my administrator account:

$ sudo sysctl -w kern.maxproc=2068
$ sudo sysctl -w kern.maxprocperuid=2068

To ensure the changes would persist after a reboot I added the two lines into the /etc/sysctl.conf file:

$ cat /etc/sysctl.conf
kern.maxproc=2068
kern.maxprocperuid=2068

Next you need to make sure that the shell limits are set high enough. The Quick Install Guide recommends editing the /etc/rc file and the /System/Library/StartupItems/IPServices/IPServices file. There are edited versions of these files in Listing 1 and Listing 2. I recommend replacing them as follows:

$ cd /tmp
$ vi rc #copy the contents of listing 1 into this file, making sure 
        #to trim any leading whitespace
$ vi IPServices #copy the contents of listing 2 into this file, making
                #sure to trim any leading whitespace
$ cd /etc
$ sudo cp -p rc rc.orig #backup the original file
$ diff /tmp/rc rc #you should only see the additions as recommended 
                  #in the guide
$ sudo mv /tmp/rc rc
$ cd /System/Library/StartupItems/IPServices 
$ sudo cp -p IPServices IPServices.orig #backup
$ diff /tmp/IPServices IPServices #again, make sure the only
                                  #differences are the additions
$ sudo mv /tmp/IPServices IPServices

If there are any problems with the edited versions of the files you can rename the saved files to their original names to restore the original version.

Next, you will need to make sure that you can ping your hostname. First check the hostname:

$ hostname
yournamehere

If you see a name that ends in .local, you will may have to set your hostname to a different value:

$ sudo hostname yournamehere

To make sure that the change persists after reboot, edit /etc/hostconfig via sudo and change the "HOSTNAME=-AUTOMATIC-" line to "HOSTNAME=yournamehere"

Now check to see if you can ping your hostname (this will probably fail if you had to manually set it in the previous step).

$ ping -c 1 `hostname`

If you get "ping: unknown host yournamehere", you'll have to add your hostname to the /etc/hosts file. Edit /etc/hosts via sudo and change the line that says "127.0.0.1 localhost" to "127.0.0.1 localhost yournamehere". Save and repeat the previous command. You should see several lines of output including "1 packets transmitted, 1 packets received, 0% packet loss".

Next, you should create the /opt directory if it doesn't exist and move aside the /etc/oratab file if it does exist:

$ test ! /opt && sudo mkdir /opt
$ test /etc/oratab && sudo mv /etc/oratab /etc/oratab.orig

Finally, move the Disk1 directory into the oracle user's home directory and make sure it's owned by the oracle user:

$ sudo mv Disk1 /Users/oracle
$ sudo chown -R oracle:oinstall /Users/oracle/Disk1

Running the Oracle Universal Installer

Now switch back to the terminal running a shell as the oracle user. Before starting the installer you will have to configure your environment. For more details refer to the "Log In as the oracle User and Configure the oracle User's Environment" section of the Quick Install Guide, though the following commands should do the trick:

$ export ORACLE_BASE=/Users/oracle/10gEAR2 #if you chose a different
                                           #path above, use it here
$ export ORACLE_SID=test10g
$ umask 022
$ unset ORACLE_HOME
$ unset TNS_ADMIN

Now start the installer:

$ cd Disk1
$ ./runInstaller

The installer is a java application that leads you through several screens of configuration information. Each screen, and the recommended input, is described in the Quick Installer Guide. I won't repeat all of that information here, but there are a few steps that require clarification and a few others where we will not be using the defaults — they are listed below.

On the "Specify File Locations" screen change the Directory Path value from /Users/oracle/10gEAR2/OraHome_1 to /Users/oracle/10gEAR2/orahome. This will be the $ORACLE_HOME directory. Choose the Enterprise Edition on the "Installation Type" screen , and chose "Do not create a starter database" on the "Select Database Configuration" screen. You will be creating, populating and configuring your database from a script in the next step.

Finally, the root.sh shell script that the Early Adopter Release generates has a few errors related to the location of several utilities . Copy the script from Listing 3 to a new root.sh file. If you have chosen a different value for $ORACLE_HOME be sure to edit the script before running it via sudo. You may still see errors about Oracle Cluster Keys — you can safely ignore them unless you plan to set up a database cluster, which is beyond the scope of this article.

When the installation has completed you can exit the installer and continue to the next section to create a database and test your installation.

Creating a Database

Copy the contents of Listing 4 to a file called createDb. This script will create the listener.ora and tnsnames.ora files, create a database, and populate it with an example schema. There are several areas where you can customize the script for your application, although the defaults will be sufficient for testing. Execute the following commands to set up your environment and run the script:

$ chmod +x createDb
$ export ORACLE_HOME=$ORACLE_BASE/orahome
$ export PATH=$PATH:$ORACLE_HOME/bin	
$ ./createDb

After the script has run, which may take up to an hour depending on your hardware, test your installation by logging into the database as the sample user:

$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.3.0 - Production on Tue Jul 27 22:16:20 2004

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

When you've confirmed that the database was created properly, you can change the default passwords, load your application, or just poke around the example schema. To permanently configure the oracle user's environment to use the new database, create a file called .bashrc.oracle in the oracle user's home directory with the following contents:

export ORACLE_HOME=/Users/oracle/10gEAR2/orahome
export ORACLE_SID=test10g
export PATH=$PATH:$ORACLE_HOME/bin

Then make sure the main shell initialization script uses this file by creating a line in .bash_profile like so:

. ~/.bashrc.oracle

You can also create a script to stop and start the database and listener by copying the lines below into a file called oraclectl. Make the file executable, and call it with "start" or "stop" as its only arguments.

#!/bin/sh

# Use to start and stop the Oracle database
# Must be run by member of dba group 

start() {
$ORACLE_HOME/bin/sqlplus /nolog <<__EOF__
connect / as sysdba
startup
__EOF__
sleep 2
$ORACLE_HOME/bin/lsnrctl start
}

stop() {
$ORACLE_HOME/bin/lsnrctl stop
$ORACLE_HOME/bin/sqlplus /nolog <<__EOF__
connect / as sysdba
shutdown abort
__EOF__
}

if [ -z $ORACLE_HOME ]; then
    echo "You must define ORACLE_HOME" 1>&2
    exit 2
fi

if [ ! -d $ORACLE_HOME ]; then
    echo "Unable to find $ORACLE_HOME" 1>&2
    exit 2
fi

case "$1" in
'start')
    start
    ;;
'stop')
    stop 
    ;;
*)
    echo "Usage: $0 { start | stop }"
    exit 2
    ;;
esac

exit 0

Installing PHP

First copy the tar file you downloaded to the oracle user's home directory. Switching back to the terminal running your administrator account shell, run the following commands:

$ cd ~/Desktop #or wherever you downloaded the PHP tarfile
$ sudo mv php-4.3.8.tar.gz ~oracle

Next, as the oracle user, extract the source code, configure and build PHP. In this example we just activate the OCI extension in order to connect to Oracle; your application may require additional extensions for XSLT support, image manipulation, etc. You can see all of the configuration options by running ./configure --help.

$ tar zxf php-4.3.8.tar.gz
$ cd php-4.3.8
$ ./configure --with-apxs --with-oci8
$ make
$ sudo make install

To test your PHP installation, create e a test_oci.php file with the following contents :

<?php
        $sid = 'test10g';
        $home = '/Users/oracle/10gEAR2/orahome';

        putenv("ORACLE_HOME=$home");
        putenv("ORACLE_SID=$sid");
        putenv("TNS_ADMIN=$home/network/admin");

        echo "<html><head><title>Test OCI</title></head><body>\n";

        // Persistent connections are recommended for OCI
        $conn = OCIPlogon('scott', 'tiger', $sid);
        if ($conn) {
            $stmt = OCIParse($conn, "SELECT TO_CHAR(SYSDATE,
                                     'YYYY-MM-DD HH24:MI:SS') AS
                                     datetime FROM DUAL");
            if ($stmt) {                                         
                if (OCIExecute($stmt)) {                             
                    while (OCIFetchInto($stmt, $row, OCI_ASSOC)) {        
                        // keys from OCI_ASSOC are always UPPERCASE 
                        echo "SYSDATE: ", $row['DATETIME'];
                    }                                                     
                }
            }
        }

        echo "</body></html>\n";
?>

PHP support is enabled for you in the Apache httpd.conf file that ships with Mac OS X Panther, and the default document root is /Library/Webserver/Documents. Copy the test_oci.php file into the document root and start or restart apache:

$ sudo apachectl start

You can now point your browser at http://127.0.0.1/test_oci.php, and you should see the current date and time. Refresh your browser a few times to see the time change, and note that the page is displayed much quicker than the first time. Each apache child process will create one persistent connection t o the database when using OCIPlogon. Subsequent requests handled by the child will be much faster as it can avoid the overhead of establishing a new connection.

PHP's OCI extension supports many advanced Oracle features, such as stored procedures, bind variables, and cursors. More information about using OCI with PHP can be found in the "Resources" box.

A Better SQL*Plus

Finally, I'd like to introduce you to a tool that has made my life as an Oracle developer much more pleasant. Ljubomir J. Buturovic created gqlplus, a wrapper for SQL*Plus that uses the GNU readline library to add command-line history, editing, and table/column name completion. It runs on most UNIX-like systems, and the download contains binaries for the Linux/Intel, Solaris/SPARC, OSF1/Alpha, IRIX, and OSX/PPC platforms.

There is one catch, though: Apple doesn't include some libraries that are useful for development with OS X, so you need to install the readline library before compiling gqlplus. I choose to use the DarwinPorts collection for this purpose, but you can certainly use Fink or just grab the source from the GNU project and compile it yourself.

First, download the latest snapshot of the DarwinPorts collection and build and install the ports tools:

$ curl -O http://darwinports.opendarwin.org/darwinports-nightly-cvs-snapshot.tar.gz [[this should be one line]]
$ tar zxf darwinports-nightly-cvs-snapshot.tar.gz
$ cd darwinports/base
$ ./configure --prefix=/usr/local
$ make
$ sudo make install

I have elected to make the base for the installed ports /usr/local, because many packages look for libraries there by default. When the ports tools are installed, you can install readline:

Resources "Setting Up Oracle9i Database and PHP on Mac OS X" "Get Started with Oracle and PHP," by Sean Hull PHP Extension for Oracle JDeveloper 10g Notes from a presentation by a maintainer of the OCI extension; see slides at http://conf.php.net/pres/index.php?p=slides%2Foci&id=oci3 Oracle's PHP troubleshooting FAQ PHP Manual, OCI section Open Source Developer Center

$ cd ../dports
$ sudo port install readline

DarwinPorts automatically fetches the source and configures, compiles, and installs the library. Now all you need to do is get gqlplus from http://prdownloads.sourceforge.net/gqlplus/gqlplus-1.9.tar.gz?download and unpack and install it. Because SourceForge makes it hard to download without going through your browser, you will probably need to copy the downloaded file to the oracle account.

$ cd Desktop   # (or wherever you downloaded the file)
$ sudo mv gqlplus-1.9.tar.gz ~oracle
 

Now switch back to the Oracle user, compile the program and try it out using the same syntax as for SQL*Plus:

$ tar zxf gqlplus-1.9.tar.gz
$ cd gqlplus-1.9
$ make
$ cd Darwin
$ ./gqlplus scott/tiger

Play around with the various line editing commands—some of the highlights include table and column name tab-completion, and Emacs or vi -like line editing. An online reference for the readline library is available at http://cnswww.cns.cwru.edu/php/chet/readline/rluserman.html.

Conclusion

Oracle, Mac OS X, and open source technologies such as Apache, PHP and gqlplus make for an enjoyable, productive, and stable development environment. Now that Mac OS X is an officially supported platform for the Oracle Database, IT departments can take a serious look at migrating developer workstations and servers to Macs.