【DB筆試面試480】 RAC中如何指定JOB的運行實例？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
RAC中如何指定JOB的運行實例？
♣
答案部分
在RAC中，可以讓JOB在某個指定的實例上運行。對於DBMS_JOB和DBMS_SCHEDULER來說，它們的指定方法不同：
（1）在DBMS_JOB下，在執行SYS.DBMS_JOB.SUBMIT包創建JOB的時候，可以指定INSTANCE參數，該參數指定了JOB運行的實例。在RAC環境中，採用DBMS_JOB包可以指定JOB運行時候的實例。
示例代碼如下所示，該例子展示了獲取RAC數據庫的IP地址並記錄到臨時表T_IPADDRESS_LHR中，例子很經典，希望讀者可以理解。
1） 創建臨時表用於記錄RAC數據庫的IP地址
DROP TABLE T_IPADDRESS_LHR; CREATE TABLE T_IPADDRESS_LHR(INST_ID NUMBER, HOST_NAME VARCHAR2(255), HOST_IP VARCHAR2(255) );
2） 創建視圖VH_IPADDRESS_LHR用於從V$DIAG_ALERT_EXT中獲取IP地址
CREATE OR REPLACE VIEW VH_IPADDRESS_LHR AS SELECT A.HOST_ID || ': ' || A.HOST_ADDRESS HOST_IP1, A.HOST_ADDRESS HOST_IP2, A.HOST_ID HOST_NAME2 FROM V$DIAG_ALERT_EXT A WHERE A.COMPONENT_ID = 'RDBMS' AND UPPER(A.FILENAME) = (SELECT UPPER(SUBSTR(D.VALUE, 1, (LENGTH(D.VALUE) - 5)) || 'ALERT' || SUBSTR(D.VALUE, -6, 1) || 'LOG.XML') FROM V$PARAMETER D WHERE D.NAME = 'BACKGROUND_DUMP_DEST') AND A.INDX = (SELECT MAX(B.INDX) FROM V$DIAG_ALERT_EXT B WHERE B.COMPONENT_ID = 'RDBMS' AND UPPER(B.FILENAME) = (SELECT UPPER(SUBSTR(D.VALUE, 1, (LENGTH(D.VALUE) - 5)) || 'ALERT' || SUBSTR(D.VALUE, -6, 1) || 'LOG.XML') FROM V$PARAMETER D WHERE D.NAME = 'BACKGROUND_DUMP_DEST')); /
3） 創建存儲過程用於在指定的實例上運行程序，首先從函數UTL_INADDR.GET_HOST_ADDRESS中獲取，若是系統沒有該包，則從視圖VH_IPADDRESS_LHR中獲取IP地址
CREATE OR REPLACE PROCEDURE PH_IP_LHR AS V_SQL VARCHAR2(4000); BEGIN INSERT INTO T_IPADDRESS_LHR (INST_ID, HOST_NAME) SELECT V.INSTANCE_NUMBER, V.HOST_NAME FROM V$INSTANCE V; COMMIT; V_SQL := 'UPDATE T_IPADDRESS_LHR T SET T.HOST_IP = UTL_INADDR.GET_HOST_ADDRESS WHERE T.INST_ID = USERENV(''INSTANCE'')'; EXECUTE IMMEDIATE V_SQL; COMMIT; EXCEPTION WHEN OTHERS THEN V_SQL := 'UPDATE T_IPADDRESS_LHR T SET T.HOST_IP = (SELECT V.HOST_IP2 FROM VH_IPADDRESS_LHR V) WHERE T.INST_ID = USERENV(''INSTANCE'')'; EXECUTE IMMEDIATE V_SQL; COMMIT; END PH_IP_LHR; /
4） 創建JOB來獲取IP地址
DECLARE X NUMBER; BEGIN FOR CUR IN (SELECT B.JOB FROM DBA_JOBS B WHERE B.WHAT = 'PH_IP_LHR;') LOOP SYS.DBMS_IJOB.REMOVE(CUR.JOB); COMMIT; END LOOP; FOR CUR IN (SELECT B.INST_ID FROM GV$INSTANCE B) LOOP SYS.DBMS_JOB.SUBMIT(JOB => X, WHAT => 'PH_IP_LHR;', NEXT_DATE => SYSDATE+CUR.INST_ID/8640, INTERVAL => 'NULL', NO_PARSE => FALSE, INSTANCE => CUR.INST_ID); COMMIT; END LOOP; END; /
（2）DBMS_SCHEDULER下指定實例運行JOB稍微有點複雜，首先創建SERVICE，再創建JOB_CLASS，最後創建JOB才可以，具體過程可以參考如下案例的代碼。
該例子和上面的DBMS_JOB例子一樣展示了獲取集群數據庫的IP地址並記錄到臨時表T_IPADDRESS_LHR中，例子的前3步和上面的例子一樣，不再贅述，接下來就是創建SERVICE和JOB_CLASS，最後是創建JOB的過程。
BEGIN FOR CUR IN (SELECT V.INST_ID, V.INSTANCE_NAME, 'INST_LHR_' || V.INST_ID SERVICE_NAME, 'LHR_RAC' || V.INST_ID || '_JOB_CLASS' JOB_CLASS_NAME, 'RAC_LHR_' || V.INST_ID JOB_NAME FROM GV$INSTANCE V) LOOP BEGIN DBMS_SERVICE.STOP_SERVICE(SERVICE_NAME => CUR.SERVICE_NAME, INSTANCE_NAME => CUR.INSTANCE_NAME); DBMS_SERVICE.DELETE_SERVICE(SERVICE_NAME => CUR.SERVICE_NAME); EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DBMS_SCHEDULER.DROP_JOB_CLASS(JOB_CLASS_NAME => CUR.JOB_CLASS_NAME, FORCE => TRUE); EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DBMS_SCHEDULER.DROP_JOB(JOB_NAME => CUR.JOB_NAME, FORCE => TRUE); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; FOR CUR IN (SELECT V.INST_ID, V.INSTANCE_NAME, 'INST_LHR_' || V.INST_ID SERVICE_NAME, 'LHR_RAC' || V.INST_ID || '_JOB_CLASS' JOB_CLASS_NAME, 'RAC_LHR_' || V.INST_ID JOB_NAME FROM GV$INSTANCE V) LOOP DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME => CUR.SERVICE_NAME, NETWORK_NAME => CUR.SERVICE_NAME); DBMS_SERVICE.START_SERVICE(SERVICE_NAME => CUR.SERVICE_NAME, INSTANCE_NAME => CUR.INSTANCE_NAME); DBMS_SCHEDULER.CREATE_JOB_CLASS(JOB_CLASS_NAME => CUR.JOB_CLASS_NAME, SERVICE => CUR.SERVICE_NAME); DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'RAC_LHR_' || CUR.INST_ID, JOB_TYPE => 'STORED_PROCEDURE', JOB_ACTION => 'PH_IP_LHR', REPEAT_INTERVAL => NULL, --'FREQ=MINUTELY;INTERVAL=1' JOB_CLASS => CUR.JOB_CLASS_NAME, END_DATE => NULL, ENABLED => TRUE); END LOOP; END; /
& 說明：
有關具體的操作過程可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2072635/
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
