Oracle Database 12c in VM on Mac OSX

Question

I'm attempting to get a development environment up and running on my OSX laptop, creating JSPs within Eclipse, running with a Tomcat server, and connecting to an Oracle database.

Eclipse and Tomcat are playing nice together, and I can make JSPs and so forth. The hard part now is getting an Oracle database up and running on Mountain Lion, and getting Eclipse to connect to it.

After extensive Googling, I have found a number of resources:

http://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/ http://barrymcgillin.blogspot.com/2011/12/using-oracle-developer-days-virtualbox.html

(or even https://docs.google.com/document/d/1Th5MSIhS13YIJYCD8W1GLnOQEfrfov-92-He1cluTec/pub)

Following these (rather thorough) guides, I have downloaded the latest Virtual Machine from Oracle, here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Note: All three of the above tutorials use a slightly older version of the VM, which has Oracle DB 10 or 11, but the one available now is 12c. The new one also has a different version of Oracle Linux, and probably some other differences. Various usernames and passwords seem to be slightly different.

I installed VirtualBox, loaded in the VM, brought it online. I have the Network settings configured with just a NAT adapter, with the adapter type as "PCnet-FAST III", and port forwarding from 127.0.0.1 to 10.0.4.15 on port 1521 to 1521 and 2222 to 22.

Now, if I open Terminal on my host machine, I can SSH into my VM via: ssh -p 2222 oracle@localhost. I get the Terminal in the VM, and it gives me the same welcome message I get when I first boot up the VM in VirtualBox. Also, I can ping localhost or 127.0.0.1 and it responds with a variable but short lag.

Based on this I can only assume that my VM is working, and that my port forwarding (which is laid out in all 3 of the tutorials I linked to up there) is in good shape.

Now comes the part where I would want to connect Eclipse to the Oracle database that's within the VM. If you look under the first tutorial, dimitrisli sets up IntelliJ with a datasource pointing to the VM. I want to do the same within Eclipse.

I am new to Eclipse, so I looked around for exactly how to do this, and I came up with:

1) Make a new Database Driver.

I downloaded the JDBC for Oracle Database 12c, JDK 7, from Oracle, here. I stuck the .jar file in my Documents folder (in case that's wrong). Within Eclipse I made a new Database Driver, based off one of the other Oracle Driver templates, and changed the settings. Under "Driver Files" I pointed to my ojdbc7.jar file, and under "Properties" I have the following--let me know if this is wrong:

Catalog: USER
Connection URL: jdbc:oracle:thin:@localhost:1521:orcl
Database Name: pdb1
Driver Class: oracle.jdbc.OracleDriver
Password:
User ID:

I tried to use what came in the Oracle Driver template and just tweak to match my exact scenario (based on what was said in the walkthroughs and what I could divine from poking around the modern version of the VM).

2) Make a new Database Connection.

When I go to make a new Database Connection, I go to the Driver Properties area, and I can pick my custom Driver. There is also a Properties field, which I enter the following into: (some of it seems to overwrite or duplicate what I typed in when I made the Driver itself?)

SID: orcl
Host: 127.0.0.1
Port Number: 1521
User Name: hr // Gotten from one of the tutorials.  Have also tried 'system/oracle', neither works yet.
Password: oracle
Connection URL: jdbc:oracle:thin:@127.0.0.1:1521:orcl // Immutable, set by the Driver I suppose.
Catalog: User

When I click Test Connection, it hangs for a while and then I get a 'Ping failed!' error, with details:

java.sql.SQLRecoverableException: IO Error: Socket read timed out
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:298)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:81)
    at org.eclipse.datatools.enablement.internal.oracle.JDBCOracleConnectionFactory.createConnection(JDBCOracleConnectionFactory.java:27)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)
Caused by: oracle.net.ns.NetException: Socket read timed out
    at oracle.net.ns.Packet.receive(Packet.java:350)
    at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:153)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
    ... 14 more

Unfortunately, being new to Java and Eclipse and Oracle DB, I'm not quite sure what to do with this, and I have been poking around for several hours trying to determine what could be wrong. I'm hoping someone with some experience on this can point me in the right direction.

(Oh, and I'm not an inexperienced developer--just new to these tools.)

Thanks!

Answer 1

I solved my issue, after roughly 6 or 7 hours wasted.

Thanks to this post: Virtualbox "port forward" from Guest to Host

And specifically to the comment from @Nicholas which made me realize that I had deactivated my Mac's firewall, but not the firewall in the guest OS. Turning that off made everything work suddenly.

I suppose that the default configuration of the guest OS's firewall did allow for SSH on port 2222, as that worked fine even with the firewall in place, which served to mislead me into thinking that there was something wrong with my database driver or connection.

Hopefully this will benefit posterity.

---

EDIT:

As @HarpreetDawar mentions, the correct connection string to access the database is:

jdbc:oracle:thin:@localhost:1521/PDB1

The one that I was using, ending in :orcl instead of /PDB1, connects to the "container database". I don't fully understand Oracle 12c and the "pluggable database" implementation, but the rough idea is that you can have multiple databases within a single one, and you can turn them on and off by plugging/unplugging them. Hence, "PDB1", Pluggable Database 1.

If you connect to the Container, you will find that you are unable to create a new User/Schema without prefixing it with "C##", which is a tad awkward. That's because the Container is not meant to be used as a normal DB. You connect to the Container to plug/unplug the pluggable dbs (and other things, no doubt).

So if you want to connect to the Container, use a colon and the "SID" (orcl in the VM) in the connection string, and if you want to connect to a Pluggable, use a forward slash and the "Service Name" (PDB1 in the VM) in the connection string.

Anyway, this is additional data that I learned after solving my original problem.

---

One more thing! A note on port forwarding.

Let's say you now have your VM up and running, and you can connect to your Oracle DB properly, etc. But you have a friend who wants to use the DB with you? Well, that's easy. He should just use the same connection string, with your IP address instead of localhost, and the port forwarding that you set up in VirtualBox should send his connection (using port 1521) straight into your VM. Right?

Well, if you set up your Port Forwarding as From: 127.0.0.1, it won't work, because your buddy's connection is going to your external IP address. So add an additional port forwarding setting, from your external IP address to your VM. Then it will work great!

-Matt

Answer 2

I am the author of the first guide Matt Mc is quoting and taking the liberty to post my comment to his answer as a separate answer for visibility purposes.

I've initially written this guide ~ 2 years ago explaining in detail the process to get the Oracle 11gR2 installed on a Mac using VirtualBox.

Yesterday (25 Apr 14), I've upgraded the same guide outlining all the extra steps needed to get Oracle 12cR1 installed on a Mac using VirtualBox.