本文介绍了Oracle SQLNet配置文件sqlnet.ora中各项参数的作用及设置方法。包括认证服务、连接控制、压缩设置、超时控制等内容，帮助读者理解并合理配置SQLNet参数。
The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server.
By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.
Note:参数太多只列了部分
里面有很多涉及认证方式的参数，详细使用可以查官方文档《Database Security Guide》的
- 常用设置
- NAMES.DIRECTORY_PATH
解析数据连接方式的顺序
NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect)
tnsnames (local naming method)
ldap (directory naming method)
ezconnect or hostname (Easy Connect naming method)
通过简单连接到数据库会有限制：不能使用任何oracle高级网络特性，如不能用连接池
Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service name.
nis (external naming method)
Set to resolve service information through an existing Network Information Service (NIS).
- SQLNET.AUTHENTICATION_SERVICES
To enable one or more authentication services.
When using the SQLNET.AUTHENTICATION_SERVICES value all, the server attempts to authenticate using each of the following methods. The server falls back to the ones lower on the list if the ones higher on the list were unsuccessful:
Authentication based on a service external to the database, such as a service on the network layer, Kerberos, or RADIUS.
Authentication based on the operating system user's membership in an administrative operating system group. Group names are platform-specific. This authentication is applicable to administrative connections only.
Authentication performed by the database.
Authentication based on credentials stored in a directory server.
默认为ALL, 可选值：
none for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database.
all for all authentication methods.
beq for native operating system authentication for operating systems other than Microsoft Windows
kerberos5 for Kerberos authentication
nts for Microsoft Windows native operating system authentication
radius for Remote Authentication Dial-In User Service (RADIUS) authentication
tcps for SSL authentication
Example
SQLNET.AUTHENTICATION_SERVICES=(nts,kerberos5)
亲测：
不设置: 本地sysdba不用输入用户名与密码，sys as sysdba能远程连接, 其它用户 as sysdba不能远程连， 一般用户可以远程连接
none:本地sysdba登陆必须输入用户名与密码，sys as sysdba能远程连接, 其它用户 as sysdba不能远程连， 一般用户可以远程连接
all: 本地sysdba不用输入用户名与密码，所有用户的网络连接均连不了
none,all: 等同于none
nts: windows上的没测试
- TCP.VALIDNODE_CHECKING
To enable and disable valid node checking for incoming connections.
The TCP.INVITED_NODES and TCP.EXCLUDED_NODES parameters are valid only when the TCP.VALIDNODE_CHECKING parameter is set to yes.
TCP.VALIDNODE_CHECKING=yes
- TCP.EXCLUDED_NODES
To specify which clients are denied access to the database.
This parameter can use wildcards for IPv4 addresses and CIDR notation for IPv4 and IPv6 addresses.
TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)
TCP.EXCLUDED_NODES=(finance.us.example.com, mktg.us.example.com, 192.0.2.25,
172.30.*, 2001:DB8:200C:417A/32)
To specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.
TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)
This parameter can use wildcards for IPv4 addresses and CIDR notation for IPv4 and IPv6 addresses.
TCP.INVITED_NODES=(sales.us.example.com, hr.us.example.com, 192.0.*,
2001:DB8:200C:433B/32)
如果tcp.invited_nodes与tcp.excluded_nodes都存在，则tcp.invited_nodes优先
要将本地地址，或者Cluster群集其他节点的地址都加入到允许列表，否则监听器可能无法启动
修改之后，一定要重起监听或reload才能生效
仅提供对TCP/IP协议的支持
- USE_DEDICATED_SERVER
To append (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor used by the client.
It overrides the current value of the SERVER parameter in the tnsnames.ora file.
If set to on, then the parameter 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.
Default:off Values：on to append (SERVER=dedicated)
- 优化
- DEFAULT_SDU_SIZE
To specify the session data unit (SDU) size, in bytes to connections.
Oracle recommends setting this parameter in both the client-side and server-side sqlnet.ora file to ensure the same SDU size is used throughout a connection.
When the configured values of client and database server do not match for a session, the lower of the two values is used.
You can override this parameter for a particular client connection by specifying the SDU parameter in the connect descriptor for a client.
默认8192 ,单位bytes
- RECV_BUF_SIZE
To specify the buffer space limit for receive operations of sessions.
You can override this parameter for a particular client connection by specifying the RECV_BUF_SIZE parameter in the connect descriptor for a client.
This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.
The default value for this parameter is operating system specific. The default for Linux 2.6 operating system is 87380 bytes.
- SEND_BUF_SIZE
To specify the buffer space limit for send operations of sessions.
You can override this parameter for a particular client connection by specifying the SEND_BUF_SIZE parameter in the connect descriptor for a client.
This parameter is supported by the TCP/IP, TCP/IP with SSL, and SDP protocols.
The default value for this parameter is operating system specific. The default for Linux 2.6 operating system is 16 KB.
- SQLNET.COMPRESSION
To enable or disable data compression. If both the server and client have this parameter set to ON, then compression is used for the connection.
The SQLNET.COMPRESSION parameter applies to all database connections, except for Oracle Data Guard streaming redo and SecureFiles LOBs (Large Objects).
- SQLNET.COMPRESSION_ACCELERATION
To specify the use of hardware accelerated version of compression using this parameter if it is available for that platform. This parameter can be specified under Oracle Connection Manager alias description.
compression_acceleration = on
- SQLNET.COMPRESSION_LEVELS
To specify the compression level.
The compression levels are used at time of negotiation to verify which levels are used at both ends, and to select one level.
For Database Resident Connection Pooling (DRCP), only the compression level low is supported.
Values:
low : to use low CPU usage and low compression ratio. default
high : to use high CPU usage and high compression ratio.
- SQLNET.COMPRESSION_THRESHOLD
To specify the minimum data size, in bytes, for which compression is needed.
Compression is not be done if the size of the data to be sent is less than this value.
Default: 1024 bytes
- SQLNET.EXPIRE_TIME
To specify a time interval, in minutes, to send a check to verify that client/server connections are active.
Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the system supports TCP keepalive tuning, then Oracle Net Services automatically uses the enhanced detection model, and tunes the TCP keepalive parameters
If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.
This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Recommended Value 10
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. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.
The default value of this parameter is appropriate for typical usage scenarios. However, if you need to explicitly set a different value, then Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying the values for these parameters, note the following recommendations:
Set both parameters to an initial low value.
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and SQLNET.INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.
Default：60 seconds
- SQLNET.OUTBOUND_CONNECT_TIMEOUT
Bug 5052337
To specify the time, in seconds, for a client to establish an Oracle Net connection to the database instance.
If an Oracle Net connection is not established in the time specified, then the connect attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error.
The outbound connect timeout interval is a superset of the TCP connect timeout interval, which specifies a limit on the time taken to establish a TCP connection. Additionally, the outbound connect timeout interval includes the time taken to be connected to an Oracle instance providing the requested service.
Without this parameter, a client connection request to the database server may block for the default TCP connect timeout duration (60 seconds) when the database server host system is unreachable.
The outbound connect timeout interval is only applicable for TCP, TCP with SSL, and IPC transport connections.
This parameter is overridden by the CONNECT_TIMEOUT parameter in the address description.
Default: None
- SQLNET.RECV_TIMEOUT
To specify the time, in seconds, for a database server to wait for client data after establishing a connection. A client must send some data within the time interval.
Setting this parameter is recommended for environments in which clients shut down on occasion or abnormally. If a client does not send any data in time specified, then the database server logs ORA-12535: TNS:operation timed out and ORA-12609: TNS: Receive timeout occurredmessages to the sqlnet.log file. Without this parameter, the database server may continue to wait for data from clients that may be down or are experiencing difficulties.
You can also set this parameter on the client-side to specify the time, in seconds, for a client to wait for response data from the database server after connection establishment. Without this parameter, the client may wait a long period of time for a response from a database server saturated with requests. If you choose to set the value, then set the value to an initial low value and adjust according to system and network capacity. If necessary, use this parameter with the SQLNET.SEND_TIMEOUT parameter.
To specify the time, in seconds, for a database server to complete a send operation to clients after establishing a connection.
Setting this parameter is recommended for environments in which clients shut down occasionally or abnormally.
If the database server cannot complete a send operation in the time specified, then it logs ORA-12535: TNS:operation timed out and ORA-12608: TNS: Send timeout occurred messages to the sqlnet.log file. Without this parameter, the database server may continue to send responses to clients that are unable to receive data due to a downed computer or a busy state.
You can also set this parameter on the client-side to specify the time, in seconds, for a client to complete send operations to the database server after connection establishment. Without this parameter, the client may continue to send requests to a database server already saturated with requests. If you choose to set the value, then set the value to an initial low value and adjust according to system and network capacity. If necessary, use this parameter with the SQLNET.RECV_TIMEOUT parameter.
- TCP.CONNECT_TIMEOUT
To specify the time, in seconds, for a client to establish a TCP connection (PROTOCOL=tcp in the TNS connect address) to the database server.
If a TCP connection to the database host is not established in the time specified, then the connection attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error.
The timeout applies to each IP address that resolves to a host name. For example, if a host name resolves to an IPv6 and an IPv4 address, and if the host is not reachable through the network, then the connection request times out twice because there are two IP addresses. In this example, the default timeout setting of 60 would cause a timeout in 120 seconds.
To preempt delays in buffer flushing within the TCP/IP protocol stack. Default yes
To configure the maximum length of the queue for pending connections on a TCP listening socket.System-defined maximum value. The defined maximum value for Linux is 128.
To specify the base directory into which tracing and logging incidents are stored when ADR is enabled.
Default: The default on the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined.
To specify whether ADR tracing is enabled.
If the DIAG_ADR_ENABLED parameter is set to OFF, then non-ADR file tracing is used.
Default: on
To turn client tracing on at a specified level or to turn it off.
This parameter is also applicable when non-ADR tracing is used.
Default：off or 0
off or 0 for no trace output
user or 4 for user trace information
admin or 10 for administration trace information
support or 16 for Oracle Support Services trace information
To turn server tracing on at a specified level or to turn it off.
This parameter is also applicable when non-ADR tracing is used.
Default：off or 0
off or 0 for no trace output
user or 4 for user trace information
admin or 10 for administration trace information
support or 16 for Oracle Support Services trace information
To add a time stamp in the form of dd-mmm-yyyy hh:mm:ss:mil to every trace event in the client trace file, which has a default name of sqlnet.trc.
Default：on
Values：on or true | off or false
To add a time stamp in the form of dd-mmm-yyyy hh:mm:ss:mil to every trace event in the database server trace file, which has a default name of svr_pid.trc.
Default：on
Values：on or true | off or false
（四）Non-ADR Diagnostic Parameters in sqlnet.ora
The default value of DIAG_ADR_ENABLED is on. Therefore, the DIAG_ADR_ENABLED parameter must explicitly be set to offin order for non-ADR tracing to be used.
扫一扫
1160
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
