Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are currently having one Oracle instance on RAC with one database created. A new requirement came to our database team to temporarily (1-2 months) host a new application that is independent of existing one and after that we will migrate it to a new RAC.

Our technical teams discuss two options: the first suggestion is to create a new database (instance) for that application and the other is to have another schema on the same database.

From performance (how much is the overhead when creating a new instance compared to having the database on the same instance), administration (security, troubleshooting, backup, etc..) and migration perspectives, which option is better considering that we want to seamlessly migrate to the new server without complications or long outage.

share|improve this question

migrated from stackoverflow.com May 16 '13 at 4:13

This question came from our site for professional and enthusiast programmers.

    
Are you comfortable with testing on the same database as production? –  Ben May 15 '13 at 16:48
    
The other database will be in production as well –  PyQL May 15 '13 at 16:53
4  
Why would you create a second Oracle instance (= database) just for a second application? Usually a new schema is absolutely enough. –  a_horse_with_no_name May 15 '13 at 17:23

3 Answers 3

When using one database with two schemas you also share SGA structures(like library cache) and also possibly TEMP tablespace and also backups. In rare cases one application can have negative impact on other's performance. Also if you are asked to revert one schema it will be much harder to "restore" just one schema. The same also applies to patching, it's harder to request a downtime from two different customers.

If you use two schemas then definitely create separate DATA and TEMP tablespaces. (You can not have separate UNDO).

If you use two databases then you can not share RAM between them - for buffer cache. You can partially bypass this limitation by using OS level buffer cache. Simply disable direct file access. A separate database can also be easily migrated onto another machine using RMAN or simple pure scp/rsync.

share|improve this answer

It looks like a new schema in the existing database is the best option. Forget about having more instances on one server, this does not give the optimal performance and utilization. One of the biggest advantages of having schema's for different applications in one database is that Oracle Resource Control can be used to control the workload. In the ideal case, one server has one instance.

When you create a new database for this application, Oracle Resource Control can not be used so the databases are fighting each other.

share|improve this answer

It's a bit scary that you're working on a RAC database but are getting confused between databases and instances.

The database is the set of files that store the data plus the supporting configuration files, and the instance is the set of memory allocations and processes that access them. A RAC system is distinguished by having multiple instances accessing a single database.

So, you could conceivably have data for multiple systems stored in a single RAC database, but have have different applications connecting to it via different instances. That's actually not a bad way to arrange things, as you are likely to use much less inter-instance messaging to share locks and buffers.

If you want to keep the data for the new application separate from existing ones, then as others have stated it should be sufficient to use a different schema. Whether you then want to separate access to the applications by instance is a different matter.

share|improve this answer

Your Answer

 
discard

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