Oracle监听器是一个服务器端程序,用于监听所有来自客户端的请求,并为其提供数据库服务。
一. 配置侦听器(listener)
网络配置可以使用netmanager(网络配置管理器)来完成。Net manager用于网络连接服务名,侦听器等的创建和设置。同一台服务器可以配置多个侦听器,但侦听的端口不能相同。
不同的侦听器可以侦听对同一个数据库的请求,同一个侦听器也可以侦听对不同数据库的请求,这样当一个监听器忙时,另外的侦听器仍然可以为客户端请求提供服务,以减少等待时间。
侦听器的配置信息存放在lsitener.ora中:$ORACLE_HOME/network/admin,使用netmgr工具相当于修改该文件。
侦听器的设置主要包括侦听位置和相应的数据库服务,侦听位置(listener.ora)包括主机,端口和使用的通讯协议,oracle默认的标准端口是1521,通常采用的协议是TCP/IP.
*协议:用于指定监听程序要使用的网络协议,监听程序可以使用多种网络协议,但最常用的是TCP/IP协议。
*主机:用于指定oracle服务器所在的机器的主机名或IP地址。当侦听器和oracle服务器位于同一台机器,主机名也可以输入为localhost
*端口号:用于指定监听程序所要使用的TCP/IP端口号,默认监听端口号为1521.如果要制定其他端口号,必须为操作系统未占用的端口。
案例1:最简单的监听配置(默认端口1521,动态注册)
CASE1:服务器端配置有listener.ora但没有配置tnsnames.ora,客户端上配置有tnsnames.ora
服务器端:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat5)(PORT= 1521))
)
)
客户端:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
此时数据库服务会动态注册到监听,也可以手工注册
Alter system register
客户端可以正常连接
服务器端不能通过网络连接:
oracle@redhat5 admin]$ sqlplussys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Jan 21 02:28:20 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve theconnect identifier specified
而且这里不管客户端的tnsnames.ora中的网络配置服务名如何更改都是可以连接的,比如
ORCL0411 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
结论: tnsnames.ora是客户端必须的配置文件,其中的网络服务名与数据库实例无关,可以自行调整,客户端关注的是listener的位置信息,即端口,协议和主机名。
CASE2:动态监听下,改变服务器的service参数
SQL> show parameter service;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
service_names string orcl
SQL> alter system set db_domain=testscope=spfile;
System altered.
重启实例
SQL> show parameter service
NAME TYPE VALUE
----------------------------------------------- ------------------------------
service_names string orcl.TEST
此时再通过网络连接:
[oracle@redhat5 admin]$ sqlplussys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Jan 21 06:04:17 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currentlyknow of service requested in connect
Descriptor
改变tnsnames.ora中的service配置
[oracle@redhat5 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/oracle/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.TEST)
)
)
再通过网络连接,即可正常连接
[oracle@redhat5 admin]$ sqlplussys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Jan 21 06:06:55 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
结论:动态注册监听的配置下,tnsnames.ora中的service需要和服务器配置的service一致。
Case3:配置非默认端口的监听
[oracle@redhat5 admin]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.3.0 -Production on 21-JAN-2016 06:24:50
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat5)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.3.0 - Production
Start Date 21-JAN-2016 06:20:05
Uptime 0 days 0 hr. 4 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/redhat5/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat5)(PORT=1522)))
The listener supports no services
The command completed successfully
由于动态注册只能注册到默认的端口,此时监听无法注册数据库服务。只能改为静态注册
重启监听,可以看到数据库服务已经注册到了监听
[oracle@redhat5 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 -Production on 21-JAN-2016 06:26:59
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting/oracle/app/oracle/product/11.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 -Production
System parameter file is/oracle/app/oracle/product/11.2/db_1/network/admin/listener.ora
Log messages written to/oracle/app/oracle/diag/tnslsnr/redhat5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat5)(PORT=1522)))
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat5)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.3.0 - Production
Start Date 21-JAN-2016 06:26:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/redhat5/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat5)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for thisservice...
The command completed successfully
[oracle@redhat5 admin]$ vi listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
这里需要注意一个细节!!!当静态注册的配置如下时
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/oracle/product/11.2/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat5)(PORT = 1522))
)
ADR_BASE_LISTENER = /oracle/app/oracle
发现并不能正常的通过网络连接数据库
[oracle@redhat5 ~]$ sqlplus sys/oracle@orclas sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Jan 21 06:32:44 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
因为ORACLE_SID在linux环境下是区分大小写的!!
改为和实例名一致的orcl即可正常的连接了。
此时再次改变service的配置,tnsnames中的service仍然保持不变,仍然可以正常连接数据库
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.10)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
结论,非默认端口的监听必须采用静态注册的方式,因为监听只能注册到默认端口。在静态注册的情况下,tnsnames.ora中的servicename与服务器配置不一样时也可以正常连接。因为客户端根据tns中的配置已经可以找到服务器监听,而数据库服务已经注册在了监听中。
二.关于注册
1.注册的理解。
2.参数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string TEST
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl.TEST
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl,aaa
3.动态注册(待续)
如果需要向非默认监听注册,则需要配置local_listener参数
4.静态注册
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/oracle/product/11.2/db_1)
(SID_NAME = orcl)
)
)