RAC環境下安裝部署OWB問題總結

Allen2312發表於2009-06-28

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章