Skip to Main Content
  • Questions

  • connect string, sid , dbname, instance name

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ps.

Asked: July 18, 2001 - 8:39 pm UTC

Last updated: August 19, 2004 - 9:06 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

what is the difference between connect string, sid , dbname, instance name?

can I have multiple connect strings for the same database..

what is the relation between sqlnet.ora and tnsnames.ora?

what is the meaning of the parameters..

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)


and

where does v$instance get its value for instance_name from..

and

what is the difference mts and dedicated server..

thats all for now..

more networking questions later..

and Tom said...

A connect string is simply a user defined alias. It is an alias to a longer connect string that tells the software how to connect over the network to a database.

A sid is the Site Identifier. It, plus the oracle_home, uniquely identify a database instance on a single machine. If you are on the machine with the database, you only need the oracle_sid and oracle_home to get connected directly.

A dbname is the name of the database, it typically is not relevant.

The instance name is generally the sid.


You can definitely have dozens of connect strings for the same database. They could specify the same or different connect time options.



SQLNET.AUTHENTICATION_SERVICES= (NTS)
enabled OS authentication on windows.


NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
says when you get: scott/tiger@db

use the local tnsnames.ora file to try and resolve db, if not found, then use Oracle Names to resolve it, if not found, then using hostname naming to resolve it (that involves connecting to a machine named DB on the default port 1521)

The instance name is the SID


for mts/dedicated see
</code> http://docs.oracle.com/cd/A81042_01/DOC/network.816/a76933/concepts.htm#1011542 <code>
...


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 06, 2003 - 3:32 pm UTC

Can you explain once again about instance name and database name? Can they be different? Can you please explain this with an example?

Tom Kyte
February 06, 2003 - 4:34 pm UTC

Yes, an instance name could be different -- an instance is just the set of processes -- the dbwr, lgwr, etc and some memory.

A database -- a set of files.


A single database could have many instances mounting it all at the same time -- that is called RAC or OPS (real application clusters, oracle parallel server).


Here -- I just exported my Oracle sid as "notOra920" instead of my normal "ora920" and copied the init files:


[ora920@tkyte-pc-isdn dbs]$ export ORACLE_SID=notOra920
[ora920@tkyte-pc-isdn dbs]$ cp orapwora920 orapwnotOra920
[ora920@tkyte-pc-isdn dbs]$ cp spfileora920.ora spfilenotOra920.ora

Now I startup

[ora920@tkyte-pc-isdn dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 16:34:21 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area 303108296 bytes
Fixed Size 450760 bytes
Variable Size 134217728 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> select instance_name from v$instance;

INSTANCE_NAME
----------------
notOra920

idle> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ora920
idle>

and my instance name is notOra920 (just used to find files to startup) whilst my db_name (burned into the datafiles) is ora920



A reader, February 07, 2003 - 12:32 pm UTC

Interesting. This is what I understood: (Please correct me if I am wrong.)

So when someone talks about renaming an instance, that is just setting a new name to the ORACLE_SID. Right? In the above example, even if you dont copy the init files, still you can open the instance by giving the pfile parameter at startup. So in short, the renaming of an instance is just setting a new name to the memory areas and processes and mounting the same old database. (Seems like a meeningless excercise unless under OPS.). I think most of the time when people talk about renaming an instance, they mean renaming both the instance and the database.

Thank you for time.

Tom Kyte
February 07, 2003 - 1:32 pm UTC

I cannot say what someone means by "renaming an instance" since the term is bandied about by so many people to mean so many different things.

They *probably* mean a database, not an instance.

You see -- I didn't really "rename" my instance -- you cannot rename an instance. An instance is a set of processes and memory. When you shutdown -- that INSTANCE goes away, never to exist EVER again. An instance is transient. shutdown and its gone forever.

OK

Gerhard, April 26, 2004 - 7:18 am UTC

Dear Tom,
How to switch between instances of a database while working
with a current instance?Also say some useful points about
".oraenv".Is there any equivalent environment variable in
WINDOWS?
Please do reply.
Bye!


Tom Kyte
April 26, 2004 - 7:44 am UTC

connect scott/tiger@database1
connect scott/tiger@database2

??? you are connected to an instance at a time, in order to switch - you must logout/login.

i don't use a ".oraenv", that's just a script to set an oracle home/sid and such. the ohsel.exe (oracle home selector) can be used on windows.

Srinivas, August 19, 2004 - 9:06 pm UTC

I would like to see some more elaboration on these concepts as these are very important ones which are not explained clearly in Oracle's docs..

Thanks,
Srinivas.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.