数据库启动之后,我们希望数据库可以提供远程访问的能力,这时候需要启动数据库的监听功能(lsnrctl),监听一般在1521端口上监听数据库的连接,接受来自客户端的访问。
我们首先看一下tnsnames.ora的配置:
ZXNM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.46.35)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zxnm)
)
)
UEP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = liweiah)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = uep)
)
)
其中,address是连接的协议、地址和端口信息,connect_data是包括了连接的信息,这里service_name定义了服务的名称。
C:/Documents and Settings/liweiah>tnsping uep
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 10-7月 -2009 11:26:54
Copyright (c) 1997 Oracle Corporation. All rights reserved.
已使用的参数文件:
D:/oracle/ora92/network/admin/sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.46.35)(P
OK(80毫秒)
C:/Documents and Settings/liweiah>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 7月 10 11:28:55 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn system/netnumen@uep
已连接。
SQL> conn system/netnumen@zxnm
ERROR:
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
这时候连接不上,我们查看一下数据库的service_names。
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string uep
下面,采取两种方法可以使得上述的连接成功。
第一种方式是修改数据库的参数,添加服务名:
SQL> alter system set service_names='uep,zxnm' scope=both;
System altered.
SQL> conn system/netnumen@zxnm
已连接。
第二种方式是修改listener.ora参数文件:
在参数文件里添加
(SID_DESC =
(GLOBAL_DBNAME = zxnm)
(ORACLE_HOME = /export/home/oracle/product/10)
(SID_NAME = uep)
)
SQL> conn system/netnumen@zxnm
已连接。
然后,我们看一下lsnrctl status的输出。
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.2.0 - Production on 10-JUL-2009 11:49:53
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.2.0 - Production
Start Date 10-JUL-2009 11:47:32
Uptime 0 days 0 hr. 2 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /export/home/oracle/product/10/network/admin/listener.ora
Listener Log File /export/home/oracle/product/10/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=liweiah)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "uep" has 1 instance(s).
Instance "uep", status READY, has 1 handler(s) for this service...
Service "uepXDB" has 1 instance(s).
Instance "uep", status READY, has 1 handler(s) for this service...
Service "uep_XPT" has 1 instance(s).
Instance "uep", status READY, has 1 handler(s) for this service...
Service "zxnm" has 1 instance(s).
Instance "uep", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
从上面的输出中,我们可以看到数据库实例uep对应着两个服务,一个服务名是uep,另一个服务名是zxnm。其中uep服务的状态是READY,而zxnm服务的状态时UNKNOWN。原因是uep服务是数据库动态监听注册的服务,而zxnm是修改listener.ora文件添加的服务。在客户端连接数据库的时候,可以选择uep或者zxnm其中一个服务名,达到负载均衡的效果。
下面,我们来看一下listener.ora配置文件
bash-3.00$ more listener.ora
# listener.ora Network Configuration File: /export/home/oracle/product/10/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /export/home/oracle/product/10)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = zxnm)
(ORACLE_HOME = /export/home/oracle/product/10)
(SID_NAME = uep)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = liweiah)(PORT = 1521))
)
)
这个文件分成两个部分,LISTENER信息包括了监听的协议、地址和端口信息。SID_LIST_LISTENER信息提供数据库服务列表。PLSExtProc是数据库对外部存储过程提供的本地监听。
我们总结客户端连接服务器的过程:在服务端,首先数据库会根据参数service_names动态创建监听服务,服务名就是service_name,实例名就是这个数据库的实例名;然后根据listener.ora参数文件创建监听服务,服务名是GLOBAL_DBNAME,SID_NAME是实例名。
客户端在连接服务器时首先根据ADDRESS里的地址连接到服务器,然后比较SERVICE_NMAE和数据库service_names里的名称,再和listener.ora里的GLOBAL_DBNAME比较,也就是说SERVICE_NAME和lsnrctl status输出的service名比较,如果匹配了则连接成功,否则连接失败。