Last modified on 5 April 2013, at 05:55

RAC Attack - Oracle Cluster Database at Home/File Test


Our second PL/SQL test will look at the UTL_FILE package. With any PL/SQL operations on RAC you must be aware that the code could execute on any node where its service lives. This could also impact packages like DBMS_PIPE, UTL_MAIL, UTL_HTTP (proxy server source IP rules for example), or even DBMS_RLS (refreshing policies).



  1. Login to RAC1 as sh and create a file that we can try reading later.

    collabn1:/home/oracle[RAC1]$ sqlplus sh/sh@RAC1
     
    create directory orahome as '/home/oracle';
     
    declare
      fl utl_file.file_type;
    begin
      fl := utl_file.fopen('ORAHOME','data.txt','w');
      utl_file.put_line(fl, 'Some sample data for an oracle test.', TRUE);
      utl_file.fclose(fl);
    end;
    /
    


  2. Exit SQLPLUS. At the prompt, copy this command to connect to the RAC service as sh again and attempt to read the file you just wrote. Run this command 10-20 times in a row. (Cut-and-paste is recommended.) What happens? Why?

    sqlplus -S sh/sh@RAC <<EOF
    declare 
      fl utl_file.file_type;
      data varchar2(1024);
    begin
      fl := utl_file.fopen('ORAHOME','data.txt','r');
      utl_file.get_line(fl, data);
      utl_file.fclose(fl);
    end;
    /
    exit;
    EOF