一次访问问题排查-涉及TNS-03505、ORA-12154、TNS-12560、动态注册、防火墙、tnsping跟踪等 - 数据库编程

建了一个库，想通过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_clie

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