一次訪問問題排查-涉及TNS-03505、ORA-12154、TNS-12560、動態註冊、防火牆、tnsping跟蹤等，tns-03505tnsping
建了一個庫，想通過Oracle Net訪問，需要配置監聽器和tnsnames.ora，接下來碰到一系列的問題。。。
1. 添加監聽器配置，listener.ora檔案預設包括：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /opt/app/ora11g
為了建立監聽器名稱，添加如下：
DCSOPEN =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.101.19.57)(Port = 1521)))
或
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dcsopen2Node)(PORT = 1521))
)
)
ADR_BASE_DCSOPEN = /opt/app/ora11g
或
DCSOPEN =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.101.19.57)(Port = 1521)))
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dcsopen)
(ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)
(SID_NAME = dcsopen)
)
)
ADR_BASE_DCSOPEN = /opt/app/ora11g
或
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
)
ADR_BASE_DCSOPEN = /opt/app/ora11g
2. 添加原生tnsnames.ora檔案配置：
dcsopen =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
3. 使用tnsping dcsopen測試，報錯：
ora11g>tnsping dcsopen
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 00:38:55
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
4. 使用sqlplus登入測試，報錯：
ora11g>sqlplus dcsopen/dcsopen1@dcsopen
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 13 23:11:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
5. 檢查監聽器狀態，
ora11g>lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 01:55:32
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 07-JAN-2015 20:19:09
Uptime 0 days 5 hr. 36 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora
Listener Log File /opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dcsopen2Node)(PORT=1521)))
...
並未看到dcsopen的配置。
6. 開啟trace，查看tnsping失敗的原因：
建立sqlnet.ora檔案：
# sqlnet.ora Network Configuration File: /opt/oracle/102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Trace_level_client=16
Trace_directory_client=/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
tnsping.trace_directory=/opt/app/ora11g/product/11.2.0/dcsopen/network/admin
tnsping.trace_level=admin
執行tnsping報錯後，查看tnsping.trc檔案：
tail: tnsping.trc: file truncated
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2015 19:21:31
Copyright (c) 1997, 2009, Oracle. All rights reserved.
--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
New trace stream is /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsping.trc
New trace level is 6
--- TRACE CONFIGURATION INFORMATION ENDS ---
--- PARAMETER SOURCE INFORMATION FOLLOWS ---
Attempted load of system pfile source /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet.ora
Parameter source loaded successfully
-> PARAMETER TABLE LOAD RESULTS FOLLOW <-
Successful parameter table load
-> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
Diag_adr_enabled = off
tnsping.trace_level = admin
Trace_level_client = 16
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
tnsping.trace_directory = /opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_unique_client = on
Trace_directory_client = /opt/app/ora11g/product/11.2.0/dcsopen/network/admin
Trace_timestamp_client = on
--- PARAMETER SOURCE INFORMATION ENDS ---
--- LOG CONFIGURATION INFORMATION FOLLOWS ---
Log stream will be "standard output"
Log stream validation not requested
--- LOG CONFIGURATION INFORMATION ENDS ---
nlstdipi: entry
nlstdipi: exit
nnfun2awanm: entry
nnfgiinit: entry
nncpcin_maybe_init: default name server domain is [root]
nnfgiinit: Installing read path
nnfgsrsp: entry
nnfgsrsp: Obtaining path parameter from names.directory_path or native_names.directory_path
nnfgsrdp: entry
nnfgsrdp: Setting path:
nnfgsrdp: checking element TNSNAMES
nnfgsrdp: checking element EZCONNECT
nnfgsrdp: Path set
nnfun2a: entry
nlolgobj: entry
nnfgrne: entry
nnfgrne: Going though read path adapters
nnfgrne: Switching to TNSNAMES adapter
nnftboot: entry
nlpaxini: entry
nlpaxini: exit
nnftmlf_make_local_addrfile: entry
nnftmlf_make_local_addrfile:
construction of local names file failed
nnftmlf_make_local_addrfile: exit
nlpaxini: entry
nlpaxini: exit
nnftmlf_make_system_addrfile: entry
nnftmlf_make_system_addrfile: system names file is /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames.ora
nnftmlf_make_system_addrfile: exit
nnftboot: exit
nnftrne: entry
nnftrne: Original name: dcsopen
nnfttran: entry
nnfgrne:
Query unsuccessful, skipping to next adapter
nnfgrne: Switching to EZCONNECT adapter
nnfhboot: entry
nnfhboot: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo:
getaddrinfo() failed with error -3
snlinGetAddrInfo: exit
nnfgrne: Query unsuccessful, skipping to next adapter
nnfgrne: exit
nnfun2a:
address for name "dcsopen" not found
nnfun2awanm: Getting the path of sqlnet.ora
nnfun2awanm: Getting the path of local and system tnsnames.ora
nnfun2awanm: exit
nlse_term_audit: entry
nlse_term_audit: exit
可以看到其中出現的一些錯誤：
construction of local names file failed
Query unsuccessful, skipping to next adapter
getaddrinfo() failed with error -3
address for name "dcsopen" not found
直觀看，沒有識別出dcsopen監聽。
後來查詢MOS有篇文章(Client Connections Fail With TNS-12154 / ORA-12154 (文檔 ID 1150680.1))
說這個情況的原因可能是：This means Oracle Net is
unable to read the file correctly or entry inside the file.
解決方案是：
Rebuild the TNSNAMES.ORA file, using the GUI Net Manager tool, is the recommend solution. This will ensure there are no mistakes in the net admin file, for example, brackets, tab, spacing, etc. Also ensure the tnsnames.ora file can be read by the oracle user.
6. 於是先使用圖形化netca建立dcsopen的監聽項錯誤依舊，經過一系列測試，確定了檔案
listener.ora：
# listener.ora Network Configuration File: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora
# Generated by Oracle configuration tools.
DCSOPEN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_DCSOPEN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)
(PROGRAM = extproc)
)
)
ADR_BASE_DCSOPEN = /opt/app/ora11g
其中：
(1) 參考eygle的經驗，
使用動態註冊服務。
(指當執行個體啟動後，由後台進程PMON在監聽器中註冊資料庫服務資訊。動態註冊機制下，原來監聽器中的SID_LIST部分將不再需要。)
(2) 上面之所以還有一個SID_LIST，這是預設的PLSExtProc是為外部預存程序調用而配置。一個簡單的監聽器配置如上所述。
啟動監聽，提示：
ora11g>lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 01:55:32
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 07-JAN-2015 20:19:09
Uptime 0 days 5 hr. 36 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora
Listener Log File /opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dcsopen2Node)(PORT=1521)))
Services Summary...
Service "
dcsopen" has 1 instance(s).
Instance "
dcsopen", status
READY, has 1 handler(s) for this service...
Service "dcsopenXDB" has 1 instance(s).
Instance "dcsopen", status READY, has 1 handler(s) for this service...
The command completed successfully
本機測試tnsping正常了。
7. 接著，使用netca新增tnsnames.ora檔案：
# tnsnames.ora Network Configuration File: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DCSOPEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
本機測試sqlplus ...@dcsopen正常了。
8. 從另外一台機器訪問這個資料庫dcsopen，修改tnsnames.ora檔案，執行tnsping dcsopen報錯：
ora10g@localhost.localdomain$tnsping dcsopen
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 08-JAN-2015 00:51:37
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =172.101.19.57)(PORT = 1521))) (CONNECT_DATA = (service_name = dcsopen)))
TNS-12560: TNS:protocol adapter error
執行sqlplus ...@dcsopen報錯：
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 00:58:14 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
於是先看看連接埠是否開通，執行telnet報錯：
ora10g@localhost.localdomain$telnet 172.101.19.57 1521
Trying 172.101.19.57...
telnet: connect to address 172.101.19.57:
No route to host
如果連接埠未開，實際報錯：
ora10g@localhost.localdomain$telnet 172.27.19.56 1521
Trying 172.27.19.56...
telnet: connect to address 172.27.19.56:
Connection refused
是不是防火牆的問題？？？
從資料庫伺服器關閉防火牆：
[root@dcsopen2Node ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: nat mangle filter [ OK ]
iptables: Unloading modules: [ OK ]
再從遠程機器執行：
ora10g@localhost.localdomain$telnet 172.101.19.571521
Trying 172.101.19.57...
Connected to 172.101.19.57.
Escape character is '^]'.
說明連接埠已開，更重要的是，明確了，就是防火牆問題。於是參考，將/etc/sysconfig/iptables檔案新增一行，表示允許1521連接埠訪問：
[root@dcsopen2Node sysconfig]# vi iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
啟動防火牆：
[root@dcsopen2Node sysconfig]# service iptables start
iptables: Applying firewall rules: [ OK ]
或service iptables restart
從遠程機訪問：
ora10g@localhost.localdomain$sqlplus dcsopen/dcsopen1@dcsopen
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 01:11:12 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
正常了。
總結：
1. listener.ora和tnsnames.ora設定檔最好使用netca工具建立，否則手工修改很可能出現各式問題導致無法解析讀取。
2. 我的排查思路是，先確保本機可以tnsping和sqlplus ...@xxx，本機可訪問了，再看遠程機器。
遠端存取tnsping和sqlplus報錯TNS-12560: TNS:protocol adapter error，說明可能兩台機器之間的串連有問題而不是監聽自身的問題。
3. telnet報錯是No route to host，不是Connection refused，不可輕易認為是網路連接埠未開。因為此時表示可以正常訪問到另一台遠程機器了，應該考慮到是否是防火牆的問題。此時可以通過關閉防火牆測試是否會出現這個問題來判斷。
4. 會使用tnsping的trace跟蹤，設定sqlnet.ora設定檔。
5. 動態註冊的使用。好處：簡化監聽器配置、串連時failover(RAC)、運行時負載平衡(RAC)。
6. 解決過程中，有的文章說需要設定$TNS_ADMIN環境變數，儘管這是有兩個Oracle版本同機，但後來測試不配置，也可以訪問，說明這不是關鍵問題。
解決過程中，有的文章說sqlnet.ora中的NAME.DEFAULT_DOMAIN參數，以為從追蹤記錄檔tnsping.trc看default name server domain is [root]，可後來沒有設定，也可以訪問，說明這不是關鍵問題。
解決過程中，對我有協助的文章：
http://blog.itpub.net/7199859/viewspace-374281/
http://blog.itpub.net/519536/viewspace-673794/
http://blog.sina.com.cn/s/blog_9151e7300101ksui.html
http://www.cnblogs.com/chinaairforce1/archive/2009/10/22/1588103.html
http://blog.csdn.net/huzia/article/details/21526043
7.最重要的一條總結：不放棄！
輔助知識：
執行netca前需要root執行xhost +，否則提示：
java.lang.NullPointerException
at oracle.ewt.lwAWT.BufferedApplet.<init>(Unknown Source)
at oracle.net.ca.NetCA.<init>(NetCA.java:420)
at oracle.net.ca.NetCA.main(NetCA.java:406)
仍存問題：
對比如下配置，未發現不同點，奇怪：
手工建立：
dcsopen =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
工具建立：
DCSOPEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.101.19.57)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dcsopen)
)
)
