Network Topology   «Prev  Next»

Lesson 12

Basic Net and SQL*Net Architecture Conclusion

Three Names That Refer to Remote Databases in Oracle:
When working with "remote databases" in Oracle, three distinct names are used to identify and connect to them:
  1. Database Name (ORACLE_SID)
    • The database name is the System Identifier (SID) of an Oracle database instance.
    • It uniquely identifies a running instance of an Oracle database on a server.
    • It is defined during Oracle installation and is stored in the ORACLE_SID environment variable.
    • It is typically used in local connections (e.g., export ORACLE_SID=mydb in Unix/Linux).
    • Example: 
      export ORACLE_SID=prodDB
sqlplus sys/password as sysdba
    • Limitation: The ORACLE_SID alone is not sufficient for remote access; it requires a listener and TNS configuration.
  2. Service Name (TNS Service Name)
    • A Service Name is an alias for a remote database connection and is defined in the tnsnames.ora file.
    • It maps to the database listener and provides connection details like:
      • Protocol (e.g., TCP)
      • Host/IP Address
      • Port
      • SID or Service Name
    • Used for networked client connections instead of ORACLE_SID.
    • Example tnsnames.ora entry: 
        REMOTE_DB =
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = remote_host)(PORT = 1521))
	  (CONNECT_DATA =
		(SERVICE_NAME = remote_service)
	  )
	)
    • Usage Example (SQL*Plus Connection): 
                sqlplus user/password@REMOTE_DB
    • Key Benefit: Service names enable high availability and load balancing across multiple instances (especially in Oracle RAC environments).
  3. Database Link Name (DB Link)
    • A Database Link (DB Link) is an Oracle object that allows a database to query or manipulate data on a remote database.
    • The DB Link name is a logical alias that points to the TNS service name.
    • Defined in the local database using the CREATE DATABASE LINK command.
    • Example of Creating a Database Link: 
                CREATE DATABASE LINK my_remote_db
          CONNECT TO remote_user IDENTIFIED BY remote_password
          USING 'REMOTE_DB'; -- Matches TNS Service Name
    • Usage Example: 
      SELECT * FROM employees@my_remote_db;
    • Key Benefit: Seamless querying of remote tables using normal SQL syntax.

Comparison of the Three Names
Name Purpose Where Defined? Scope Example
Database Name (ORACLE_SID) Identifies the database instance Environment Variable / OS Local prodDB
Service Name (TNS Service Name) Network alias for the database tnsnames.ora Remote REMOTE_DB
Database Link Name Logical name pointing to a remote service DBA_DB_LINKS in Oracle Remote my_remote_db

Summary
  • ORACLE_SID → Identifies a local database instance.
  • TNS Service Name → Maps to a remote database in tnsnames.ora.
  • Database Link Name → Provides a logical alias for querying a remote database via SQL.

These three naming conventions allow Oracle databases to communicate efficiently across local and remote environments.

Various components of Oracle Net

This module discussed the various components of Oracle Net. Let us review how they fit together.
Remember, there are three "names" in Net that refer to remote databases:
  1. The database name (for example, ORACLE_SID)
  2. The service name (logged in the tnsnames.ora file)
  3. The database link name (points to the service name)
The image below illustrates the complete process surrounding a remote connection.
Remote connection process
The image represents an Oracle Database Link (DB Link) Architecture, illustrating how a query from one Oracle database can retrieve data from a remote Oracle database using database links.
Key Components and Flow:
  1. SQL Query Using DB Link:
    • A query such as SELECT * FROM customer@rdb is executed in the local database.
    • The @rdb signifies that this query is using a database link to access a remote database.
  2. Oracle Dictionary (DBA_DB_LINKS):
    • The dictionary table DBA_DB_LINKS maintains database link definitions.
    • This table stores the remote database username, password, and link name.
  3. TNS (Transparent Network Substrate) Service Name:
    • The TNS Service Name is required to resolve the network location of the remote database.
    • The connection details are defined in the tnsnames.ora file.
  4. tnsnames.ora File:
    • This file maps a TNS Service Name to connection parameters like:
      • ORACLE_SID (Oracle System Identifier)
      • PROTOCOL (TCP/IP)
    • It helps the local database resolve the remote database connection.
  5. Hostname Resolution:
    • The host file or DNS is used to resolve the hostname to an IP Address.
    • If a hostname is used, the system looks it up in the host file or via a DNS query.
  6. Remote Oracle Listener:
    • The Remote Oracle Listener on the target database server listens for incoming database connection requests.
    • It accepts the connection if the credentials, service name, and network configuration match.
  7. Network Communication:
    • The connection is established using IP Address, Protocol, Oracle SID, and User Credentials.
    • The request passes through the listener, authenticates using credentials, and retrieves data from the remote database.
Summary of Oracle Network Topology Components:
Component Function
SQL Query (`SELECT * FROM customer@rdb`) Retrieves data from a remote database using a DB link.
DBA_DB_LINKS Stores the DB link definitions, including remote credentials.
TNS Service Name Maps a logical name to network connection parameters.
`tnsnames.ora` File Contains the TNS Service Name and database connection parameters.
Host File / DNS Resolution Resolves the hostname to an IP address.
Remote Oracle Listener Listens for incoming database connections and processes them.
Network Connection (IP, Protocol, Credentials) Establishes communication between local and remote Oracle databases.
This diagram effectively illustrates how "database links" work in an Oracle network environment to enable cross-database communication. Remote connection process

Distributed SQL

You begin by issuing the distributed SQL, which produces the following:
  1. The DBA_DB_LINKS view is interrogated (using the link name) to get the user ID and password.
  2. The tnsnames.ora file uses the service name to look up:
    1. Port number
    2. Host name
    3. Database SID name
  3. The hosts file on the server then gathers the IP address (using the host name from tnsnames.ora) for the remote host.

  • Remote Database
    At this point we have everything we need to connect successfully to the remote database. Net then establishes the network connection, and the remote listener creates a server process. The server process establishes the connection and signs on to Oracle with our user ID and password. The next module discusses how to configure a Net client and server, then examines Net's internal details.

Oracle sqlNet Architecture - Quiz

Before moving on to the next module, click the Quiz link below to check your mastery of basic Net and SQL*Net architecture.
Oracle sqlNet Architecture - Quiz

SEMrush Software