自我總結：
服務端：
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))

oracle 啟動監聽報錯TNS-12547: TNS:lost contact

相關文章