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?
Best Answer
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.