当前位置：首页 -> 基础 -> 数据库编程

一次访问问题排查-涉及TNS-03505、ORA-12154、TNS-12560、动态注册、防火墙、tnsping跟踪等(二)

2015-02-02 20:14:10 · 作者: · 浏览: 49
标签: 一次 访问 问题 排查 涉及 TNS-03505 ORA-12154 TNS-12560 动态 注册 防火墙 tnsping 跟踪
nt=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