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 = '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 = '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;
/
