One Database, Two Instances
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: One Database, Two Instances

  1. #1
    Join Date
    Nov 2000
    Posts
    178

    Question

    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

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    why not create 2 databases ?
    projectA and ProjectB.
    Idential underlying structure, different data.

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    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 ?
    svk

  5. #5
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Cool

    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just create two schemas

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Nov 2001
    Location
    Chicago
    Posts
    5
    You can cretae standby database on same server iwth different SID

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width