ORACLE监听
今天来学习⼀下监听的相关内容，昨晚被⽼⼤问了两个关于监听很简单的问题，但是却吞吞吐吐回答，⽽且有⼀个问题还答错了，刚刚查了下资料，才发现“驴头对了马嘴”，哭笑不得。
⼀、监听(listener)是什么
是Oracle基于服务器端的⼀种⽹络服务，主要⽤于监听客户端向数据库服务器端提出的连接请求。既然是基于服务器端的服务，那么它也只存在于数据库服务器端，进⾏的设置也是在数据库服务器端完成的。
oracle⽹络配置有三个配置⽂件 a，a，a，其⽬录均在$ORACLE_HOME/network/admin 。
Oracle客户端与服务器端的连接是通过客户端发出连接请求，由服务器端对客户端连接请求进⾏合法检查，如果连接请求有效，则进⾏连接，否则拒绝该连接。⼆、如何配置监听
1）a 主要负责服务器端的监听配置，配置的基本模板：
# a Network Configuration File: /home/oracle/app/product/11/db/network/a
# Generated by Oracle configuration tools.
LISTENER_ORA = （注：LISTENER_ORA 为监听名字）
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.1.4.5)(PORT = 1521))
)
)
SID_LIST_LISTENER_ORA = （注：LISTENER_ORA 为监听名字）
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = wsj)
(ORACLE_HOME = /home/oracle/app/product/11/db)
(SID_NAME = wsj)
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
其中：
PROTOCOL指的是监听接收链接的协议；
HOST是指的监听运⾏的主机或者IP地址；
PORT指的是监听运⾏的端⼝；
SID_NAME指的是监听服务的实例名；
GLOBAL_NAME指的是监听服务的服务名；
ORACLE_HOME指的是监听服务的$ORACLE_HOME。
如果[监听名]和SID_LIST_[监听名]配置是成对出现的，这代表是静态监听；如果a⽂件只有[监听名]的配置，监听就变成动态监听。
动态注册：客户端（sys⽤户）只能在远程数据库启动的情况下连接上，远程数据库关闭时客户端连接不上远程数据库。
静态注册：⽆论远程数据库处于开启还是关闭状态，客户端（sys⽤户）都可连接上。
静态注册可以在客户端控制服务器端数据库的启停，⽽动态注册则不可以，动态注册的监听在实例关闭时会被注销。
数据库关闭的状态下，只有sys⽤户可连⼊数据库，其他⽤户不可连⼊。
测试：
当数据库关闭状态时，sys⽤户可以连⼊数据库，提⽰接⼊空实例。其他⽤户不能连接⾄数据库：
[oracle@localhost ~]$ sqlplus /as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 1121:14:562018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
[oracle@localhost ~]$ sqlplus hr/12
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 11 21:16:47 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
a.当为动态配置时：
查看a:
[oracle@localhost ~]$ cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@localhost admin]$ a
# a Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/a
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
在数据库关闭的情况下查看监听状态：
[oracle@localhost ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0- Production on11-FEB-201821:27:00
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
-
-----------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0- Production
Start Date 11-FEB-201813:07:16
Uptime 0 days 8 hr. 19min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/a
Listener Log File/oracle/app/oracle/diag/tnslsnr/localhost/listener/l
Listening
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
此时，远程连接数据库，发现有报错：
[oracle@localhost ~]$ sqlplus sys/oracle@rcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 1121:28:182018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
当打开数据库再次查看监听状态：
[oracle@localhost ~]$ sqlplus /as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 1121:29:152018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 318767104 bytes
Redo Buffers 6586368 bytes
Database mounted.
Database opened.
SQL> !
[oracle@localhost ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0- Production on11-FEB-201821:30:09
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0- Production
Start Date 11-FEB-201813:07:16
Uptime 0 days 8 hr. 22min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/a
Listener Log File/oracle/app/oracle/diag/tnslsnr/localhost/listener/l
Listening
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Service "ORCLXDB" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for
Service "mydb" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for
The command completed successfully
b.当为静态监听时：
查看a : <；当配置a中GLOBAL_DBNAME的值 == a中的SERVICE_NAME> [oracle@localhost admin]$ a
# a Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/a
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT =1521))
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
)
)
ADR_BASE_LISTENER =/oracle/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mydb) ##a中GLOBAL_DBNAME的值 == a中的SERVICE_NAME
(ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = mydb) ##可通过env | grep ORACLE查看SID_NAME和$ORACLE_HOME的值
)
)
在数据库关闭的情况下查看监听状态：
[oracle@localhost admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0- Production on11-FEB-201822:16:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0- Production
Start Date 11-FEB-201822:13:25
Uptime 0 days 0 hr. 2min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/a
Listener Log File/oracle/app/oracle/diag/tnslsnr/localhost/listener/l
Listening
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Service "mydb" has 1 instance(s).
Instance "mydb", status UNKNOWN, has 1 handler(s) for
The command completed successfully
此时，远程连接数据库，成功连接：
[oracle@localhost ~]$ sqlplus sys/oracle@rcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 1122:16:462018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
开启数据库，再次查看监听状态（UNKNOWN即表⽰静态监听）：
[oracle@localhost ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0- Production on11-FEB-201822:17:49
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER安装oracle客户端
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0- Production
Start Date 11-FEB-201822:13:25
Uptime 0 days 0 hr. 4min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/app/oracle/product/11.2.0/dbhome_1/network/a
Listener Log File/oracle/app/oracle/diag/tnslsnr/localhost/listener/l
Listening
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Service "ORCLXDB" has 1 instance(s).
Instance "mydb", status READY, has 1 handler(s) for
Service "mydb" has 2 instance(s).
Instance "mydb", status UNKNOWN, has 1 handler(s) for
Instance "mydb", status READY, has 1 handler(s) for
The command completed successfully
2）a：主要负责客户端的监听配置，基本模板：
# a Network Configuration File: /home/oracle/app/product/11/db/network/a
# Generated by Oracle configuration tools.
#RCL为实例名
RCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.2)(PORT = 1521)) #TNSNAME 对应的主机，端⼝，协议
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb) #可通过show parameter service_name；查询service_name 配置a中GLOBAL_DBNAME的值 == a中的SERVICE_NAME
)
)
PROTOCOL ：客户端与服务器端通讯的协议，⼀般为TCP ，该内容⼀般不⽤改。
HOST ：数据库侦听所在的机器的机器名或IP 地址，数据库侦听⼀般与数据库在同⼀个机器上，所以当我说数据库侦听所在的机器⼀般也是指数据库所在的机器。在UNIX 或
WINDOWS 下，可以通过在数据库侦听所在的机器的命令提⽰符下使⽤hostname 命令得到机器名，或通过ipconfig(for WINDOWS) or ifconfig （for UNIX ）命令得到IP 地址。
需要注意的是，不管⽤机器名或IP 地址，在客户端⼀定要⽤ping 命令ping 通数据库侦听所在的机器的机器名，否则需要在 hosts ⽂件中加⼊数据库侦听所在的机器的机器名的解
析。
PORT ：数据库侦听正在侦听的端⼝，可以察看服务器端的a ⽂件或在数据库侦听所在的机器的命令提⽰符下通过lnsrctl status [listener name] 命令察看。此处Port 的
值⼀定要与数据库侦听正在侦听的端⼝⼀样。
SERVICE_NAME ：在服务器端，⽤system ⽤户登陆后，sqlplus> show parameter service_name命令察看。
RCL: 对应的本机，SALES 对应的另外⼀个IP 地址，⾥边还定义了使⽤主⽤服务器还是共享服务器模式进⾏连接
3）a
是驻留在客户端机器和数据库服务器上。使⽤该⽂件存储和实现概要⽂件。数据库服务器可以在a⽂件中配置访问控制参数。这些参数指定基于协议的客户机是否允许或
拒绝访问。
列出⼀些常见参数：
BEQUEATH_DETACH：控制unix系统中signal handling 的开关，默认是no，即signal handling 打开。DEFAULT_SDU_SIZE：指定session data unit (SDU) 的⼤⼩，单位是bytes，建议在client端和server端都设置这个参数，确保链接的时候使⽤相同LOG_DIRECTORY_CLIENT/LOG_DIRECTORY_SERVER：指定客户端/server端log⽇志⽂件的位置
NAMES.DEFAULT_DOMAIN：设定客户端解析名字的域
NAMES.DIRECTORY_PATH：指定client name解析⽅法的次序，默认是NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname。取值可以是tnsnames,ldap（dictionary server),hostname/ezconnect,cds (分布式环境下），nis (Network Infor SQLNET_ALLOWED_LOGON_VERSIONS：指定运⾏链接的oracle的版本SQLNET_ALLOWED_LOGON_VERSIONS=(10,9,8)
SQLNET.AUTHENTICATION_SERVICES：指定启动⼀个或多个认证服务
SQLNET.INBOUND_CONNECT_TIMEOUT：指定客户端没有链接成功超时的时间。超时之后oracle会
中断链接，同时报错。
SSL_VERSION：指定ssl链接的版本
TCP.EXCLUDED_NODES：指定不允许访问oracle的节点，可以使⽤主机名或者IP地址
TCP.INVITED_NODES：指定允许访问db的客户端，他的优先级⽐TCP.EXCLUDED_NODES⾼。
TCP.VALIDNODE_CHECKING：使⽤这个参数来启⽤上边的两个参数。
TNSPING.TRACE_DIRECTORY：使⽤这个参数指定tnsping trace⽂件的⽬录，默认是$ORACLE_HOME/network/trace⽬录
三、⼀些常⽤命令
a. lsnrctl start/stop 开启监听/关闭监听
b.lsnrctl status 查看监听状态 (ready：动态，unknown：静态，)
c.lsnrctl reload 重新装⼊，重新读取a⽂件，但不关闭。如果该⽂件发⽣了变化，重新刷新。
d.lsnrctl version 显⽰oracle net软件与协议适配器的版本。
e.lsnrctl services 列举的服务信息，列出服务的⼀个汇总表及为每个协议服务处理程序所建⽴和拒绝的连接信息个数。 ………………
先到这⾥啦，后续再加~~~
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论