oracle共享服务器配置汇总(53天)

oracle共享服务器配置汇总 对于共享服务器的配置进行了汇总,也发现一些隐藏的错误

方式一,多种监听(专用连接有专有连接的监听,共享服务有共享服务的监听) 方式二,一种监听,专有和共享的都设定在一个监听上 以下,liste1是专有服务器连接方式,    liste2是专门的共享服务器连接方式    liste3是专有服务和共享服务混合的方式

listener.ora 配置如下。 LISTE1 = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1521)) )

SID_LIST_LISTE1= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD.oracle.com) (SID_NAME=PROD) (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) ) )

LISTE2 = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1529)) ) SID_LIST_LISTE2= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD.oracle.com) (SID_NAME=PROD) (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) ) )

LISTE3 = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520)) (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1530)) )

SID_LIST_LISTE3= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD.oracle.com) (SID_NAME=PROD) (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) ) )

tnsnames.ora 文件配置如下 --配置了多个端口的专用服务连接 PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1520)) (ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PROD.oracle.com) ) ) --配置了共享服务连接 SPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1529)) ) (CONNECT_DATA = (SERVICE_NAME = PROD.oracle.com) (SERVER=SHARED) ) ) --配置共享服务连接,验证可以有多个共享服务器并存 XPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = oel1.oracle.com)(PORT = 1530)) ) (CONNECT_DATA = (SERVICE_NAME = PROD.oracle.com) (SERVER=SHARED) ) )

--配置dispatcher SQL> show parameter dispatch

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (protocol=tcp)(service=PROD.or acle.com)(dispatchers=2) max_dispatchers integer 2 --根据需要来配置共享服务的参数。当然session,processes也需要相应的设置,如果值太小,共享服务连接就会很慢。

SQL> show parameter shared

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 max_shared_servers integer 2 shared_memory_address integer 0 shared_pool_reserved_size big integer 4404019 shared_pool_size big integer 0 shared_server_sessions integer 50 shared_servers integer 1

--配置本地监听 !!!如果配置的共享服务时非默认监听Listeners,默认端口1521,则需要在local listener中注册。 因为我建了两个监听liste2,liste3,所以把它们的端口信息都注册进去 alter system set local_listener='(address_list=(address=(protocol=tcp)(host=oel1.oracle.com)(port=1529))(address=(protocol=tcp)(host=oel1.oracle.com)(port=1530)))';

SQL> show parameter local_lis

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (address_list=(address=(protoc l=tcp)(host=oel1.oracle.com)( port=1529))(address=(protocol= tcp)(host=oel1.oracle.com)(por t=1530))) -启动监听 LSNRCTL> start liste1

LSNRCTL> start liste2

LSNRCTL> start liste3

--查看共享服务是否配置正常。加粗的地方因为起了2个dispatcher,所以有2个D开头的handlers. LSNRCTL> service liste3 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1520)) Services Summary... Service "PROD.oracle.com" has 2 instance(s). Instance "PROD", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "PROD", status READY, has 3 handler(s) for this service... Handler(s): "D001" established:0 refused:0 current:0 max:972 state:ready DISPATCHER <machine: oel1, pid: 23114> (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966)) "D000" established:0 refused:0 current:0 max:972 state:ready DISPATCHER <machine: oel1, pid: 23112> (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=57952)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "PROD_XPT.oracle.com" has 1 instance(s). Instance "PROD", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully

--测试一把 [oracle@oel1 admin]$ sqlplus sys/oracle@XPROD as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL>

--判断连接session是否接入了共享服务 [oracle@oel1 admin]$ sqlplus sys/oracle@SPROD as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:37:34 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select sid,server from v$session where sid=(select sid from v$mystat where rownum<=1);

SID SERVER ---------- --------- 160 SHARED

--使用端口连接来确认Liste1,liste3的两个端口都可以正常访问 [oracle@oel1 admin]$ sqlplus sys/oracle@oel1.oracle.com:1520/PROD.oracle.com as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:44:38 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

[oracle@oel1 admin]$ sqlplus sys/oracle@oel1.oracle.com:1521/PROD.oracle.com as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:45:12 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL>

--共享服务器关闭失败,所以在做data guard等场景时,不适合用共享服务器模式 SQL> shut immediate ORA-00106: cannot startup/shutdown database when connected to a dispatcher SQL>

--复现共享服务绑定监听错误的情况 --把本地监听设置为空 SQL> alter system set local_listener='';

System altered.

LSNRCTL> service liste1 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1521)) Services Summary... Service "PROD.oracle.com" has 2 instance(s). Instance "PROD", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:8 refused:0 LOCAL SERVER Instance "PROD", status READY, has 3 handler(s) for this service... Handler(s): "D001" established:0 refused:0 current:0 max:972 state:ready DISPATCHER <machine: oel1, pid: 23114> (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966)) "D000" established:0 refused:0 current:0 max:972 state:ready DISPATCHER <machine: oel1, pid: 23112> (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=57952)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "PROD_XPT.oracle.com" has 1 instance(s). Instance "PROD", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully LSNRCTL> service liste2 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=1529)) Services Summary... Service "PROD.oracle.com" has 1 instance(s). Instance "PROD", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully

--看起来好像也影响不大,后台的共享服务都起来了。但是连库连不上去。 [oracle@oel1 admin]$ sqlplus sys/oracle@XPROD as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 26 20:34:44 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR: ORA-12523: TNS:listener could not find instance appropriate for the client connection 所以本地监听的配置是必须的。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

10g升级至11g后数据库无法启动的问题解决

今天升级数据库碰到一个很郁闷的问题,把10g的数据库升级到11g以后,结果有一个改动,需要重启数据库,就敲了shutdown immediate,结果再star...

4086
来自专栏数据和云

偷梁换柱 | 无备份情况下的数据恢复实践

在实际环境中,许多数据库环境并没有做好完整的数据备份恢复计划及容灾方案,无法保证数据安全,并且出现一些灾难性的错误。那么我们就面临这样的问题:在什么样的最极端情...

3305
来自专栏Ryan Miao

Spring-AOP实践 - 统计访问时间

公司的项目有的页面超级慢,20s以上,不知道用户会不会疯掉,于是老大说这个页面要性能优化。于是,首先就要搞清楚究竟是哪一步耗时太多。 我采用spring aop...

5128
来自专栏linux驱动个人学习

高通 sensor 从native到HAL

前几篇sensor相关的文章介绍了sensor的hal的知识,以press_sensor实时显示气压坐标来分析,app层数据获取的过程,其实实现数据监控非常简单...

4532
来自专栏杨建荣的学习笔记

容灾半自动化的实现思路(二) (r7笔记第93天)

容灾的半自动化的部分,自己写了下面的脚本,也算是一个基本实现,因为时间仓促,还是存在一些不足,稍后完善 整个切换的步骤分为三部分,第一部分是备份当前备库的配置文...

2488
来自专栏乐沙弥的世界

ORACLE RAC 监听配置 (listener.ora tnsnames.ora)

    Oracle RAC 监听器的配置与单实例稍有不同,但原理和实现方法基本上是相同的。在Oracle中 tns进程用于为指定网络地址上的一个或多个Orac...

7723
来自专栏蓝天

Redis模块开发示例

实现一个Redis module,支持两个扩展命令: 1) 可同时对hash的多个field进行incr操作; 2) incrby同时设置一个key的过期时...

1183
来自专栏Strive

zabbix服务zabbix server is not running: the information...

搭建环境 | 系统:centos 7 | Java 1.8 | zabbix 3.4

2K4
来自专栏乐沙弥的世界

ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)

        不论是单实例还是RAC,对于非缺省端口下(1521)的监听器,pmon进程不会将service/instance注册到监听器,即不会实现动态注册...

1162
来自专栏10km的专栏

cmake:vs2015/MinGW静态编译leveldb

leveldb是google的开源项目(https://github.com/google/leveldb), 在linux下编译很方便,然而官方版本却没有提供...

5826

扫码关注云+社区

领取腾讯云代金券