One Database, Two Instances

My CEO wants to have two instances access one database. These are two research projects with identical table names but different project codes. The tables will be populated by the two separate projects. Reports will be generated for the projects based on the project codes. The users that populate the tables should not be able to access the data from the other project.

I have not done this before and I'm not quite sure how to start. I guess someone has done it so I need as much help as I can get please. Even if you've not really done it, I need some suggestions based on your Oracle experience.

Ac

You can't use 2 instance for a single database unless you're implementing Oracle Parallel Server.

You can setup two database in one server and establish a database link. Have a replication for necessary updates of two database.

But from my understanding to your problem you might just need to
grant INSERT privs of the table for the users who will input the data.
And grant SELECT privs of the table who will access the data of the table.

why not create 2 databases ?
projectA and ProjectB.
Idential underlying structure, different data.

Why cann't only one database and just one instance (normal ORACLE setup - withour OPS) serve your purpose ?

Is there any special reason behind having two instances and one database ?

What Oracle version will you be using ? Is it 8i ?

Hi ac,

This smells like FGAC (=Fine Grain Access Controls) if you are on Oracle 8i.

You have to set an application context for every user logging in and only one instance is needed.

Exam cram update 8 to 8i explains the subject very well.

HTH,

Tycho

just create two schemas

Looking at your requirements, why don't you just create a different schema as pando suggested, or create a set of roles with the access privileges and grant those roles based upon the user requirements.

project-A.schema
project-B.schema
project-AB.schema

Create roles with insert update delete

role_IUD-AB

Grant role_IUD-AB to project-A
Grant role_IUD-AB to project-B

Now go ahead and set up the table level triggers with AFTER insert,update, delete and fire them on the project-A.schema,project-B.schema to make the changes in the project-AB.schema

Now you can give full/select privileges to the person who ever want to use this AB schema to view objects.

But remember here you would be maintaining two sets of redundant information.

-Sam

You can cretae standby database on same server iwth different SID

Quote:

---

Originally posted by aaf207
You can cretae standby database on same server iwth different SID

---

I wonder how standby could be a solution to this problem

Sam

if I had to create one instance per project I would had to create 30 isnatnces in the first shop I was, just create one schema per project and you are set, standby database hmmm I wonder too! It's for HA nothing more!

Yes, I don't think Standby would be relevant to this case.

Personally I'd look at seperate schmea in the one database. Make managing the database a heck of alot simpler. I'd expect the Buffer Cache and Library Cache to take a bit of a hit becasue of the differences in data and queries though.

Cheers,

Thank you all for the suggestions. I've suggested two schemas and one db. to the CEO and we're considering that option. Any other suggestions will be highly appreciated.

I believe that is your best option.
good luck