[20180125]環境變數TNS_ADMIN與RAC.txt

[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環境下斜線的問題,不知道那裡的問題,先放一放.