Oracle数据库服务器CPU100造成应用程序异常记录

前言 

本文针对Oracle的避免无解决方案，只尽量保持程序可以顺利运行。只是遇到问题的现象描述。如今很多项目的数据库即将转为mysql的国内二开数据库等其他类型的数据库。不知道以后会不会还有人用Oracle数据库。

程序背景

非记账类程序。不属于严格一致性的程序。项目遇到故障以业务优先抢通为主。故障时，对落盘要求不严格。

2023-05-07 如果记账要求严格的入库要求。宁可阻塞夜不能数据丢弃。今日又重新阅读oralce文献。Oracle提供的参数确定客户端也可配置。

https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html

程序为服务端。

系统环境linux。

故障描述

运维反馈服务异常，登入利用ps查看程序正常运行，运维通过ss命令查看大量连接未被接收，说明网络连通性正常。

查日志程序除首次退出记录数据库线程僵死，利用sqlplus直接连接，发现也僵死。针对这个现象作为开发，客户端我们是可以控制阻塞和非阻塞。一般为了防止因为服务端异常僵死，我们一般采用非阻塞连接，其他环境数据库异常我们一般会获得连接超时。而这次却僵死很久。因此反馈数据库异常，运维排查数据库服务器CPU100%。

程序在线却不服务，进行源码分析。发现首次重启进行主线程连接数据库加载。因此数据库连接僵死。造成程序无法正常服务。而以前其他环境数据库异常会报道time out 无连接僵死的情况。根据程序对落盘要求不高的项目性质，提供程序进行优化方案，数据库操作脱离主线程。而数据库提供的数据进行本地备份，作为程序启动优先加载项目，保证数据库异常可以业务畅通。

针对开发经验反馈给DBA客户端配置是否有相关设置可以设置超时配置，后续DBA没有给出解决方案。

自己查阅Oracle官方文档。反馈给DBA，但DBA没有给我们客户端的设置这些选项。

2023-05-07 今日查阅官方文档有描述可以在客户端设置收和发超时参数!! 记账类需要谨慎考虑，记账的宁可卡死让系统人工维护介入，防止系统瘫痪还产生交易信息。

针对实时数据最优原则的系统切故障期间得计价不重要的，建议设置。新文档根据新系统。因此旧版的是否可行最好实测一下。

会话层鉴权登入超时，服务端喝客户端各自维护超时等待时间。

会话层保活机制，是否启用-快速故障检测，客户端和服务端要都支持才行，目前普遍数据库客户端不支持。

会话层收发超时，一般IO非阻塞模式下经常使用。记账类的谨慎使用。

 

SQLNET.EXPIRE_TIME

Purpose

To specify a time interval, in minutes, to send a check to verify that client/server connections are active. The following usage notes apply to this parameter:

• Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.

• 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.

SQLNET.INBOUND_CONNECT_TIMEOUT

Purpose

 

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.

 

SQLNET.OUTBOUND_CONNECT_TIMEOUT

Purpose

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.

TCP.CONNECT_TIMEOUT

Purpose

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 to which a host name resolves. 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 the TCP.CONNECT_TIMEOUT setting because there are two IP addresses. In this example, the default timeout setting of 60 would cause a timeout in 120 seconds.

SQLNET.RECV_TIMEOUT

Purpose

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.

For environments in which clients shut down on occasion or abnormally, setting this parameter is recommended. 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 occurred messages 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.

 

SQLNET.SEND_TIMEOUT

Purpose

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.

 

bb

​​​​​​

2023-05-07

sqlnet.ora Profile Parameters

These are the sqlnet.ora profile configuration parameters that you use to administer database clients and servers.

ADR Diagnostic Parameters in sqlnet.ora

The diagnostic data for the critical errors is quickly captured and stored in the ADR for sqlnet.ora.

5.2.76 SQLNET.RECV_TIMEOUT

Use the sqlnet.ora parameter SQLNET.RECV_TIMEOUT to specify the duration of time that a database client or server should wait for data from a peer after establishing a connection.

 

Purpose

 

To specify the time for a database client or server to wait for data from the peer after establishing a connection. The peer must send some data within the time interval.

 

You can specify the time in hours, minutes, seconds, or milliseconds by using the hr, min, sec, or ms keyword respectively. If you do not specify a unit of measurement, then the default unit is sec.

 

Usage Notes

 

Setting this parameter for clients ensure that receive operation is not left in wait state indefinitely or for a long period due to an unusual termination of server process or server busy state. If a client does not receive response data in time specified, then it logsORA-12535: TNS:operation timed out and ORA-12609: TNS: Receive timeout occurred messages to the sqlnet.log file. If you choose to set the value, then set the value to an initial low value and adjust according to the system and network capacity. If necessary, use this parameter with the SQLNET.SEND_TIMEOUT parameter.

 

You can also set this parameter on the server-side to specify the time, in ms, sec, or min, for a server to wait for client data after connection establishment. 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 occurred messages 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. The server usually blocks on input from the client and gets these timeouts frequently if set to a low value.

 

Default Value

None

Minimum Value

1 ms

Recommended Value

 

Any number greater than the minimum value of 1 ms up to 4294967295 ms.

5.2.77 SQLNET.SEND_TIMEOUT

Use the sqlnet.ora parameter SQLNET.SEND_TIMEOUT to specify the duration of time for a database server to complete a send operation to clients after establishing a connection.

 

Purpose

 

To specify the time for a database server to complete a send operation to clients after establishing a connection.

 

You can specify the time in hours, minutes, seconds, or milliseconds by using the hr, min, sec, or ms keyword respectively. If you do not specify a unit of measurement, then the default unit is sec.

 

Usage Notes

 

Setting this parameter is recommended for environments in which clients shut down occasionally or unusually.

 

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 ms, sec, or min , for a client to complete send operations to the database server after connection establishment. It accepts different timeouts with or without space between the value and the unit. 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.

 

Default Value

None

Minimum Value

1 ms

Recommended Value

 

Any number greater than the minimum value of 1 ms up to 4294967295 ms.

5.2.61 SQLNET.OUTBOUND_CONNECT_TIMEOUT

Use the sqlnet.ora parameter SQLNET.OUTBOUND_CONNECT_TIMEOUT to specify the amount of time, in milliseconds, seconds, or minutes, in which clients must establish Oracle Net connections to database instances.

 

Purpose

 

To specify the time, in ms, sec, or min, for a client to establish an Oracle Net connection to the database instance.

 

Usage Notes

 

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. It accepts different timeouts with or without space between the value and the unit.

 

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. In case, no unit is mentioned, the default unit is sec.

 

The outbound connect timeout interval is only applicable for TCP, TCP with TLS, and IPC transport connections.

 

This parameter is overridden by the CONNECT_TIMEOUT parameter in the address description.

5.2.50 SQLNET.EXPIRE_TIME

Use the sqlnet.ora parameter SQLNET.EXPIRE_TIME to specify how often, in minutes, to verify that client and server connections are active.

5.2.24 RECV_BUF_SIZE

Use the sqlnet.ora parameter RECV_BUF_SIZE to specify buffer space limit for session receive operations.

 

Purpose

 

To specify the buffer space limit for receive operations of sessions

5.2.28 SEND_BUF_SIZE

Use the sqlnet parameter SEND_BUF_SIZE to specify the buffer space limit for session send operations.

 

Purpose

 

To specify the buffer space limit for send operations of sessions.

 

Usage Notes

 

You can override this parameter for a particular client connection by specifying the SEND_BUF_SIZE parameter in the connect descriptor for a client.

5.2.45 SQLNET.DOWN_HOSTS_TIMEOUT

Purpose

 

To specify the amount of time in seconds that information about the down state of server hosts is kept in client process cache.

官方信息和部分中文搜到的信息

 

SQLNET.RECV_TIMEOUT=3

 

SQLNET.SEND_TIMEOUT=3

https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF227

 

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

注﹕在修改sqlnet.ora文件之后重新启动监听﹐修改才能生效﹗﹗﹗

oracle网络设置主要包括三个文件，sqlnet.ora\ lisnter.ora\ tnsnames.ora

1、通过netmgr 可以配置 lisnter.ora 和 tnsnames.ora

2、lisnter.ora 为oracle服务器使用的监听器配置文件，监听器可以通过 lsnrctl 进行管理

3、tnsnames.ora 为客户端配置文件，为连接服务器的参数信息

4、sqlnet.ora 为服务端sql*net 网络配置文件，主要参数如下：

1.设置日志参数

#设置客户端和服务器端的log文件的目录

LOG_DIRECTORY_CLIENT

LOG_DIRECTORY_SERVER

#设置客户端和服务器端的log文件的名称

LOG_FILE_CLIENT

LOG_FILE_SERVER

2.设置默认的domain，会在连接中自动追加domain

NAMES.DEFAULT_DOMAIN

如设置NAMES.DEFAULT_DOMAIN=us.acme.com

使用conn scott/tiger@test连接数据库时会自动追加domain，变成conn scott/tiger@test.us.acme.com，而直接使用connscott/tiger@test.us.acme.com连接数据库则不会追加

//可以通过show parameter domain查看,一般将该参数注释掉；如果设置了该参数

//通过netmgr设置本地服务名时系统会在本地服务名后面自动追加该参数

//sql*plus访问时如果该参数存在，则会在服务名追加该参数，然后利用追加后的服务名，在tns中查找

//造成tnsping可以 但是使用sqlplus不可以的现象

3.设置客户端的命名方法和优先级（最常用的参数），可选值有tnsnames,onames,hostname,ldap等

NAMES.DIRECTORY_PATH

例如：names.directory_path =（tnsnames,onames,hostname)

//服务端的解析路径1.tnsname; 2.oname; 3.hostname

4.设置允许连入数据库的客户端版本，可选值10,9,8,7

SQLNET_ALLOWED_LOGON_VERSIONS

5.设置使用何种验证方式，可选值

none（使用用户名/密码的方式连接数据库）

all（使用所有方式的验证，包括OS验证）

nts（OS验证方式，连入OS后可以不用密码连接数据库）

SQLNET.AUTHENTICATION_SERVICES

6.设置客户端和服务器是否使用加密，可选值

accepted（如果对方设置为requested或required，则使用加密服务）

rejected（不使用加密服务，即使对方设置为requested也不使用）

requested（如果对方使用加密服务，则使用加密服务）

required（使用加密服务，如果对方没有使用则无法连接）

SQLNET.ENCRYPTION_CLIENT

SQLNET.ENCRYPTION_SERVER

#设置使用的加密算法

SQLNET.ENCRYPTION_TYPES_CLIENT

SQLNET.ENCRYPTION_TYPES_SERVER

7.设置连入数据库后必须在多长时间内完成认证（如：输入用户名/密码），超过此时间没有完成的话，数据库会断开此连接，并将客户端的IP地址和ORA-12170: TNS:Connect timeout occurred错误信息记录到sqlnet.log，而且客户端会收到ORA-12547: TNS:lost contact或ORA-12637: Packet receive failed错误信息。这个设置主要是为了防止denial-of-service攻击

SQLNET.INBOUND_CONNECT_TIMEOUT

8.设置在指定的时间间隔内必须有数据接收/发送，为了防止长时间的等待

SQLNET.RECV_TIMEOUT

SQLNET.SEND_TIMEOUT