RAC Attack - Oracle Cluster Database at Home/Scheduler Test

In RAC, PL/SQL can execute on either node - and this must be taken into account when processes are architected. We will take a brief look at this property through two simple tests. First we'll have a look at the scheduler. Note that this lab relies on the PXTEST service created in the Parallel Query Test. Login to the node collabn1 as oracle and confirm that the pxtest service is running on instance RAC2. collabn1:/home/oracle[RAC1]$ srvctl status service -d RAC -s pxtest Service pxtest is running on instance(s) RAC2 collabn1:/home/oracle[RAC1]$ Shutdown the service. collabn1:/home/oracle[RAC1]$ srvctl stop service -d RAC -s pxtest collabn1:/home/oracle[RAC1]$ Login to the RAC1 service as sh and create a job class and a PL/SQL procedure that we can execute from the job. Note that the service name is case sensitive! collabn1:/home/oracle[RAC1]$ sqlplus sh/sh@RAC1 exec dbms_scheduler.create_job_class('TESTOFF1',service=>'pxtest'); create or replace procedure traceme(id varchar2) as x number; begin execute immediate 'alter session set tracefile_identifier=||id||'; dbms_session.session_trace_enable(true,true); select count(*) into x from sh.customers; dbms_session.session_trace_disable(); end; /Schedule the job to run immediately with the job class that's tied to the PXTEST service. Check to see if it ran. Query the user_schedule_jobs table a few times in a row. Did anything happen? select job_name, schedule_type, job_class, enabled, auto_drop, state from user_scheduler_jobs; begin dbms_scheduler.create_job('TESTJOB1','PLSQL_BLOCK', job_action=>'traceme(scheduler01);', job_class=>'TESTOFF1',enabled=>true); end; / select job_name, schedule_type, job_class, enabled, auto_drop, state from user_scheduler_jobs;Startup the PXTEST service and check the status of the job again. Make sure to query the user_schedule_jobs table a few times in a row. (Be patient for at least one minute.) Did the job execute? If so, then on which node? host srvctl start service -d RAC -s pxtest select job_name, schedule_type, job_class, enabled, auto_drop, state from user_scheduler_jobs; host ssh collabn2 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC2/trace/*scheduler01.trc host ssh collabn1 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC1/trace/*scheduler01.trcModify the PXTEST service to run on both nodes and stop it. SQL> host srvctl modify service -d RAC -s pxtest -n -i RAC1,RAC2 SQL> host srvctl stop service -d RAC -s pxtestSubmit 20 jobs to run the program and then enable the service. (This way all of the jobs should get scheduled nearly concurrently.) On which node(s) did they execute? begin FOR i IN 10..29 LOOP dbms_scheduler.create_job('TESTJOB'||i,'PLSQL_BLOCK', job_action=>'traceme(scheduler'||i||);', job_class=>'TESTOFF1',enabled=>true); END LOOP; end; / select job_name, schedule_type, job_class, enabled, auto_drop, state from user_scheduler_jobs; SQL> host srvctl start service -d RAC -s pxtest' select job_name, schedule_type, job_class, enabled, auto_drop, state from user_scheduler_jobs; host ssh collabn2 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC2/trace/*scheduler*.trc host ssh collabn1 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC1/trace/*scheduler*.trc Parallel Query Test File Test