Oracle database connections - what are all the fields I need to fill in?

Question

I'm used to using SQL Server and I'm now faced with connecting to Oracle. Can I get some completely unambiguous descriptions for what all the fields I need to fill in are?

Specifically, I want to understand what these are:

Home
Hostname
Port
SID
Service name

Network Alias
Connect identifier

My basic understanding is that hostname is the pooter it's sat on, but is this different to home? Port is the TCP port and it defaults to 1521 - that seems pretty clear.

I only need to provide either SID or service name? And what's the difference - why one or the other?

If I have a TNS file, what is the network alias and connect identifier? Are these in anyway the same as the other fields I have if I don't use a TNS file?

Sorry to be such a noob, but my preliminary searching to get answers still has me very confused.

Thanks!

Answer 1

If you are using Oracle 10g or above, use Easy Connect syntax:

//servername/dbname

, as in:

sqlplus scott/tiger@//servername/dbname

If you need to use TNS, here's the example of TNSNAMES.ORA:

XE = 
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XE)
    )
  )

In CONNECT_DATA, you can use either SERVICE_NAME (which is an identifier for an instance registered with a listener), or SID (which is a database identifier).

In a couple of words:

• SERVICE_NAME is an identifier of an instance: a running Oracle executable which you will connect to
• SID is an identifier of a database: a set of files your data are stored in.

One database can be used by multiple Oracle instances.

When in doubt use SERVICE_NAME.

In this case, connect as following:

sqlplus scott/tiger@XE

Answer 2

Home:     ORACLE_HOME, an environment variable that points to the location 
of the Oracle binaries (either location Instance runs from on server or client
runs from on client)
Hostname: name of the server
Port:     Port on which the Listener is listening for Oracle connections
SID:      **S**ervice **ID**entifier.  The name of the the Database.  This is
one of the identifiers that the Listener will expose
Service name: An alternate identifier that may be exposed by the Listener

On an existing, configured site the easiest way to find these details is from a tnsnames.ora file on a client (or server) from which connections can be made to the Database. Look under $ORACLE(underscore)HOME/network/admin. Find the ORACLE(underscore)HOME with a set (Windows) or env (Unix) command. The tnsnames.ora might also be in a location pointed to by the variable $TNS_ADMIN.

If a tnsnames.ora cannot be found and you have access to the server try the following command, generally as user Oracle

lsnrctl status

lsnrctl is the Listener. Status will show SIDs and Service Names it knows of (and a couple of other details)

Or find the files listener.ora and sqlnet.ora under $ORACLE_HOME/network/admin or in the location pointed to by the env variable $TNS_ADMIN

Generally there will be one listener per host, therefore one Port per host (there could be more but its not common)

To connect to Oracle you have to point the client to the Listener at a location specified by a Hostname/Port combination and tell it which SID or Service to connect too.

DCookie's comment about SID versus Service name is essentially correct.

The SID can be found with (depending on version)

select db_unique_name from v$database
   or
select db_name from v$database

Or by looking in the file $ORACLE_HOME/dbs/init(SID NAME).ora or by doing

ps -ef | grep pmon

and noting the last part of the process name, e.g. ora(underscore)pmon(underscore)SID