[20180125]环境变量TNS_ADMIN与RAC.txt
--//这几天一直折腾SQLNET.EXPIRE_TIME参数,我发现测试与想像对不上.
--//才发现如果client端连接服务器,SQLNET.EXPIRE_TIME参数是从数据库环境变量继承过来,当然如果没有定义,
--//缺省来自oracle用户的$ORACLE_HOME/network/admin/sqlnet.ora.而不是从监听进程.
--//我通过测试来说明问题:
1.环境:
SYS@fyhis1> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//以grid用户执行:
$ env | grep -i tns
TNS_ADMIN=/u01/app/11.2.0/grid/network/admin
--//可以发现安装者定义环境变量TNS_ADMIN,有许多安装者不定义,我们实施人员定义这个环境变量在grid用户,这样更容易理解问题所在.
# ps -ef | grep tns[l]
grid 13398 1 0 12:11 ? 00:00:02 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid 13656 1 0 12:11 ? 00:00:02 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
grid 14250 1 0 12:12 ? 00:00:04 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
# cat /proc/13398/environ | tr '\0' '\n' | grep -i tns
TNS_ADMIN=/u01/app/11.2.0/grid/network/admin/
# cat /proc/13656/environ | tr '\0' '\n' | grep -i tns
TNS_ADMIN=/u01/app/11.2.0/grid/network/admin/
# cat /proc/14250/environ | tr '\0' '\n' | grep -i tns
TNS_ADMIN=/u01/app/11.2.0/grid/network/admin/
--//对应的监听在启动时继承环境变量TNS_ADMIN.
--//注:我不知道为什么进程里面的环境变量后面有斜线.而定义的环境变量没有.我也重启监听还是一样.不知道为什么?
--//也许oracle启动监听时自动加上了.
# ps -ef | grep pmon_f[y]
oracle 17109 1 0 2017 ? 00:47:01 ora_pmon_fyhis1
# cat /proc/17109/environ | tr '\0' '\n' | grep -i tns
--//你可以发现rac环境启动数据库并不继承grid定义的环境变量TNS_ADMIN.或者启动数据库时清除了(我的理解)
--//实际上这样很好理解,比如你使用dblink,定义的tnsnames.ora正常都会在oracle用户的$ORACLE_HOME/network/admin/tnsnames.ora查询.
--//如果继承这个参数这样就要修改grid用户的$ORACLE_HOME/network/admin/tnsnames.ora.
2.测试:
SYSTEM@192.168.90.14:1521/fyhis> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
239 57823 9548:6160 DEDICATED 8084 137 215 alter system kill session '239,57823' immediate;
--//进程号=8084.
# cat /proc/8084/environ | tr '\0' '\n' | grep -i tns
--//没有显示,说明client连接数据库时TNS_ADMIN环境变量不是来自监听,而是数据库进程.而这里数据库没有定义,这样缺省选择oracle
--//用户的$ORACLE_HOME/network/admin/sqlnet.ora.
--//在rac环境要TNS_ADMIN参数有效.必须执行如下(例子):
srvctl setenv listener -l LISTENER -t TNS_ADMIN='/u01/app/11.2.0/grid/network/admin/'
srvctl setenv database -d DB_NAME -T TNS_ADMIN='/u01/app/11.2.0/grid/network/admin/'
3.在单机数据库测试比较就更清晰了:
--//关闭监听与数据库.步骤略.
$ export TNS_ADMIN=$ORACLE_HOME/network/admin
$ lsnrctl start
$ export TNS_ADMIN=/tmp
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
--//数据库进程与监听进程各自使用的TNS_ADMIN环境变量.
$ ps -ef | egrep "pmon_boo[k]|tnslsn[r]"
oracle 23904 1 0 08:39 ? 00:00:00 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 23917 1 0 08:40 ? 00:00:00 ora_pmon_book
$ cat /proc/23904/environ | strings | grep -i tns
TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin
$ cat /proc/23917/environ | strings | grep -i tns
TNS_ADMIN=/tmp
--//各自继承当时定义的环境变量.注意这里后面有没有斜线,不知道那里的问题....^_^.
--//开启会话:
R:\fyhis>sqlplus scott/book@78
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 26 08:36:05 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@78> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@78> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
41 7 7188:4932 DEDICATED 24004 27 4 alter system kill session '41,7' immediate;
--//进程号=24004
$ cat /proc/24004/environ | strings | grep -i tns
TNS_ADMIN=/tmp
--//从这里可以看出client端连接数据库并不从监听进程继承环境变量,而是来自数据库启动时的定义TNS_ADMIN的值.
--//而且从这里可以推断,因为与监听无关,修改SQLNET.EXPIRE_TIME 并不需要重启数据库,重新的连接自动使用这个新定义参数.
$ grep -i sqlnet.expire_time $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME = 2
$ grep -i sqlnet.expire_time /tmp/sqlnet.ora
SQLNET.EXPIRE_TIME = 1
--//关闭旧会话,在服务端打开tcpdupm跟踪,重新启动新的会话.
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nn -vv
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
08:48:39.008669 IP (tos 0x0, ttl 127, id 8648, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.58966 > 192.168.100.78.1521: S, cksum 0x2417 (correct), 749914719:749914719(0) win 8192 <mss 1460,nop,wscale 2,nop,nop,sackOK>
08:48:39.008706 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.58966: S, cksum 0x47cc (incorrect (-> 0x6721), 1497320088:1497320088(0) ack 749914720 win 14600 <mss 1460,nop,nop,sackOK,nop,wscale 7>
...
08:48:39.260793 IP (tos 0x0, ttl 64, id 40456, offset 0, flags [DF], proto: TCP (6), length: 57) 192.168.100.78.1521 > 192.168.98.6.58966: P, cksum 0x47d1 (incorrect (-> 0x97a3), 6607:6624(17) ack 7936 win 330
08:48:39.261060 IP (tos 0x0, ttl 127, id 8701, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.58966 > 192.168.100.78.1521: ., cksum 0xb6f4 (correct), 7936:7936(0) ack 6624 win 16307 <nop,nop,sack 1 {6607:6624}>
--//等看看.不执行任何sql语句
08:50:39.053253 IP (tos 0x0, ttl 64, id 40457, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.58966: P, cksum 0x47ca (incorrect (-> 0xa0a7), 6624:6634(10) ack 7936 win 330
08:50:39.258030 IP (tos 0x0, ttl 127, id 13633, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.58966 > 192.168.100.78.1521: ., cksum 0x6863 (correct), 7936:7936(0) ack 6634 win 16304
08:51:39.063621 IP (tos 0x0, ttl 64, id 40458, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.58966: P, cksum 0x47ca (incorrect (-> 0xa09d), 6634:6644(10) ack 7936 win 330
08:51:39.268202 IP (tos 0x0, ttl 127, id 14595, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.58966 > 192.168.100.78.1521: ., cksum 0x685b (correct), 7936:7936(0) ack 6644 win 16302
08:52:39.073980 IP (tos 0x0, ttl 64, id 40459, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.58966: P, cksum 0x47ca (incorrect (-> 0xa093), 6644:6654(10) ack 7936 win 330
08:52:39.276417 IP (tos 0x0, ttl 127, id 15443, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.58966 > 192.168.100.78.1521: ., cksum 0x6854 (correct), 7936:7936(0) ack 6654 win 16299
--//开始间隔2分钟,后面间隔1分钟.
--//修改/tmp/sqlnet.ora的SQLNET.EXPIRE_TIME = 3.
$ grep -i sqlnet.expire_time /tmp/sqlnet.ora
SQLNET.EXPIRE_TIME = 3
--//退出会话,再次重新建立连接新会话:
...
08:55:08.907574 IP (tos 0x0, ttl 64, id 20174, offset 0, flags [DF], proto: TCP (6), length: 57) 192.168.100.78.1521 > 192.168.98.6.60590: P, cksum 0x47d1 (incorrect (-> 0x662d), 6607:6624(17) ack 7934 win 330
08:55:09.103359 IP (tos 0x0, ttl 127, id 24535, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.60590 > 192.168.100.78.1521: ., cksum 0x36f4 (correct), 7934:7934(0) ack 6624 win 16307
--//等看看.不执行任何sql语句
09:01:08.908987 IP (tos 0x0, ttl 64, id 20175, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.60590: P, cksum 0x47ca (incorrect (-> 0x6f31), 6624:6634(10) ack 7934 win 330
09:01:09.102596 IP (tos 0x0, ttl 127, id 31141, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.60590 > 192.168.100.78.1521: ., cksum 0x36ed (correct), 7934:7934(0) ack 6634 win 16304
09:04:08.939223 IP (tos 0x0, ttl 64, id 20176, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.60590: P, cksum 0x47ca (incorrect (-> 0x6f27), 6634:6644(10) ack 7934 win 330
09:04:09.133210 IP (tos 0x0, ttl 127, id 3945, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.60590 > 192.168.100.78.1521: ., cksum 0x36e5 (correct), 7934:7934(0) ack 6644 win 16302
09:07:08.969410 IP (tos 0x0, ttl 64, id 20177, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.60590: P, cksum 0x47ca (incorrect (-> 0x6f1d), 6644:6654(10) ack 7934 win 330
09:07:09.165767 IP (tos 0x0, ttl 127, id 10811, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.60590 > 192.168.100.78.1521: ., cksum 0x36de (correct), 7934:7934(0) ack 6654 win 16299
--//现在间隔6分钟,后面间隔3分钟.而测试过程我并没有重启监听以及数据库,建立的新连接自动使用新定义的SQLNET.EXPIRE_TIME参数.
总结:
--//这么小问题,折腾这么长时间,好像有点钻牛角尖了.
--//rac环境下斜线的问题,不知道那里的问题,先放一放.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2150539/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2150539/