Oracle and connection knowledge

Oracle Database service
An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. A database can have one or more services associated with it.

Oracle connection descriptor connect descr extends ptor
To connect to a database service, clients use a connect descr extends ptor that provides the location of the database and the name of the database service.

Listener
The address portion of the connect descriptor is actually the protocol address of the listener.

Protocol address: protocol address
Much like a business address, the listener is configured to accept requests from clients at a protocol address. This address defines the protocol the listener is listening on and any other protocol specific information.

For some invocations, such as Oracle Real Application Clusters, multiple listeners on multiple nodes can be configured to handle client connection requests for the same database service.

An example of a connection descriptor in a 4-node 10gR2 RAC environment (from the $ ORACLE_HOME/network/admin/tnsnames. ora file ):

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = aif05-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif06-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif14-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aig06-vip) (PORT = 1521 ))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

For the above instance

(ADDRESS = (PROTOCOL = TCP) (HOST = aif05-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif06-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif14-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aig06-vip) (PORT = 1521 ))

It is four Protocol addresses. "Rac" in (SERVICE_NAME = rac) is the name of the database service. Therefore, by connecting the four different nodes, client connection requests can be smoothly connected to the same database service "rac.

Connect string
Connect identifier
Network service name net service name
Users initiate a connection request by providing a connect string. A connect string between des a username and password, along with a connect identifier. A connect identifier can be the connect descr extends ptor itself or a name that resolves to a connect descr extends ptor. one of the most common connect identifiers is a net service name, a simple name for a service.

Then the complete connection string instance is:

Scott/tiger @ (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = sales-server1) (PORT = 1521 ))
(CONNECT_DATA = (SERVICE_NAME = sales.us.acme.com )))

Note that the part (that is, the connection identifier) after the "@" symbol must be on the same line.

An example from the network service name of the same RAC mentioned above (from the file $ ORACLE_HOME/network/admin/tnsnames. ora ):

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = aif05-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif06-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif14-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aig06-vip) (PORT = 1521 ))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

The "RAC" on the left of the equal sign is the network service name, and the "connection descriptor" on the right of the equal sign is the connection descriptor mentioned above. The so-called connection identifier is either "RAC" or the connection descriptor on the right of the equal sign.

In practice, the two functions are the same. You can use SQL * Plus in the following method to perform a simple test.

The first connection method utilizes the network service name:

Sqlplus scott/tiger @ RAC

The second connection method utilizes the connection descriptor:

Sqlplus scott/tiger @ (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = aif05-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif06-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aif14-vip) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = aig06-vip) (PORT = 1521 ))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)