3.10 Networked Oracle ---------------- - SQL*NET v1 and SQL*Net v2. Client-server and server-server connections. Muliti- and single-threading. Process-per-user (single-thread) and multi-thread architecture. Listener or server process. Important Notes: 1. SQL*Net V1 on one side can ONLY work with V1 on other side and V2 can interact with V2 only. V1 <-> V2 and V2 <-> V1 combinations are NOT ACCEPTABLE. 2.Oracle has a VERY CONFUSING terminology in SQL*Net V1! There is a process who listens for connection requests from client. This process is called "SERVER process" in SQL*Net V1 and "LISTENER process" in SQL*Net V2. The valid name for this process is LISTENER of course, because server (again, sometimes called user) process is responsible for interaction with user machine in single-thread environment. Thing called "shared server" is SQL*Net V2 counterpart in multi-thread environment. - SQL*Net v1 connect string: usnm/pwd@PROTOCOL_PREFIX:SERVER_NAME:INSTANCE_NAME[,BUFFER_SIZE] where INSTANCE_NAME is system identifier (SID) and BUFFER_SIZE is the size of the context area used to pass data between SQL*NET and communication protocol. For TCP/IP default buffer size is 4096. For some platforms like Netware or OS/2 which do not support multiple instances on the same machine connect string looks like: usnm/pwd@PROTOCOL_PREFIX:LISTENER_NAME where LISTENER_NAME - name of the listener process specified when listener starts. examples: sqlplus scott/tiger@t:vabank:test sqlforms dima/dima@x:ora7 It is also possible to set DEFAULT connection and ALIASES on client side. Default connections are provided with environment var. TWO_TASK (UNIX), LOCAL and ORACLE.INI or CONFIG.ORA parameters for Windows, DOS(OS/2, NetWare). Example for UNIX client: $ TWO_TASK=T:hq:loc;EXPORT $ sqlplus scott/tiger will try to connect to hq:loc ALAIASES can be specified in /etc/sqlnet file for UNIX platform or REMOTE and DB_NAMES parameters for desktop platforms. UNIX example: /etc/sqlnet may contain the line: hq t:hq:loc then you could enter sqlplus scott/tiger@hq - SQL*Net V2 (see also APPENDIX A). Transparent Network Substrate (TNS) resolves server-server connectivity issues. Multiprotocol Interchange and the TNS allows SQL*Net v2 connection to be made independent of the communication protocol and operating system. - Connect descriptors: the server and instance portions of an object's Fully Qualified Object Name (FQON) are identified by CONNECT DESCRIPTOR: (DESCRIPTION = (ADDRESS= (PROTOCOL=TCP) (HOST=VABANK) (PORT=1521) /* port for V1 is in /etc/services file (orasrv 1525/tcp) */ (CONNECT DATA = (SID= test))) - SID instance name. ADDRESS part of this descriptor is PROTOCOL DEPENDENT. For DECnet and IPX/SPX it will be different. To connect you should issue: sqlplus scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=... - Service names. TNSNAMES.ORA file in TNS_ADMIN (oracle_home/network/admin). It's silly to type in connect descriptor each time a tool is invoked. You can assign so called SERVICE NAME or ALIAS to a connect descriptor, for example: HQ=(DESCRIPTION = /* HQ - service name (Alias) */ (ADDRESS= (PROTOCOL=TCP) (HOST=loc) (PORT=1521) /* port for V1 is in /etc/services file (orasrv 1525/tcp) */ (CONNECT DATA = (SID= test))) - SID instance name. Now to connect you should only type: sqlplus scott/tiger@HQ Note: Service name is CASE INSENSITIVE Default connection mechanisms (TWO_TASK var, LOCAL=) are the SAME as ones described above for SQL*Net V1. Igor?? Such service names are stores in file TNSNAMES.ORA. This file should locate on each client or server on network and should contain alaises for all servers avalable from this computer. An additional file SQLNET.ORA may be created to specify additional diagnostic beyond the default diagnostic provided. SQLNET.ORA is user on both client and sever (during server-server communications only) side. - Interprocess communications (IPC) OM 5-6.1995 p.87 When the client and server portions of an Oracle application run on the same computer, they can communicate efficiently without a network by IPC. But remote clients automatically attempt IPC to a TNS listener. This feature can be disabling by inserting AUTOMATIC_IPC = OFF in the client version of SQLNET.ORA - Detecting dead connections (OM 5-6.1995 p.87) Keep alive interval. SQL*NET 2.1 has a new feature that allows it to detect broken client (dead) connections and have the server automatically terminate them. This feature works by regularly sending a probe packet thorough a connection to determine its status. The interval for sending probe packets can by set by SQLNET.EXPIRE_TIME parameter in the SQLNET.ORA on client and server sides. For example, SQLNET.EXPIRE_TIME = 10 set interval to 10 minutes. This parameter should be set on both (client & server) sides. Important note: If this interval is set to low value then frequent probes sent to all clients generate lots of network traffic! Oracle recommends set 10 minutes interval. - Listener. Only ONE listener per machine not depending on number of instances. Each server MUST contain a LISENER.ORA file, which lists names and address of all the TNS "listener" processes on the machine and the instances they support. Listener process receives connection from SQL*Net v2 clients. LISTENER.ORA contains: - a header section - an interprocess call (IPC) address definition section - instance definitions - operational parameters - Supporting the SQL*Net V2 configuration tool. TNSNAMES.ORA, LISTENER.ORA and others should be automatically generated via the SQL*net v2 configuration tool. In order to use this tool account NET_CONT (with full DBA privileges) must be created. The script NCSSHEMA.SQL located in $ORACLE_HOME/network/config directory creates all the necessary tables which should be filled with data by user via special forms. The configuration tool called net_conf. - Multi Protocol interchange. A network community is a set of SERVERS that communicate with each other via a single protocol. MPIs may be physically configured so that multiple access paths are available between servers. The MPI will select the most appropriate path based on path availability and network load. Each MPI is comprised of three components: -- connection managers, which manages a Listener process to detect connection requests -- a Navigator which chooses the best possible path through the TNS network; -- the Interchange Control Utility. The NETWORK COMMUNITY in which server exists should be added to the connect descriptors for its databases: hq=(description = (address= (COMMUNITY=TCP.HQ.COMPANY) (protocol=tcp) (host=loc) (port=1521) (connect data = (sid= test))) TNSNAMES.ORA is automatically generated via Configuration Tool. Other files are also generated: TNSNAV.ORA - describes the communities of each MPI on the network TNSNET.ORA - contains an overview of the layout of the network for ALL MPIs. Lists all the communities on the network and relative costs of traversing them. These costs reflect the throughput capability of the community. They are used to choose which of multiple available access path should be used. INTCHG.ORA - contains parameters that control the behavior of each MPI. - Oracle Names is coming with SQL*Net v2.1. It users Distributed Option to manage the distribution of the network configuration files. - Tuning SQL*Net. - the use of distributed objects, such as snapshots, to replicate static data to remote database; - the use of DB procedures to reduce the amount of data sent across the network; - the use SQL*Net V2 whenever applicable. V2 sends fewer network packets; - providing enough MPIs; - using homogeneous servers to eliminate the needs of MPI. - Networking in UNIX - Identification of hosts in UNIX TCP/IP protocol for BOTH SQL*Net V1 and SQL*Net V2 ???Igor??? A host is a server that is capable of communicating with another server via network. Each host maintains a list of hosts with which it can communicate in file: /etc/hosts Sample of /etc/hosts 128.0.0.1 vabank VABANK 128.0.0.2 fors 128.0.0.3 docum archive The first field is an internet address, the second field - a host name and the third and subsequent fields - host name aliases. PC software emulates this mechanism. It's possible to use TCP/IP on NetWare, DOS, OS/2, Windows NT and MS-Windows. Location of file "hosts" depends on emulation package and platform. The current server (where the file "hosts" is located) should also be mentioned in this file. etc/hosts file is mandatory for both SQL*Net V1 and SQL*Net V2. Yes but IP address can be used instead of host name. - Identification of Databases. /etc/oratab file. All databases that are run on the on a host and ACCESSIBLE to the network MUST be listed in a file named /etc/oratab. Oracle starter - dbstart (dbshut) from Oracle -> oratab /etc/oratab MUST contain 3 components, delimited by colon: ORACLE_SID - Instance name (System ID) ORACLE_HOME - full path name of the Oracle software used by database. Startup_Flag - flag to indicate whether the instance should be started when the host is started. (Y or N) Example: LOC:/orasw/v7016:Y CC1:/orasw/v713:N OLD:/orasw/v637:Y Ones an instance is listed in etc/oratab on an identified (via file /etc/hosts) host on the network, it can be accesses via SQL*Net connection string (if SQL*Net is enabled of course). - Identification of services for SQL*Net versions 1 and 2. - SQL*Net V1. The service for SQL*Net V1 must be listed in /etc/services file, named "orasrv" and should be assigned to port 1525: . . . orasrv 1525/tcp . . . - SQL*Net V2. TNSNAMES.ORA file on each host will include listings of service names with their associated connect descriptors: hq=(description = (address= (protocol=tcp) /* Service's protocol */ (port=1521) /* Service's port */ (host=loc) . . . I thought that TNSNAMES.ORA is needed for remote servers only, not for the local host! ???Igor???. - Starting and controlling listener (called server in SQL*Net V1) processes. - SQL*Net V1. - Starting server process manually. Type "orasrv" on Unix prompt. Format: orasrv file_1 flag_1 ... flag_n file_2 Parameters' description: Mapfile- filename (without keyword) used to map instance name (ORACLE_SID) to their Oracle software root directory (ORACLE_HOME) If not specified then defaults to \etc\oratab (see above) I - a flag to indicate if in-bank breaks are used (default). is sent in ordinary sequence. O - out-of-band breaks. (I and O are mutually exclusive). is sent by separate request (out of sequence) logon - SQL*Net activities should be written to Logfile logoff - a flag used to turn logging OFF. logon and logoff are mutually exclusive debugon- a flag indicates that debugging activities should be started. This reports a greater level of details on connection attempt and writes to logfile debugoff- a flag used to turn debugging off dbaon - a flag to indicate that remote users will be able to CONNECT INTERNAL into local database. dbaoff - reverse to dbaon opson - remote users will be able to autologin (OPS$ account) opsoff - disables autologin -O - in conjunction with debug parameter this determines the level of trace info written (for Oracle support) opsrooton - flag to indicate that the database should allow remote access to a local account named OPS$ROOT opsrootoff - no remote access to OPS$ROOT. This parameter may be used in conjunction with "opson" parameter to allow only non-root remote autologins port= - specifies the port to be used if other then default listen= - specifies the length of the listen queue. timeout= - specifies how long a handshake with orasrv should be attempted before attempt times out forkon, forkoff, detachon, detachoff - tell SQL*Net how to run orasrv process. The default is "detachon" which run orasrv as detached process. "detachoff" will require separate terminal. Examples: tcpctl stop - stops the orasrv process tcpctl stat - list orasrv status info tcpctl stat @host_name - orssrv status for REMOTE host tcpctl version @host_hname tcpctl start tcpctl start log debug - SQL*Net V2. - Starting listener. Listener Control Utility named LSNRCTL. LSNRCTL START - Starts listener LISTENER LSNRCTL MY_LSNR - Starts listener called MY_LSNR. LSNRCTL STATUS - get listener status This utility runs $ORACLE_HOME/bin/tnslsnr executable. File LISTENER.ORA should be located by default in $ORACLE_HOME/network/admin or in directory pointed by TNS_ADMIN environment variable. To show if listener active your can issue: > ps -ef | grep tnslsnr - Controlling the listener process. LSNRCTL utility. Parameter's description: SET PASSWORD - allows the user access to administrative options within LSNRCTL. The listener password is set via PASSWORD_listener_name parameter in the LISTENER.ORA. START - starts the listener STOP - stops the listener. STATUS - provides status info for listener. Can be used to query the status of a listener on a remote server. VERSION - displays version info for the Listener, TNS, and the protocol adapter SERVICES - displays services available, along with its connection history. It also lists whether each service is enabled for remote DBA or autologin process RELOAD - allows to modify the listener services after the listener has been started. It forces SQL*Net to read and use the most current LISTENER.ORA file TRACE - set trace level of the listener to one of three choices: OFF, USER (limited tracing), and ADMIN (high level of tracing) Examples: > lsnrtctl LSNRTCTL> set password LSNRTCTL> stop > lsnrtctl status > lsnrtctl status hq - status on the another host. HQ - service neme > lsnrtctl version > lsnrtctl LSNRTCTL> set password LSNRTCTL> services > lsnrtctl LSNRTCTL> set password LSNRTCTL> reload > lsnrtctl LSNRTCTL> set password LSNRTCTL> trace user > lsnrtctl LSNRTCTL> set password LSNRTCTL> start Most of these commands require password. - Identifying of networked user and getting O/S server process ID in single-threaded mode (for SQL*Net V1 and V2). Q. Is there a way to identify a client connection if all clients are connecting with the same Oracle username? What's the answer for a client that is a unix client on the same machine as the db, or for a unix client that is a remote client, or for a desktop client? A customer would like to be able to identify a client connection by the tcp/ip ip address that it is coming from. A. Here is a mail to helpnet from Victor Grigorieff that can answer all of the above questions. This is for identifying shadows from PC to UNIX. Here is how I figure these things out, using a Data Browser Query like this: select ALL SYS.V_$session.osuser, SYS.V_$session.username, SYS.V_$session.terminal, SYS.V_$session.program, SYS.V_$process.program FROM SYS.V_$PROCESS, SYS.V_$SESSION WHERE SYS.V_$PROCESS.Addr=SYS.V_$SESSION.Paddr; The output looks like this: oracle@squid (PMON) oracle@squid (DBWR) oracle@squid (LGWR) oracle@squid (SMON) oracle@squid (RECO) Victor Gri SYSTEM OraUser Oracle Data Browser (TCP Two-Task) orapid:6957 vgrigori SCOTT Windows PC C:\ORAWIN\BIN\PLUS31.EXE (TCP Two-Task) orapid:6962 usupport SCOTT pts/2 sqldba@tcaix TCP Two-Task) (TCP Two-Task) orapid:6968 oracle SCOTT ttyp1 sqldba@fleetwoo (Appletalk Two-Task) orapid:6998 Notice that for the Mac Client: Victor Gri SYSTEM OraUser Oracle Data Browser The "Victor Gri" is the first 10 letters of the name I entered in the File Sharing Setup control panel on my Mac.) Notice that for the Windows Client: vgrigori SCOTT Windows PC C:\ORAWIN\BIN\PLUS31.EXE The "vgrigori" is the USERNAME= parameter of the ORACLE.INI or CONFIG.ORA USERNAME= for DOS or OS/2 For the UNIX client: usupport SCOTT pts/2 sqldba@tcaix The "pts/2" are the first 5 characters of the tty, which is not too helpful, as the real tty in this case was pts/26. In any case, this should give you enough information to tell which shadow process relates to which Windows-client. You just need to set the USERNAME parameter properly on each of your Windows clients. This should work with SQL*Net v1 and V2, but I have not tried v2. To get a server (user) process operating system ID you should join V$SESSION table with V$PROCESS table over PADDR field.