Home > Oracle, PL / SQL, RAC, Unix > Running Shell Script in PL/SQL on a RAC Environment

Running Shell Script in PL/SQL on a RAC Environment

Sometimes we need to run shell script within the PL/SQL code. To do accompplish this we have two options :

1 ) Create a java stored class that runs shell script with Java Runtime class.
2 ) Using DBMS_SCHEDULER Oracle supplied package

When you are on second option and you work on a RAC environment you should be aware of somethings that are listed below:

1 ) Unix user who had installed the Oracle Database ( generally oracle ) should have been granted to run specified shell script. This is a requirement whether you use Oracle RAC or not.
2 ) In shell script, use exact path of supplied unix utilities instead of using its actual name. For example, if shell script use “echo” utility and you do not call explicitly profile file or set environment variables, you should specify its path exactly what it is, like “/bin/echo”.
3 ) Shell script that will be run by Oracle DB should be placed on all of the nodes with same grants. Because you do not know which node will execute the shell script.
Assume that you have 2 nodes a. Your shell script is located on /home/oracle/demoSh/demo.sh . And instance names that match nodes which are listed below :

node1 : galatats1
node2 : galatats2

By the way you can learn your instance name in your session via querying v$instance system view.

On instance2 :

[oracle@galatats02.no.turkcell.tgc]:/home/oracle/demoSh> ls -la
total 20
drwxr-xr-x 2 oracle oinstall 4096 Sep 26 01:49 .
drwx—— 6 oracle 501 4096 Sep 26 01:49 ..
-rwxr-xr-x 1 oracle oinstall 1239 Sep 26 01:49 demo.sh
-rw-r–r– 1 oracle oinstall 20 Sep 26 01:49 output.txt

In order to execute demo.sh inside the PL/SQL we are creating a DBMS_SCHEDULER job like that :

job_name => ‘DEMO’,
job_type => ‘EXECUTABLE’,
job_action => ‘/home/oracle/demoSh/demo.sh’,
start_date => SYSTIMESTAMP,
enabled => true,
auto_Drop => true,
comments => ‘Demo’);

As soon as job is created, it started to run. As soon as job completed its run, it is dropped automatically. Because of auto_drop clause was set to true while job was creating. ( By the way you do not necessary to do this ). How can I check status of my job that has already dropped? You can use USER_SCHEDULER_JOB_RUN_DETAILS view to check status of all scheduler jobs that run. Also In a RAC environment you can see node that job was executed on.

If you change name of demo.sh file on node1, the job we have created above will run successfully sometimes. If you check the status code of user_scheduler_job_run_details you can see both SUCCESSFUL and FAILED status of your job. In the FAILED row you may see this in ADDITIONAL_INFO column:

ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

So that, in order to execute shell script as successful via PL/SQL you have to obey some rules. In this article I tried to tell what they are.

Categories: Oracle, PL / SQL, RAC, Unix Tags: , ,
  1. January 11, 2013 at 1:43 pm

    Very good article. I will be going through many of these
    issues as well..

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: