详解Oracle的连接模式：专有模式和共享模式

最新推荐文章于 2024-11-14 09:41:50 发布

crreksx5913

最新推荐文章于 2024-11-14 09:41:50 发布

阅读量462

点赞数

文章标签：数据库

oracle的网络连接模式

专有服务和共享服务模式

专有模式

每个用户进程和服务进程间通过监听器建立连接

进程信息存放于PGA中,也就是说有多少个用户进程就有多少个PGA产生

单单只有进程连接还不足以操作数据库,还需要产生的会话信息

会话信息存储在UGA中,UGA在专有模式中存在于PGA

因为进程和进程间是相互隔离的,所以会话信息也相对独立

这就导致了服务进程只能获知当前用户进程的会话请求信息只能为当前用户进程服务

共享模式

用户进程的请求被监听器接收,监听器不委派服务器进程,而是将调度器信息返回给客户端

调度器将用户进程的请求放入请求队列

多个服务进程中的一个服务进程从队列中获取用户进程的请求,并处理这个用户进程的请求

服务进程处理完后将处理结果放入响应队列,每个调度器都有自己的响应队列

响应队列的信息反馈给对应的调度器

调度器再把服务进程处理的结果返回给用户进程

共享模式中的用户进程的会话信息对每一个服务器进程来说都是可见的.

因为共享模式的UGA信息存在于SGA中,所以此时一个用户进程的请求可以由多个服务进程来完成.

监听器的特点

监听程序进程可监听多个数据库

多个监听程序可为一个数据库进行监听，以实现负载均衡，可以通过每个监听协议地址上的负载序列值来判断使用负载最低的监听。

可以实现连接时的故障转移，如果第1个监听失败，可以请求第2个监听

监听程序可监听多个协议

Oracle Net 中的监听程序的缺省名称是LISTENER

每个listener.ora文件中的监听程序的名称必须唯一

专有服务器配置

oracle的网络配置分为服务器端和客户端

服务器端监听器listener

主要是$ORACLE_HOME/network/admin/listener.ora文件

描述了监听的网络连接类型 TCP ICP LDAP等

例如:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

)

通过lsnrctl命令来启动/停止/查看/重载监听器/服务

lsnrctlstart|stop|status|reload|service

客户端

主要是$ORACLE_HOME/network/admin/tnsname.ora文件

对具体的数据库连接信息浓缩成别名

例如:

ORA10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10g)

)

客户端申请链接的过程

connscott/seker@ora10g

1.客户端通过@ora10g的名字去tnsname.ora文件获取服务器的具体连接信息

2.客户端通过tnsname.ora中的描述向服务器发出链接请求服务器端

3.服务器的监听器接收到连接请求后,验证请求的服务的有效性

4.服务器端产生一个服务进程和客户端进程建立连接

客户端配置tnsname.ora

ORA10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10g)

)

ORACLE NET服务端配置

实例通过注册让监听器得知服务器运行的信息

注册分两种

静态注册

LISTENER主动去找要支持的实例信息，叫静态注册

动态注册

PMON进程将实例信息告诉listener,叫动态注册

静态注册和动态注册

什么是静态注册

就是监听器的配置文件中写明了监听哪个实例需要配置SID_DESC字段

定位实例的方式可以使用SID_NAME或者SERVICE_NAME来定位

什么是动态注册

就是监听器的配置文件中没写明监听哪个实例

要通过PMON告知监听器要监听的具体实例

PMON是将SERVER_NAME告诉给监听器这个过程就是注册

默认一分钟PMON注册一次也就是说启动监听还没注册时是无法连接的

区分静态注册和动态注册

lsnrctl status

是 ready 就是动态

是unknow就是静态

静态注册listener.ora文件信息:

[oracle@dba admin]$ catlistener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /u01/oracle/product/10.2.0)

(SID_NAME =orcl)

)

(SID_DESC =

(ORACLE_HOME = /u01/oracle/product/10.2.0)

(SID_NAME =ora10g)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1522))

)

[oracle@dba admin]$

[oracle@dba admin]$ lsnrctl start

.....

Services Summary...

Service "ora10g" has 1 instance(s).

Instance "ora10g", status UNKNOWN, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@dba admin]$

状态总是显示未知的,当有请求时,监听器才去确认数据是否存在

动态注册

Oracle9i起实例使用动态服务注册来通知监听程序有关其数据库服务的信息。

服务注册依赖PMON 进程向监听程序注册实例信息注册间隔为1分钟左右

手动注册命令 alter system register;

无需在listener.ora文件中设置任何信息此文件可以不存在

[oracle@dba admin]$ rm -rflistener.ora

[oracle@dba admin]$

[oracle@dba admin]$ lsnrctl start

....

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba.up.com)(PORT=1521)))

The listener supports no services

The command completed successfully

[oracle@dba admin]$

[oracle@dba admin]$ sqlscott/seker@ora10g

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 2 15:59:20 2011

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Enter user-name:

[oracle@dba admin]$ 因为没有注册所以不能连接

手动注册一次

SQL> alter system register;

System altered.

SQL>

[oracle@dba admin]$ sqlscott/seker@ora10g

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 2 15:59:42 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>连接成功

如果listener.ora中定义了监听端口(非1521)

此时还想使用动态注册需要设置local_listener参数

[oracle@dba admin]$ catlistener.ora

# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/oracle/product/10.2.0)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1522))

)

[oracle@dba admin]$

[oracle@dba admin]$ lsnrctl start

......

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba.up.com)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@dba admin]$

手动注册无效 PMON默认只能注册默认端口1521

SQL> alter system register;

System altered.

SQL>

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba.up.com)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@dba admin]$

修改参数local_listener注册成功

SQL> alter system set local_listener='ORxx';

System altered.

SQL> alter system register;

System altered.

SQL>

[oracle@dba admin]$ lsnrctl status

....

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "ora10g" has 1 instance(s).

Instance "ora10g", status READY, has 1 handler(s) for this service...

Service "ora10gXDB" has 1 instance(s).

Instance "ora10g", status READY, has 1 handler(s) for this service...

Service "ora10g_XPT" has 1 instance(s).

Instance "ora10g", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@dba admin]$

local_listener='ORxx' 是什么?

local_listener的值一定要在tnsname.ora中设置否则报错

[oracle@dba admin]$ grep -A 7 'ORxx' tnsnames.ora

ORxx =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10g)

)

[oracle@dba admin]$ grep -A 7 'ORnn' tnsnames.ora

[oracle@dba admin]$

SQL> alter system set local_listener='ORnn';

alter system set local_listener='ORnn'

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'ORnn'

SQL>

共享服务器

在专有模式的基础上又增加了一个调度器(dispatcher)来管理用户

请求连接不再直接和服务进程联系而是先被调度器放到请求列队里排队

1.用户进程发起请求

2.调度器接收

3.放到请求队列里

4.服务进程从队里获取用户的请求

5.服务进程向实例请求用户数据

6.将请求的数据反馈给用户进程

银行取钱排队叫号的例子

共享服务器的配置通过初始化参数dispatchers来配置

可以通过DBCA来修改很方便

调度器设置中不同协议走不同队列

调度器的数量 (好比发号机的数量)

每个调度器的最大分配数量 (发号机能发出去的号的最大数)

最大的session数

调度器参数

*.dispatchers='(protocol=TCP)(disp=2)(con=200)(sess=789)'

*.max_dispatchers=5

*.max_shared_servers=4

*.shared_servers=2

SQL> show parameter dispatchers

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dispatchers string (PROTOCOL=TCP) (SERVICE=ora10g

XDB)

max_dispatchers integer

SQL>

SQL> show parameter shared_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers integer

shared_server_sessions integer

shared_servers integer 1

SQL>

SQL> show parameter dispa

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dispatchers string (protocol=TCP)(disp=2)(con=200)(sess=789)

max_dispatchers integer 5

SQL>

SQL> show parameter shared_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers integer 3

shared_server_sessions integer

shared_servers integer 2

SQL>

[oracle@dbabdump]$ lsnrctl service

....

服务 "ora10g" 包含 1 个例程。

例程 "ora10g", 状态 READY, 包含此服务的 3 个处理程序...

处理程序:

"DEDICATED" 已建立:0 已拒绝:0 状态:ready

LOCAL SERVER

"D001" 已建立:0 已被拒绝:0 当前: 0 最大: 200 状态: ready

DISPATCHER <machine: dba.up.com, pid: 3541>

(ADDRESS=(PROTOCOL=tcp)(HOST=dba.up.com)(PORT=54306))

"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 200 状态: ready

DISPATCHER <machine: dba.up.com, pid: 3539>

(ADDRESS=(PROTOCOL=tcp)(HOST=dba.up.com)(PORT=26395))

....

告警日志中启动信息

Mon Apr 4 00:32:12 2011

starting up 2 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 2 shared server(s) ...

Mon Apr 4 00:32:12 2011

ALTER DATABASE MOUNT

专有模式和共享模式是可以兼容在一起使用的

此时看用户如何选择

tnsname.ora中

ORA10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED) # 不写这个值就是按服务器的模式来匹配

# shared 指定使用共享方式连接

# DEDICATED 指定使用专有模式连接

(SERVICE_NAME = raw10g)

)

测试三种模式

ORA10G_noset =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = ora10g)

)

ORA10G_ded =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10g)

)

ORA10G_shd =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = SHARED)

(SERVICE_NAME = ora10g)

)

SQL> select USERNAME,SID,SERVICE_NAME,SERVER,PROGRAM from v$session where USERNAME='SYS';

USERNAME SID SERVICE_NA SERVER PROGRAM

---------- ----------- ---------- --------- ------------------------------

SYS 136 SYS$USERS DEDICATED OMS

SYS 137 ora10g SHARED sqlplus@dba.up.com (TNS V1-V3)

SYS 144 ora10g SHARED sqlplus@dba.up.com (TNS V1-V3)

SYS 145 ora10g DEDICATED sqlplus@dba.up.com (TNS V1-V3)

SYS 159 SYS$USERS DEDICATED sqlplus@dba.up.com (TNS V1-V3)

SQL> select USERNAME,SID,SERVICE_NAME,SERVER,PROGRAM from v$session where USERNAME='SCOTT';

USERNAME SID SERVICE_NA SERVER PROGRAM

---------- ----------- ---------- --------- ------------------------------

SCOTT 133 ora10g NONE sqlplus@dba.up.com (TNS V1-V3)

SCOTT 146 ora10g DEDICATED sqlplus@dba.up.com (TNS V1-V3)

SCOTT 153 ora10g NONE sqlplus@dba.up.com (TNS V1-V3)

SQL>普通用户并不能显示

限制IP访问

限制IP访问:SQLNET.ora

tcp.validnode_checking=yes

tcp.invited_nodes=(ip1,ip2......) # 被邀请IP

tcp.excluded_nodes=(ip1,ip2......) # 被限制IP

重新启动监听器设置才生效

想要动态注册自己的IP必须在被邀请的列表里

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25116248/viewspace-2122141/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25116248/viewspace-2122141/