Oracle 监听
oracle 监听
1 listener.ora文件
(服务器端) --listener监听器进程的配置文件，官方链接：
--https://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#NETRF008
文件路径ORACLE_HOME/network/admin
--listener.ora (服务器端) --listener监听器进程的配置文件
接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不是使用的远程的连接，listener进程就不是必需的，同样的如果关闭listener进程并不会影响已经存在的数据库连接。
11.2.0.4 rac 环境
[oracle@rac2 admin]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
Oracle Net Listener configuration, stored in the listener.ora file, consists of the following elements:
1 Name of the listener
2 Protocol addresses that the listener is accepting connection requests on
3 Database services
4 Control parameters
Dynamic service registration, eliminates the need for static configuration of supported services. However, static service configuration is required if you plan to use Oracle Enterprise Manager.
By default, the listener.ora file is located in the ORACLE_HOME/network/admin directory. The listener.ora file can also be stored the following locations:
Example 7-1 listener.ora File
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.example.com)
(ORACLE_HOME=/oracle11g)
(SID_NAME=sales))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle11g)
(PROGRAM=extproc)))
Protocol Address Parameters
The protocol address section of the listener.ora file defines the protocol addresses on which the listener is accepting connection requests.
This section describes the most common parameters used in protocol addresses. The ADDRESS_LIST parameter is also supported.
ADDRESS
To specify a single listener protocol address.
Put this parameter under the DESCRIPTION parameter.
DESCRIPTION
To contain listener protocol addresses.
IP
To determine which IP address the listener listens on when a host name is specified. This parameter is only applicable when the HOST parameter specifies a host name.
Example
listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))
===
listener_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)
(SEND_BUF_SIZE=11280))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)
(SEND_BUF_SIZE=11280))))
listener_name=
(DESCRIPTION=
(ADDRESS_LIST=
(SEND_BUF_SIZE=11280))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
===
Connection Rate Limiter Parameters
The connection rate limiter feature in the Oracle Net Listener enables a DBA to limit the number of new connections handled by the listener.
When this feature is enabled, Oracle Net Listener imposes a user-specified maximum limit on the number of new connections handled by the listener every second.
CONNECTION_RATE_listener name
To specify a global rate that is enforced across all listening endpoints that are rate-limited. When this parameter is specified, it overrides any endpoint-level numeric rate values that might be specified.
LISTENER= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)(RATE_LIMIT=5))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)(RATE_LIMIT=10))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1523))
)
===
Control Parameters
===
ADR Diagnostic Parameters for Oracle Net Listener
Beginning with Oracle Database 11g, Oracle Database includes an advanced fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving problems.
The problems are critical errors such as those caused by database code bugs, metadata corruption, and customer data corruption.
===
Non-ADR Diagnostic Parameters for Oracle Net Listener
This section lists the parameters used when ADR is disabled (when DIAG_ADR_ENABLEDlistener_name is set to off). "ADR Diagnostic Parameters for Oracle Net Listener" includes the parameters when ADR is enabled.
===
Class of Secure Transports Parameters
The class of secure transports (COST) parameters specify a list of transports that are considered secure for administration and registration of a particular listener.
The COST parameters identify which transports are considered secure for that installation and whether the administration of a listener requires secure transports. Configuring these parameters is optional.
2 sqlnet.ora文件
官方详细文件链接：
--https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF006
参数比较多，请参考官方文档。
oracle的sqlnet.ora是一个文本配置文件，里面一些配置项，控制客户端访问数据库服务器（--客户端和服务端）
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
//指定启动一个或多个认证服务
//指定client name解析方法的次序，默认是NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname)
文件路径
单实例：/u01/app/oracle/product/11.2.0/db_1/network/admin/
RAC:/u01/app/11.2.0/grid/network/admin/ (--11.2)
11.2的rac环境，默认在grid用户下，oracle用户没有sqlnet.ora文件
常见的作用
1 指定启动一个或多个认证服务
认证服务就是指oracle的身份认证服务，当登录oracle时，需要输入用户名密码，有时候不需要输入，这里有3个值：
--NONE: 不允许用过OS系统用户登录数据库，需要提供用户名及密码
--ALL:允许所有的登录方式，Default
--NTS:允许本地操作系统用户登录
Oracle的用户认证：
--用户密码认证 conn user/pass [@db_alisa]
--操作系统认证 conn / as sysdba,sqlplus / as sysdba,conn user/pass[@db_alias as sysdba
--密码文件认证
--外部认证--
--全局认证--LDAP
--
2 指定client name解析方法的次序
连接方式：
CONN USER/PASS @[DB_ALIAS] AS SYSDBA|SYSOPER
语法中有db_alias，是tnsname中定义的数据库的别名
xxx =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxx)
)
)
上面db_alias，就是xxx,那么在连接的时候和sqlnet.ora有关。
当指定为TNSNAME时，就先到tnsname.ora文件中找到对应的db_alis并找到对应的iphost，如果找不到，再按照第二种方式进行查找，如果都找不到，就讲指定的db_alias当做主机名在网络上进行解析，得到数据库服务器的地址。
EZCONNECT,表示简单连接，就是说可以不用在tnsname.ora文件去查询服务名就进行数据库连接，
user/pass@hostname:port/service_name
oracle 11.2.0.4 rac环境
[oracle@rac2 admin]$ cat /u01/app/11.2.0/grid/network/admin/sqlnet.ora
# sqlnet.ora.rac2 Network Configuration File: /u01/app/11.2.0/grid/network/admin/sqlnet.ora.rac2
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/grid
sqlnet.ora File
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF006
The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are
stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file.
These parameters specify whether clients are allowed or denied access based on the protocol.
--客户端和服务端
The sqlnet.ora file enables you to do the following:
1 Specify the client domain to append to unqualified names
2 Prioritize naming methods
3 Enable logging and tracing features
4 Route connections through specific processes
5 Configure parameters for external naming
6 Configure Oracle Advanced Security
7 Use protocol-specific parameters to restrict access to the database
By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.
sqlnet.ora Profile Parameters
This section lists and describes the following sqlnet.ora file parameters:
NAMES.DIRECTORY_PATH
Purpose: To specify the order of the naming methods used for client name resolution lookups. //指定用于客户端名称解析查找的命名方法的顺序。
Default: NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect)
tnsnames (local naming method) Set to resolve a net service name through the tnsnames.ora file on the client.
ldap (directory naming method) Set to resolve a database service name, net service name, or net service alias through a directory server.
ezconnect or hostname (Easy Connect naming method) Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service name.
nis (external naming method) Set to resolve service information through an existing Network Information Service (NIS).
Example NAMES.DIRECTORY_PATH=(tnsnames)
SQLNET.AUTHENTICATION_SERVICES
To enable one or more authentication services. If authentication has been installed, then it is recommended that this parameter be set to either none or to one of the listed authentication methods.
When using the SQLNET.AUTHENTICATION_SERVICES value all, the server attempts to authenticate using each of the following methods.
The server falls back to the ones lower on the list if the ones higher on the list were unsuccessful.
1 Authentication based on a service external to the database, such as a service on the network layer, Kerberos, or RADIUS.
2 Authentication based on the operating system user's membership in an administrative operating system group. Group names are platform-specific.
This authentication is applicable to administrative connections only.
3 Authentication performed by the database.
4Authentication based on credentials stored in a directory server.
Operating system authentication allows access to the database using any user name and any password when an administrative connection is attempted,
such as using the AS SYSDBA clause when connecting using SQL*Plus. An example of a connection is as follows.
sqlplus ignored_username/ignored_password AS SYSDBA
Default all
none for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none,
a valid user name and password can be used to access the database.
all for all authentication methods.
beq for native operating system authentication for operating systems other than Microsoft Windows
kerberos5 for Kerberos authentication
nts for Microsoft Windows native operating system authentication
radius for Remote Authentication Dial-In User Service (RADIUS) authentication
tcps for SSL authentication
Example SQLNET.AUTHENTICATION_SERVICES=(kerberos5)
SQLNET.EXPIRE_TIME
To specify a time interval, in minutes, to send a check to verify that client/server connections are active. The following usage notes apply to this parameter:
1 Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.
2 If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.
3 This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
4 Limitations on using this terminated connection detection feature are:
1 It is not allowed on bequeathed connections.
2 Though very small, a probe packet generates additional traffic that may downgrade network performance.
3 Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event
from other events that occur. This can also result in degraded network performance.
Example SQLNET.EXPIRE_TIME=10
SQLNET.INBOUND_CONNECT_TIMEOUT
To specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.
If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.
In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file.
The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.
Example SQLNET.INBOUND_CONNECT_TIMEOUT=3
SQLNET.OUTBOUND_CONNECT_TIMEOUT
To specify the time, in seconds, for a client to establish an Oracle Net connection to the database instance.
Example SQLNET.OUTBOUND_CONNECT_TIMEOUT=10
SQLNET.RECV_TIMEOUT
To specify the time, in seconds, for a database server to wait for client data after establishing a connection. A client must send some data within the time interval.
SQLNET.SEND_TIMEOUT
TCP.CONNECT_TIMEOUT
To specify the time, in seconds, for a client to establish a TCP connection (PROTOCOL=tcp in the TNS connect address) to the database server.
If a TCP connection to the database host is not established in the time specified, then the connection attempt is terminated. The client
receives an ORA-12170: TNS:Connect timeout occurred error.
Default 60
Example TCP.CONNECT_TIMEOUT=10
TCP.EXCLUDED_NODES
To specify which clients are denied access to the database.
TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)
Usage Notes
1 This parameter is only valid when the TCP.VALIDNODE_CHECKING parameter is set to yes.
2 This parameter can use wildcards for IPv4 addresses and CIDR (Classless Inter-Domain Routing) notation for IPv4 and IPv6 addresses.
Example TCP.EXCLUDED_NODES=(finance.us.example.com, mktg.us.example.com, 192.168.2.25, 172.30.*, 2001:DB8:200C:417A/32)
ADR Diagnostic Parameters in sqlnet.ora
The data is then stored in the Automatic Diagnostic Repository(ADR), a file-based repository outside the database.
ADR_BASE To specify the base directory into which tracing and logging incidents are stored when ADR is enabled.
Default The default on the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined.
Values Any valid directory path to a directory with write permission.
Example ADR_BASE=/oracle/network/trace
3 tnsname.ora文件
类似于unix 的hosts文件，提供的tnsname到主机名或者ip的对应，只有当sqlnet.ora中类似
NAMES.DIRECTORY_PATH= (TNSNAMES) 这样，也就是客户端解析连接字符串的顺序中有TNSNAMES时，才会尝试使用这个文件。
https://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF007
This tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.
By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net will check the other directories for the configuration files.
For example, the order checking the tnsnames.ora file is as follows:
1 The directory specified by the TNS_ADMIN environment variable. If the file is not found in the directory specified, then it is assumed that the file does not exist.
2 If the TNS_ADMIN environment variable is not set, then Oracle Net will check the ORACLE_HOME/network/admin directory.
General Syntax of tnsnames.ora
net_service_name=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))
Multiple Descriptions in tnsnames.ora
A tnsnames.ora file can contain net service names with one or more connect descriptors. Each connect descriptor can contain one or more protocol addresses.
Example 6-2 shows two connect descriptors with multiple addresses. DESCRIPTION_LIST defines a list of connect descriptors.
net_service_name=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name))))
Multiple Address Lists in tnsnames.ora
The tnsnames.ora file also supports connect descriptors with multiple lists of addresses, each with its own characteristics.
net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=on)
(FAILOVER=off)
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information)))
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=on)
(ADDRESS=(protocol_address_information))
(ADDRESS=(protocol_address_information)))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))
Connect-Time Failover and Client Load Balancing with Oracle Connection Managers
When a connect descriptor in a tnsnames.ora file contains at least two protocol addresses for Oracle Connection Manager,
parameters for connect-time failover and load balancing can be included in the file.
sample1=
(DESCRIPTION=
(SOURCE_ROUTE=yes)
(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630)) # 1
(ADDRESS_LIST=
(FAILOVER=on)
(LOAD_BALANCE=off) # 2
(ADDRESS=(PROTOCOL=tcp)(HOST=host2a)(PORT=1630))
(ADDRESS=(PROTOCOL=tcp)(HOST=host2b)(PORT=1630)))
(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=1521)) # 3
(CONNECT_DATA=(SERVICE_NAME=Sales.us.example.com)))
Connect Descriptor Descriptions
Each connect descriptor is contained within the DESCRIPTION parameter. Multiple connect descriptors are characterized by the DESCRIPTION_LIST parameter. These parameters are described in this section.
DESCRIPTION To specify a container for a connect descriptor. Put this parameter under the DESCRIPTION_LIST parameter.
//为连接描述符指定容器。将该参数放在DESCRIPTION_LIST参数下
DESCRIPTION_LIST To define a list of connect descriptors for a particular net service name.
net_service_name=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=...)
(CONNECT_DATA=(SERVICE_NAME=sales.example.com)))
(DESCRIPTION=
(ADDRESS=...)
(CONNECT_DATA=(SERVICE_NAME=sales2.us.example.com))))
Protocol Address Section
Protocol Address Section
The protocol address section of the tnsnames.ora file specifies the protocol addresses of the listener.
If there is only one listener protocol address, then use the ADDRESS parameter. If there is more than one address, then use the ADDRESS_LIST parameter.
ADDRESS To define a single listener protocol address. Put this parameter under either the ADDRESS_LIST parameter or the DESCRIPTION parameter.
ADDRESS_LIST To define a list of protocol addresses. If there is only one listener protocol address, then ADDRESS_LIST is not necessary. Put this parameter under either the DESCRIPTION parameter or the DESCRIPTION_LIST parameter.
net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))
Optional Parameters for Address Lists
ENABLE
FAILOVER
LOAD_BALANCE
RECV_BUF_SIZE
Connection Data Section
CONNECT_DATA To define the service to which to connect, such as SERVICE_NAME.Put this parameter under the DESCRIPTION parameter.
GLOBAL_NAME To identify the Oracle Rdb database. Put this parameter under the CONNECT_DATA parameter.
INSTANCE_NAME To identify the database instance to access. Set the value to the value specified by the INSTANCE_NAME parameter in the initialization parameter file.
SERVER To direct the listener to connect the client to a specific type of service handler.
Values
dedicated to specify whether client requests be served by dedicated server
shared to specify whether client request be served by shared server
pooled to get a connection from the connection pool if database resident connection pooling is enabled on the server
SERVICE_NAME To identify the Oracle Database database service to access. Set the value to a value specified by the SERVICE_NAMES parameter in the initialization parameter file.
net_service_name=
(DESCRIPTION=
(ADDRESS=...)
(ADDRESS=...)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SERVER=dedicated)))
Security Section
Timeout Parameters
CONNECT_TIMEOUT To specify the timeout duration in seconds for a client to establish an Oracle Net connection to an Oracle database. Put this parameter under the DESCRIPTION parameter.
RETRY_COUNT To specify the number of times an ADDRESS list is traversed before the connection attempt is terminated. The default value is 0.
4 endpoints_listener.ora
oracle 11.2.1.4 rac
[oracle@rac2 admin]$ cat /u01/app/11.2.0/grid/network/admin/endpoints_listener.ora
LISTENER_RAC2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.7.12)(PORT=1521)(IP=FIRST)))) # line added by Agent
[oracle@rac2 admin]$ su - grid
Password:
[grid@rac2 ~]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-DEC-2018 09:05:31
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "bol" has 1 instance(s).
Instance "bol2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:19 refused:0 state:ready
LOCAL SERVER
Service "bolXDB" has 1 instance(s).
Instance "bol2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: rac2, pid: 6992>
(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=16485))
The command completed successfully
LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode displaymode
rules trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
snmp_visible save_config_on_stop
dynamic_registration enable_global_dynamic_endpoint
oracle_home pid
connection_rate_limit valid_node_checking_registration
registration_invited_nodes registration_excluded_nodes
LSNRCTL> version
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
TNS for Linux: Version 11.2.0.4.0 - Production
Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production,,
The command completed successfully
--参考
https://www.cnblogs.com/thb-blog/p/7776941.html
http://www.askmaclean.com/archives/11gr2-rac-add-listener-static-register.html
