Oracle数据库中的几个名字及监听的配置问题
学习数据库的时候，由于数据库只建了一个库，而且只是本机访问，所以没有对listener.ora与tnsname.ora这两个文件进行过多设置，但是实际中要区分客户端与服务器端，相互之间的访问就存在微妙的关系了，处理不好又是一件头疼的事情。。而且Oracle中的名字比较多。。
首先要清楚listener.ora是存在于服务端的针对客户端访问数据库的，tnsnames.ora是针对于客户端的网络访问的，而且本地访问不需要监听器的。。
1.本地连接不需要listener
C:\Windows\system32>lsnrctl stop //关闭监听 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-MAR-2016 22:36:34 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.32.1)(PORT=1521))) The command completed successfully C:\Windows\system32>sqlplus root/root //可以注意到这里是能够访问的 SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 26 22:41:52 2016 Copyright (c) 1982, 2010, 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 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Windows\system32>sqlplus root/root@wang //但是@service_name后，就不走本地连接了，因为监听器没起，也起不来了 SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 26 22:42:10 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12541: TNS:no listener //报错 没有监听
2.先看一下两个文件的部分内容
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = e:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:e:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
#(SID_DESC = #####这一部分是静态注册手动添加内容，比动态注册稳定，相对于网络访问比较稳定，建议配置
###因为再刚起服务器的时候，由于延迟，数据库可能还没有连接监听器，造成客户端不能访问数据库。。。
# (SID_NAME = wang)#数据库实例名字
#(ORACLE_HOME = e:\oracle\product\11.2.0\dbhome_1)
#(GLOBAL_DBNAME = wang)#数据库名
#)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.32.1)(PORT = 1521))#网络监听地址要对应于tnsnames中的地址
)
tnsnames.ora
WANG = #网络服务域名，就是在root/root@后的字符串，这里可以随便起名不区分大小写,只要对应即可
(DESCRIPTION =
#(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.32.1)(PORT = 1521))
#(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wang)#网络服务名 这里不可以随便起名字，必须对应数据库中的service_names
)
)
LISTENER_WANG =
#(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.32.1)(PORT = 1521))
#(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
再看一下数据库的各种名字
Connected. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string wang --数据库名字 对应于sid db_unique_name string wang global_names boolean FALSE instance_name string wang --库实例名 lock_name_space string log_file_name_convert string service_names string wang --网络服务名
3.这里是刚刚启动监听服务
C:\Windows\system32>lsnrctl start ####启动监听服务 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-MAR-2016 23:08:27 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting tnslsnr: please wait... TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production System parameter file is e:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora Log messages written to e:\oracle\diag\tnslsnr\xx\listener\alert\log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.32.1)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.32.1)(PORT=1521))) STATUS of the LISTENER ###监听服务现在的一些状态 ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production Start Date 26-MAR-2016 23:08:30 Uptime 0 days 0 hr. 0 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File e:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora Listener Log File e:\oracle\diag\tnslsnr\xx\listener\alert\log.xml Listening Endpoints Summary... #######这里注意到 监听器还没有注册到wang数据库实例，所以静态注册实例是很有必要的，正如上方listener文件注释处 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.32.1)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully C:\Windows\System32\drivers>sqlplus root/root@wang ######可以注意到现在网络访问时出现问题，报错了，因为上边service_name还没有注册到监听器中 SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 26 23:08:36 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name:
4.稍等一会，数据库才把实例信息注册到监听器，可以使用网路服务访问数据库了。。
C:\Windows\system32>lsnrctl status #####查看监听器的状态 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-MAR-2016 23:18:58 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.32.1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production Start Date 26-MAR-2016 23:08:30 Uptime 0 days 0 hr. 10 min. 29 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File e:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora Listener Log File e:\oracle\diag\tnslsnr\xx\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.32.1)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "wang" has 1 instance(s). #####这里可以注意到数据库实例已经建立监听了 Instance "wang", status READY, has 1 handler(s) for this service... Service "wangXDB" has 1 instance(s). Instance "wang", status READY, has 1 handler(s) for this service... The command completed successfully C:\Windows\System32\drivers>tnsping wang ######而且tnsping也能ping通数据库了。。。。 TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-MAR-2016 23:16:26 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: e:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.32.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = wang))) OK (10 msec)
总结一下
第一尽量使用静态注册的方式
############类似于上方listener.ora添加这一段信息，稳定
(SID_DESC =
(SID_NAME = baiwang)
(ORACLE_HOME = e:\oracle\product\11.2.0\dbhome_1)
(GLOBAL_DBNAME = wang)
)
第二网络配置要一致，尽量使用ip，不要使用主机名，因为解析也需要时间；
第三可以实现多ip地址配置，访问同一数据库
