linux中sqlnet.ora路径,sqlnet.ora 配置(Linux 平台)

1.SQLNET.AUTHENTICATION_SERVICES

场景1 ： 没有配置 AUTHENTICATION_SERVICES 或 注释掉 AUTHENTICATION_SERVICES

此场景下 -> 无密、错密、正确密码都可以登录sysdba

[oracle@XAG193 admin]$ cat sqlnet.ora

#SQLNET.AUTHENTICATION_SERVICES =(none)

[oracle@XAG193 admin]$ sqlplus / as sysdba

[oracle@XAG193 admin]$ sqlplus sys/error_pass as sysdba

[oracle@XAG193 admin]$ sqlplus sys/123456 as sysdba --正确密码

场景2：(none) or (nts) 此场景下 -> 需要验证oracle的用户密码

------------------------------------------------------------------------------------

[oracle@XAG193 admin]$ sqlplus / as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

[oracle@XAG193 admin]$ sqlplus sys/error_pass as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

[oracle@XAG193 admin]$ sqlplus sys/123456 as sysdba

------------------------------------------------------------------------------------

场景3：(all) -- 同场景1 未配置时

NAMES.DIRECTORY_PATH

[oracle@XAG193 admin]$ cat tnsnames.ora

------------------------------------------------------------------------------------

MYCDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = XAG193)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = MYCDB)

)

)

LISTENER_MYCDB =

(ADDRESS = (PROTOCOL = TCP)(HOST = XAG193)(PORT = 1521))

------------------------------------------------------------------------------------

场景1 : (TNSNAMES)

那么客户端就只会从tnsnames.ora查找你要连接的字符串(db_alias)记录，

如果tnsname.ora文件中没有此记录，则连接不上数据库。

[oracle@XAG193 admin]$ cat sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (none)

NAMES.DIRECTORY_PATH = (TNSNAMES)

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@MYCDB2

场景2：(EZCONNECT)

就是说可以不用在tnsnames.ora文件去查询db_alias就可以通过Service Name进行数据库的连接

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@XAG193:1521/MYCDB

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@XAG193:1521/MYPDB

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@MYCDB2

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

场景3：(TNSNAMES,EZCONNECT)

[oracle@XAG193 admin]$ cat sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (none)

NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@MYCDB2

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@XAG193:1521/MYCDB

[oracle@XAG193 admin]$ sqlplus C##XAG/xag123@XAG193:1521/MYPDB

3.SQLNET.EXPIRE_TIME (单位分钟)

DCD: Dead Connection Detection ，可以用于检测、标记僵死而没有断开会session，

再由PMON进行清理，释放资源。开启DCD，只需要在服务端的sqlnet.ora文件中

添加SQLNET.EXPIRE_TIME参数，单位为分钟。如果时间达到这个值，server端就

是发出一个”probe” packet 给客户端，如要客户断是正常的，这个packet就被忽略，

timer重新计时；如果客户端异常中断，则server端就会收到一个消息，用以释放连接。

DCD还可以用于防止防火墙的timeout,由于防火墙设置timeout原因，当client和server在timeout时间内

没有数据传输的时候，会话就会被防火墙断开。而设置SQLNET.EXPIRE_TIME参数，使其小于防火墙

的timeout时间，就可以避免这一情况的发生

——————————————————————————————

ORA-03135: connection lost contact.

——————————————————————————————

某B/S架构的应用程序在测试过程中每隔1到2小时出现“错误信息：ORA-03135: 连接失去联系的报错”，

详细报错信息如下：

ORA-03135出现的原因较多，问题有可能出在网络设备、操作系统、数据库上，

问题最有可能是由于网络闪段和防火墙配置所导致。

测试案例： SQLNET.EXPIRE_TIME=1 (本次是为测试方便，正常应该配置10分钟左右)

[oracle@XAG193 admin]$ cat sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (none)

NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)

SQLNET.EXPIRE_TIME=1

#客户端

D:\Tools\sqlcl\bin>sql C##XAG/xag123@MYCDB2 ---MYCDB2 为客户端tnsnames.ora 中配置的别名

or

D:\Tools\sqlcl\bin>sql C##XAG/xag123@192.168.40.193:1521/MYPDB

or

D:\Tools\sqlcl\bin>sql /nolog

SQL> CONN C##XAG/xag123@192.168.40.193:1521/MYPDB

OR

SQL> CONN C##XAG/xag123@//192.168.40.193:1521/MYPDB

#服务端(查到 192.168.40.1 客户端有连接到服务器端)

[root@XAG193 ~]# netstat -pan | grep 192.168.40.193:1521

tcp 0 0 192.168.40.193:33741 192.168.40.193:1521 ESTABLISHED 1651/ora_lreg_MYCDB

tcp6 0 0 192.168.40.193:1521 192.168.40.193:33741 ESTABLISHED 1565/tnslsnr

tcp6 0 0 192.168.40.193:1521 192.168.40.1:62171 ESTABLISHED 3631/oracleMYCDB

[root@XAG193 ~]# tcpdump -i ens33 -nnn host 192.168.40.1 and port 1521

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on ens33, link-type EN10MB (Ethernet), capture size 262144 bytes

-------------------------------------------------------------------------------------------------------------------------

01:44:11.882228 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1131350432, win 353, length 0

01:44:11.882496 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0

01:45:12.042737 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1, win 353, length 0

01:45:12.043181 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0

01:46:12.203380 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1, win 353, length 0

01:46:12.204089 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0

可以看到每隔1分钟，服务端向客户端发起连接，检测客户端是否存在(客户端一直有ack应答，则表示客户端正常)

4.SQLNET.INBOUND_CONNECT_TIMEOUT (单位秒)

它表示等待用户认证超时的时间，单位是秒，缺省值是60秒，如果用户认证超时了，

alert.log显示出错信息"WARNING: inbound connection timed out (ORA-3136)"，

sqlnet.log里面出现TNS-12535: TNS:operation timed out错误信息。

[oracle@XAG193 admin]$ cat sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (none)

NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)

SQLNET.EXPIRE_TIME=1

SQLNET.INBOUND_CONNECT_TIMEOUT=10

#客户端用错误密码登录，如下监控日志

[oracle@XAG193 trace]$ tail -20f /u01/app/oracle/diag/rdbms/mycdb/MYCDB/trace/alert_MYCDB.log

***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:

TNS for Linux: Version 19.0.0.0.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production

Version 19.3.0.0.0

Time: 10-JAN-2021 23:13:38

Tracing not turned on.

Tns error struct:

ns main err code: 12535

TNS-12535: TNS:operation timed out

ns secondary err code: 12606

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.1)(PORT=50359))

2021-01-10T23:13:38.159872+08:00

WARNING: inbound connection timed out (ORA-3136)

LOG_FILE_SERVER

[oracle@XAG193 admin]$ cat sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (none)

NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)

SQLNET.EXPIRE_TIME=1

SQLNET.INBOUND_CONNECT_TIMEOUT=10

DIAG_ADR_ENABLED=off

LOG_DIRECTORY_SERVER=$ORACLE_HOME/network/log

LOG_FILE_SERVER=sqlnet_server.log

LOG_DIRECTORY_CLIENT=$ORACLE_HOME/network/log

LOG_FILE_CLIENT=sqlnet_client.log