OCA/OCP Oracle 数据库12c考试指南读书笔记：第14章: Oracle Networking
Oracle的客户端/服务器架构是通过Oracle Net建立会话，Oracle Net可通过CLI或GUI配置，最终生成文本文件。
配置Oracle Net服务
用户不能直接访问数据库，而是通过服务器进程。客户端进程产生SQL，服务器进程执行SQL。
Oracle Net与C/S架构Oracle Net提供一种机制，即启动一个服务器进程代表客户端进程执行代码，这建立了会话，Oracle Net负责维护此会话。
Oracle Net可运行在不同协议之上，如TCP/IP，SDP，IPC等。
建立会话需要用户名，口令和连接串。
> connect hr/oracle@orclpdb1 Connected. 12
@后面即连接串，如果没有@，表示连接本地。
连接本地实例通过IPC，只是唯一不需要监听的连接。只需要通过环境变量指定连接的实例名。
$ echo $ORACLE_SID ORCLCDB $ sqlplus / as sysdba 123 命名解析
即解析@后面的连接串，需要得到监听的IP地址和端口，以及在监听上注册的服务。
启动服务进程监听负责接收连接请求。当接收到请求时，监听首先验证请求的资源是否可用，如果可用，监听则启动服务器进程来为用户进程服务。此服务器进程会分配一个端口，此端口通过监听返回用户进程，之后用户进程通过此端口与服务器进程直接沟通，监听不再干预。
所以，监听如果停止，不会影响已建立的会话。
配置文件为listener.ora，位于$ORACLE_HOME/network/admin:
$ cat $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) DEDICATED_THROUGH_BROKER_LISTENER=ON DIAG_ADR_ENABLED = off 123456789101112 动态服务注册
数据库可提供多个服务。
A service is a logical name to which sessions can attach through the instance.、
数据库有默认的服务名，通常就是实例名，或者是在tnsnames.ora中定义的Net Service别名。
实例启动时，会在监听中注册服务名，数据库关闭时，会取消注册。这些通过LREG后台进程实现。
前面提到的是dedicated Server模式，即每一个客户端进程都有一专门的服务器进程为其服务。
与之对应的是Shared Server模式，即少量的共享服务器进程为大量客户端进程提供服务。
当会话越多，服务器进程就越多。在多个进程间切换需要context switch，此操作很耗资源。
一个进程启动多次时，使用的是共享内存，因此启动上千个服务器进程比启动一个消耗的内存多不了多少。但PGA是问题，因为PGA要为每一个服务器进程分配，而且不能共享。
升级硬件可解决内存问题，但解决不了最重要的context switch的问题。此时可考虑shared server架构
在服务器端实现，对用户是透明的。
配置了shared server的实例启动时，除shared服务器进程外，还会启动dispatcher进程。
客户端请求连接时，会首先连接到dispatcher，dispatcher会将客户端请求放到Request Queue（或common queue）中，此Queue是共享的。然后空闲的shared服务器进程从请求队列中取任务并执行，然后返回到Response Queue中，每一个disptahcher有自己的Response queue。
另一个问题是，会话的私有信息（状态信息）之前是保留在PGA中的，在Shared Server架构下，则保存在SGA中的UGA区域。
与客户端和数据库无关，只与实例有关。通过实例初始化参数配置。主要是shared_servers和dispatchers：
ORCLCDB> show parameter shared_server NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer shared_server_sessions integer shared_servers integer 1 ORCLCDB> show parameter dispatchers; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP) (SERVICE=ORCLCD BXDB) max_dispatchers integer 123456789101112131415 何时使用Shared Server
Shared Server增加了扩展性，但可能影响性能和增加CPU消耗（队列机制）。也就是说，也许每个请求会慢些，但可接受更多的请求。
Shared Server人架构适合会话中大部分的工作都在客户端，服务器端执行的是小交易（短事务）。因为在此架构下，shared服务器进程可以为多个会话服务。但不适合数仓这种批处理任务。
Dedicate Server架构适合数仓和DBA管理性任务。
12c对通过XDB的连接默认使用Shared Server架构：
ORCLCDB> select name, value from v$parameter where name like '%dispatchers'; NAME VALUE -------------------- -------------------------------------------------- dispatchers (PROTOCOL=TCP) (SERVICE=ORCLCDBXDB) max_dispatchers ORCLCDB> select name, value from v$parameter where name like '%shared_servers'; NAME VALUE -------------------- -------------------------------------------------- shared_servers 1 max_shared_servers 1234567891011121314
使用工具配置和管理Oracle网络
3个文件：
listener.ora - 监听配置tnsnames.ora - 客户端命名解析sqlnet.ora - 可以位于客户端或服务器端。可选。影响客户端和监听的配置。两个GUI工具：
Net ManagerNet Configuration Assistant Net Managernetmgr, GUI，功能较全。
Net Configuration Assistantnetca, GUI，也可静默执行。功能比Net Manager少，只涉及监听和tnsnames.ora。
Listener控制使用程序CLI，lsnrctl：
$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 10:51:14 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-OCT-2019 08:20:49 Uptime 0 days 2 hr. 30 min. 24 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-19c-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/19c/dbhome_1/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "94b31c5bdd3f055ee0530100007fae63" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDBXDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... The command completed successfully
123456789101112131415161718192021222324252627282930313233 TNS_ADMIN环境变量tnsnames.ora, sqlnet.ora,和listener.ora的默认位置为：$ORACLE_HOME/network/admin
TNS_ADMIN可指定其他位置。
配置客户端网络
包括tnsnames.ora, sqlnet.ora，后者是可选的。客户端需要提供连接串以连接数据库，Oracle提供4种解析方式，local naming最常用：
easy connectlocal namingdirectory namingexternal naming Easy Connect命名解析无需tnsnames.ora。只支持TCP协议，不支持一些高级配置如负载均衡。不适合用户应用，DBA可偶尔用。
$ sqlplus hr/oracle@localhost:1521/orclpdb1 1 Local Naming 命名解析
指定Net service别名，然后通过tnsnames.ora解析，展开为完整的网络地址。
$ cat $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCLCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLCDB) ) ) LISTENER_ORCLCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ORCLPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) ) $ sqlplus hr/oracle@orclpdb1 SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 15 11:00:39 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue Oct 15 2019 10:57:41 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 orclpdb1>
1234567891011121314151617181920212223242526272829303132333435363738394041支持所有协议和高级配置，但每个客户端需要维护一份。
Directory 和外部命名解析好处是集中的，便于维护。
Directory解析指使用LDAP，如Microsoft Active Directory或Oracle Internet Directory。
外部解析是使用第三方的名称服务如NIS+等。
tnsping，接收连接串，测试到监听一层，不会到数据库。
$ tnsping localhost:1521 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 11:06:13 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora Used HOSTNAME adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) OK (0 msec) $ tnsping orclpdb1 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 11:06:26 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1))) OK (0 msec)
123456789101112131415161718192021222324252627 练习 - 配置新的监听在listener.ora中添加新的监听条目，端口为2252:
$ tail listener.ora NEW_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 2521)) ) ) 12345678
启动监听：
$ lsnrctl start NEW_LISTENER 1
在tnsnames.ora中添加以下，实例通过以下地址来指定监听，而非listener.ora：
NEW_LISTENER_ORCLCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 2521)) 123
在数据库中注册服务：
-- 当前的服务名和注册的监听 ORCLCDB> show parameter service_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string ORCLCDB ORCLCDB> select name, network_name from dba_services; NAME NETWORK_NAME -------------------- ------------ SYS$BACKGROUND SYS$USERS ORCLCDBXDB ORCLCDBXDB ORCLCDB ORCLCDB -- LISTENER_ORCLCDB存在于tnsnames.ora中 ORCLCDB> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_ORCLCDB ORCLCDB> alter session set container=orclpdb1; Session altered. ORCLCDB> select name, network_name from dba_services; NAME NETWORK_NAME ------------ ------------ ORCLPDB1 ORCLPDB1 ORCLCDB> show con_name; CON_NAME ------------------------------ ORCLPDB1 -- NEW_LISTENER_ORCLCDB定义在tnsnames.ora而非listener.ora中 ORCLCDB> alter system set local_listener='NEW_LISTENER_ORCLCDB' scope=memory; System altered. ORCLCDB> alter system register; System altered.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546以上的过程有几点需要注意，一是每个数据库都可以注册自己的监听，以上是切换到PDB修改监听，因此CDB的监听并没有改变。
另外：
local_listener parameter controls which listener the instance will register with dynamically; it will also accept connections from any listener that has it statically registered.
相应的，PDB在tnsnames中的定义也需要修改如下，即端口改为监听的端口2521：
ORCLPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 2521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) ) 123456789
可以看到，PDB的服务已从老的监听中注销并注册到新的监听：
$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 14:00:54 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-OCT-2019 08:20:49 Uptime 0 days 5 hr. 40 min. 4 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-19c-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDBXDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... The command completed successfully $ lsnrctl status NEW_LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 14:01:24 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=2521))) STATUS of the LISTENER ------------------------ Alias NEW_LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-OCT-2019 12:00:26 Uptime 0 days 2 hr. 0 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/new_listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=2521))) Services Summary... Service "94b31c5bdd3f055ee0530100007fae63" has 1 instance(s). Instance "ORCLCDB", status READY, has 2 handler(s) for this service... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status READY, has 2 handler(s) for this service... The command completed successfully
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556连接到PDB通过新的监听：
$ sqlplus hr/oracle@orclpdb1 1
连接到CDB仍走老的监听：
$ sqlplus system/Welcome1@orclcdb 1
tnsnames.ora中的别名，服务名和实例名可以不一样，但为方便，通常设为一样。
在orclpdb1中创建并启动一个新服务：
ORCLCDB> show con_name; CON_NAME ------------------------------ ORCLPDB1 ORCLCDB> exec DBMS_SERVICE.create_service('new_service', 'new_service'); PL/SQL procedure successfully completed. ORCLCDB> select name from v$active_services; NAME ---------------------------------------------------------------- orclpdb1 ORCLCDB> exec DBMS_SERVICE.start_service('new_service'); PL/SQL procedure successfully completed. ORCLCDB> select name from v$active_services; NAME ---------------------------------------------------------------- new_service orclpdb1
123456789101112131415161718192021222324252627在监听中发现服务已注册：
$ lsnrctl status NEW_LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 14:36:04 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=2521))) STATUS of the LISTENER ------------------------ Alias NEW_LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-OCT-2019 12:00:26 Uptime 0 days 2 hr. 35 min. 38 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/new_listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=2521))) Services Summary... Service "94b31c5bdd3f055ee0530100007fae63" has 1 instance(s). Instance "ORCLCDB", status READY, has 2 handler(s) for this service... Service "new_service" has 1 instance(s). Instance "ORCLCDB", status READY, has 2 handler(s) for this service... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status READY, has 2 handler(s) for this service... The command completed successfully
1234567891011121314151617181920212223242526272829然后在tnsnames.ora中新增一条目：
NEWPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 2521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = new_service) ) ) 12345678
然后就可以连接了：
$ sqlplus hr/oracle@newpdb newpdb> show con_name; CON_NAME ------------------------------ ORCLPDB1 1234567
Service的概念参见这里。
A service name is a logical representation of a service used for client connections.
When a client connects to a listener, it requests a connection to a service. When a database instance starts, it registers itself with a listener as providing one or more services by name. Thus, the listener acts as a mediator between the client and instances and routes the connection request to the right place.
A single service, as known by a listener, can identify one or more database instances. Also, a single database instance can register one or more services with a listener. Clients connecting to a service need not specify which instance they require.
下图就是我们演示的例子，即1个PDB输出了两个服务，不同的服务可以施加不同的控制。
理解数据库驻留连接池（DRCP）
Database Resident Connection Pool (DRCP)模拟了中间件层连接池的概念。
DRCP适合于Web应用，就是大并发，但每一个会话时间很短，并且最好是同一schema。
启用DRCP：
ORCLCDB> EXEC DBMS_CONNECTION_POOL.start_pool; PL/SQL procedure successfully completed. 1234
用easy connect测试：
$ rlwrap sqlplus hr/oracle@localhost:2521/orclpdb1:pooled SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 15 14:59:29 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue Oct 15 2019 14:56:41 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 localhost:2521/orclpdb1:pooled> 123456789101112131415
其实原理特简单，就是启一些服务器进程，由broker统一管理，然后客户端与broker通讯。
配置数据库间通讯
Oracle Net除了之前的C/S通讯，也可用于数据库间通讯。即通过Database Link。
参考
Oracle Net Services 12c：Best Practices for Database Performance and ScalabilityDBMS_SERVICE : Define Database Services in a Single Instance Database3.Database Resident Connection Pool (DRCP) in Oracle Database 11g Release 1
相关知识
《Oracle编程艺术：深入理解数据库体系结构（第3版）》试读：1.3 开发数据库应用的正确（和不正确）方法
Java实现鲜花预定系统：Oracle数据库与Spring Boot框架整合实战
7个工具！让Oracle 迁移至 PostgreSQL成为可能！
plsql连接oracle模糊查询中文不成功
Oracle存储过程语法学习
国产化数据迁移：从Oracle到MySQL平台的数据无缝迁移工具
Oracle 删除表后多出了类似BIN$bdqTEdDrT7iRIC2+iRTfXQ==$0的表
ORACLE
oracle中id=id(+)意思
MongoDB 发展历史及各主要版本新特性概述
网址: OCA/OCP Oracle 数据库12c考试指南读书笔记：第14章: Oracle Networking https://m.huajiangbk.com/newsview1462232.html
| 上一篇: anaconda3 安装指南 d |
下一篇: 《WebGL编程指南》 |
