Last modified on 5 April 2013, at 05:55

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.



  1. 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]$ 
    
  2. Shutdown the service.

    collabn1:/home/oracle[RAC1]$ srvctl stop service -d RAC -s pxtest
    collabn1:/home/oracle[RAC1]$ 
    
  3. 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;
    /
    


  4. 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;
    


  5. 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.trc
    


  6. Modify 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 pxtest
    


  7. Submit 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