Multiple instances or multiple users? Please help! - Forms

Dear all,
I am going to develop two systems on one database server. So which one is better? Using two instances or two owners? In term of performance, ease of management, backup....etc., thanks. Besides, does it require more resource to create two instances?

Before making this decission I will consider many things. Like size of each application, number of users using application 'A' and application 'B' and 'A and B', availability of each application, etc. From backup, recovery, scalability point and security of view it is easier to have two instences but if each applications have only a few tables then you are better-off using one instance with two separate schemas.
If applications are fairly big then I will highly recommend using separate instences it will enable you to choose distinct optimization, backup/recovery plan as well.


how can i do this

Basically i have two DB Production Servers (one is 100 g "400 users "and other one is 50g "400 users) and i want to make a single DB Server.I want to know what issue r involve and what problem i will face.
1)You need to export the smallest of the two sy 'B' (in terms of size) and import into the other database 'A'.
2) Shutdown database B
3) Make sure that all the applications are diverted to database A. 
I hope the schema names are unique? 
Yes Schema name is unique. 
1)You need to export the smallest of the two sy 'B' (in terms of size) and import into the other database 'A'.No, I don't think you can make that generalisation (especially given the lack of information in the original post).
Can the OP use export/import here?
We can't say. If they are talking about two entirely distinct applications then yes, it could be an option. But if they are talking about two databases running the same (or similar) applications then they must get advice from the application vendor and/or their developers on the best way to merge the data from the two environments - simply exporting the data from one and importing it into another will likely cause some application problems.
Should they concentrate on migrating the smallest data set into the larger?
Maybe, maybe not - but the volume of data to be migrated shouldn't be the driving factor. I'd be more inclined to look at which of the two servers is best suited to deliver the end product. Is one of them a better specification? Is one of them in a better physical location (e.g. if you have two datacenters 1000 miles apart and one of these servers is in each, which is the better choice to serve all of the users)? Do they both have the physical storage required to host both sets of data (it may be that the 50GB database is on a server with 1TB free where the 100GB database is on a server with only 10GB free)?
Other things that need to be considered (and this isn't an exhaustive list by any means):
You'd be doubling the number of users - are you also doubling the number of concurrent active sessions? If so then you need to account for the increased workload. Are your SGA and PGA going to need to be resized? You are going to have to look at any limitations you have (such as the 'processes' initialisation parameter) which could prevent these connections. If you are using UNIX/LINUX, are you going to have to modify any kernel parameters? Do you have sufficient UNDO/Rollback for the new workload? Are your redo logs (and your log buffer) going to need to be resized?
More generally - You don't say whether these databases are running on the same OS or even the same Database versions. If they aren't then it could impact both your options for combining the two and also factors (such as performance) afterwards.
Do both of the databases have all of the required options installed? If you need Oracle JVM in one database but Oracle Text in the other then you need to ensure that your new environment has both.
Are the two applications easily hosted in the same database? You need to find out if either has any specific requirements for initialisation parameter settings and consider whether these will impact on the other application (things like OPTIMIZER_MODE, CURSOR_SHARING, etc). You'll also need to look at any other parameters which have been changed - say as part of a tuning exercise - and understand their impact on the final solution.
Are the two applications self contained (i.e. entirely within their own schemas)? If not then you need to consider how the two will combine. What if both have a public synonym for different objects with the same name? That's not going to work too well.
Are you using standard block sizes for both? If not, what are you going to do - keep different block sizes (and undertake the admin of that scenario) or have a single block size on the new system? If the latter, is that going to impact performance?
How are your servers licensed for Oracle? If you are licensed per-user then do you need to buy some more licenses?
Like I say, not an exhaustive list - but I think the point has to be taken that this isn't a job you should rush in a weekend. Also, it's something you need to test, test and test again before you think of doing it live.
If the two DB servers serving two different applications and there's no interaction between them. I would consider two instances on one server if you plan to combine server.
Of course, we need to know what's the Oracle version and OS of these two DB. Are they identical ?
If yes, then clean shutdown DB2, copy all corresponding Oracle files (datafile, redo logfile, control files, spfiles etc.) to DB1 server. If you need to change the orginal path of these files then recreate your control file.
set ORACLE_SID and try to startup. 
I would consider two instances on one server if you plan to combine server. Hmm... yes, that would be a whole lot easier than trying to fit the two into one database. I could have typed a whole lot less if I'd thought it through ;o) 
I would consider two instances on one server if youplan to combine server.
Hmm... yes, that would be a whole lot easier than
trying to fit the two into one database. I could
have typed a whole lot less if I'd thought it through
;o)Well, OP didn't really provide much detail for us to make suggestion.
I know some DBAs out there are strongly against running multiple instances on one server. I will leave that debate open.
What your suggested is also true under certain circumstance :) 
From the start, this problem is not well defined nor constrained, there are much more factors to consider than just database size. Definitely database size and concurrent users doesn't provide any information at all about how to merge these two databases.
400 users on one database and 400 on the other could mean this that you will have 800 concurrent users on the same database or are they the same 400 concurrent users? How are those 400 concurrent users authenticated? are those application level users? database users or single sign on users?
Is there a particular reason why both databases have to be merged? Are they running the same application? this could mean a problem even bigger than merging two different applications, since this is a lot more than just a simple export/import as someone above has stated. A blind export/import could very easily at least duplicate information. Assuming it is the same application, then it, most probably, uses the same catalogs, so those could easily be duplicated, so this is a very reckless and dangerous suggestion.
You must further analyze your data, talk with application developers and determine if the application itself has some sort of logical backup that allows you to perform this task. Application developers should know better than any body how this application was built and how you could perform this merging in the safest and most convenient way.
~ Madrid 
Another points to consider is points of failure. Can both applications be unavailable at the same time? How will patching work - you now require approval for an outage by two or more customers. How will restore operations work - for example, if you need to restore one of the applications/schemas do you need to restore elsewhere and import or the entire db, do one of the applications live with potential data loss? Are they both OLTP?

Database Design - multiple schemas

We're currently designing a DB for an AUTHENTICATION SYSTEM where several users from different companies (around 40) will have to be authenticated -- connected to ORACLE. Authentication and faster recovery is important.
An issue raise where what if the schema encountered a problem then of course you have to backup the entire data. So we are considering to use multiple schemas.
One Company = One Schema
So if one schema is down then other schemas will not be affected and faster to recover.
Actualy, we're quite hesistant to use multiple schemas because of maintainability -- Managing different schemas and too much burder for our developers.
Will the idea of having multiple schemas be advantageous to what we want to achieve?
Is this a good design or any other idea to handle this kind of situation?
Can Partitioning do the same?
Thanks a lot 
Advantages of multiple schemas:
- each schema is entirely separate
- you can maintain at different times/dates for different companies
- different schemas could be on different databases / servers
- any 'shared' data may have to be duplicated (but you can always use a shared schema for reference data)
- yes, you have to maintain each schema separately (but that would be by scripts, and at least they'd be well tested!)
The dictionary (SYS tables) will be somewhat larger (40 copies of table, index, pl/sql definitions)
- you'll have 40 identical sets of SQL cached; they all look the same, but relate to different schemas. So you need a bigger SGA.
Can Partitioning do the same?No - partitioning is a solution to a physical problem, not a security problem
Is this a good design or any other idea to handle this kind of situation?I think either way works - it depends on size, number of users, are you using a third tier, etc.
Or, with a single schema, you can use VPD - virtual private database (otherwise known as FGAC - fine grained access control or RLS - row level security).
See eg and also Ask Tom which refers to the documents.
You can also implement a kind of VPD on the cheap by using user defined namespaces and the SYS_CONTEXT function, combined with application logic and clever view definitions.
Regards Nigel 
>> Can Partitioning do the same?
No - partitioning is a solution to a physical problem, not a security problem
I'm not sure he has a security problem. The multiple-schema approach seems to be for backup purposes, which I'm sure could be managed with partitions etc.
I've never liked multiple schema designs. If it was up to me I'd put everything in one schema unless there was some compelling reason to do otherwise. 
So if one schema is down then other schemas will not be affected and faster to recover.How does one schema go down?
It seems you are attempting to design for a problem that is unlikely to happen. 
As 3360 pointed out, different schemes alone doesn't make recovery much easier, unless they are stored in different tablespaces. Otherwise, I can't imagine, how one scheme could go down without affecting the others.
Can Partitioning do the same?No - partitioning is a solution to a physical problem, not a security problemWe've had similar questions on hosting crop up before. The advantage of partioning on the basis of CUSTOMER_ID is that any query for a single customer can be constrained to a partion rather than sprawling across the whole table. So it is still something to at least try, especially combined with Row Level Security.
Cheers, APC 
Following up on Martin's point, another advantage of partitioning in this scenario is, if the partions are on separate tablespaces - say one tablespace per customer - you could "lose" one customer without inconveniencing the other thirty-nine.
Cheers, APC 
Thanks for all your suggestions...
Actually, developing the application using multiple schemas is one of the thing we have to consider. Our developers are not the optimistic in using multiple schemas. We are using now JAVA/JSF/Hibernate on a Spring Framework for our applications.
However, our main objective in doing multiple schemas is to minimize downtime in case of problems (faster recovery) and not to affect the other companies so that authentication can still be used. We have 25,000 users right now from 40 different companies who uses are authentication system.
Btw, there are no share information between companies. Each company has its own set of policies. Thus one company to one schema maybe a good method. But people are saying it's a nightmare to manage multiple schemas as well as during app development. Now, not sure now if multiple schemas is the right choice.
How's application development when multiple schema is used? is it really difficult?
It's really hard to decide whether to multiple schemas or just a single schema...
What sort of "problems" are you anticipating having to work around? For database or hardware issues, you're generally better off adding redundancy that benefits the entire database (i.e. a standby database, a RAC cluster, better hardware). If your server fails, for example, that's going to fry everybody. If you are concerned about the failure of a single file not part of the SYSTEM tablespace, partitioning by company would be an option.
Unless individual companies have customized schemas or have the need to upgrade at different times, I would strongly advise against separate schemas. If you have 40 companies, upgrading or maintaining the system would be much more painful if every script had to be run 40 times in each company schema. Every test would have to be run 40 times to make sure every schema was upgraded correctly. DBAs would have 40 times the objects to maintain, possibly 40 times the tablespaces, etc.
It would add more pain, this is what I can smell at that moment. I would recommened not to go for 40 different schemas, when this can be easily
done with one.You need to see the broder picture, As this already suggested
by oracle gurus.
hare krishna
Our developers are not the optimistic in using multiple schemas. We are using now
JAVA/JSF/Hibernate on a Spring Framework for our applications.I wouldn't be optimistic either if that's how you're building the application.
Good luck.
For one more rather serious drawback of multiple schemas in onwards - read carefully this Oracle-L thread:
Regards Nigel 
I'll be weighing the advantages and disadvantages but so far it looks like I'll be sticking with a single schema... Thanks for the replies.

problem in 10g

i have a database running on my server for a single organisation,what should i do if i have to do for it multiple organisation?????? is it better if i use multiple schemas or multiple instances...... which is a good option??????? 
Multiple instances. You can go with multiple schemas, but in that case you will have multiple applications accessing a single db. In case of an incomplete recovery, you would end up losing some data for all schemas, while at the same time, no applications can be used. 
Is there any commonality between your "organizations"?
Same applications? Same tables? Obviously the data is different, but what else separates them?
If you have only 1 machine (depending on its capabilities and your requirements) running multiple instances can be quite a drain. 
yes, same apllication and same tables...... 
If I were you, I would find answers to the following.
1. What are inter-dependencies among these various applications?
2. What are the availability requirements of these applications?
3. What are the backup/recovery requirements of these applications?
4. If single instance - managing patches is lot easier but multi instance, depending on availability needs could pose a problem.
5. As Eric mentioned if you can find some commonality among the applications you could go for multiple schemas, else multiple instances are going to cause excessive overhead in terms of CPU (processes) and memory requirements slowing down response times for all applications.
and there would be some other considerations as to size of DB/Schema for each application, concurrency for each application, and how you would size multiple instances on a single box or multiple boxes, etc.
Also you would need to find out if any of these applications need to access data related to other applications (messaging across applications)
Hope this gets you started 
Same application, same tables.. hmmm
I would consider single schema, single DB (possibly multiple instances in RAC environment)
To isolate multiple organizations you may want to look into Fine Grained access control/security or try virtual private db (VPD).
just a thought. 
My first option is to go with Multiple "Databases" if the organisation are not related and do not need to be in one database.
If they are related and must share data or you cannot afford a separate database, then for mangeability and recoverabilty purposes, ensure that each schema has its own tablespace and tables and indexes within these schemas are grouped into related unit areas and assigned separate tablespaces. This will allow you to take an organisation's or unit area's tablespace offline to manage or recover without interrupting the others. 
yes, same apllication and same tables...... In that case, I might not use multiple instances or multiple schemas. Put all the data in your existing tables and add an organization_id to separate them logically.
This is how Oracle Applications (11i) does it. For a given table, all rows for all organizations are in the same table and the table has an org_id column. When a user signs on, they are only allowed to access the rows for their organization.
But like user551318 suggested, there are many things you have to take into consideration (things we don't know about). 
I would keep the one database, and separate out the organizations within their own schemas. 
Dup Multiple instances for a single database 
your case is similar like my company so my dear friend for that what i did is multiple instance is not good practise because the resource will be shared. like you have 4gb ram and for a min we assume you set 1.5 gb , 1.5 gb sga for both instances means you have more than 70% utilize ur ram for instances sgas so it will be a bad performance you will get. i hope you understand the issue.
ok now about your actual issue what i did due to security and audit purposes its not possible data recite in same scehma. so its better schema should be separate but Tablespaces must be separate.
its easy way of doing rest there are some other options which are relevant to advance feature like some one adviced about FGA ......

2 Applications pointing to single Database

I faced one question in Interview
Can 2 Applications point to single Database?
Can anybody describe is it possble or not? 
If by "pointing" you mean connect to a single database, then the answer is - of course they can. Although it barely can be considered best practice. 
Yes..both aplications will connect to single database..and uses same tables...will insert recods,delete and update..everything they want they can do...
is it really possible 
You 'may' only face a problem when you rebuild the index for the two application if they where containing big indexes. 
Yes they can,
Applications are mere clients wrt the database.
Just like you have multiple sessions connecting to one database.
So, can two different applications point to a single database?
yes but is it a feasible solution?
Message was edited by:
Its as good as mulitple users accessing a single application. There is hardly a difference.
Amardeep Sidhu 
If two different applications access the same schema and execute DML against the same table(s), I presume that there would be some way to identify which rows belong to which application ?
You wouldn't want one application deleting the other application's data.
You might want to later migrate an application to another schema or database and you will need to identify the rows that need to be migrated out.
One application might report "logical data corruption/inconsistency" [user deleted the wrong records] and you would need to be able to identify which rows to reinsert from a backup- restore. 
The database doesn't know from 'applications'. The database has tables (and other objects) and receives and services requests from client processes. Those client processes can be any program written in any language with the ability to place calls to the database. It (the db) doesn't know or care if MYAPP.EXE is a key component of an HR application and YOURAPP.EXE is a key component of a warehouse scheduling application, and this particular connection from sqlplus is running a PL/SQL script on behalf of the departmental Christmas Party Committee.
I wouldn't even go so far as the others as to say it is necessarily bad practice for two apps to share a database -- at least not on their descriptions of data corruption, etc. That is all a matter of design. If my db is hosting data for an HR app and a warehouse app, does that mean that my HR app is going to be willy-nilly wandering into the warehouse data? No, it is a client process requesting services on certain tables, just as is the warehouse app. Now, if the developers designed both apps to be shareing tables that were nothing more than amorphous data dumps, then you've got some real problems.
But to recap ... the datatabase does not know from 'applications'. 
Depends what you mean by two applications,
Two copies of the same application for different clients/departments are better sperated into two seperate schemas, you could potentially use VPD to seperate application data but it depends on your application what is appropriate.
Two different applications acessing the dame data isn't uncommon, an OLTP, database may also be used to generate reports, and have a different appication connecting for adminstrative access.
Two completely different applications (e.g. the HR database and the marketing/sales database) can happliy co-exist in seperate schemas, and there are tools available to manage the database e.g. resource manager.
a little more detail on what you are trying to achieve might help.. 
Yes 2 Same applications pointing to single database using same tables and indexes...insert data into same tables.....

DBs, Schemas and tables.

I have a set of very similar applications, all of the using the exact same set of tables, running now on different instances of DBs on the same server. The data for every application needs to be kept independent from each other.
I was wondering what would be the pros and cons of moving all the applications tables into the same DB instance, but on different schemas each. Would it be worth to have every schema in a different tablespace?
These apps are not very transaction intensive, and they're running Oracle 10g, on a Dual Xeon 3ghz server with 2Gb ram. The datafiles are in two RAID 0+1 disk arrays.
Thanks in advance. 
I have done a similar consolidation. Without much more information I can only tell you what we did, this may or may not be valid in your situation.
We took each individual database, added a column to every single table called APP_NAME and populated that column with the logical name of that app.
Then we created the necessary tables in a new oracle database and made the app_name part of every primary key or at least part of the necessary indexes and not null.
When then loaded each individual app into this new consolidated DB ensuring that the APP_NAME column was properly populated.
As our apps run with oracle DB users all accessing the central schema, we simply created individual schema's and made views that look into the new consolidated schema limited by the APP_NAME. Fine grained access in oracle will probably take care of doing that for you too.
We now have 11 schema's all with views into the new consolidated schema. If for some reason one of the 11 schema's had to have its own table then that table was contained in its own schema and not the consolidated schema.
Administration time dropped considerably, tuning is easier and we now have one SID where we used to have 11.
Now, the only problem is if we need down time (which is very rare) all 11 apps have to go down instead of just one at a time for patching or the like. 
>>I was wondering what would be the pros and cons of moving all the applications tables into the same DB instance, but on different schemas each. Would it be worth to have every schema in a different tablespace?
Maybe is interesting instead of to have many databases on the same server, but maybe it depends of the business, technical and operational requirements of your company.
Se if this thread is useful to you:
Re: Copy of Database 10G
jbirk, thanks for your answer, altought is not exactly what I was looking for.
I don't need to consolidate the DBs, data must be kept independent. What I'm looking forward to is, for example, App A will have schema A, App B will have schema B, and so on, each with its own set of tables with different data in it.
However, some of your information is useful to me, now I see that you minimized admin time, easier tuning, and a single SID. That's good.
But what about improvements memory and/or processor-wise? Is this way more efficient?
Here is another thread that discusses something similar.
Multiple Oracle DB, Different versions
Having single instance over multiple instances sure will help you reduce number of processes/memory foot print in addition to ease of administering single instance vs multiple.
Keeping tables/indexes of each schema in their own tablespaces will help you get better throughput (better spread of IO and ease of administration here as well by logically separating the objects). 
Its difficult to be certain without the specifics, but it general its a good idea. Some things to look at:
What's the size of the schemas compared with the size of the database as a whole? Assuming you only need to add the new data into one of the existing databases, you could completely lose 2 dbs, which is nice to have.
Do these applications have multiple users? Do they handle this within the schema or do they use Oracle users? If they use Oracle users, then you need to handle this by having more capabilities/roles per user?
Upgrades to Oracle? To what extent do they keep up with Oracle releases? What if you ever need to upgrade one application, but cant upgrade the other? You'd have to split them again. 
It was dramatically more effecient. We had the 11 apps running spread across 3 servers, each with their own ram, power etc. Now we have 1 server, the best of the original 3 running all 11 apps and the throughput to the users is actually faster now than it was before. The apps are pure OLTP with the longest transactions being well under a second. 
Well, thank you so much to everyone. I have a much clearer view now, so I'll go the way of a single DB.