oracle共享服务器配置汇总

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

方式一，多种监听（专用连接有专有连接的监听，共享服务有共享服务的监听）
方式二，一种监听，专有和共享的都设定在一个监听上
以下，ｌｉｓｔｅ１是专有服务器连接方式，
　　　ｌｉｓｔｅ２是专门的共享服务器连接方式
　　　ｌｉｓｔｅ３是专有服务和共享服务混合的方式

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)
    )
    )

－－配置ｄｉｓｐａｔｃｈｅｒ
SQL> show parameter dispatch

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

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，默认端口１５２１，则需要在ｌｏｃａｌ　ｌｉｓｔｅｎｅｒ中注册。
因为我建了两个监听ｌｉｓｔｅ２，ｌｉｓｔｅ３，所以把它们的端口信息都注册进去
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

 

－－查看共享服务是否配置正常。加粗的地方因为起了２个ｄｉｓｐａｔｃｈｅｒ，所以有２个Ｄ开头的ｈａｎｄｌｅｒｓ．
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 < oel1 pid:>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966))
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER < oel1 pid:>
         (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>

--判断连接ｓｅｓｓｉｏｎ是否接入了共享服务
[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

       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 < oel1 pid:>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel1.oracle.com)(PORT=46966))
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER < oel1 pid:>
         (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
所以本地监听的配置是必须的。

阅读(8104) | 评论(0) | 转发(4) |

0

上一篇：传输表空间及问题处理

下一篇：ORACLE数据文件名导致的奇怪问题

时间： 2024-10-03 02:07:30

oracle共享服务器配置汇总的相关文章

oracle sql sql-SQL Oracle 查询数据,汇总数据 oracle数据查询

问题描述 SQL Oracle 查询数据,汇总数据 oracle数据查询 序号 数量 日期 1 1 20131001 2 1 20131007 3 8 20131008 4 9 20131009 5 5 20131010 使用SQL语句查询出这样的结果: 1 1 20131001 2 2 20131007 3 9 20131008 4 17 20131009 5 22 20131010 也就是数量这一列为当天数量加上前一天的数量,谢谢 解决方案 假设存储这些数据的表名为cal_amount,su

oracle中sql汇总查询相关的问题

问题描述 oracle中sql汇总查询相关的问题 怎么通过一条sql查询出如下表中的所有记录数和交易成功记录数,其中status是交易状态(交易状态 [0:未付款;1:交易关闭;2:已付款;3:交易完成(含退款);4:交易成功;5:交易失败(担保交易)]6.支付中), 表结构如下: 解决方案 http://www.oschina.net/question/2257111_177653 解决方案二: select count(1) as zongshu,'总数' as leixing from a

oracle常用函数汇总

以下是对oracle中的常用函数进行了汇总介绍,需要的朋友可以过来参考下   一.运算符算术运算符:+ - * / 可以在select 语句中使用 连接运算符:|| select deptno|| dname from dept; 比较运算符:> >= = != < <= like between is null in 逻辑运算符:not and or 集合运算符: intersect ,union, union all, minus 要求:对应集合的列数和数据类型相同     

Oracle常用脚本汇总

  1. 安装与卸载 Oracle常用脚本--安装前的配置脚本 Oracle常用脚本--卸载RAC Oracle常用脚本--卸载Grid Control Oracle常用脚本--Agent问题处理脚本 2. 配置 Oracle常用脚本--Catalog注册 Oracle常用脚本--通过RMAN配置RAC环境的分布式磁带机 3. 监控 Oracle常用脚本--监控数据库中的活跃用户及其运行

Oracle 常见函数使用汇总

INSTR用法:INSTR(string,subString,position,ocurrence)解释:string:源字符串      subString:要查找的子字符串      position:查找的开始位置.若起始位置为0,返回值为0,因为下标是从1 开始的,起始位置为0,则表示不查找.既然不查找,就直接返回0,表示找不到了喽;当起始位置为负数的时候,从右边开始查找.      ocurrence:源字符串中第几次出现的子字符串 返回找到的位置(字符串下标是从0开始,如果查找到),

Oracle常见操作汇总（转）

前言:Oracle学习也有十几天了,但是呢,接下来还要学习许多其他的东西,并不能提步不前,所以在此总结了以下Oracle中常用的命令和语句,没有语法都是实例,以便以后工作的时候随时翻看,毕竟是自己的东西,一看就懂. 有关的语句和操作基本都是按照实战中的顺序来总结的,比如创建用户,建表,序列初始化,插入数据的顺序呢. 这篇文章的基表是大家最为熟知的Scott用户下的emp员工表,dept部门表以及salgrade薪水等级表,一切的语句都是围绕它写的. 下面来看一下Oracle中常用的操作都有哪些吧

oracle常用函数汇总(分享)_oracle

一.运算符算术运算符:+ - * / 可以在select 语句中使用连接运算符:|| select deptno|| dname from dept; 比较运算符:> >= = != < <= like between is null in逻辑运算符:not and or 集合运算符: intersect ,union, union all, minus 要求:对应集合的列数和数据类型相同     查询中不能包含long 列     列的标签是第一个集合的标签     使用orde

Red Hat 6.5安装Oracle 10g故障汇总

  目前Oracle 12c第一版已经推出接近一年时间,行业普遍接受11gR2作为安装标准配置.笔者接受了一个在64bit Red Hat安装老版本Oracle 10gR2的任务.在这个过程中,遇到一些问题,将解决策略记录下来,留待需要的朋友待查.   1.OS版本兼容   OS与Database Software之间的兼容性问题是我们需要考虑的第一关.在Oracle 10g时代,Linux普遍版本是4.x,所以对于我们安装的Red Hat 6.5,在安装兼容性检查过程中是会报错的. 这个问题还

持续更新关于ORACLE X$视图汇总

以后看到一个记录一个,不然老是忘记. 1.X$KSMSP 名称含义为:[K]ernal [S]torage [M]emory Management [S]GA Hea[P],其中每一行都代表着shared pool中的一个chunk KSMCHCOM是注释字段,每个内存块被分配以后,注释会添加在该字段中.x$ksmsp.ksmchsiz代表块大小 x$ksmsp.ksmchcls列代表类型,主要有四类,说明如下: freeFree chunks--不包含任何对象的chunk,可以不受限制的被分配