This content has been marked as final. Show 8 replies
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.
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
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:
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.
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)) )
We have hundreds of databases in our enterprise, and we make extensive use of the aliasing capability of the TNSNAMES.ORA entries.
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.
Senior Oracle DBA