ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RAC环境下安装部署OWB问题总结

RAC环境下安装部署OWB问题总结

原创 Linux操作系统 作者:Allen2312 时间:2009-06-28 16:17:33 0 删除 编辑

ITpub空间的图片管理非常不好,这篇文章弄4次了,看看这次情况如何:

前几天被OWB折磨,感觉10.2.0.1.31版本的owb像个半成品

一、安装方面
1、安装路径
OWB安装目录一定要和ORACLE数据库软件的ORACLE_HOME目录分开,不能放在Oracle软件的安装目录下,要使用单独的目录安装OWB,不能和Oracle其他软件共享ORACLE_HOME。

2、安装的问题
如果安装过程中出现如下报错:


提示Error in invoking target 'isqlldr' of makefile '/ora/u01/app/oracle/product/10.2.0/owb/rdbms/lib/ins_rdbms.mk'.

安装OWB没有设定正确的ORACLE_HOME环境变量,使用了原来的ORACLE_HOME变量,在安装OWB时,需要设定环境变量ORACLE_HOME到owb的安装路径,如:

export ORACLE_HOME=/ora/u01/app/oracle/product/10.2.0/owb

我们原来的ORACLE_HOME=/ora/u01/app/oracle/product/10.2.0/db_1,默认数据库软件都装在该ORACLE_HOME目录下,但是owb不能安装在该目录下。

 

解决方法:

In addition to new location(ORACLE_HOME), create a new Inventory and inventory location file as follows.

1. Set the new location for installing OWB  like

export ORACLE_HOME=/u01/app/oracle/product/owb

ensure that the location /u01/app/oracle/product/ exists.

2. Create a directory oraInventory inside the ORACLE_HOME

3. Create  a file oraInst.loc pointing to oraInventory in $ORACLE_HOME by doing:

echo "inventory_loc=$ORACLE_HOME/oraInventory" > oraInst.loc

Note: the directory oraInventory and file oraInst.loc will be present under ORACLE_HOME

4. Invoke the installer by providing the parmater as

./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

以上方法来自metalink文档:317510.1 OWB Installation Terminates With Message Error in Invoking Target Isqlldr of Makefile

RAC环境下这样安装可能会找不到CRS信息,所以需要每个节点都执行安装

 

二、OWB配置方面

1、RAC数据库配置
RAC数据库必须为每个节点配置唯一的服务名,默认RAC对外使用统一服务名,也就是说所有节点都是同一个服务名,但是owb要求每个节点使用唯一的服务名!

为每个节点添加服务名,例如:

连接到RAC数据库中,执行命令:

SQL> select inst_id, instance_number, instance_name, host_name from gv$instance;

 

INST_ID INSTANCE_NUMBER INSTANCE_NAME DATABASE_STATUS STATUS HOST_NAME

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

1                     1 INST1         ACTIVE          OPEN   HOST1

2                     2 INST2         ACTIVE          OPEN   HOST2

查看数据库节点,然后查看服务名:

SQL> select s.inst_id, instance_number, instance_name,

name service_name, host_name

from gv$services s, gv$instance i where s.inst_id=i.inst_id;

 

INST_IDINSTANCE_NUMBER INSTANCE_NAME SERVICE_NAME HOST_NAME

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

1       1               INST1         INST     HOST1

2       2               INST2         INST     HOST2

使用命令为每个节点添加唯一的服务名:

$srvctl add service -d INST -s RACSRVC1 -r INST1

$srvctl add service -d INST -s RACSRVC2 -r INST2

启动新添加的服务:

$srvctl start service -d INST -s RACSRVC1

$srvctl start service -d INST -s RACSRVC2

然后再执行上面的查询命令:

SQL> select s.inst_id, instance_number, instance_name,

name service_name, host_name

from gv$services s, gv$instance i where s.inst_id=i.inst_id;

 

INST_IDINSTANCE_NUMBER INSTANCE_NAME SERVICE_NAME HOST_NAME

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

1       1               INST1         RACSRVC1     HOST1

2       2               INST2         RACSRVC2     HOST2

查看服务状态命令:

$srvctl status service -d INST -s RACSRVC1

$srvctl status service -d INST -s RACSRVC2

编辑tnsnames.ora文件(每个RAC节点都需要修改),修改相应service_name,例如:

RACSRVC1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = HOST1-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = RACSRVC1)

      (INSTANCE_NAME = INST1)

    )

  )

 

RACSRVC2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = HOST2-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = RACSRVC2)

      (INSTANCE_NAME = INST2)

    )

  )

2、在OWB中注册RAC中所有节点
根据owb安装文档,要求注册其他RAC节点。但是在实际配置过程中发现,目前我们的版本中(10.2.0.1.31),步骤需要调整下,需要先注册其他节点,然后再创建owb注册用户才能完全正确配置成功。具体步骤参加安装手册,这里只说下步骤:

1、 执行$OWB_HOME/owb/bin/unix/reposinst.sh脚本,在第一步选择advanced setup。

2、 然后下一步:


这里输入sys口令,Host Name输入服务器host实际ip地址,不要使用oracle的vip地址,输入端口号,Oracle Service Name输入我们前面定义的该节点的service name,如果按照上面的例子这里应该输入RACSRVC1。

3、 然后下一步:


选择Register a Real Application Cluster(RAC) instance,然后一直完成即可。

4、 注册其他节点,步骤与上面一样,注意ip地址和service name输入正确。

5、 在第一个节点创建所有owb用户

6、 创建完所有用户后,copy第一个节点$OWB_HOME/ owb/bin/admin/下rtrepos.properties文件到其他节点相同目录下。

完成owb上RAC所有节点注册。

安装完成后可以使用附件一:owbracdiag.sql脚本来检查安装是否成功!

 

 三、OWB创建用户中的问题
1、不能使用windows owb client创建owb注册用户
创建owb用户的操作必须使用服务器端脚本:$OWB_HOME/owb/bin/unix/ reposinst.sh来执行,如果出现问题,根据实际错误情况解决!

千万不要使用windows上的owb client来创建owb用户,这样容易出现很多问题,而且导致问题无法正确定位。切记!!

2、Failed To Load Java Into Db While Using Repository Assistant Ins-0029
使用$OWB_HOME/owb/bin/unix/reposinst.sh,创建用户到21%时候报错:


后台提示错误为:

(Spawn Token) Error loading Java into DB:

Error occured in 'processSPAWN': java.io.BufferedInputStream@ 6128453c

[processSPAWN]: A spawned program error. Exception = java.lang.Exception: Error occurred in

'processSPAWN': java.io.BufferedInputStream@ 6128453c

根据metalink文档:405746.1 Failed To Load Java Into Db While Using Repository Assistant Ins-0029

在$OWB_HOME/owb/bin/unix/run_service.sh脚本中,java加入-verbose参数,如:$JAVAPATH/bin/java -verbose -Xmx768M

然后再去执行$OWB_HOME/owb/bin/unix/reposinst.sh,提示报错:

ORA-12516 "TNS:listener could not find instance with matching protocol stack "

 

解决方法:

需要配置listener运行使用外部过程,配置方法参考Oracle Database Net Services Administrator's Guide手册中的'Default Configuration for External Procedures',这里列出步骤:

1、         配置listener.ora文件,如下:

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=sales.us.acme.com)

      (ORACLE_HOME=/oracle)

      (SID_NAME=sales))

    (SID_DESC=

      (SID_NAME=plsextproc)

      (ORACLE_HOME=/oracle)

      (PROGRAM=extproc)))

2、         配置tnsnames.ora文件,加入如下内容:

EXTPROC_CONNECTION_DATA=           

 (DESCRIPTION=                    

   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))                     

   (CONNECT_DATA=

(SID=plsextproc)))

配置完成后,再去执行,创建脚本,一切正常!

 

3、JAVA error
使用$OWB_HOME/owb/bin/unix/reposinst.sh,创建用户到51%时候报错:


根据错误提示,发现是JAVA调用有问题,使用Oracle用户,找到reposinst.sh使用的java环境,/ora/u01/app/owb/jre/1.4.2/bin目录下,执行:

$./java -version

#

# An unexpected error has been detected by HotSpot Virtual Machine:

#

#  SIGILL (0x4) at pc=0x20000000039a4070, pid=7665, tid=2305843009217006144

#

# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.4.2_10-b03 mixed mode)

# Problematic frame.:

# Segmentation fault

报错Segmentation fault,然后发现原Oracle使用的java和owb使用的java不同,owb下的java异常报错,而原Oracle软件的java一切正常,所以决定使用原Oracle软件的Java环境,然后再执行reposinst.sh,一切正常!

 

解决方法:

使用好的java环境替换owb有问题java环境,包括owb下的jre和jdk。

拷贝好的java环境到owb中或者修改reposinst.sh脚本中的jre和jdk路径。

 

4、重装owb后的java错误
前期错误的将owb安装在Oracle软件的ORACLE_HOME目录下,所以需要重新安装。根据owb手册,卸载owb,最后一步是:在数据库中删除owb的注册用户的schema objects,执行删除操作:

SQL>DROP USER OWBRT_SYS CASCADE;

User dropped

OWBRT_SYS用户删除成功!

SQL> drop user OWB_OWNER cascade;

drop user OWB_OWNER cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-29516: Aurora assertion failure: Assertion failure at joncomp.c:125

jtc_active_clint_init_ncomp_slots(java/lang/System, 0) returned 0

所有owb注册用户均无法drop。

查找metalink文档,没有有效信息,根据查询到的信息综合判断,觉得是该Oracle数据库中java调用异常,所以尝试创建新owb用户(owb为重新安装后的版本),结果在创建到51%时候,报错:


报错信息与drop用户错误提示一样为:

ORA-29516: Aurora assertion failure: Assertion failure at joncomp.c:125

jtc_active_clint_init_ncomp_slots(java/lang/System, 0) returned 0

ORA-06512: at "SYS.DBMS_JAVA", line 313

ORA-06512: at line 2

再次查询metalink,依然没有有效信息,判断还是Oracle数据库中java调用异常,但是不好定位错误具体信息,其中有文档提示可以重新加载java环境,但是有很大风险,而且因为该数据库的服务器上还有其他生产数据库在运行,所以放弃尝试该操作!

 

解决方法:

测试在该服务器创建新数据库,然后在新创建的数据库上创建注册owb用户,结果创建正常,所以放弃原来异常的owb注册用户的数据库,全部在新创建的数据库中重新创建,然后迁移用户数据到新数据库,一切正常!

这里需要说明的是:原owb安装在Oracle软件的ORACLE_HOME目录下,所以在卸载了owb后,可能使得owb使用的数据库java环境异常,从而导致上述drop owb注册用户以及新建用户都出现异常。

由于metalink上也没有关于这个问题的有效解决办法,所以只好选择重建库的方式解决。这里也再次强调:一定不要将owb安装在Oracle其他软件的目录下!!!

 

5、RAC环境下OWB用户表WB_RT_SERVICE_NODES的Host列显示为 LOCALHOST
在owb用户创建成功后,根据metalink文档455999.1 How to Verify if OWB is Installed Correctly on a RAC检查安装是否正常,发现执行语句:

SQL> select node_id node, instance_number inst, host, port,

service_name, server_side_home

from OWB_OWNER.wb_rt_service_nodes

发现第二个节点host结果为LOCALHOST

NODE INST HOST     PORT  SERVICE_NAME  SERVER_SIDE_HOME

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

 1  1    172.16.16.5     1521    RACSRVC1      /ora/u01/app/owb

 2  2    LOCALHOST     1521    RACSRVC2      /ora/u01/app/owb

 

解决方法:

根据metalink文档:782072.1 OWB RAC Installation Shows LOCALHOST in The Host Column in The WB_RT_SERVICE_NODES Table,需要修改host字段为正确值。

使用下面语句更新该字段即可:

SQL> update wb_rt_service_nodes set host = 'hostname' where host ='LOCALHOST' and node_id = x;

例如上面例子中的:

SQL>update OWB_OWNER.wb_rt_service_nodes set host = '172.16.16.6' where host ='localhost' and node_id = 2;

修改后结果:

SQL> select node_id node, instance_number inst, host, port,

service_name, server_side_home

from OWB_OWNER.wb_rt_service_nodes

 

NODE INST HOST     PORT  SERVICE_NAME  SERVER_SIDE_HOME

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

 1  1    172.16.16.5     1521    RACSRVC1      /ora/u01/app/owb

 2  2    172.16.16.6     1521    RACSRVC2      /ora/u01/app/owb

 

 

四、workflow中的问题
1、创建workflow用户
文档中没有提到RAC环境下创建workflow用户的情况,根据上面owb的处理方式,我也是采用了owb的方式,每个节点分别创建注册,从现在运行情况看,没有任何异常

步骤说明如下:

1、         在每个节点创建时,输入每个节点的ip地址和service name

   

2、         每个用户每个节点都执行一次

2、为用户授权
在安装工作流服务的文档中,已经提示:

To enable the Oracle Workflow server to invoke activities through the Warehouse Builder Runtime service, you must grant the EXECUTE ANY PROCEDURE system privilege to the Oracle Workflow repository user.

但是因为这是在一段英文中说明的,可能很多人都没有注意看,所以忽略掉了!

这里特别说明下:必须要执行,不然应用中有些job可能无法正常运行

所以写出这个简单的命令提醒:

grant EXECUTE ANY PROCEDURE to WF_MGR;

 

参考文档:

B28224-03《Oracle Warehouse Builder Installation and Administration Guide 10g Release 2 (10.2.0.2) for Windows and UNIX》

455999.1 How to Verify if OWB is Installed Correctly on a RAC

791362.1 Configuring OWB 11g On RAC, Option To Register Node is Missing

316623.1 How to Install the OWB Runtime Repository on a Cluster

782072.1 OWB RAC Installation Shows LOCALHOST in The Host Column in The WB_RT_SERVICE_NODES Table

737058.1 Diagnostical Script. for OWB on a RAC

317510.1 OWB Installation Terminates With Message Error in Invoking Target Isqlldr of Makefile

附件一:owbracdiag.sql
Owb在RAC安装后的诊断脚本:

owbracdiag.sql

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

rem

rem This script. can be used to check the Control Center Service on RAC nodes.

rem

rem USAGE

rem Using SQL*Plus, logon as the Control Center Owner from any node

rem   

rem    @/owb/rtp/sql/owbracdiag.sql

rem

 

 

set serveroutput on format wrapped

set feedback off;

set linesize 120

 

prompt

prompt DATA COLLECTED

prompt ==============

prompt

 

declare

  l_host_name varchar2(30);

  l_instance_name varchar2(30);

  l_platform_name varchar2(60);

  l_version varchar2(30);

  l_user_name varchar2(30);

  l_str varchar2(50);

 

  type t_cursor is ref cursor;

  l_cursor t_cursor;

 

begin

 

      select

           host_name, instance_name, version

      into

           l_host_name, l_instance_name, l_version

      from

           v$instance;

     

      if substr(l_version,1,1) not in ('8','9') then

 

           l_str :=  'select platform_name from v$database';

      

           open l_cursor for l_str;

           fetch l_cursor into l_platform_name;

           close l_cursor; 

      end if;

 

      select

           sys_context('USERENV','CURRENT_USER')

      into

           l_user_name

      from

           dual;

 

      dbms_output.put_line('OWBRACDIAG.SQL 1.1');

      dbms_output.put_line('Collecting from host : '||lower(l_host_name));  

 

      if substr(l_version,1,1) not in ('8','9') then

           dbms_output.put_line('Platform             : '||l_platform_name);  

      end if; 

 

      dbms_output.put_line('Instance Name        : '||lower(l_instance_name)||'. Version '||l_version);

      dbms_output.put_line('Executed by          : '||lower(l_user_name));

      dbms_output.put_line('Collection time      : '||lower(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')));  

end;

/

 

prompt ==========================================================================

 

prompt

 

declare

      l_cluster_database VARCHAR2(256);

      l_number_of_instances NUMBER;

      l_active_instances NUMBER;

      l_cluster_status VARCHAR2(30);

      l_max_commit_propagation_delay NUMBER;

      l_down_instances NUMBER;

begin

 

      select

           value into l_cluster_database

      from

           v$parameter

      where 

           name='cluster_database';

          

      select

           value into l_number_of_instances

      from

           v$parameter

      where 

           name='cluster_database_instances';

          

      select

           count(*)

      into

           l_active_instances

      from

           gv$active_instances;

          

    if l_active_instances < l_number_of_instances  then

           l_cluster_status := '(Not all nodes are active!)';    

      else

           l_cluster_status := '(All nodes active)';

      end if;

     

      select

           value into l_max_commit_propagation_delay

      from

           v$parameter

      where 

           name='max_commit_propagation_delay';

          

      dbms_output.put_line('Real Application Cluster (cluster_database)                  : '||l_cluster_database);      

      dbms_output.put_line('Instances configured in cluster (cluster_database_instances) : '||l_number_of_instances);

      dbms_output.put_line('Active instances in the cluster (gv$active_instances)        : '||l_active_instances||' '||l_cluster_status);

      dbms_output.put_line('Parameter max_commit_propagation_delay (Oracle 9.2 and 10.1) : '||l_max_commit_propagation_delay);

     

     

    if l_active_instances < l_number_of_instances  then

          l_down_instances := l_number_of_instances-l_active_instances;

           dbms_output.new_line;

           dbms_output.new_line;

           dbms_output.put_line('WARNING');

           dbms_output.put_line('=======');

           dbms_output.put_line('Unable to verify OWB install on all nodes of the RAC.');

           dbms_output.put_line('Because '||l_down_instances||' are currently down.');

           dbms_output.put_line('Proceeding with testing the '||l_active_instances||' active instance(s)...');

      end if;

end;

/

 

prompt

prompt

prompt RAC Views (gv$services joined with, gv$instance)

prompt ================================================

 

col inst_id for 999999

col instance_number for 999999

col instance_name for a15

col host_name for a20

col service_name for a20

 

select

      s.inst_id inst_id, instance_number, lower(instance_name) instance_name, lower(name) service_name, lower(host_name) host_name

from

      gv$services s, gv$instance i

where

      s.inst_id=i.inst_id

and

      name not in ('SYS$BACKGROUND','SYS$USERS')

and

      name not like ('%XDB')

order by

      inst_id, instance_number;

     

prompt

prompt

prompt Verifying wb_rt_service_nodes entries

prompt =====================================

 

col node_id for 999999

col instance_number for 999999

col host for a20

col service_name for a20

col server_side_home for a30

 

 

select

      node_id, instance_number, lower(host) host , lower(service_name) service_name, server_side_home

from

      wb_rt_service_nodes

order by

      node_id, instance_number;

 

prompt

prompt

prompt Verifying if wb_rt_service_nodes has Unique Servicenames...

 

declare

      l_count number;

      l_count_distinct number; 

 

begin

      select

           count(*), count(distinct service_name)

      into

           l_count, l_count_distinct

    from

          wb_rt_service_nodes;

     

    if l_count <> l_count_distinct then

           dbms_output.put_line('WARNING');

           dbms_output.put_line('=======');

           dbms_output.put_line('ServiceNames in wb_rt_service_nodes are not Unique.');  

      else

           dbms_output.put_line('Verified and OK.'); 

      end if;

end;

/

 

prompt

prompt Verifying if all RAC nodes are defined in wb_rt_service_nodes...

 

declare

      l_count number;

      l_number_of_instances number;

 

begin

 

      select

           value into l_number_of_instances

      from

           v$parameter

      where 

           name='cluster_database_instances';

 

      select

           count(*)

      into

           l_count

    from

          wb_rt_service_nodes;

     

    if l_count < l_number_of_instances then

           dbms_output.put_line('WARNING');

           dbms_output.put_line('=======');

           dbms_output.put_line('Not all '||l_number_of_instances||' RAC nodes are defined in wb_rt_service_nodes.');

           dbms_output.put_line('Parameter cluster_database_instances      = '||l_number_of_instances);

           dbms_output.put_line('But number of rows in wb_rt_service_nodes = '||l_count);

      else

           dbms_output.put_line('Verified and OK.'); 

      end if;

end;

/

 

 

 

prompt

prompt

prompt Verifying if all RAC nodes are defined in owbrt_sys.owbrtps

prompt ===========================================================

 

col key for a20

col value for a30

 

select

      key, value

from

      owbrt_sys.owbrtps;

     

declare

      l_count number;

      l_number_of_instances number;

 

begin

 

      select

           value into l_number_of_instances

      from

           v$parameter

      where 

           name='cluster_database_instances';

 

      select

           count(*)

      into

           l_count

    from

          owbrt_sys.owbrtps;

     

      dbms_output.new_line;

      dbms_output.new_line;

          

    if l_count <> l_number_of_instances then

           dbms_output.put_line('WARNING');

           dbms_output.put_line('=======');

           dbms_output.put_line('Not all '||l_number_of_instances||' RAC nodes are defined in owbrt_sys.owbrtps.');

      else

           dbms_output.put_line('Verified and OK.'); 

      end if;

end;

/

 

 

prompt

prompt

 

 

declare

      l_inst_id NUMBER;

      l_host VARCHAR2(256);

      l_service_name VARCHAR2(256);

      l_count NUMBER;

     

begin

      select

           count(*)

      into

           l_count

      from

           wb_rtv_service_nodes

      where

           currently_active = 1;

     

      if l_count = 1   then

     

           select

                 host, service_name

           into

                 l_host, l_service_name

           from

                 wb_rtv_service_nodes

           where

                 currently_active = 1;

                

           dbms_output.put_line('Control Center Service currently Active on host: '||lower(l_host));

        dbms_output.new_line;

           dbms_output.put_line('Shutting down CC Service before proceeding with test section. Please wait...');

           dbms_output.new_line;

      end if;

end;

/

 

 

declare

     

          

      l_inst_id NUMBER;

      l_host VARCHAR2(256);

      l_service_name VARCHAR2(256);

      l_count NUMBER;

     

      function wait_for_not_available

      return number

      is

           l_available number := 1;

           l_count number := 0;

      begin

 

           service_available(l_available);

 

           while l_available <> 0 and l_count < 40

                 loop

                      dbms_lock.sleep(2);

                      service_available(l_available);

                      l_count := l_count + 1;

                 end loop;

   

           return l_available;

 

      end;

 

     

begin

 

      select

           count(*)

      into

           l_count

      from

           wb_rtv_service_nodes

      where

           currently_active = 1;

     

      if l_count = 1   then

          

           wb_rt_service_management.disable_all;

          

           commit;

          

           if wait_for_not_available <> 0

                 then

                      dbms_output.put_line('Service still Available.');

                 else

                      dbms_output.put_line('CC Service shutdown on all nodes.');

           end if;

          

           dbms_output.new_line;

      else

           dbms_output.put_line('CC Service not available. Proceeding to test section...');

      end if;

end;

/

 

prompt

prompt Testing RAC node(s) in progress, please wait...

prompt

 

declare

     

      l_node_id number;

      l_host varchar2(256);

      l_port number;

      l_service_name varchar2(256);

     

      cursor l_node_cursor is

          select

                 node_id, host, port, service_name

           from

                 wb_rtv_service_nodes wb, gv$active_instances gv

           where

                 wb.node_id = gv.inst_id

           and

                 gv.inst_id = gv.inst_number

           order by

                 node_id;

 

      function wait_for_not_available

      return number

      is

           l_available number := 1;

           l_count number := 0;

      begin

 

           service_available(l_available);

 

           while l_available <> 0 and l_count < 40

                 loop

                      dbms_lock.sleep(2);

                      service_available(l_available);

                      l_count := l_count + 1;

                 end loop;

   

           return l_available;

 

      end;

 

      function wait_for_is_available

      return number

      is

           l_available number;

           l_org_log_count number;

           l_log_count number;

           l_count number := 0;     

      begin

 

           service_available(l_available);

     

           select

                 count(*)

           into

                 l_log_count

           from

              wb_rt_service_job_logs;

                

           l_org_log_count := l_log_count;

     

           while l_available = 0 and l_log_count = l_org_log_count and l_count < 40

                 loop

                      dbms_lock.sleep(2);

                      service_available(l_available);

                      select

                            count(*)

                      into

                            l_log_count

                      from

                            wb_rt_service_job_logs;

                                 

                      l_count := l_count + 1;

 

                      end loop;

 

           return l_available;

      end;

     

     

      procedure list_service_nodes

      as

          

           l_str   varchar2(255);

           type t_cursor is ref cursor;

           l_cursor t_cursor;

           l_enabled varchar2(10);

      begin

          

           l_str :=  'select enabled from wb_rt_service_nodes';

 

           open l_cursor for l_str;

 

           loop

                 fetch l_cursor into l_enabled;

                 exit when l_cursor%notfound;

 

                 dbms_output.put_line('Enabled => '|| l_enabled);

 

           end loop;

 

           close l_cursor; 

      end;

          

          

      procedure list_errors

      as

          

      begin

          

           for d in (

                 select

                      message

                 from

                      (select * from wb_rt_service_job_logs order by time_stamp desc)

                            where

                                  rownum < 2

                      )

           loop

                     

      

                 declare

                      l_next number := 1;

                      l_length number := length(d.message);

                      l_fraglen number;

                 begin

                      while l_next <= l_length

                            loop

                                  l_fraglen := l_length - l_next + 1;

                                  if l_fraglen > 79

                                       then

                                             l_fraglen := 79;

                                  end if;

                                  dbms_output.put_line(substr(d.message, l_next, l_fraglen));

                                  l_next := l_next + l_fraglen;

                            end loop;         

                 end;

           end loop;

      end;

     

                     

begin

      dbms_output.enable(100000);

      open l_node_cursor;

      fetch l_node_cursor into l_node_id, l_host, l_port, l_service_name;

      loop

          

           dbms_output.put_line('Testing Node '||l_node_id||': '||lower(l_host)||':'||l_port||':'||lower(l_service_name)||'...');

      dbms_output.put_line('======================================================================');

           dbms_output.put_line('Starting Service...');

                

            wb_rt_service_management.enable_node(l_node_id,wb_rt_constants.STARTUP_KIND_MANUAL);

           commit;

 

           -- list_service_nodes();

     

           if wait_for_is_available = 0

                 then

                      dbms_output.put_line('Not Available!');    

                      list_errors();

                 else

                      dbms_output.put_line('Available');

                      dbms_output.put_line('Verified and OK.');

           end if;

   

           dbms_output.put_line('Stopping Service...');

           wb_rt_service_management.disable_all;

           commit;

                           

           if wait_for_not_available <> 0

                 then

                      dbms_output.put_line('Available');

                 else

                      dbms_output.put_line('Not Available');

           end if;

          

           dbms_output.new_line;

                     

           fetch l_node_cursor into l_node_id, l_host, l_port, l_service_name;

           exit when l_node_cursor%notfound;

 

      end loop;

     

end;

/

 

 

set verify off

 

ACCEPT answer PROMPT "CC service disabled. Enable service on all nodes? (Y/N) "

 

declare

 

      l_answer varchar2(1) := upper(ltrim(substr(nvl('&answer', 'N'), 1,1)));

     

      function wait_for_is_available

      return number

      is

           l_available number;

           l_org_log_count number;

           l_log_count number;

           l_count number := 0;     

      begin

 

           service_available(l_available);

     

           select

                 count(*)

           into

                 l_log_count

           from

              wb_rt_service_job_logs;

                

           l_org_log_count := l_log_count;

     

           while l_available = 0 and l_log_count = l_org_log_count and l_count < 40

                 loop

                      dbms_lock.sleep(2);

                      service_available(l_available);

                      select

                            count(*)

                      into

                            l_log_count

                      from

                            wb_rt_service_job_logs;

                                 

                      l_count := l_count + 1;

 

                      end loop;

 

           return l_available;

      end;

     

begin

 

      if l_answer = 'Y' then

     

           wb_rt_service_management.enable_all;

     

           if wait_for_is_available = 0 then

                 dbms_output.put_line('Not Available!');    

           else

                 dbms_output.put_line('Available');

           end if;

      end if;

end;

/

 

prompt

 

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

请登录后发表评论 登录
全部评论

注册时间:2008-11-03

  • 博文量
    12
  • 访问量
    69012