Oracle Database Administration

1. Oracle Database Administration CSCI E256 Session 7 Oracle Networking

2. 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.

3. 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).

4. 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

5. 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.

6. OSI URL • More Information • http://www.ietf.org/ for information about the OSI stack

7. OSI Communications Stack

8. Oracle Client/Server Connections

9. 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.

10. 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.

11. 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.

12. OSI Stack

13. OSI Layers

14. Oracle Stack

15. 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).

16. 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

17. 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.

18. 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.

19. 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.

20. 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.

21. 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

22. 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.

23. 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.

24. Bequeath • 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

25. 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

26. 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

27. Java Application Connections

28. Web Connections

29. Listener Connection

30. Client/Server

31. 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

32. Application Server

33. Server to Server

34. 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

35. 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

36. 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

37. Architecture • 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 /u01/app/oracle/product/11.1.0/network/admin

38. Architecture • 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

39. Application Server

40. Tnsnames • This file can hold connect descriptors to many databases • The connect descriptor specifies the communications protocol, server name and the instance name

41. 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

42. Connect Descriptors CLASS.HARVARD.EDU = ----This is an alias name (DESCRIPTION = (ADDRESS = (COMMUNITY = TCP.edu) (PROTOCOL = TCP) (Host = kermit.dce.harvard.edu) (Port = 1521)) (CONNECT_DATA = (SID = E256)))

43. Tnsnames • The protocol is TCP • The HOST kermit.dce.harvard.edu, 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

44. Tnsnames • 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

45. Tnsnames • To connect to a database using the user scott and the password tiger, use the following connect string sqlplus> scott/tiger@class.harvard.edu

46. Tnsnames • The Oracle Net Assistant tool can generate the connect descriptor

47. Listeners • 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

48. Listeners • The listener.ora file is broken into 4 parts • The header section • The address list • The instance definitions • Operational parameters

49. Listeners <Listener_name> = -- The default name is LISTENER (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY= E253)) (ADDRESS= (PROTOCOL= IPC)(KEY= E256)) (ADDRESS(PROTOCOL=TCP)(Host=kermit) (Port= 1521)) )

50. Listeners SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=E256.HARVARD.EDU) (ORACLE_HOME=/u01/app/oracle/product/11.1.0) (SID_NAME = E256) )