配置共享模式:
找到初始化文件pfile,然后对其编辑或加入如下内容:
max_shared_servers =39 #数据库支持的最大的服务进程数
shared_servers =3 #一般应该是数据库连接数的十分之一
max_dispatchers=19 #调度进程的最大数量 = 数据库最大session数 /每个调度器服务的session数;
dispatchers ="(PROTOCOL=TCP)(SERVICE=orclXDB)(list=orcl)(con=30)(dispa=5)(sess=200)"
保存退出,reboot数据库,然后生成spfile
create spfile from pfile;
查看共享服务器配置是否正确:
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 39
shared_memory_address integer 0
shared_pool_reserved_size big integer 5872025
shared_pool_size big integer 117440512
shared_server_sessions integer 330
shared_servers integer 3
SQL>
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP)(SERVICE=orclXDB
)(list=orcl)
max_dispatchers integer 19
mts_dispatchers string (PROTOCOL=TCP)(SERVICE=orclXDB
)(list=orcl)
mts_max_dispatchers integer 19
SQL>
然后再客户端配置tnnames.ora
245 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
然后用客户端连接数据库,总是报:
ora-12523 tns 监听程序未找到适用于客户机连接的例程
然后修改客户端配置tnnames.ora如下所示:
245 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SID=ORCL)
)
)
在连接 ,就ok了
如果遇到报错 ora-12520: tns 监听程序无法找到需要的服务器类型的可用句柄那说明你的共享服务器配置错误,你的服务器还是专有服务器模式,而你的客户端正视图用共享连接你的数据库(server=shared),解决方法是:
1.更改客户端的tnsnames.ora为专有模式(server=dedicated)
2.把服务器配置成共享模式数据库
如要把数据库配置成专有模式,要让如下参数都等于0
shared_servers=0
但我改完之后还可以用共享模式连接,只要我的客户端tnsnames配置如下:
245 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SID=ORCL)
)
)
但是如果客户端的tnsnames如果改为
245 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
就只能以专有模式连接
在改为专有模式时,很可能遇到ORA-12519错误。这个错误是因为客户端指定的SID与dedicated server handler匹配不上,这就需要用LOCAL_LISTENER参数来指定服务名
服务器的tnsname文件如下:
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
那么就需要执行
SQL> alter system set local_listener='orcl' scope=both;
System altered.
现在你的客户端又可以正常连接了,以DEDICATED SERVER模式
注意:如果dispatchers中指定service, 那么在客户端默认连接是dedicate连接,如果不指定service,则是是shared server
alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)';
查看你的服务器目前的连接是否是共享连接
select * from v$dispatcher
select * from v$shared_server
select * from v$shared_server_monitor
select count(*) from v$dispatcher;
select * from v$circuit
查看注册的服务
lsnrctl services listener
--转自