自我总结：
服务端：
1、在修改完/etc/hosts文件后，需将hostname临时重置一下。
[root@localhost ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 zwl localhost

(这个可以忽略)2、/u01/oracle/product/10.2.0/network/admin/sqlnet.ora
tcp.validnode_checking=no SQLNET.AUTHENTICATION_SERVICES= (ALL)

3、检查实例名
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_SID=ora10g
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

4、检查监听
[oracle@localhost ~]$ cat /u01/oracle/product/10.2.0/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora10g)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = ora10g)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.131)(PORT = 1521))
)

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-DEC-2013 16:50:40

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.46.131)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 17-DEC-2013 16:31:26
Uptime 0 days 0 hr. 19 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /u01/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.46.131)(PORT=1521)))
Services Summary...
Service "ora10g" has 1 instance(s).
Instance "ora10g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

客户端：
1、tnsnames.ora可以手动添加
D:\app\Administrator\product\11.1.0\client_1\NETWORK\ADMIN\tnsnames.ora

ORA10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
2、客户端cmd tnsping ora10g 能解析即OK，或者使用Net manager 配置一下tns也一样，和第一步效果是一样的，然后就可以使用pl/sql访问了。
注意tnsping 的有可能是网络服务名，这个可以看一下Net manager 配置。但是也可能是实例名与网络服务名是一样的。直接写文件tnaname.ora也是一样的道理。

C:\Documents and Settings\Administrator> tnsping ora10g_clone2
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 17-12月-
2013 16:33:50
Copyright (c) 1997, 2007, Oracle. All rights reserved.
已使用的参数文件:
D:\app\Administrator\product\11.1.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.46.131)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora10g)))
OK (10 毫秒)

C:\Documents and Settings\Administrator> tnsping ora10g_clone3
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 17-12月-
2013 16:38:27
Copyright (c) 1997, 2007, Oracle. All rights reserved.
已使用的参数文件:
D:\app\Administrator\product\11.1.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.46.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora10g)))
OK (0 毫秒)

3、客户端监听与tns
D:\app\Administrator\product\11.1.0\client_1\NETWORK\ADMIN\tnsnames.ora

ORA10G_CLONE2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)

ORA10G_CLONE3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.128)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)

D:\app\Administrator\product\11.1.0\client_1\NETWORK\ADMIN\listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Oracle8)
(SID_NAME = ora10g)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.128)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.131)(PORT = 1521))
)
)

一般监听发生问题需检查如下五项：

1、/etc/hosts及域名解析配置文件（注意hostname要和tnsname.ora的host一致，实在搞不清楚就直接使用固定的IP地址吧）

2、监听日志大小

3、监听状态

4、$ORACLE_HOME/oracle可执行文件权限
5、检查数据库配置show parameter local_listener

总结：
show parameter local_listener查到的参数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string ORA11G

该VALUE参数对应了tnsnames.ora文件中的监听配置
ORA11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11G)
)
)

LISTENER_ORA11G =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))

ITPub博客

oracle 启动监听报错TNS-12547: TNS:lost contact

最新文章