Database Network : Configuration Parameters

LSNRCTL> command [listener_name]

lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script

Listener.ora file parameters:

·         PASSWORDS_listener_name  #an unencrypted password

·         LSNRCTL> change_password #to establish an encrypted password

·         LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora

·         LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands

·         Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subsequently registered again

·         SERVICES : To obtain detailed information about the database services, instances, and service handlers (dispatchers and dedicated servers) to which the listener forwards client connection requests. Lsnrctl> services listener_name

·         SET : to alter the parameter values for the listener.

LSNRCTL> set

The following operations are available after set:

·         password                   

·         rawmode

·         displaymode : lsnrctl> set displaymode {COMPAT | NORMAL | VERBOSE | RAW}                 

·         trc_file

·         trc_directory              

·         trc_level : To set a specific level of tracing for the listener.

LSNRCTL> SET TRC_LEVEL {level}. Level:

off: for no trace output

user: for user trace information

admin: for administration trace information

support: for Oracle Support Services trace information

·         log_file : To set the name for the listener log file. LSNRCTL> SET LOG_FILE list.log                   

·         log_directory : to set destination directory where the listener log file is written

·         log_status : to turn listener logging on or off                

·         current_listener  : to set current listener. Lsnrctl> set current_listener listener_name;

·         inbound_connect_timeout : To specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.   

·         startup_waittime

·         save_config_on_stop : LSNRCTL> SET SAVE_CONFIG_ON_STOP  {on | off}  : to specify whether or not changes made to the parameter values for the listener by the SET commands are to be saved to the listener.ora file at the time the listener is stopped with the STOP command.

·         LSNRCTL> show # The following operations are available after show

·         rawmode                             

·         displaymode

·         rules                                

·         trc_file

·         trc_directory                       

·         trc_level

·         log_file                            

·         log_directory

·         log_status                          

·         current_listener

·         inbound_connect_timeout             

·         startup_waittime

·         snmp_visible                         

·         save_config_on_stop

·         dynamic_registration                

·         enable_global_dynamic_endpoint

·         oracle_home                         

·         pid

·         connection_rate_limit               

·         valid_node_checking_registration

·         registration_invited_nodes          

·         registration_excluded_nodes

·         remote_registration_address

Network Configuration Parameters:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = Restoration-kg.org)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

))

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORCL)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)

(SID_NAME = ORCL)

))

#Service:

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = restoration-kg.org)(PORT = 1522))

(CONNECT_DATA = (SERVER = DEDICATED)  (SERVICE_NAME = ORCL) ))

ADDRESS parameter defines a protocol address. Embed this parameter under an ADDRESS_LIST or DESCRIPTION parameter.

·         ADDRESS_LIST : defines a list of protocol addresses that share common characteristics. For e.g.

(ADDRESS_LIST=

(LOAD_BALANCE=on)

(ADDRESS=   (PROTOCOL=tcp)   (HOST=sales-server)   (PORT=1521))

(ADDRESS=   (PROTOCOL=tcp)   (HOST=hr-server)   (PORT=1521)))

(ADDRESS_LIST=

(ADDRESS=   (PROTOCOL=tcp)   (HOST=finance-server)   (PORT=1521)))

net_service_name=

(DESCRIPTION=                  # Description contains the connect descriptor

(ADDRESS=(protocol_address_information))   #ADDRESS contains the protocol address. Protocol, host, port

(CONNECT_DATA=              # CONNECT_DATA contains the database service identification information

(SERVICE_NAME=service_name)))

·         DESCRIPTION_LIST defines a list of connect descriptors.

·         tnsnames.ora file also supports connect descriptors with multiple lists of addresses, each with its own characteristics.

net_service_name=

(DESCRIPTION=

(ADDRESS_LIST=

(LOAD_BALANCE=on)

(FAILOVER=off)

(ADDRESS=(protocol_address_information))

(ADDRESS=(protocol_address_information)))

(ADDRESS_LIST=

(LOAD_BALANCE=off)

(FAILOVER=on)

(ADDRESS=(protocol_address_information))

(ADDRESS=(protocol_address_information)))

(CONNECT_DATA=

(SERVICE_NAME=service_name)))

sample2=

(DESCRIPTION=

(LOAD_BALANCE=on)

(FAILOVER=on)

(ADDRESS_LIST=

(SOURCE_ROUTE=yes)

(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))

(ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521)))

(ADDRESS_LIST=

(SOURCE_ROUTE=yes)

(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(port=1630))

(ADDRESS=(PROTOCOL=tcp)(HOST=host4)(port=1521)))

(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))

·         FAILOVER to enable or disable connect-time failover for multiple protocol addresses. Value : on, yes or true. Default on for DESCRIPTION_LISTs, DESCRIPTIONs, and ADDRESS_LISTs

·         LOAD_BALANCE to enable or disable client load balancing for multiple protocol addresses. Default on for DESCRIPTION_LISTs.

·         RECV_BUF_SIZE, SEND_BUF_SIZE

·         SOURCE_ROUTE to enable routing through multiple protocol addresses. When you set to on or yes, Oracle Net uses each address in order until the destination is reached. Embed this parameter under either the DESCRIPTION_LIST parameter, theDESCRIPTION parameter, or the ADDRESS_LIST parameter.

·         CONNECT_DATA to define the service to which to connect. Subparameters: failover_mode, global_name, hs, instance_name, rdb_database, server, service_name, sid

·         FAILOVER_MODE: instructs Oracle Net to fail over to a different listener if the first listener fails during runtime. Depending upon the configuration, session or any SELECT statements which were in progress are automatically failed over. This type of failover is called Transparent Application Failover (TAF).

Subparameter of failover-mode:

·         BACKUP: Specify the failover node by its net service name. A separate net service name must be created for the failover node.

·         TYPE: Specify the type of failover. 3 types:

·   Session: Fails over the session; that is, if a user's connection is lost, a new session is automatically created for the user on the backup. This type of failover does not attempt to recover selects.

·   Select: Allows users with open cursors to continue fetching on them after failure. However, this mode involves overhead on the client side in normal select operations.

·   None: This is the default, in which no failover functionality is used. This can also be explicitly specified to prevent failover from happening.

·         METHOD: Specify how fast failover is to occur from the primary node to the backup node:

·         Basic: Establishes connections at failover time. This option requires almost no work on the backup database server until failover time.

·         Preconnect: Pre-establishes connections. This provides faster failover but requires that the backup instance be able to support all connections from every supported instance.

·         RETRIES: Specify the number of times to attempt to connect after a failover. IfDELAY is specified, RETRIES defaults to five retry attempts.

·         DELAY: Specify the amount of time in seconds to wait between connect attempts. If RETRIES is specified, DELAY defaults to one second.

·         HS : instructs Oracle Net to connect to a non-Oracle system through Heterogeneous Services. Value=ok

·         SERVER : instruct the listener to connect the client to a specific type of service handler (dedicated|shared).

LISTENER CONFIGURATION:

LISTENER=

(DESCRIPTION=                                           # container for listener protocol addresses

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521)) # to specify a single listener protocol address

(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=sales.us.acme.com)

(ORACLE_HOME=/oracle10g)

(SID_NAME=sales))

(SID_DESC=

(SID_NAME=plsextproc)

(ORACLE_HOME=/oracle10g)

(PROGRAM=extproc)))

·         QUEUESIZE to specify the number of concurrent connection requests that the listener can accept on a TCP/IP or IPC listening endpoint. For e.g.  (ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=20)))

·         RECV_BUF_SIZE, SEND_BUF_SIZE

Static Service Registration (SID_LIST) Section

·         SID_LIST to identify a list of SID descriptions.

·         SID_DESC: specify service information for a specific database instance or a non-database service. SID_DESC permits following parameters:

·         ENVS:  specify environment variables for the listener to set prior to executing (as a child process) a dedicated server program or an executable specified with the PROGRAM parameter. For e.g. (ENVS="variable=value")

·         GLOBAL_DBNAME: DB name with domain name.

·         ORACLE_HOME:

·         PROGRAM: to identify the service executable program name.

·         SID_NAME:

·         SDU: session data unit in bytes

Control Parameters:

·         ADMIN_RESTRICTIONS_listener_name : to restrict runtime administration of the listener. Value: on|off

·         INBOUND_CONNECT_TIMEOUT_listener_name: specify the destination directory of the listener log file. Default: $ORACLE_HOME/network/log

·         LOG_DIRECTORY_listener_name

·         LOG_FILE_listener_name: Default: listener.log

·         LOGGING_listener_name: to turn logging on or off.

·         PASSWORDS_listener_name: For e.g. PASSWORDS_LISTENER=(2D6C48144CF753AC)

·         SAVE_CONFIG_ON_STOP_listener_name

·         SSL_CLIENT_AUTHENTICATION

·         TRACE_DIRECTORY_listener_name

·         TRACE_FILE_listener_name

·         TRACE_FILELEN_listener_name: specify the size of the listener trace files in kilobytes (KB).

·         TRACE_FILENO_listener_name: specify the number of trace files for listener tracing

·         TRACE_LEVEL_listener_name

·         TRACE_TIMESTAMP_listener_name

·         WALLET_LOCATION

Protocol parameters:

(PROTOCOL=ipc)(KEY=sales)  #Key: Unique name for service/SID

(PROTOCOL=nmp)(SERVER=sales)(PIPE=dbpipe0)  #nmp=named pipes,pipe=pipe_name used to connect with database server

PROTOCOL=sdp)(HOST=sales-server)(PORT=1521)

(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)

(PROTOCOL=tcps)(HOST=sales-server)(PORT=2484)  #TCP/IP with SSL

Profile parameters (sqlnet.ora)

·         BEQUEATH_DETACH=YES|NO:  to turn signal handling on or off for UNIX systems.

·         DEFAULT_SDU_SIZE: to specify the session data unit (SDU)size, in bytes to connections. Default 2k. 512-32kb

·         DISABLE_OOB : If turned off, the parameter enables Oracle Net to send and receive "break" messages using urgent data provided by the underlying protocol.

·         LOG_DIRECTORY_CLIENT to specify the destination directory for the client log file.

·         LOG_DIRECTORY_SERVER to specify the destination directory for the database server log file.

·         LOG_FILE_CLIENT to specify the name of the log file for the client.

·         LOG_FILE_SERVER to specify the name of the log file for the database server. LOG_FILE_SERVER=srv.log

·         NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests

·         NAMES.DIRECTORY_PATH to specify the order of the naming methods used for client name resolution lookups. For e.g. NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname)

·         RECV_BUF_SIZE to specify the buffer space limit for receive operations of sessions. Default 32k (32768)

·         SEND_BUF_SIZE to specify the buffer space limit for send operations of sessions. Default 8K

·         SQLNET_ALLOWED_LOGON_VERSIONS to specify which authentication protocols are allowed by the client or database. For e.g. SQLNET_ALLOWED_LOGON_VERSIONS=(10,9,8)

·         SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services.

Authentication Methods Available with Oracle Net Services:

·         none for no authentication methods. A valid username and password can be used to access the database.

·         all for all authentication methods

·         nts for Windows NT native authentication

Authentication Methods Available with Oracle Advanced Security: 

·         kerberos5 for Kerberos authentication

·         radius for RADIUS authentication

·         dcegssapi for DCE GSSAPI authentication

·         For e.g.: SQLNET.AUTHENTICATION_SERVICES=(kerberos5)

·         SQLNET.CLIENT_REGISTRATION to set a unique identifier for this client computer. For e.g. SQLNET.CLIENT_REGISTRATION=1432

·         SQLNET.CRYPTO_CHECKSUM_CLIENT to specify the checksum behavior for the client.

·         SQLNET.CRYPTO_CHECKSUM_SERVER to specify the checksum behavior for the database server

·         SQLNET.ENCRYPTION_CLIENT to turn encryption on for the client.

·         SQLNET.ENCRYPTION_SERVER to turn encryption on for the database server. Values:

·         Accepted to enable the security service if required or requested by the other side

·         Rejected to disable the security service, even if the required by the other side

·         Requested to enable the security service if the other side allows it

·         Required to enable the security service and disallow the connection if the other side is not enabled for the security service

·         SQLNET.ENCRYPTION_TYPES_CLIENT to specify a list of encryption algorithms for the client to use.

·         SQLNET.ENCRYPTION_TYPES_SERVER to specify a list of encryption algorithms for the database server to use. Values:

•       3des112 for triple DES with a two-key (112 bit) option

•       3des168 for triple DES with a three-key (168 bit) option

•       des for standard 56 bit key size

•       des40 for 40 bit key size

•       rc4_40 for 40 bit key size

•       rc4_56 for 56 bit key size

•       rc4_128 for 128 bit key size

•       rc4_256 for 256 bit key size. For e.g. : SQLNET.ENCRYPTION_TYPES_CLIENT=( rc4_56, des, ...)

·         SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT to specify a list of crypto-checksum algorithms for the client to use.

·         SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER to specify a list of crypto-checksum algorithms for the database server to use. Values

·         md5 for the RSA Data Security's MD5 algorithm

·         sha1 for the Secure Hash algorithm. For e.g. SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(MD5)

·         SQLNET.CRYPTO_SEED to specify the characters used when generating cryptographic keys. The string should be 10-70 random characters. This optional parameter is required for when encryption or checksumming are turned on. For e.g. SQLNET.CRYPTO_SEED="qwertyuiopasdfghjkl;zxcvbnm,.s1"

·         SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. For e.g. SQLNET.EXPIRE_TIME=10

·         SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information. If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection with ORA-12170, ORA-12547 or ORA-12637 error.  Default 60 seconds. Oracle recommends to set the value of the INBOUND_CONNECT_TIMEOUT_listener_name (for e.g. 2 secs.) parameter to a lower value than theSQLNET.INBOUND_CONNECT_TIMEOUT (for e.g. 3 secs.) parameter.

·         SQLNET.RECV_TIMEOUT to specify the time, in seconds, for a database server to wait for client data after connection establishment. A client must send some data within the time interval.

·         SQLNET.SEND_TIMEOUT  to specify the time, in seconds, for a database server to complete a send operation to clients after connection establishment.

·         TCP.EXCLUDED_NODES to specify which clients are denied access to the database. For e.g. TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)

·         TCP.INVITED_NODES to specify which clients are allowed access to the database. This list takes precedence over theTCP.EXCLUDED_NODES parameter if both lists are present.

·         TCP.VALIDNODE_CHECKING to check for theTCP.INVITED_NODES and TCP.EXCLUDED_NODES to determine which clients to allow or deny access. For e.g. TCP.VALIDNODE_CHECKING=yes

·         TCP.NODELAY to preempt delays in buffer flushing within the TCP/IP protocol stack.

·         TNSPING.TRACE_DIRECTORY to specify the destination directory for the TNSPING utility trace file, tnsping.trc

·         TNSPING.TRACE_LEVEL to turn TNSPING utility tracing on, at a specific level, or off. Level: off, user, admin, support

·         TRACE_DIRECTORY_CLIENT to specify the destination directory for the client trace file.

·         TRACE_DIRECTORY_SERVER to specify the destination directory for the database server trace file.

·         TRACE_FILE_CLIENT to specify the name of the client trace file. For e.g. clientsqlnet.trc

·         TRACE_FILE_SERVER to specify the name of the database server trace file.

·         TRACE_FILELEN_CLIENT to specify the size of the client trace files in kilobytes (KB). When the size is met, the trace information is written to the next file. TRACE_FILELEN_CLIENT=100

·         TRACE_FILELEN_SERVER to specify the size of the database server trace files in kilobytes (KB).

·         TRACE_FILENO_CLIENT to specify the number of trace files for client tracing. When this parameter is set along with theTRACE_FILELEN_CLIENT parameter, trace files are used in a cyclical fashion.

·         TRACE_FILENO_SERVER to specify the number of trace files for database server tracing.

·         TRACE_LEVEL_CLIENT to turn client tracing on, at a specific level, or off. Level: off, user, admin, support

·         TRACE_LEVEL_SERVER to turn server tracing on, at a specific level, or off.

·         TRACE_TIMESTAMP_CLIENT|SERVER to add a time stamp in the form of dd-mon-yyyy hh:mi:ss:mil to every trace event in the client|server trace file.  Value on or true| off or false.

·         TRACE_UNIQUE_CLIENT to specify whether or not a unique trace file is created for each client trace session.

·         USE_DEDICATED_SERVER automatically appends(SERVER=dedicated) to the connect data for a connect descriptor. This way connections from this client use a dedicated server process, even if shared server is configured. Value : on|off

·         WALLET_LOCATION to specify the location of wallets. Wallets are certificates, keys, and trustpoints processed by SSL.

WALLET_LOCATION=

(SOURCE=

(METHOD=file)

(METHOD_DATA=

(DIRECTORY=directory)

[(PKCS11=TRUE/FALSE)]))

·         SOURCE: Specify the type of storage for wallets and storage location.

·         METHOD: Specify the type of storage. For e.g.: file, entr (Entrust Wallet), reg (windows registry), mcs (Microsoft certified store)

·         METHOD_DATA: Specify the storage location.

·         DIRECTORY: Specify the location of Oracle wallets on file system.

·         KEY: Specify the wallet type and location in the Windows registry.

·         PROFILE: Specify the Entrust profile file (.epf).

·         INIFILE: Specify the Entrust initialization file (.ini).

·         WALLET_OVERRIDE: This parameter determines whether the client should override the strong authentication credential with the password credential in the secret store to log into the database.

Commands for mkstore to manage the entries in the secret store:

·         To create a wallet: mkstore -wrl <wallet location> –create

·         To create an entry: mkstore –wrl <wallet location> –createCredential <alias> <username> <password>

·         To modify an entry: mkstore -wrl <wallet location> –modifyCredential <alias> <username> <password>

·         To delete an entry: mkstore -wrl <wallet location> –deleteCredential <alias>

·         To list all entries: mkstore -wrl <wallet location> –listCredential