Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

How to configure Mac OS X as an Oracle Client

with 17 comments

An updated version of this blog is on my newer site here.

The best thing about working on a MacBook or MacBook Pro is working on it, not working on the VMWare Fusion instance. The virtual machine should be a background process, whether it is a Linux, Windows XP Pro or Vista instance. You’ll find out how to do that here. You’ll be able to run SQL*Plus from the Mac OS command-line, SQL*Developer natively in Mac OS, and Oracle Enterprise Manager in Mac browser (of your choosing). Oddly, the few distracting Java errors that SQL*Developer raises on a PC don’t get raised on the Mac OS X.

At present, Oracle databases don’t run natively on Mac OS X with an Intel processor. You can run Oracle Database 10gR1 on a G5 (PowerPC 970) processor (a detail I apparently missed in the installation notes on the Oracle Technical Network), but why do that? You can run Oracle Database 10gR2, Oracle Database 10g Express Edition, or Oracle Database 11g in a virtual machine on a MacBook, MacBook Pro, or Mac Pro.

Unless you require Java or User Defined Object Types (UDTs) inside your development instance, I’d recommend you install Oracle Database 10g Express Edition on a MacBook or MacBook Pro when you have 2GBs of memory or less. If you have 4GBs of memory, they all work fine. They actually do work on Vista but I’ve heard the new Vista license agreements may prohibit their use on Mac OS X in a VM. I’ve found Windows XP Pro x64 bit works best if you want to run Windows, and Red Hat 4.0 Advanced Server screams in the VM with enough memory.

Enough introduction, here are the steps. A word to the wise, read carefully!!!

1. I’d recommend VMWare Fusion for the virtual server. There were enough problems with Parallels that I simply ditched it. When you install VMWare, download the current release from the web (VMWare Fusion 2.0 is available as of 9/12/2008). After downloading, use your license during the installation. You can use the copy in the box, but I’ve never found one in the box to work (though I’ve only tried 4 of them).

2. Install your operating system in the Virtual Machine (VM). I’d recommend that you take the time to configure memory before hand because it runs a lot faster if you do. Skipping the one-click installation may save you time because I’ve found it still has some warts. You have two choices for configuring the network. One is NAT and the other is a bridged network. The bridged network is the most like separate machines but it may not work in certain commercial settings. For example, if a router disallows calls from one machine to another in a subnet, you should use NAT. NAT means your native Mac OS acts as the gateway, and it ensures that your external router configurations won’t block communication between your Mac OS and the virtual machine instances. The down side for NAT is that you can’t communicate between servers without some additional work (hopefully, I’ll be able to add those steps after Oracle Open World).

2(a). A quick screen shot of how you want to configure VMWare NAT network, you get there by clicking Virtual Machine, Network, Network Settings…

2(b). A quick screen shot of how you want to configure VMWare bridged network, you get there by clicking Virtual Machine, Network, Network Settings…

3. Once you’ve installed the operating system and patched it to the current level, you should name your machine before installing the Oracle Database. You should also configure your hosts file with the computer name, which is covered in the next step. You’ll regret it if you do forget it. All is not lost If you forget to edit your hosts file, you can rebuild the Enterprise Manager by following the instructions in this earlier blog page. Also, the standard and enterprise releases of the Oracle Database requires that you avoid white spaces in the user name.

4. Don’t forget to add that name into the C:>WINDOWS\System32\Drivers\etc\hosts file or the Linux /etc/hosts file, like this:

xxx.xxx.xxx.xxx hostname hostname.domain.com

You can find the IP address by using the “ifconfig -a” command in Linux, and the “ipconfig” command in Microsoft Windows.

4(a). The following is the screen shot for a NAT networking configuration. In case you’re not a networking guru, there are three addresses left open by Internet Assigned Numbers Authority (IANA) RFC 1918. 172.16/12 prefix is one of those. VMWare Fusion uses one of two dependent on the release. VMWare Fusion, Version 1.x,  typically uses 172.16.113.x for private addressing. VMWare Fusion, Version 2.x, typically uses 172.16.153.x for private addressing. If you want a virtual machine to call another virtual machine, you’ll need to map the other virtual machine in its hosts file.

Note:
The range for static IP addresses in your NAT range is 172.16.113.3 to 172.16.113.127. IP addresses 172.16.113.128 and above are DCHP assigned addresses for VMWare Fusion 1.x, which was used for this article. VMWare Fusion, Version 2.x, uses 172.16.153.3 to 172.16.153.127 for static IP addresses and 172.16.153.128 and above.

4(b). The following is the screen shot for a bridged networking configuration. In case you’re not a networking guru, there are three addresses left open by Internet Assigned Numbers Authority (IANA) RFC 1918. 192/16 prefix is one of those. This is the one you’d most likely see if you’re working in a home netwroking environment.

If you’re configuring Microsoft Vista, you should note that you can’t edit the hosts file without changing your security permissions. You begin that process by right clicking on the file in Windows Explorer. In the hosts Properties dialog, choose the Security tab and click on your user name before clicking the Edit … button. Allow Full Control to make the changes, but note the original settings. Good system administrations skills would indicate you re-establish security after making the change, and that’s why you’ll need those original settings.

Changing IP Addresses:
If you switch between bridged and NAT, or vice versa, don’t forget that you need to release and renew a DHCP address. The following two steps let you do that:

a. As the Administrator, or privileged account, type the following:

C:> ipconfig /release

b. As the Administrator, or privileged account, type the following:

C:> ipconfig /renew

Note:
A caveat here for those using DHCP addresses. They may change over time. You may need to revisit this multiple times. If you’re on several networks, you may opt to put all IP addresses that your computer typically holds. Multiple addresses aren’t a problem because it looks for any match in the list to resolve the hostname. This means you can have the information in the file to support multiple network environments where you may travel with your Mac Book or Mac Book Pro, and avoid reconfiguring between locations.

Static IP Addresses in VMWare Fusion:
I’d strongly recommend that you use static IP addresses. They’re straight forward if you remember a couple things: (a) you need to use a port address in the defined range – 172.16.113.3 to 172.16.113.127 (version 1.x and 172.16.153.3 to 172.16.153.127 (version 2.x), and (b) you need to explicitly provide a DNS server IP address.

The following shows how to set a static IP address by platform (only Windows at present):

a. Navigate to the Control Panel, and open the Network Connections. This presents you with the Network Connections window. You should click the Local Area Connection icon, as shown in the screen capture:

b. In the Local Area Connection window, click the Properties button, as shown in the screen capture:

c. In the Local Area Connection Properties window, choose the Internet Protocol (TCP/IP) and then click the Properties button, as shown in the screen capture:

d. In the Internet Protocol (TCP/IP) window, you should enter an IP address in the static range of values, a subnet mask of 255.255.255.0, and a default gateway – which should always be 172.16.113.2 (at least in version 1.x) and 172.16.153.2 (in version 2.x). You should install DHCP first to discover the default gateway. You can discover it by issuing the following command:

C:\> ipconfig

The last step requires you to select a DNS server. You can find the DNS server of your Mac OS by running the following command:

# cat /etc/resolv.conf

Enter one of the nameserver values. I generally take the first one offered. You can also create a Ubuntu virtual machine, and run DNS there. The following shows the screen capture for the Internet Protocol (TCP/IP) window (by the way the one used was snagged in the screen capture came from the wireless network at Oracle OpenWorld 2008):

After you’ve made these entries, click the OK button for the Local Area Connection Properties window and the Local Area Connection window. Then, click the Close button for the Local Area Connection window and close window widget for the the Network Connections window.

Note #1:
Please remember the DNS server changes when you connect to a different network. You’ll have to check it and change it as you change networks.

In a home network, the Airport Extreme typically double NAT’d, and it uses 10.0.1.1 as the DNS server. This DNS server only lets you manage outbound requests from your VM. You need to configure your own DNS server locally on the Mac OS if you want to interconnect between VMs, or you can use hosts file validation as shown in this blog page.

5. Install the Oracle Database version of your choice in the VM, then confirm that you can connect to the database through the SQL*Plus. This connection should be self-contained in the VM. You use syntax like this for a schema or user named PLSQL with a (trivial) password of PLSQL.

C:> sqlplus plsql/plsql@orcl

Note #2:
Before you move ahead with the installation, you should check your listener.ora and tnsnames.ora files. You can find the files in the $ORACLE_HOME\network\admin directory. They should have your hostname and not an IP address as the HOST variable values, like those in the following illustration. The top image is the listener.ora file, and the bottom image is the tnsnames.ora file.

For reference, the equivalent Windows environment variable is %ORACLE_HOME%. The environment variables should point to your Oracle home directory.

6. Once you’ve done that, you’re ready for the Mac OS X part of the equation. Download the Oracle Database 10g Client Release 2 from the otn.oracle.com web site.

7. Unzip the file and put it into a folder named instantclient, which I chose to put in the /Applications folder.

8. Now for the Linux tricks. You need to setup a couple environment variables. The easiest way to do this in Mac OS X is to put them in a .bash_login shell script in your user account. Go to the Macintosh HD icon and click it. Then, navigate to the Applications folder. Open the Utilties folder from the Applications folder, and launch the Terminal application. If you put the Oracle Client where I said to put it, then do exactly what I’ve got but if you improvised, adjust here too.

8(a). Open vi (it won’t hurt too badly), and enter command mode by typing an “i“. Then, type the following (slowly, if you’re new to vi), backspace should help cleanup errors:

export set ORACLE_HOME=/Applications/instantclient/ohome
export set DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export set TNS_ADMIN=$ORACLE_HOME/network/admin
export set PATH=$PATH:$ORACLE_HOME/bin

8(b). Close vi by taking these two steps. First, click the “Esc” key, it puts you in command mode. Second, type “:wq” (that’s a colon, a w and a q) from command-mode. The file is now closed.

8(c). Open a new Terminal session, and check your handiwork by typing the following, which should show you what you typed in above. If this didn’t work, return to the step 8(a) and 8(b) until it works.

# echo $ORACLE_HOME
# echo $DYLD_LIBRARY_PATH
# echo $TNS_ADMIN
# echo $PATH

This step means that every time you open a Terminal, you can run SQL*Plus against the Oracle Database in the VM instance. Naturally, you’ll need the VM instance up and running concurrently.

9. You need to enter the IP address and hostname for your virtual machine in the /etc/hosts file on your Mac. This requires the superuser account. You will again use vi to edit this file. You’ll need to use sudo to open this file, like this:

# sudo vi /etc/hosts

Add the following line at the bottom of the existing file, and don’t change anything else (unless you’d like to reinstall everything). This entry is generally required with NAT IP addresses, and it is optional with bridged networkds when the DNS server resolves IP addresses. If you’re running disconnected from a network, it is also required.

xxx.xxx.xxx.xxx hostname_of_vm_machine

You should use the IP value from your virtual machine’s hostname file. You’ll need to use :wq! to close the file. This is required of key files. The bang operator (!) tells the OS you’re sure you want to make the change.

This change lets you reach the virtual machine because you’ve now qualified the server to your Mac OS. This is known as host file resolution.

You actually have two options to qualify virtual machines to the local Mac OS. They are host file or DNS resolution. Host file resolution is easy because you simply add the private IP address and name to the Mac OS hosts file. DNS resolution can be done in the VMWare network configuration files, or on an external DNS server. An example of the Mac OS hosts file is:

As noted above, DHCP IP addresses may change because another computer can snag your DHCP lease. You may have to change this IP address over time. Also, you can enter multiples when you connect to different networks. Resolution of the IP address or hostname looks for a match in the list.

It’s simpler then the server VM instance because you won’t need a domain at this time. You would if you’re configuring Apache but that’ll have to be another blog page. You can test whether you can connect to the virtual machine by running a simple ping test, like …

# ping hostname

If it resolves, you can continue. Otherwise you need to stop and fix it.

Ah, the question is how do you fix it? The first thing you need to do, is open ICMP requests in your firewall, or disable your firewall. I recommend the former because when I post how you can open communication into these virtual machines you’ll want to have your firewall running. There are two steps in Windows to open the firewall.

First, navigate to your control panel, and open the Windows Firewall. Choose the Advanced tab as shown in the screen capture:

Second, allow incoming echo requests. You choose the Settings… button in the ICMP region to enable echo requests. This also allows echo acknowledgments or replies. After enabling the echo requests, you should click the OK button in the ICMP Settings and Windows Firewall windows.

Since there are too many Linux firewalls, I leave those instructions to you. Just remember that the ping utility resolves everything at networking level 3, which is IP, UDP, ICMP, et cetera.

At this point, you should have also added port exceptions for SQL*Plus on port 1521 and Oracle Enterprise Manager on port 1158. You do that from the Windows Firewall too. The Add Port … button lets you create port exceptions. Port exceptions allow traffic through only one port, and let your firewall protect other aspects of your machine.

You only need to open TCP for both the SQL*Plus and Oracle Enterprise Manager exceptions. The exception configuration for the Oracle Enterprise Manager looks like the following:

Now that your firewall is open a bit, you can proceed with testing the Mac OS side of the configuration. Don’t forget to click the OK button to save the port exception.

10. Copy your tnsnames.ora file from the Oracle home directory in your VM. It is found in the (Linux or Unix) $ORACLE_HOME/network/admin or (Windows) %ORACLE_HOME%\network\admin directory. You need to put it in the equivalent Oracle home directory on the client installation, which is this unless you improvised:

/Applications/instantclient/ohome/network/admin

11. Now, you’re prepared to test how well you’ve setup the environment. The first step confirms that Oracle’s TNS (Transparent Network Substrate) is configured properly. You test it with the Oracle tnsping utility. The argument is the TNS alias (or service name) in a tnsnames.ora file. It is typically orcl or xe for demonstration instances. I’ve used the one from the general releases.

# tnsping orcl

If you receive a success, move on to the next step and check SQL*Plus connectivity. The tnsping utility uses the tnsnames.ora file to resolve where it should request an acknowledgment. It checks in the local directory before the $ORACLE_HOME/network/admin directory, unless you’ve set the $TNS_ADMIN environment variable. If the $TNS_ADMIN variable is set, tnsping looks there first.

A failure is generally represented by an ORA-12514 or ORA-12162. These typically mean you’ll need to troubleshoot the configuration of the Oracle listener. Either typically means that the IP addresses between the OS X client and VM server differ. If you’re using hosts file validation, please check to make sure you have the same IP address is both the Mac /etc/hosts file and VM hosts file. If you’re using DNS validation, you should ensure that the IP addresses map to a valid DNS host name. You can use the nslookup command to see if the IP address returns the hostname or hostname returns the IP address.

After making changes, you need to stop and restart the Oracle listener to test any changes. Linux also requires you to restart network services. These changes are made inside the respective virtual machines.

Unfortunately, you could get a message that the executable isn’t found. You can check whether you’ve configured your (Mac OS X) $PATH variable with this command:

# which -a tnsping

You’re $PATH variable is pointing to the wrong place if nothing is returned. Return to step 8(a) and fix it. The which command lets you find executable programs in your current $PATH. The -a option lets you see the order in which two copies of the same executable are called. The one returned first by the which -a call is always the one called because of the order of precedence set in the current $PATH environment variable.

12. You can check whether Oracle SQL*Plus works here by using the following type of syntax:

# sqlplus plsql/plsql@orcl

If you connect, that’s great! Move on to the next step.

If not, there are a couple errors that might occur. One is that the executable can’t be found, you can check that with similar syntax to that found in step 11 above:

# which -a sqlplus

If nothing is returned, fix the $PATH environment variable in step 8(a). The other is an ORA-12545, which in this case most likely means that the IP address in the VM hosts file is incorrect.

13. Hopefully, this is a lucky number. Download SQL*Developer for the Mac OS X from the otn.oracle.com web site, here’s the one you want:

You should unzip it, and put it into the Applications folder. Launch SQL*Developer. Right click the Connections in the left column, and you should complete the connection like this:

14. Now you can test SQL*Developer. Here’s a quick screen shot of it working. You can click the image for the full size screen shot, which wouldn’t fit the style of the blog.

15. You can configure the Oracle Enterprise Manager (OEM) to run now. The only possible missing link is the security credentials. You’ll find out how to override those in this prior blog post. The following is a screen shot of the OEM product running on a Mac through NAT to a virtual machine:

This concludes the steps. I hope it works for you. If it didn’t work, please post a comment. Also, if I didn’t catch the right tags, please let me know.

For those who want to configure Oracle Internet Directory from the Mac OS, John Piwowar has laid it out in his blog here. That’s the oidadmin utility.

Gotchas:
1. If you encounter an “the network bridge device on /dev/vmnet0 is not running” when trying to start your VMWare instance, don’t panic. A service didn’t start correctly. You can reboot your Mac or you can run the following command from the /Library/Application Support/VMWare Fusion directory (options are --start, --stop, or --restart):

# sudo boot.sh --restart

You can also find how to configure a VMWare Windows virtual machine here. It has instructions for both NAT and bridged networks.

Written by maclochlainn

September 2, 2008 at 5:23 am

17 Responses

Subscribe to comments with RSS.

  1. […] You can find the steps here … Tagged with: Configuring SQL*Developer on the Mac OS X, ORA-12162, ORA-12514, ORA-12545, SQL*Developer on Mac OS X, SQL*Plus on Mac OS X « Cows don’t fly and LOBs don’t resolve across a DB_LINK […]

  2. This is exactly the configuration I recommend for your CIT 320, 420, 425 students. They all think it’s a pain, but once you’ve got this up and running it is very nice to be able to develop and code in OS X and assume the Oracle server is exactly that, an Oracle server on another machine. This is how they will use it in the work environment so they’d better be used to it in class as well. Very nice post.

    Mike Farmer

    September 2, 2008 at 1:30 pm

  3. Thank you for posting this for all of us. Very helpful! It is too bad that Oracle isn’t on top of this. Way to be on the ball and shame on Oracle.

    Mike Grace

    September 9, 2008 at 8:29 pm

  4. Yes, it’s unfortunate that the install notes don’t clearly state what all the steps are to setup the Oracle Client software. They do however say to reference the libraries but they assume people know how to do that, which is probably not a good assumption. They also assume that those configuring the Oracle Client software understand the networking model, and how to configure and troubleshoot the listener.ora and tnsnames.ora files. I think this shortfall is common with all software vendors because documentation doesn’t get too much actionable user acceptance testing.

    maclochlainn

    September 10, 2008 at 3:00 am

  5. […] You can find it here … Tagged with: NAT Configuration for MacOS and VMWare, Oracle 10g Client on Mac OS, Oracle Client on Mac OS, VMWare configuration for Oracle Client « An ugly VMWare Fusion error “/dev/vmnet0″ is not running […]

  6. […] 20th, 2008 I’ve been revising and correcting typos plus adding new content to that Configuring Mac OS for the Oracle 10g Client blog page. It’s more complete now. It also shows you how to connect from one virtual machine to another […]

  7. Correct me if I am wrong but I think Oracle has now come out with 10gR2 that will run native on the Intel macs. If this is really what I think it is I am going to be really excited. I found it while looking for oracle to install on my tower that I just set up at home as a server.

    Download
    * http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204macsoft.html

    Installation guide
    * http://download.oracle.com/docs/cd/B19306_01/install.102/e12121/toc.htm

    Mike Grace

    September 21, 2008 at 6:42 am

  8. Mike, That’s the Oracle Client software that the article discusses. While the Oracle client is large, it doesn’t let you have the database natively on the Mac OS. For a moment, I’d hope it was something I missed. I’ve asked a lot of people at Oracle to consider releasing at least Oracle Database 10g Express Edition (XE). It appears there’s some talk, maybe that will translate to action, and a native Mac Intel port of the product.

    maclochlainn

    September 21, 2008 at 2:34 pm

  9. Sad day! :-( I was hoping that Oracle had made a breakthrough in their thinking. In the mean time I will just have to install Oracle on my XP server that I just got running at home. I have done a little bit of searching and found that Oracle and Apache can work together but in your experience do they “play nice”? In your opinion what would be the best companion for Oracle? Thanks!

    Mike Grace

    September 21, 2008 at 9:44 pm

  10. Yes, Apache plays well with Oracle and vice versa. The Application Express web application runs on an Apache implementation that is deployed internally in Oracle. I show how you can access it for your own applications in Chapter 16 of my Oracle Database 11g PL/SQL Programming.

    Oracle also ships their Oracle Application Server (AS), which includes some serious production utilities missing in generic Apache. If you plan to implement PHP on Oracle it is called OPAL (Oracle, PHP/Perl/Python, Apache, and Linux) and you’ll need Zend Core for Oracle to have a supported environment. More on that stack is found in my OPAL book found in the @About section.

    maclochlainn

    September 21, 2008 at 10:22 pm

  11. […] a comment » I’ve added the Windows static IP networking steps to the How to configure Mac OS X as an Oracle Client. I’ll try to get back and update the entry for a couple Linux distros (distributions) next […]

  12. […] How to configure Mac OS X as an Oracle Client […]

  13. […] eliminates additional searches when running sqlplus and tnsping executables. I’ve updated that blog page with those instructions. It’s not necessary to make it work but it does avoid a read to the […]

  14. […] been revising and correcting typos plus adding new content to that Configuring Mac OS for the Oracle 10g Client blog page. It’s more complete now. It also shows you how to connect from one virtual machine to another […]

  15. Hi there,

    Great post! I was hoping maybe you’d have the solution to my problem: I have Oracle 10g installed on a XP virtual machine run by VirtualBox. Works great, but I always carry my laptop from home to work and therefore my XP machine’s IP address constantly changes. To use the Oracle client on OSX I constantly have to change my hosts file (or tnsnames.ora) since OSX (at least from the command line) doesn’t resolve NetBios names. Any idea how I could work around that?

    amorican

    January 7, 2009 at 11:36 pm

  16. Assuming you’re using a bridged network with DHCP and that you’re hostname is in the tnsnames.ora file, you should be able to list the different IP addresses in the hosts file with the same hostname value. A better solution would be NAT addressing, which would ensure the internal VM IP won’t change between boot cycles at home and work.

    Update the new blog with other questions because I don’t check this one too often.

    maclochlainn

    January 8, 2009 at 5:44 am

  17. Nice write up! Very helpful. Thank you.

    Peter

    March 28, 2009 at 7:48 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: