Forum Stats

  • 3,758,586 Users
  • 2,251,413 Discussions
  • 7,870,251 Comments

Discussions

2 different instances on RAC: Same Public Synonyms on same 2 schemas - Any interaction?

cayenne
cayenne Member Posts: 246
edited Feb 4, 2014 5:28PM in General Database Discussions

I'm having a senior moment.

I've not worked with public synonyms that often, so I just want to make sure I have it right.

If you have two instances:  Instance1 and Instance2

They both live on the same Oracle RAC cluster.

Intance1 and Instance2 both have a schema named FRED.  Both of them have a Public synonym THISTABLE for FRED.THISTABLE

Now, I just want to make sure that public synonyms do NOT connect between two different instances, even if on the same RAC system, right?

Someone on Instance2 inserting into fred.thistable is ONLY for that instance, not affecting instance1 at all, correct?

To do anything like this, you'd basically need a database link between them, and could possibly have a public THISTABLE link on Instance2 pointing to FRED.THISTABLE on Instance1...but just wanting to make sure that I understand that only a dblink between the two schemas on the two instances will allow traffic/access between the two instances...and that public synonyms on their own will not transverse the instance gap, even with both instances being on the same cluster.

Again, senior moment here, and just wanting to make sure I have it straight.

Thanks in advance,

cayenne

Tagged:

Best Answer

  • Brian Bontrager
    Brian Bontrager Member Posts: 767
    edited Feb 4, 2014 2:31PM Accepted Answer
    That if I set up the same schema FRED and same table name FRED.TABLETHIS on database instance instsqa....there is no connection between the two with that public synonym on instdev. The only way you could link between the two instances and schemas to the table(s) would be through a database link...correct?
    
    

    Yep. You've got it right.

    Take RAC out of it.  It is the same as if you had 2 single-instance databases names instdev and instqa.

    Message was edited by: Brian Bontrager

Answers

  • We need to clarify the term "instance".

    In standard terminology, A RAC cluster is made of multiple instances (memory structures and processes on a given host) accessing a single shared database (tables, procedures, synonyms, PUBLIC namespace).  If Instance ORCL1 is running on node HOST1 and ORCL2 is running on HOST2 both share the same PUBLIC synonyms.  Creating a public synonym (or any database object) in one instance creates it in the shared database, and is immediately available to all instances in the cluster (both ORCL1 and ORCL2).

    In RAC there is no database link between instances.  They share the database and have an interconnect behind the scenes.

    Since you mention a link it sounds like a configuration I have seen used where multiple RAC databases run on a single cluster of servers.  For example, instances PROD1 and PROD2 access their shared database and instances QA1 and QA2 access a different database, shared between them. 

    When you say "instance" do you mean any of PROD1,PROD2,QA1,QA2 or do you mean one of the RAC databases shared by either PROD1/PROD2 or QA1/QA2?

    PROD1/PROD2 have their own public synonyms.  QA1/QA2 have their own public synonyms.  PROD and QA do not know about each other's public synonyms. PROD1 and PROD2 share public synonyms.

    Brian Bontrager
  • cayenne
    cayenne Member Posts: 246
    edited Feb 4, 2014 2:16PM

    Ok, one cluster, 4 nodes running.

    Each instance (renaming to instdev and instsqa) each have their own SGA and PGA, each instance runs across all 4 nodes (instdev1, instdev2, instdev3, instdev4 running along side instsqa1, instsqa2, instsqa3, instsqa4....as they are accessed  on each node).

    So, yes, this is the case of multiple database instances running across all nodes of the cluster.

    So, I was asking if instdev  schema FRED has table  FRED.TABLETHIS...and there is a public synonym on it for TABLETHIS.

    That if I set up the same schema FRED and same table name FRED.TABLETHIS on database instance instsqa....there is no connection between the two with that public synonym on instdev. The only way you could link between the two instances and schemas to the table(s) would be through a database link...correct?

    I believe this is what you said to me with this:

    "PROD and QA do not know about each other's public synonyms. PROD1 and PROD2 share public synonyms."

    Your PROD and QA are basically what I"m talking about with instdev and instsqa....

    Thank you,

    C

  • Brian Bontrager
    Brian Bontrager Member Posts: 767
    edited Feb 4, 2014 2:31PM Accepted Answer
    That if I set up the same schema FRED and same table name FRED.TABLETHIS on database instance instsqa....there is no connection between the two with that public synonym on instdev. The only way you could link between the two instances and schemas to the table(s) would be through a database link...correct?
    
    

    Yep. You've got it right.

    Take RAC out of it.  It is the same as if you had 2 single-instance databases names instdev and instqa.

    Message was edited by: Brian Bontrager

  • cayenne
    cayenne Member Posts: 246

    Ok, thank you for the confirmation.

    Again, just having a bad brain [email protected] and just could not seem to get it right in my head.

    Thank you for the prompt and helpful answer!!

    Cayenne

  • Intance1 and Instance2 both have a schema named FRED.  Both of them have a Public synonym THISTABLE for FRED.THISTABLE
    
    Now, I just want to make sure that public synonyms do NOT connect between two different instances, even if on the same RAC system, right?
    
    Someone on Instance2 inserting into fred.thistable is ONLY for that instance, not affecting instance1 at all, correct?
    
    The only way you could link between the two instances and schemas to the table(s) would be through a database link...correct?

    NO - NO - NO and NO!

    See the Database Concepts doc - it defines 'instance' and 'database' clearly so won't repeat it here:

    http://docs.oracle.com/cd/E29505_01/server.1111/e25789/startup.htm

    An 'instance' is NOT a database.

    An 'instance' does NOT have schemas, public synonyms, database links or any other such 'object'.

    An 'instance' is NOT linked to other instances using database links.

    It is a DATABASE that has objects such as schemas, public synonyms, and so on. A database CAN BE linked to other databases using database links.

    Per the concepts doc:

    A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.
    
    Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.

    . . .
    Database Instance Configurations

    You can run Oracle Database in either of the following mutually exclusive configurations:
    
      Single-instance configuration  
    A one-to-one relationship exists between the database and an instance.
    
      Oracle Real Application Clusters (Oracle RAC) configuration  
    A one-to-many relationship exists between the database and instances.
    
    
    
This discussion has been closed.