Forum Stats

  • 3,750,221 Users
  • 2,250,134 Discussions
  • 7,866,837 Comments

Discussions

Difference between Service Name and SID.

2

Answers

  • Psystec
    Psystec Member Posts: 5
    edited May 31, 2017 9:34AM

    Thank you for a very nice explanation.

  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited May 31, 2017 9:34AM
    Psystec wrote:Hello Fellow EarthlingsI'm a bit new when it comes to creating Oracle drivers and I was the lucky winner assigned to make an oracle driver.My driver works but, the connection string that the driver creates bypasses TNSnames, meaning its a bit of a manual string creation.Currently my driver only replaces the SID variable and does not cater for Service_Names.I know that Service Names are used in Cluster's and SID is a unique DB name usually, but beyond this is know nothing more.Questions:What is the difference between a Service Name and SID in the TNSnames? (technical answer if possible)Can I use both in the TNSnames?Kind Regards and thanks in advance.

    Here some concepts you should to understand.

    https://docs.oracle.com/cd/E11882_01/network.112/e41945/concepts.htm#NETAG002

    Read the concepts carefully and probably you can find answers to your questions.

    PsystecPsystec
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 10:58AM

    David,

    It also goes the other way - a given database instance (let's call it a SID in this case) can have multiple services associated with it, whether in single instance or RAC configurations. Yes, in RAC, it can easily be used to direct specific connections (say for a "reporting" service) to a certain instance/instances (and to move that service to another instance if one crashes), but it can also be used to do interesting things in a single instance. For example, you can set up several services on a single instance and use the Resource Manager to set up different resource limits depending on the service that was used to connect.

    J

    JuanM
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 11:07AM

    Mark,

    I'll disagree with your disagreement. In a RAC environment (where presumably, you're concerned at least in part with high availability), using a SID to connect is brittle. If an instance becomes unavailable, then the connection doesn't work. If, however, you use a service (which could be assigned to your preferred instance), you have the ability to deal with failures without having to do failover configuration at the client. Services can have preferred instances as well as available instances such that if the preferred instance fails, the service will be instantiated on one of the other instances. cf https://docs.oracle.com/cd/B28359_01/rac.111/b28252/configwlm.htm

    J

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 31, 2017 3:28PM

    John, from prior post >> better yet creating a service in the database that connects to only specific instances  << so I am not sure exactly how much we disagree except that in our case we do not want some applications to automatically fail over.  If the time to restore service to an instance is short we would rather have the users wait rather than have updates to certain tables come from multiple instances.  We have other applications that we failover immediately.

    - -

    HTH -- Mark D Powell --

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 3:36PM

    Mark - my point was that services can be configured on the server side. You don't have to have "available" instances for a service. What I'm saying is that services can do anything you can do with a SID connection, don't require client-side configuration for failover/load balancing, and can do more (e.g. resource manager) - so I don't see any reason, in 2017, to be using SID for connections, whether it be RAC or single instance. I guess I was disagreeing with the specific sentence "To address the idea that you should always use the database service name with RAC I will disagree" - you do go on to explain how services can do the same thing, and I would suggest that is always the way you should be doing it (services).

  • Dude!
    Dude! Member Posts: 22,826 Blue Diamond
    edited May 31, 2017 6:44PM
    ddf_dba wrote:An SID is specific to a database, it is unique in an environment and 'points' to one, and only one, database in that environment. A service name can be associated with one or more SIDs; think about a RAC environment where each instance is uniquely named yet all can be accessed through the SERVICE_NAME:

    Would it be correct to say the SID is specific to a database instance? From what I understand, the database are just files, but it's the instance that that people connect to.

  • Unknown
    edited May 31, 2017 6:59PM
    Would it be correct to say the SID is specific to a database instance? From what I understand, the database are just files, but it's the instance that that people connect to. 

    How do you reconcile those statements with the reality of 12c?

    There is only ONE instance but multiple PDBs/databases. People connect to the a PDB/database using a service name not a SID.

    The SID can be set as an environment variable (TWO_TASK) and then you can connect to the pdb directly from a command line.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jun 1, 2017 11:11AM

    John, you have a good point especially with new environments; however, when you environment has a long history, your customers expect to use a SID and get the dear in headlights look on their face when you mention service names, then you use SID's. But I concede that service names should be what you set up and use for locally developed applications.

    - -

    HTH -- Mark D Powell --

  • Dude!
    Dude! Member Posts: 22,826 Blue Diamond
    edited Jun 1, 2017 12:47PM

    I don't think 12c multitenant makes a fundamental difference in the given context. There still is a SGA and Oracle server processes, which constitute the database Instance. Speaking of the SID in 12c multitenant, it still relates to the Instance, but in order to connect to a PDB directly, using a service name is required. How that service connects to the instance is a matter of how the instance or related server process handle the connection request. The TWO_TASK variable overwrites the ORACLE_SID and uses SQL*NET to connect. At least that's how I understand it works.

This discussion has been closed.