oracle database administration l.
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle Database Administration PowerPoint Presentation
Download Presentation
Oracle Database Administration

Loading in 2 Seconds...

play fullscreen
1 / 66

Oracle Database Administration - PowerPoint PPT Presentation

  • Uploaded on

Oracle Database Administration. CSCI E256 Session 7 Oracle Networking. Oracle Networks. The function of Oracle Net is to establish and maintain connections between a client application and an Oracle database server.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Oracle Database Administration' - Pat_Xavi

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
oracle database administration

Oracle Database Administration


Session 7

Oracle Networking

oracle networks
Oracle Networks
  • The function of Oracle Net is to establish and maintain connections between a client application and an Oracle database server.
  • Oracle Net has many communication layers that enable clients and database servers to share and modify data.
tcp ip
  • The Transmission Control Protocol/Internet Protocol (TCP/IP) is the de facto standard Ethernet protocol used for client/server conversations over a network.
  • TCP/IP enables an Oracle application on a client to communicate with remote Oracle databases (if the Oracle database is running on a host system that supports network communication using TCP/IP).
oracle networks4
Oracle Networks
  • The communication architecture is based on the Open Systems Interconnection (OSI) model
  • The OSI model uses a communication stack where information moves from one node to the other, through several layers of code
communications stack
Communications Stack
  • The concept of distributed processing relies on the ability of computers separated by both design and physical location to communicate and interact with each other.
  • This is accomplished through a process known as stack communications.
  • Stack communications can be explained by referencing the Open Systems Interconnection (OSI) model.
osi url
  • More Information
    • for information about the OSI stack
communications stack9
Communications Stack
  • Client Application The OSI layer closest to the user, and is dependent on the functionality requested by the user.
  • Presentation Ensures that data is represented in a format that the application and session layers can accommodate.
  • This includes keeping track of syntax and semantics of the data transferred between the client and server.
communications stack10
Communications Stack
  • Session Establishes, manages, and terminates network sessions between the client and server.
  • This is a virtual pipe that carries data requests and responses.
  • The session layer manages whether the data traffic can go in both directions at the same time (asynchronous), or in only one direction at a time (synchronous).
  • Transport Implements the data transport ensuring that the data is transported reliably.
communications stack11
Communications Stack
  • Network Ensures that the data transported is routed through optimal paths, through a series of interconnected sub-networks.
  • Link Provides reliable transit of data across a physical link.
  • Physical Defines the electrical, mechanical, and procedural specifications for activating, maintaining and deactivating the physical link between client and server.
client application
Client Application
  • Oracle client applications provide data presentation and application flow.
  • The application identifies database operations to send to the server and passes them through to the Oracle Call Interface (OCI).
oracle call interface oci
Oracle Call Interface (OCI)
  • Oracle Call Interface (OCI) code contains all the information required to initiate a SQL dialog between the client and the server.
  • It defines the calls made to the server
    • Parse SQL statements for syntax validation
    • Open a cursor for the SQL statement
    • Close the cursor
two task common ttc
Two-Task Common (TTC)
  • This is Oracle’s implementation of the presentation layer.
  • TTC provides character set and data type conversion between different character sets or formats on the client and server.
  • It performs conversion only when required
  • At the time of initial connection, it determines if conversions are required for the two computers to communicate.
oracle net
Oracle Net
  • Oracle Net provides all the session and transport layer functionality in an Oracle communication stack.
  • It is responsible for establishing and maintaining the connection between the client application and server, as well as exchanging messages between them.
  • Oracle Net is also responsible for mapping session functionality into industry-standard protocols.
network interface ni
Network Interface (NI)
  • This layer provides a generic interface for Oracle clients, servers, or external processes to access Oracle Net functions.
  • The NI layer handles the "break" and "reset" requests for a connection.
  • NI uses Network Naming (NN) to resolve names to connect descriptors.
network session ns
Network Session (NS)
  • This layer receives requests from NI, and settles all generic machine-level connectivity issues, such as: the location of the server
  • NS uses Network Route (NR) to route the network session to the destination and Network Authentication (NA) to negotiate any authentication requirements with the destination.
oracle protocols
Oracle Protocols
  • Oracle protocols are Oracle’s implementation of the transport layer.
  • Oracle protocols are responsible for mapping NS functionality to industry-standard protocols used in the client-server connection. protocol.
  • Oracle protocols include:
    • TCP/IP
    • TCP/IP with SSL
    • SPX
network specific protocols
Network-Specific Protocols
  • All Oracle software in the client-server connection process requires an existing network protocol stack to make the machine-level connection between the two machines for the transport layer.
  • The network protocol is responsible only for getting the data from the client machine to the server machine, at which point the data is passed to the server-side Oracle protocol.
oracle program interface opi
Oracle Program Interface (OPI)
  • Oracle Program Interface (OPI) performs a complementary function to that of the OCI.
  • It is responsible for responding to each of the possible messages sent by the OCI.
  • For example, an OCI request to fetch 25 rows would have an OPI response to return the 25 rows once they have been fetched.
  • The Bequeath protocol enables clients that exist on the same machine as the server to retrieve information from the database without using the listener.
  • The Bequeath protocol internally spawns a dedicated server process for each client application.
  • Bequeath is used for local connections where an Oracle client application, such as SQL*Plus, communicates with an Oracle server running on the same machine
oracle network
Oracle Network
  • Oracle Net, Net8(Oracle8) and SQL*Net V2 (Oracle7) are used to connect a client to a server
  • The client is the application, in either a two-tier or 3-tier architecture
  • The server is the system where the database is located
  • Oracle Net and SQL*Net V2 are Oracle tools
oracle network26
Oracle Network
  • The Oracle Net software needs to be installed on both client and and the server, for 2-tier applications.
  • It is part of the RDBMS installation
  • 3-Tier or web based applications do not need Oracle Net software installed on the client
oracle network31
Oracle Network
  • The Oracle Net software must be installed on the Application Server and the Database Server, for 3-tier applications
  • Oracle Net is also used to connect distributed databases
oracle networks34
Oracle Networks
  • The Oracle Net for versions Oracle10g and Oracle11g, supports connections to databases running versions 7.3.4 or higher
  • Each object in a database is uniquely identified by it’s owner and it’s name
  • With synonyms we learned that the fully qualified object name, included the server name and the instance name, the owner and the object’s name
oracle networks35
Oracle Networks
  • Oracle Net relies on a series of configuration files
  • The main file is the tnsnames.ora file
  • It is by default located in the /$ORACLE_HOME/network/admin directory
  • From Oracle8i onwards, we need to use the TNS_ADMIN shell variable
oracle networks36
Oracle Networks
  • This variable specifies the location of the tnsnames.ora file.
  • It can also be stored in a general directory, like /var/opt/oracle or /etc
  • The 3 main files for Oracle Net and SQL*Net V2 are
    • Tnsnames.ora
    • Listener.ora
    • Sqlnet.ora
  • They are located in the directory identified by the TNS_ADMIN variable
  • echo $TNS_ADMIN


  • The sqlnet.ora holds some of the parameter settings for sql*net
  • The listener.ora file specifies the configuration of the listener
  • The tnsnames.ora file defines the sid-mappings used to connect to a database.
  • It is used by both the client and server, to connect to the database
  • This file can hold connect descriptors to many databases
  • The connect descriptor specifies the communications protocol, server name and the instance name
connect descriptors
Connect Descriptors
  • Every database requires a connect descriptor.
  • A connect descriptor describes the location of the network listener and the system identifier (SID) of the database to which to connect.
  • Database connect descriptors commonly consist of two sections:
    • the listener ADDRESS
    • the database SID
connect descriptors42
Connect Descriptors

CLASS.HARVARD.EDU = ----This is an alias name





(Host =

(Port = 1521))

(CONNECT_DATA = (SID = E256)))

  • The protocol is TCP
  • The HOST, is fully qualified
  • The default port is 1521, another choice is 1526. These are the default ports that Oracle Net uses.
  • You can choose you own, for security
  • CLASS.HARVARD.EDU is a service name or alias
  • All the service names with their associated connect descriptors are stored in the tnsnames.ora file.
  • All service names must be unique within the tnsnames file
  • To connect to a database using the user scott and the password tiger, use the following connect string



  • The Oracle Net Assistant tool can generate the connect descriptor
  • Each database server must have a listener.ora
  • The listener.ora file lists the names and addresses of all the listener processes on the machine and the instances they support
  • The Listener receives connections from Oracle Net and SQL*Net V2 clients
  • The listener.ora file is broken into 4 parts
    • The header section
    • The address list
    • The instance definitions
    • Operational parameters

<Listener_name> = -- The default name is LISTENER





(Port= 1521))








(SID_NAME = E256)


operational parameters
Operational Parameters


( startup immediately, it is the time in seconds the listener will wait to start, after the startup command)


( time, in seconds, before listener will accept new connections)

TRACE_LEVEL_LISTENER = OFF (admin,user,support)

operational parameters52
Operational Parameters


LOG_FILE = name of listener log file

LOG_DIRECTORY = the directory for the listener log file

TRACE_FILE = name of listener trace file

TRACE_DIRECTORY = the directory for the listener trace file

operational parameters53
Operational Parameters
  • In Oracle 10g and Oracle11g the database instances register themselves with the listener upon database startup

SunOS kermit 5.8 > lsnrctl

LSNRCTL for Solaris: Version 11.1.0 -

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type "help" for information.




The following operations are available

An asterisk (*) denotes a modifier or extended command:

start stop status

services version reload

save_config trace spawn

dbsnmp_start dbsnmp_stop dbsnmp_status

change_password debug test

quit exit set*


  • LSNRCTL>start -- To start the listener
  • SunOS kermit 5.8 >lsnrctl start
  • LSNRCTL>stop -- To stop the listener
  • SunOS kermit 5.8 >lsnrctl stop
  • EXIT or QUIT exits lsnrctl
  • RELOAD allows the changing of the listener services after the listener has been started
  • SAVE_CONFIG creates a backup of your existing listener.ora and updates the listener.ora file with the changed parameters
  • SET can be used to define parameter values
  • telnet (ssh ) host_name
  • ping host_name
  • tnsping <SID>
  • LSNRCTL> status E256
  • SunOS kermit 5.8 > ps -eaf | grep tns
  • oracle 11437 1 0 22:59:28 ? 0:01

/u01/app/oracle/product/11.1.0/bin/tnslsnr LISTENER –inherit

  • Must have 10g or later client installed
  • Connect username/password@hostname:port/service_name
  • Connect user/password@mercury:12007/test
  • For JDBC use Connect username/password@//hostname:port/service_name
  • Sql*net.ora file must contain names.directory_path = (tnsnames EZCONNECT)
the oratab file
The Oratab File
  • Created when the rdbms is installed.
  • It’s location is dependent on the operating system used. For Solaris, its is located in /var/opt/oracle
  • It contains a line for each database
the oratab file63
The Oratab File

SunOS kermit 5.8 > vi oratab

  • This file is used by ORACLE utilities. It is created by
  • A colon, ':', is used as the field terminator. A new line terminates the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:



E256:/u01/app/oracle/product /11.1.0dev:N

the oratab file64
The Oratab File
  • The first and second fields are the system identifier and home directory of the database respectively. The third field indicates to the dbstart utility that the database should, "Y", or should not, "N", be brought up at system boot time.
  • Multiple entries with the same $ORACLE_SID are not allowed.
  • Oracle11g Net Services Administrator's Guide
    • Chap5 Architecture of Oracle Net Services
mid term
  • Next week
  • Open book
  • I will Email the exam to all at 5:30PM next week
  • It will also be posted on the website
  • You have 24 hours to complete and return the exam.
  • It must be returned by 5:30PM Thursday
  • Others can come to class and do the exam here.