Forschungszentrum Karlsruhe in der HelmholtzGemeinschaft Oracle Network Configuration

Forschungszentrum Karlsruhe in der Helmholtz-Gemeinschaft Oracle Network Configuration Dr. Doris Wochele (Karlsruhe) LCG 3 D Database Administrator Workshop Thursday 23 March 2006 Rutherford Appleton Laboratory / UK 23. 3. 2006 Institut für Wissenschaftliches Rechnen D. Wochele IWR Ideen werden Realität

Presentation Overview Ø Ø Ø 23. 3. 2006 Recapitulation Oracle NET architecture Naming Methods The Listener Get connected. . Listener Control Utility init-Parameter, sqlnet. ora, tnsnames. ora Failover and Load Balancing Connection Manager and Directory Server Concepts Troubleshooting, Trace and Logging Security Client Types Easy Connect D. Wochele IWR Ideen werden Realität

Oracle Net Architecture Oracle Net ► is NO transport protocol ► is a software component between server and client ► is using a network protocol (TCP/IP) The Application can be ► oracle client ► JDBC OCI Client with oracle client ► JDBC Thin Client without oracle client 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Naming Methods ► Oracle Names no longer supported ► Local Naming – define DB-connection in local files (sqlnet. ora, tnsnames. ora) on DB and every client ► Directory Naming – Set up a central LDAP Server to store all connection information ► Easy Connect Naming – Identify the DB-connection completely in the connection-string ► External Naming – NIS-Service or DCE Environment -> who is using this? Tools ► Oracle Net Manager – configure naming method, profiles, listeners – $ORACLE_HOME/bin/netmgr ► Oracle Net Configuration Assistant – $ORACLE_HOME/bin/netca – basic tool, runs after installation ► Virtual IP Configuration Assistant – CRS tool runs under root – map virtual IP to nodes 23. 3. 2006 D. Wochele IWR Ideen werden Realität

The Listener Details ► Control utility to start/stop and configure the listener $ORACLE_HOME/bin/lsnrctl ► Configuration file for listener $ORACLE_HOME/network/admin/listener. ora ► Server listener process. Read the configuration file “listener. ora” for information's as port numbers or SID’s $ORACLE_HOME/bin/tnslsnr Listener Modes ► Database Access to a database service ► Executable Access to operation system executables (used by oracle) ► PLSExt. Proc Method of PL/SQL packages to access operating system executables SID_LISTENER_RACLINUX 1 = Question: Are external procedures necessary? (SID_LIST = Can we delete this entries? (SID_DESC = (SID_NAME = PLSExt. Proc) (ORACLE_HOME = /u 01/app/oracle/product/10. 2. 0/db_1) (PROGRAM = extproc) ) Ideen werden ) IWR Realität 23. 3. 2006 D. Wochele

listener. ora Is a local DB-Configuration file for. . . ► connection protocol addresses ► (static defined) services to listen for ► control parameters ► to name the listeners (in RAC: listener_name_nodename) Question: Do we need this for OEM? Some Parameters ► LOG_DIRECTORY_listener_name (FILE) ► TRACE_LEVEL_listener_name (DIRECTORY, FILE, . . ) ► QUEUESIZE (Number of concurrent requests default=5) ► RECV_BUF_SIZE Port Numbers ► 1521 ► 1522 -1540 ► 1630, [1830] ► 2483, [2484] 23. 3. 2006 older oracle standard port used for listeners default for connection manager, [cman-admin] official registered for TNS listener, [with SSL] D. Wochele IWR Ideen werden Realität

Example listener. ora LISTENER_RACLINUX 1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192. 168. 203. 11)(PORT = 1521)) ) ) Ques ) tio n: W hy is the re LISTENER 2_RACLINUX 1 = al ho st-IP (DESCRIPTION_LIST = nece (DESCRIPTION = ssary ? (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1522)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192. 168. 203. 11)(PORT = 1522)) ) Example: 2 instances, each with 1 listener on different ports on a single server 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Get Connected with Listener is a process on DB-Server to handle connections DB-registration (pmon registers himself to the listerers ) ► ► ► ► read init-parameter looks for TNS_ADMIN variable or looks in $ORACLE_HOME/network/admin read sqlnet. ora to detect profile settings uses local tnsnames. ora to detect listeners read cman. ora if existent tries to register DB-Services to the LISTENERS or/and tries to register on Connection Manager for a service default registers to Port 1521 or default „LISTENER“ Client connection ► ► client looks for connection parameter in local tnsnames. ora (or EZconnect) client broadcasts a request for connection to a service listener brokers the request and forwards it to the database listener starts server-process and delivers the address (or the address of the dispatcher) to the client 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Listener Control Utility ► ► $ORACLE_HOME/bin/lsnrctl STOP [ listener_name] lsnrctl START [ listener_name] lsnrctl STATUS [ listener_name] – is it started? – where is the log or trace file? ► lsnrctl SERVICES – what services are up/used? – Is the load balanced ? 23. 3. 2006 D. Wochele IWR Ideen werden Realität

INIT Parameter ► SERVICE_NAMES – list of possible client connection names – default is global_dbname (DB_NAME+DB_DOMAIN) Question: should we predefined common service names like atlas_geom, atlas_calib? ► INSTANCE_NAME (= SID) – set to identify the database instance to access. – used by OEM or in administrative tools ► LOCAL_LISTENER – identifies the listener for the local server instances for pmon register process – not necessary if Port 1521 is used (? ) - i suggest to set it anyway! ► REMOTE_LISTENER – identifies listeners on other instances (node 2 of RAC) for pmon registration – identifies global list of listeners – needed to ensure failover 23. 3. 2006 D. Wochele IWR Ideen werden Realität

sqlnet. ora Is a profile for. . . ► default domain to append to unqualified service names or net service names ► order of naming methods ► logging, tracing, routing ► security, access control Example: NAMES. DIRECTORY_PATH= (TNSNAMES) Parameter (can mostly be overwritten by client) ► SEND_BUF_SIZE, RECV_BUF_SIZE – default 16 k – buffer size <Byte> = (network bandwidth <bit/sec> / 8) x (roundtrip time <msec> / 1000) use an average „ping time“ as roundtrip time – set in sqlnet. ora for all connections – mostly only the receive buffer is set in the client connection string ► SQLNET. SEND_TIMEOUT – limits the send-data-process from db ► SQLNET. RECV_TIMEOUT – limits the send-data-process from client ► USE_DEDICATED_SERVER – appends (SERVER=dedicated) to the connect data for a connect descriptor. 23. 3. 2006 D. Wochele IWR Ideen werden Realität

tnsnames. ora Is a local connection-configuration file ► contains net service names mapped to connect descriptors net_service_name= (DESCRIPTION= (ADDRESS=( protocol_address_information)) (CONNECT_DATA= (SERVICE_NAME=service_name))) some parameters ► SERVER=DEDICATED vs. SHARED – as streams-user or admin use only DEDICATED – as a short-time connection (like a web server) use SHARED. – be sure not to use SHARED for fast sequences of SQL‘s Question: What are the requirements of the experiment services? ► Use explicit service_name (do not use GLOBAL_DBNAME) in RAC 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Example Server tnsnames. ora – Listeners LISTENER_RACDB 1 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1522)) ) LISTENER_RACDB 2 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521)) ) Question: You find often the real IP in addition here? Why? LISTENERS_RACDB = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192. 168. 203. 11)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192. 168. 203. 11)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521)) ) init-Parameter racdb 1. local_listener = LISTENER_RACDB 1 racdb 1. remote_listener = LISTENER_RACDB 2 Question: Is RACDB the „remote“ ? 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Example Server and Client tnsnames. ora – Services RACDB 1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB) (INSTANCE_NAME = RACDB 1) ) ) RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux 1 -vip. us. oracle. com)(PORT = 1522)) (LOAD_BALANCE = yes) (FAILOVER = false) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB) ) init-Parameter ) service_names = RACDB, list of service names 23. 3. 2006 D. Wochele IWR Ideen werden Realität

RAC Failover an Load Balancing Failover and load balancing are set in the connection parameters Failover (Instance 1 (I 1) died) ► all new connects go to I 2 and reestablishes client connections on I 2 ► no restore of session parameters or program variables ► all selects are re-executed on I 2 ► active transactions are rolled back ► DDL were canceled ► parameter (type, method, retries) for client TAF in tnsnames. ora ► see v$session for TAF status ► server side TAF policy (you do not have to encode TAF on the client connection string) Load Balancing ► shared server config. : balanced select of dispatchers ► dedicated server config. : balanced select of listeners Cluster Interconnect ► not stable over cross-over CAT 5 Cable network switch ► CRS does not support a redundant Cluster Interconnect (with 2 network cards or IP-addresses) 23. 3. 2006 Question: What services should be run in shared/dedicated mode Ideen with or without TAF or LB? werden D. Wochele IWR Realität

Connection in a Private Network node 1 public network private network IP 1 VIP 1 SAN PIP 1 VIP 1, VIP IP 1, IP 2 ? network switch PIP 2 VIP 2 Database: Services A, B, C Flash Rec Area node 2 23. 3. 2006 D. Wochele IWR Ideen werden Realität

OCM Oracle Connection Manager ► ► ► act as a TNS-proxy with access-control additional installation cmctl $TNS_ADMIN/cman. ora DB register with init-parameter REMOTE_LISTENER=proxy tnsnames. ora SOURCE_ROUTE=yes proxy=(PROTOCOL=TCP)(HOST=lcgdbka 1. fzk. de)(PORT=1541) CMAN_KA= (CONFIGURATION= (ADRESS=(PROTOCOL=TCP)(HOST=lcgdbka 1. fzk. de)(PORT=1541) (RULE_LIST= (RULE=(SRC=141. 52. *)(DST=DB 1. gridka. de)(SRV=*) (ACT=ACCEPT)) (PARAMETER_LIST= (MAX_GATEWAY_PROCESSES=12) (MIN_GATEWAY_PROCESSES=2) (REMOTE_ADMIN=YES) ) Question: Does cman fully support load balancing and failover? ) 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Connection in a Private Network node 1 public network private network IP 1 VIP 1 SAN PIP 1 CMAN (IPxxxx) NIC 1 NIC 2 network switch Proxy CMAN PIP 2 VIP 2 Database: Services A, B, C Flash Rec Area node 2 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Directory Server ► LDAP compliant directory server ► central repository for – network information – user policies – user authentication and security ► ldap. ora on db-servers and clients assets and drawbacks ► a typical bottleneck! ► no distribution of client config files Question : Is this a possible concept? 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Oracle Net Troubleshooting ► ping hostname_node 1[2] ► check network cards and connection – – – ► ► ifconfig ethtool iperf netstat tcpdump ethereal tnsping service_name from server_node 1[2] tnsping service_name from client lsnrctl services sqlplus scott@service_name 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Trace and Logging Sqlnet. log ► pmon registration to listener tail -f listener. log --> 01 -MAR-2006 16: 11: 46 * service_update * RACDB 1 * 0 Listener Log ► Set LOG_STATUS=ON (default=OFF) ► $ORACLE_HOME/network/admin/<SID>. log is default ► Contains listener commands ► Only client connects, no further information Listener Trace ► much load, capture all NET-traffic ► for debugging only 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Security Listener ► in 10 g listener runs under OS authentication, listener password only necessary to restrict remote control ► password accessible in listener. ora, protect by file-rights ► Set ADMIN_RESTRICTIONS_listener_name=ON allow no remote parameter changes (only listener. ora) #----ADDED BY TNSLSNR 13 -MAR-2006 17: 33: 29 --PASSWORDS_LISTENER_RACLINUX 1 = 1 DF 5 C 2 FD 0 FE 9 CFA 2 SAVE_CONFIG_ON_STOP_LISTENER_RACLINUX 1 = ON LOGGING_LISTENER_RACLINUX 1 = ON #---------------------- ► delete extproc–entry in listener. ora to prevent program-routines to act as oracle-user ? SQLNET ► using Certificate Authority for PKI ? (Oracle Advanced Security supports Cyber. Safe, RADIUS, Kerberos, SSL, Windows NT native authentication (NTS)) ► using checksums ? (SQLNET. CRYPTO_CHECKSUM_SERVER) ► using encryption? (SQLNET. ENCRYPTION_SERVER) 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Client Types ► full client ► instant client – installation involves copying a small number of files. – less client-side requirements – no loss of functionality or performance for applications deployed in Instant Client mode. – it is simple to package applications – configuration files are read (set TNS_ADMIN or $ORACLE_HOME to find them) url="jdbc: oracle: oci: @//example. com: 5521: bjava 21" url="jdbc: oracle: oci: @(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun 242) (PORT=5521)) (CONNECT_DATA=(SERVICE_NAME=bjava 21)))" 23. 3. 2006 D. Wochele IWR Ideen werden Realität

Easy Connect ► CONNECT username/ password@ host[: port][/ service_name][/ instance_name] ► no LDAP, no tnsnames. ora ► SQLNET. ora – NAMES. DIRECTORY_PATH=(EZCONNECT) ► only client have to be 10 g (you can connect to 9 i) ► EZConnect Alias – Client name resolution for service-name (e. g. 141. 52. 167. 211 cgcl 1. fzk. de) • DNS • /etc/hosts Examples: connect scott/tiger@orcl. fzk. de: 1521/service JDBC: connect scott/tiger@//orcl. fzk. de: 1521/service ► Easy Connect provides NO Failover or Load Balancing! 23. 3. 2006 D. Wochele IWR Ideen werden Realität