IWR - CERN Indico

Forschungszentrum Karlsruhe
in der Helmholtz-Gemeinschaft
Oracle Network Configuration
Dr. Doris Wochele (Karlsruhe)
LCG 3D Database Administrator Workshop
Thursday 23 March 2006
Rutherford Appleton Laboratory / UK
Institut für Wissenschaftliches Rechnen
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
werde
nRealitä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
werde
nRealitä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
–
–
23.3.2006
CRS tool runs under root
map virtual IP to nodes
D.Wochele
IWR
Ideen
werde
nRealität
The Listener
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)
► PLSExtProc Method of PL/SQL packages to access operating system
executables
SID_LIST_LISTENER_RACLINUX1 =
Question: Are external procedures necessary?
(SID_LIST =
Can we delete this entries?
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
Ideen
werde
)
IWR nRealitä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
werde
nRealität
Example listener.ora
LISTENER_RACLINUX1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1521)))
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.203.11)(PORT = 1521))
)
)
)
LISTENER2_RACLINUX1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-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
werde
nRealitä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
werde
nRealität
Listener Control Utility
►
►
►
►
$ORACLE_HOME/bin/lsnrctl
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
werde
nRealitä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
werde
nRealitä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 16k
– 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
–
23.3.2006
appends (SERVER=dedicated) to the connect data for a connect descriptor.
D.Wochele
IWR
Ideen
werde
nRealitä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
werde
nRealität
Example Server tnsnames.ora – Listeners
LISTENER_RACDB1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1522))
)
LISTENER_RACDB2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-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 = raclinux1-vip.us.oracle.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1521))
)
init-Parameter
racdb1.local_listener
= LISTENER_RACDB1
racdb1.remote_listener = LISTENER_RACDB2
Question: Is RACDB the „remote“ ?
23.3.2006
D.Wochele
IWR
Ideen
werde
nRealität
Example Server and Client tnsnames.ora – Services
RACDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
(INSTANCE_NAME = RACDB1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-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
werde
nRealität
RAC Failover an Load Balancing
Failover and load balancing are set in the connection parameters
Failover (Instance1 (I1) died)
► all new connects go to I2 and reestablishes client connections on I2
► no restore of session parameters or program variables
► all selects are re-executed on I2
► 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 CAT5 Cable  network switch
► CRS does not support a redundant Cluster Interconnect (with 2 network
cards or IP-addresses)
Question: What services should be run in shared/dedicated mode
Ideen
with or without TAF or LB?
werde
23.3.2006
D.Wochele
IWR
nRealität
Connection in a Private Network
node 1
public network
private network
IP1
VIP1
SAN
PIP1
VIP1,VIP
IP1, IP2 ?
network switch
PIP2
VIP2
IP2
Database:
Services A,B,C
Flash Rec Area
node 2
23.3.2006
D.Wochele
IWR
Ideen
werde
nRealitä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=lcgdbka1.fzk.de)(PORT=1541)
CMAN_KA=
(CONFIGURATION=
(ADRESS=(PROTOCOL=TCP)(HOST=lcgdbka1.fzk.de)(PORT=1541)
(RULE_LIST=
(RULE=(SRC=141.52.*)(DST=DB1.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
werde
nRealität
Connection in a Private Network
node 1
public network
private network
IP1
VIP1
SAN
PIP1
CMAN (IPxxxx)
NIC1
NIC2
network switch
Proxy
CMAN
PIP2
VIP2
IP2
Database:
Services A,B,C
Flash Rec Area
node 2
23.3.2006
D.Wochele
IWR
Ideen
werde
nRealitä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
werde
nRealität
Oracle Net Troubleshooting
► ping hostname_node1[2]
► check network cards and connection
–
–
–
–
–
–
►
►
►
►
ifconfig
ethtool
iperf
netstat
tcpdump
ethereal
tnsping service_name from server_node1[2]
tnsping service_name from client
lsnrctl services
sqlplus scott@service_name
23.3.2006
D.Wochele
IWR
Ideen
werde
nRealität
Trace and Logging
Sqlnet.log
► pmon registration to listener
tail -f listener.log --> 01-MAR-2006 16:11:46 *
service_update * RACDB1 * 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
werde
nRealität
Security
Listener
► in 10g 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_RACLINUX1 = 1DF5C2FD0FE9CFA2
SAVE_CONFIG_ON_STOP_LISTENER_RACLINUX1 = ON
LOGGING_LISTENER_RACLINUX1 = 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 CyberSafe, 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
werde
nRealitä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:bjava21"
url="jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=dlsun242) (PORT=5521))
(CONNECT_DATA=(SERVICE_NAME=bjava21)))"
23.3.2006
D.Wochele
IWR
Ideen
werde
nRealitä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 10g (you can connect to 9i)
► EZConnect Alias
– Client name resolution for service-name
(e.g. 141.52.167.211 cgcl1.fzk.de)
• DNS
• /etc/hosts
Examples:
connect scott/[email protected]: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
werde
nRealität