Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am in an enterprise environment where we have applications that need to run against multiple Oracle databases. Developers may need to manage multiple vintages of these databases to support different test data or diagnose bugs against different versions of the code.

Right now, we have a limited set of test environments set up on "real" Oracle servers within the data center. We juggle these among development and QA groups and there is a lot of conflicts and inefficiencies that arise because of it.

I am taking a look at Oracle Express Edition which would allow me to spin up a local Oracle database. This is similar to the workflow I most often see with SQL Server. Devs work on their location machine until they are ready to integration and then they push their DB changes to integration / QA environments.

However, from what I read it seems that Oracle XE only supports one database instance at a time. So if I have an application that utilizes two different databases, I can't have both of them running on my local machine. Is that correct?

Does Oracle Standard or Personal editions get around this limitation? If I had one of those installed locally, how difficult would it be to get multiple databases working on the same development machine? How do dev shops handle developing against Oracle where they need to be using several different Oracle instances for their applications?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

You are correct that XE is limited to one database per server. Technically, you should be able to run multiple VMs on your development box with different XE databases installed on each one and connect to those databases from your application. Of course, both databases would be limited by the other restrictions on XE (1 CPU core, 11 GB of data assuming we're talking about Oracle 11g XE, 1 GB of RAM). I would have to spend some quality time looking through the license to determine whether this runs afoul of the licensing terms for XE-- on the other hand, it's probably not something Oracle is going to care about assuming that the rest of your licensing is kosher.

Both the standard and personal editions of Oracle will allow you to install multiple databases on a single machine. The process isn't too difficult. You'll need to determine whether you want the databases to be run from the same Oracle Home (in which case they'll share the same executables, they'll be patched together, etc.) or whether you want them to be installed in separate Oracle Homes (in which case they'll use different executables, they can be upgraded and patched independently, etc.). Then it's just a matter of doing two installs if you want separate Oracle Homes or running the DBCA (Database Creation Assistant) twice if you want a single Oracle Home with multiple databases.

Since it sounds like you are coming from a SQL Server background, are you sure you are using the term "database" in the Oracle sense rather than in the SQL Server sense? It's pretty rare in Oracle to be developing applications that access multiple different databases. It is much more common to be developing applications that access multiple schemas in the same database. An Oracle schema is roughly equivalent to a SQL Server database. So it would be much more common in Oracle to simply have multiple schemas in a single Oracle database.

share|improve this answer
    
Thanks Justin. What is the difference between Personal and Standard editions? And yes, we have individual .NET applications that access multiple, distinct Oracle servers (and therefore separate databases). In retrospect, this may have been a bad idea, but we are where we are. –  RationalGeek Jun 26 '12 at 19:50
    
@jkohlhepp - Personal is licensed to be used by a single user (i.e. each developer needs their own person license). The standard edition is something that can be deployed to a server and accessed by multiple people either via named user or CPU licensing. The personal edition includes all the enterprise edition functionality, the standard edition doesn't include some of the more advanced enterprise edition features but it generally compares favorably with the enterprise edition of SQL Server. –  Justin Cave Jun 26 '12 at 19:54
    
Nevermind I think I figured out the difference. Personal is intended to be a one user license running on a single machine. Standard is for a more "production" situation with multiple users / processes etc. accessing the server. So if I wanted to support multiple instances on dev machines, it seems like I would aim at using Personal Edition. –  RationalGeek Jun 26 '12 at 19:59
add comment

We ran into a similar issue at my place of work. We have the standard Development, QA, Production setup. Every developer developed against the same database Dev, and for 80% of the time, all is well. We really ran into problems when we wanted to add integration testing. How could we be sure the data would be correct without wiping out entire tables? The answer we settled on was using VMs as @Justin Cave suggests.

Every developer on my team is running on Windows 7 Pro, which means we all have XP mode, which deals with some pesky licensing that can come up with running VMs (unless you are running Linux). Oracle Express 11G for Windows is still 32-bit, so setting it up in XP mode wasn't a big deal. Here are a couple of lessons we learned to help you.

  • Setup a common .vhd file everyone can copy from a file server. The .vhd file will contain the instance of Oracle and the base database structure and static data (countries, states, etc.)
  • Whenever a data structure change is made to an environment such as Dev you can use SQL Developer 3.1 (or higher) database diff tool to determine the differences in structure. Update the .vhd locally and copy it out to the shared location.
  • Connecting to Oracle Express through a VM is not as straight-forward as one would think, at least with Virtual PC and XP mode. You have to install Microsoft's Loopback adapter on the host and vm. Once installed you can select the loopback adapter as the network connection for the VM. You will need to setup the adapters on each machine. For example the IP address on the host is 192.168.1.1 while the IP address on the VM is 192.168.1.2. Instructions to install the Loopback adapter can be found here.
  • I haven't noticed a big difference between running natively vs on a VM. The only thing is make sure you have set the memory for the VM to at least 1.5 GB prior to installing Oracle Express 11G. Oracle's setup has a funny quirk where it will set Oracle's max memory limit to 100 MB instead of 1 GB, which caused a huge performance drop (queries that took a second suddenly took 10 seconds). Changing it after install is a real pain.
share|improve this answer
    
interesting suggestions thanks. –  RationalGeek Jun 26 '12 at 22:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.