Re: Forming an SQL*Plus connect string?

On Fri, 13 Jan 2006 07:38:10 -0800, laredotornado wrote:

>
> How do I use the above information to build a host connect string?
>

Preamble: Realize that the main objective of Oracle's Network subsystem is to take the connect string and resolve it to a target IP address, port on which to contact the listener, and name of service to request from the listener. It may use DNS to resolve host names to IP addresses.

I'll assume your client-side sqlnet.ora uses the following

     NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname)  % default record
     NAMES.DEFAULT_DOMAIN=mydomain.com

and that your database host name is mycomputer (not myserver) so that we have a clear distinction.

Answer: It depends. The connect string is possibly one of:

	userid/password_at_myserver.mydomain.com
	userid/password_at_myserver

	userid/password_at_mycomputer/myservername1


	userid/password_at_155.155.122.122/myservername1

where

the first form uses the TNS alias to resolve the connection  

  because of the SQLNET.ORA it will first look in the local TNSNAMES.ORA   for an entry that has the alias 'myserver.mydomain.com' asnd use that to   determine the name of the host, the port and the service

  if that fails it will look for an Oracle Names server on your network to   attempt the same resolution.

the second form is also a TNS alias uses the same mechanisms

  but, due to the sqlnet.ora default_domain, will add '.' and   'mydomain.com' to the alias before searching tnsnames.ora and onames.

the third form is the host name variant.

  it resolves the host name 'mycomputer' to an IP address using /etc/hosts   or DNS, sends the request to the listeener on that host on port 1521,   and requests connection to service (database instance) myservername1 -   passing by the need for a TNSNAMES.ORA

the fourth form is the same as the host name variant, but skips the IP resolution step.

A variant - when using the EZConnect client you could use the URL form of the host name (userid/password@//mycomputer/myservername1). EZConnect is the 'no-install client' available as a separate download, associated with 10g, useable against 9i.

Postlude: Contrary to popular belief, the tnsnames.ora entry has an alias as it's index, not a service name as the index. Most people (often unwittingly) use the service name as the alias. SO your entry

myserver.mydomain.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 155.155.122.122)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = myservername1)
    )
)

could just as easily have been

silly_service.mydomain.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 155.155.122.122)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = myservername1)
    )
)

and the TNS Alias connect string technique would have replaced 'myserver' with 'silly_service' with absolutely no difference in operation.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***