module17-network.htm; Updated July 14, 2013; Some figures in these notes are from the Oracle® Database Net Services Administrator's Guide 11g Release 2 (11.2) E10836-07

Module 17 – Oracle Network Services

Objectives

 

These notes cover Oracle network administration for an Internet environment. 

·        Familiarize with network environments, connect operations, exception operations, database identification by service name rather than SID, service resolution through naming methods, establishing a connection, and configuring a listener.

 


 

An E-Commerce Architecture

 

This figure shows a typical Internet architecture. 

·        The organization has an Intranet that connects client computers to one or more Database Servers. 

·        The client computers also connect to the Internet through an Application Web Server.

 

ch18-net1

 

Oracle Net

 

Oracle Net Services is Oracle's solution for providing enterprise wide connectivity in distributed, heterogeneous computing environments. 

·        Objective is for Oracle Net Services to make it easy to manage network configurations while maximizing performance and enabling network diagnostic capabilities when problems arise. 

·        Connectivity is provided by Oracle Net. 

o   Oracle Net is a component of Oracle Net Services and is the software that enables a connection from a client application to an Oracle database server. 

o   Oracle Net maintains the connection and exchanges messages between client and server computers. 

o   Oracle Net software is located on each computer in the network. 

o   Oracle Net is a layer of software that interfaces with the network protocol, that is, the set of rules that determine how data is subdivided and transmitted into packets on a network. 

o   Oracle Net uses the TCP/IP protocol for connectivity.

 

Oracle Net includes two components:

·        Oracle Net foundation layer establishes and maintains connections.

·        Oracle protocol support that maps the foundation layer's technology to industry-standard protocols.

 

Oracle supports Java client applications that access an Oracle database with a Java Database Connectivity (JDBC) Driver.  This is a standard Java interface to connect to a relational DBMS.  Oracle offers the following drivers:

·        JDBC OCI Driver – used for clients with Oracle client software installed.

·        JDBC Thin Driver – used for clients without an Oracle installation that use applets.

 

 

Web Client Connections Without an Application Server

 

Web clients can run programs that access Oracle databases directly without a Web Server. 

·        The .NET framework from Microsoft enables this type of connection.

o   Used to program Visual Basic ADO.NET applications to connect to Oracle.

o   Also used to program ASP.NET (active-x server pages) applications to connect to Oracle.

·        A database can accept HTTP, FTP, or WebDAV protocol connections that can connect to Oracle XML DB in an Oracle database instance.

 

The figure shows a client with a HTTP connection that connects through a web server like Apache. 

 

Description of Figure 1-3 follows

 

This figure shows a client using a Web Browser such as Internet Explorer with a JDBC Thin driver that uses a Java version of Oracle Net called JavaNet to communicate with the Oracle database server that is configured with Oracle Net.

 

 

Description of Figure 1-2 follows

 

Location Transparency

 

Many companies have more than one databases, often distributed, that support different client applications. 

 

Each database is represented in Oracle Net by one or more services. 

·        Service – identified by a service name. 

·        Client computers use the service name to identify the database to be accessed. 

·        The information about the database service and its location in the network is transparent to the client because the information needed for a connection is stored in a repository.

 

Description of Figure 1-6 follows

 

The repository is represented by one or more naming methods. 

·        Oracle Net Services offer several types of naming methods that support localized configuration on each client computer, or centralized configuration that can be accessed by all clients in the network. 

·        Easy-to-use graphical user interfaces enable you to manage data stored in the naming methods.

 


 

Naming Methods – Centralized Configuration and Management

 

One approach to establishing network connectivity is to centralize the management of a repository of service names by the use of a Directory Server as is shown in the figure below. 

·        This approach provides network administrators the ability to configure and manage the network of databases with a central facility. 

·        It authenticates database access and eliminates the need for any client and server configuration files.

 

Description of Figure 1-7 follows

 

 

Oracle Net and Oracle software are scalable meaning that an organization can maximize the use of system resources.  One way this is done is through a shared server architecture that allows many client computers to connect to a server. 

 

The shared server approach:

·        Client computers communicate their requests for data by routing requests through one or more dispatcher processes.  

·        The dispatcher process(es) will queue client requests in a common queue. 

·        When a server process becomes idle, it will select the next client to serve in the queue. 

·        Server processes are pooled and a small pool of server processes can share a large number of client computers.

 

 

The dedicated server approach:

·        One server process starts and is dedicated to each client connection until the connection is completed. 

·        This does introduce a little processing delay required to create the server process in memory. 

·        Shared server works better than dedicated server if there are a large number of connections because it reduces server memory requirements. 

 

 


 

Oracle Connection Manager

 

The Oracle Connection Manager software is another component of Oracle Net Services. 

·        It enables multiple client network sessions to be multiplexed (funneled) through a single network database connection. 

·        Reduces the resource demands needed to maintain multiple network sessions. 

·        It enables thousands of concurrent users to connect to a server.

 

Connection Pooling:

·        Enables a database server to timeout an idle web session and assign the connection to an active session. 

·        The idle session remains open and the connection can be reestablished when the session becomes active with a data request. 

·        Enables accommodating accommodates thousands of concurrent network users in a web environment.

 

Connection Manager also provides firewall protection.  A host server called an application gateway runs the Oracle Connection Manager software.

 

 

Oracle Net Listener

 

An Oracle Database Server receives initial connection information from a client through use of the Oracle Net Listener – commonly referred to as simply a listener. 

·        The listener process handles client requests and hands the request off to the appropriate server. 

·        A listener process can listen for more than one database instance. 

 

 

 

 

·        Client computers are configured with protocol addresses that enable them to send connection requests to a listener.  

·        After a connection is established, the client computer and Oracle Database Server communicate directly.

 

 

 

Database Service and Database Instance Identification

 

An Oracle database is a service to a client computer that runs on a server (In a Windows server, you can see these services quite easily through the Control Panel). 

·        A database can have more than one service associated with it although one is typical. 

·        For example, one service might be dedicated to system users accessing financial data while another one is dedicated to system users accessing warehouse data. 

·        Using more than one service can enable a DBA to allocate system resources.

 

Service Name:

·        Each database service is identified by a service name. 

·        SERVICE_NAMES init.ora parameter specifies the service name in the database’s initialization parameter file.

·        The service name defaults to a global database name when it is not specified – this is a name that comprises the database name from the DB_NAME parameter and the domain name from the DB_DOMAIN parameter.

·        The SERVICE_NAMES parameter in the initialization parameter file (init.ora) can specify more than one service entry as shown below. 

o   This enables a single instance to be identified in different ways by different client computers. 

o   This also enables a DBA to limit resource allocations for clients requesting a service.

 

SERVICE_NAMES = sobora1.siue.edu, sobora2.siue.edu

 

·        This enables using a pool of Multi-threaded service dispatchers to be used for clients requesting sobora1.siue.edu, for example, while a different dispatcher or pool of dispatchers could be configured to service sobora2.siue.edu, for example.

 

Instance Name:

·        Each database instance is identified by an instance name. 

·        INSTANCE_NAME parameter in the initialization parameter file specifies the instance name. 

·        The instance name defaults to the Oracle System Identifier (SID). 

·        This figure shows two database servers, each connected to a single database that is opened as two separate instances, each with a unique parameter file called an instance parameter file (ifile).

 

 

 

Accessing a Service

 

Client computers connect to a database service by using a connect descriptor. 

·        The connect description describes the database location and database service name. 

·        Example for a database service named sobora2.

 

DBORCL =

  (DESCRIPTION =

      (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu )(PORT = 1521)

         )

      )

      (CONNECT_DATA = (SID=DBORCL)

                      (SERICE_NAME=DBORCL.siue.edu)

      )

   )

  

·        ADDRESS= clause specifies the protocol address of the listener service. 

·        Includes the HOST= specification of the database server (the specification can be the database name, e.g., sobora2.siue.edu or the IP address, e.g. 146.163.252.41). 

·        Includes the PROTOCOL= specification for the network protocol (TCP).

·        Includes the PORT= specification – the standard listener port is 1521 for Oracle software – other ports can be used as long as no other service is using the port on the server – an alternative port, such as 1523 could be assigned if port 1521 was already in use for another service on the host.

 

 

Server Computers listen for calls from client computers.

·        The listener process for a database instance knows the services for which it can handle connection requests, because an Oracle database dynamically registers this information with the listener. 

·        This process of registration is called service registration. 

·        Service registration provides a listener process with information about the database instances and the service handlers available for each instance. 

·        Service handlers act as connection points to an Oracle database server. 

·        A service handler can be a dispatcher or a dedicated server. 

 

INSTANCE_NAME parameter:

·        Can be added to the connect descriptor to listen for a specific instance of a database where multiple instances may be in use. 

·        This is useful if you are running Oracle Real Application Clusters.

·        We are not using this on SOBORA1 or SOBORA2.

 

DBORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu)(PORT = 1521))

    (CONNECT_DATA=(SERVICE_NAME=DBORCL)

        (INSTANCE_NAME=DBORCL_repository)

    )

   )

 

SERVER= parameter – another approach is to specify a particular service handler as part of the connect descriptor.

·        One example is when a dispatcher is used for a shared server configuration. 

·        Another example is SERVER=dedicated for a dedicated server.

·        The default is SHARED; however, if no dispatcher is available, then a DEDICATED connection is made.

 

DBORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu)(PORT=1521))

    )

    (CONNECT_DATA=(SERVICE_NAME=DBORCL)

        (SERVER=shared)

    )

  )

 

This figure shows more detail with a Listener and a Dispatcher for a Shared Server Process.

·        The Listener hands the connection request to the Dispatcher for future communcation.  The steps are:

 

1.  The listener receives a client connection request.

2.  The listener hands the connect request directly to the dispatcher.

3.  The client is now connected to the dispatcher.

 

 

Description of Figure 2-5 follows

 

This figure shows more detail with a Listener for a Dedicated Server Process.

·        The Listener passes a connection request to a dedicated server process -- first it starts the process.  The steps are:

1.  The listener receives a client connection request.

2.  The listener starts a dedicated server process.

3.  The listener provides the location of the dedicated server process to the client in a redirect message.

4.  The client connects directly to the dedicated server.

 

 

 

Description of Figure 2-8 follows


 

Naming Method Configuration

 

A naming method is a resolution method used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service.

·        Users initiate a connection request by providing a connect string.

·        A connect string includes a user name and password, along with a connect identifier.

·        A connect identifier can be either:

o   the connect descriptor or

o   a name that resolves to a connect descriptor.

o   a common connect identifier is a simple net service name. 

·        The connect descriptor contains:

o   Network route to the service, including the location of the listener through a protocol address.

o   A database service name or Oracle system identifier (SID)

 

·        Example:  This example CONNECT string has a complete connect descriptor:

 

CONNECT dbock/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)

    (HOST=sobora2.siue.edu)(PORT=1521))

    (CONNECT_DATA=(SERVICE_NAME=DBORCL)))

 

·        Example:  This example uses a simple net service name of DBORCL as the connect identifier. 

o   The net service name is mapped to the proper connect descriptor by using a repository of connection information that is access through one of Oracle’s naming methods.

 

CONNECT dbock/password@dborcl

 

Oracle Net supports the following naming methods:

·        Local Naming

o   With this approach a local configuration file named tnsnames.ora is stored on each client computer. 

o   Net service names are stored in the tnsnames.ora file as was described above. 

o   The file can be configured for individual client machines and client needs.  This is the approach taken at SIUE.

o   Local naming is most appropriate for simple distributed networks with a small number of services that change infrequently.

·        Directory Naming

o   This approach was described earlier in these notes. 

o   Service addresses and net service names are stored in a Lightweight Directory Access Protocol (LDAP)-compliant directory server.

·        Easy Connect Naming

o   Clients connect to a database without any configuration.

o   Clients use a connect string for a simple TCP/IP address that consist of a host name and optional port and service name.

o   Example:  CONNECT username/password@host[:port][/service_name]

o   Recommended for simple TCP/IP environments. 

·        External Naming

o   A third-party naming service already configured for your environment is used.

 

After a naming method is configured, the client computers must be enabled for the naming method following three steps:

1.   The client contacts a naming method

o   This step converts the connect identifier to a connect descriptor

o   With local naming for a Windows computer, this is accomplished by storing the tnsnames.ora file on the $Oracle_Home/Network/Admin directory specified for the client machine when the Oracle software was initially loaded onto the machine.

2.   Based on the identified connect descriptor, the client forwards a request to the listener address given in the connect descriptor.

3.   The client connection is accepted by the listener (usually uses a TCP/IP protocol).  If the client information received in the connect descriptor matches client information in the database and in its listener configuration file (named listener.ora), a connection is made; otherwise, an error message is returned.

 

 


 

 

Configuring the Local Naming Method

 

Client Configuration

 

Local Naming configuration requires storing a tnsnames.ora file on each client computer.

·        The local naming method adds net service names to the tnsnames.ora file.

·        Each net service name maps to a connect descriptor.

·        The tnsnames.ora file specifies connect descriptors for one or more databases. 

·        Examine the tnsnames.ora file located on a computer in the computer classroom/laboratory – located in $Oracle_Home/Network/Admin. 

·        Example from the tnsnames.ora file on a client computer in our laboratory:

 

DBORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = DBORCL)

    )

  )

 

Oracle Net Configuration Assistant – Oracle software that runs automatically during installation of the Oracle RDBMS.

·        Provides a wizard interface that prompts for information needed to build a tnsnames.ora file automatically.

·        If you select Custom Installation as an option when configuring your network connection, you can select the naming method to use. 

·        If you select Directory Naming or any other method other than Local Naming, the naming method has to already be set up. 

 

You can also configure the tnsnames.ora file manually by adding service names to the file by using a simple text editor like Notepad. 

 

 

Listener Configuration on the Server

 

Listener service – configured to listen for one or more databases.

·        Includes one or more listening protocol addresses and associated destination service information.

·        Listener.ora – the listener configuration file.

o   Stores the protocol addresses. 

o   It may or may not store the service information. 

o   The default location for the listener.ora file is the ORACLE_HOME/network/admin directory on the Database Server.

 

Here is the sample code stored in the listener.ora file on the SIUE sobora2 server.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu )(PORT = 1521))

      )

    )

  )

 

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = DBORCL.siue.edu)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME = DBORCL )

    )

  )

 

Listener Name Alias

·        A listener name alias can resolved through a tnsnames.ora file located on the server (NOT the client tnsnames.ora file).

·        We do not use this approach at SIUE, but if we did, an example entry in the tnsnames.ora file would be:

 

 

# tnsnames.ora Network Configuration File:

# /u01/app01/oracle1/product/11.2.0.3/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

DBORCL.SIUE.EDU =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = DBORCL)

    )

  )

 

EMTEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sobora2.isg.siue.edu)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = EMTEST.siue.edu)

    )

  )

 

LISTENER_DBACLASS =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 146.163.252.41)(PORT = 1523))

  )

 

·        The LISTENER_DBACLASS alias specified above can be used to enable any Oracle software to connect to a student database through the listener.

·        The LOCAL_LISTENER parameter shown below is from the dbockstd (student database) initdbockstd.ora file.  It specifies the alias for the listener used to connect to this particular database.

 

###########################################

# Listener alias

###########################################

local_listener =  "LISTENER_DBACLASS"

 

 

Listener Control Utility. 

·        A configured listener can be managed with the Listener Control Utility (LSNRCTL)

·        Ensure software release of the listener is appropriate for the Oracle database software release, e.g., use a listener designed for Oracle 11g, 10g or 9i as appropriate.

·        The screen shot below gives an example of using the lsnrctl command in a LINIX environment.

 

dbock/@sobora2.isg.siue.edu=>lsnrctl

 

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 22-JUL-2009 11:12:36

 

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

 

Welcome to LSNRCTL, type "help" for information.

 

LSNRCTL>

 

 

·        A listener.ora file can be configured manually.

o   Stop the listener process.

o   Configure the listener.ora file.

o   Start the listener process.   

 

$ lsnrctl start LISTENER
$ lsnrctl stop LISTENER

 

 

Identifying the Current Listener.

·        Use the SHOW CURRENT_LISTENER command.

 

LSNRCTL> show current_listener

Current Listener is LISTENER

 

 

Listener Services Status.

·        The status of services for which a listener is listening can be checked with the listener SERVICES command.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sobora2.isg.siue.edu)(PORT=1521)))

Services Summary...

Service "DBORCL.siue.edu" has 2 instance(s).

  Instance "DBORCL", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:25 refused:3

         LOCAL SERVER

  Instance "DBORCL", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3281 refused:0 state:ready

         LOCAL SERVER

Service "DBORCLXDB.siue.edu" has 1 instance(s).

  Instance "DBORCL", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:972 state:ready

         DISPATCHER <machine: sobora2.isg.siue.edu, pid: 15972>

         (ADDRESS=(PROTOCOL=tcp)(HOST=sobora2.isg.siue.edu)(PORT=11615))

Service "DBORCL_XPT.siue.edu" has 1 instance(s).

  Instance "DBORCL", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3281 refused:0 state:ready

         LOCAL SERVER

Service "USER305.siue.edu" has 1 instance(s).

  Instance "USER305", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "USER305_XPT.siue.edu" has 1 instance(s).

  Instance "USER305", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "USER350.siue.edu" has 1 instance(s).

  Instance "USER350", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3661 refused:0 state:ready

         LOCAL SERVER

Service "USER350_XPT.siue.edu" has 1 instance(s).

  Instance "USER350", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3661 refused:0 state:ready

         LOCAL SERVER

The command completed successfully

LSNRCTL>

 

The DBA can assign different names to listener processes.  This is done in the listener.ora file.  The default name of a listener is LISTENER and is configured to listen on the following default protocol addresses:

·        TCP/IP protocol - port 1521.

(address=(protocol=tcp)(host=host_name)(port=1521))

·        IPC protocol.

(address=(protocol=ipc)(key=PNPKEY))

 

 

When a listener service is contacted by a client, one of these actions is performed as is shown in this figure.

 

 

If the database service is running a dispatcher service, then the listener hands the request to the dispatcher – the process that manages the connection of many clients to the same server in a multi-threaded server environment.

 

If a dispatcher is not in use, the listener can spawn a dedicated server process or allocate a pre-spawned dedicated server process and pass the client connection to this dedicated server process (one server per client as we have discussed in earlier lectures).

 

Either way, a redirect message is sent back to the client informing the client of the location of the dispatcher or dedicated server process.

 

Disconnecting from Servers

If a user or application requests disconnection from a server, the server disconnects when all transactions are complete.  If this server is connected to a second server in order to support the user/application, then these additional connections are also disconnected.

 

Additional Connection Request.  When an application is connected to a server and attempts to access another user account (same or different server), the application is usually disconnected from the current connection.

 

Abnormal Connection Termination.  If communications are aborted without Oracle Net being notified, Oracle Net will recognize the failure and eventually clean up the client/server operations (during the next data operation) and disconnect the operation.

 

Timer Initiated Disconnect or Dead Connection Detection.  This feature is enabled to minimize wasted resources by invalid connections.  Uncommitted transactions are automatically rolled back and locks are released for the broken connection.  Oracle detects dead connections by periodically sending a small probe packet to each client at a user-defined interval (several minutes is typical) and initiates the disconnection through the allocated Server process if the connection is invalid.

 

 

 

Additional Configuration Information

 

Refer to the Net Services Administrator's Guide for additional information on configuring other naming methods, pre-spawned dedicated servers, and handling large connection volumes.

 



END OF NOTES.