8 Replies Latest reply: Apr 30, 2009 1:36 PM by sybrand_b RSS

    Oracle SID & SERVICE_NAME

    192884
      What is the difference between Oracle SID & SERVICE_NAME ?
      and which one is better to use in tnsnames.ora and why ?
        • 1. Re: Oracle SID & SERVICE_NAME
          429812
          As per Oracle Glossary : SID is a unique name for an Oracle database instance. ---> To switch between Oracle databases, users must specify the desired SID <---. The SID is included in the CONNECT DATA parts of the connect descriptors in a TNSNAMES.ORA file, and in the definition of the network listener in the LISTENER.ORA file. Also known as System ID.
          Oracle Service Name may be anything descriptive like "MyOracleServiceORCL". In Windows, You can your Service Name running as a service under Windows Services.
          You should use SID in TNSNAMES.ORA as a better approach.
          • 2. Re: Oracle SID & SERVICE_NAME . Also ...
            429812
            Forgot to add that:
            service name is
            The name used to identify a Oracle Net server process. The services file on client and server machines maps each service name to a port number and protocol.

            • 3. Re: Oracle SID & SERVICE_NAME . Also ...
              rahulras
              Hi,

              What is global name ?
              How are where is the domain name is used/required ?

              Please explain.
              • 4. Re: Oracle SID & SERVICE_NAME . Also ...
                thp4362
                Global name is set up in your init.ora file. It can be changed later if you didn't set it up, you can search the oracle docs for that. But it's much better if you plan for it in the beginning; it's tricky to change an existing instance's global name. If you need to do this, first try it out on an old computer, then do it in your production environment.

                Global name is used for remote database links. If you don't set up a global name, your database will be known as ORACLE.WORLD . This will cause problems for people that remote link their databases into yours. So when setting up an Oracle instance, you should always set your global name to include your company's domain. Example, instead of saying oracledb for your global name, use oracledb.mycompany.com .

                Even if you don't allow other companies to remote link to your databases, what if you install a second Oracle server? Both servers will think they're ORACLE.WORLD. So you might want to set up your global name to be something like oracledb1.mycompany.com , or, oracle-dev.mycompany.com and oracle-prod.mycompany.com ... or accounting.mycompany.com versus datawarehouse.mycompany.com .. etc etc

                -Thomas
                • 5. Re: Oracle SID & SERVICE_NAME
                  Russ Cannon
                  When you use the names resolution service TNSNAMES, you always use both a service name and a SID in each entry of the client's TNSNAMES.ORA file. The SID is the actual name of an instance as recorded in the INSTANCE_NAME column of the instance's V$INSTANCE view and as specified in the instance's entry in the server's LISTENER.ORA file. The service name is an alias for the instance that is found only in the client's TNSNAMES.ORA file. Here is an example of a series of entries in a client's TNSNAMES.ORA file for three databases on three servers all having the same SID:
                  DEVDB.WORLD =
                   (DESCRIPTION =
                     (ADDRESS =
                        (PROTOCOL = TCP) 
                        (PORT = 1521)
                        (HOST = DEVSVR.OURCOMPANY.COM)
                     )
                     (CONNECT_DATA = (SID = OURSID))
                  )
                  TESTDB.WORLD =
                   (DESCRIPTION =
                     (ADDRESS =
                        (PROTOCOL = TCP) 
                        (PORT = 1521)
                        (HOST = TESTSVR.OURCOMPANY.COM)
                     )
                     (CONNECT_DATA = (SID = OURSID))
                  )
                  PRODDB.WORLD =
                   (DESCRIPTION =
                     (ADDRESS =
                        (PROTOCOL = TCP) 
                        (PORT = 1521)
                        (HOST = PRODSVR.OURCOMPANY.COM)
                     )
                     (CONNECT_DATA = (SID = OURSID))
                  )
                  The SID is OURSID and is the same for each of these databases, but each resides on a separate host--DEVSVR, TESTSVR, and PRODSVR. The service names are DEVDB.WORLD, TESTDB.WORLD, and PRODDB.WORLD, and each is an alias for a database on a particular server. The SIDs of all three databases are the same, however. If the server configurations are identical, it is possible to use copies of OURSID from PRODSVR in TESTSVR and DEVSVR without making any control file changes. Although the service name may be the same as the SID, it needn't be.

                  We have hundreds of databases in our enterprise, and we make extensive use of the aliasing capability of the TNSNAMES.ORA entries.

                  Kind regards,
                  Russ
                  • 6. Re: Oracle SID & SERVICE_NAME
                    VANPERSIE
                    On
                    installation can I use the same Sid ,service_name and global database name
                    to connect easily between database and developer?

                    Message was edited by:
                    VANPERSIE
                    • 7. Re: Oracle SID & SERVICE_NAME
                      653212
                      yes.
                      • 8. Re: Oracle SID & SERVICE_NAME
                        sybrand_b
                        Rahulsai,

                        It is incorrect using the SID is a better approach. The service_name functionality was implemented in sqlnet v3 (8.0 and onwards) and will supplant!!!! the SID approach. New functionality, like failover and load_balancing, only works using the Service_name syntax.
                        Please refer to the Net administrators manual to clear your concepts.

                        ---------------------------
                        Sybrand Bakker
                        Senior Oracle DBA