How to Use USER_DEFINED Activity in OWB Process Flow (part II)
By shawn wu on Aug 19, 2010
We have discussed how to use USER_DEFINED activity in OWB Process Flow in the past. In this topic, we will talk about another option for this property - “SCHEDULER”. With this setting, we can run the external job as a configured OS user.
Enable execution of User Defined activity
Presume we are using the UNIX platform for this simple demo. First, we need to modify the Runtime.properties as below. SCHEDULER uses a DBMS Scheduler external job submitted by the Control Center repository owner which is executed by the default operating system user configured by the DBA
To make this change take effect, we need to restart the Control Center Service. Then we can take advantage of the OS user configure settings in <ORACLE_HOME>/rdbms/admin/externaljob.ora (which is configured to run the jobs as a dedicated batch user).
By default, a low privileged user & group (nobody) is defined in this file, you need to change this to the expected OS user for the execution (In my environment, I have set it to owb user). Note: externaljob.ora is owned by root, so you need to root privilege to make the changes.
In OWB design center, let's still use the simple process flow, which contains a Start activity, a User Defined activity and an End Success activity.
Leave all parameters of activity USER_DEFINED unchanged except that we enter /tmp/test.sh into the Value column of the COMMAND parameter.
Then let's create the shell script test.sh in /tmp directory. Here is the content of /tmp/test.sh (this article is demonstrating a scenario in Linux system, and /tmp/test.sh is a Bash shell script):
echo `id` > /tmp/test.txt
Note: don't forget to grant the execution privilege on /tmp/test.sh to your dedicated OS user. For simplicity, we just use the following command.
chmod a+x /tmp/test.sh
After the change is ready, let’s deploy and execute the Process Flow. We will see that the Process Flow completes successfully.
The execution of /tmp/test.sh successfully generated a file /tmp/test.txt, which contains the owb user information as below.
UNIX platform and ORACLE 11.2 are used in above example; there are different settings with different environments though. With UNIX platform, if using release 10.2.0.2 or prior releases (where there is no rdbms/admin/externaljob.ora) we need to change the owner of the extjob executable (who should be nobody by default); With Windows platform, instead of modify externaljob.ora, we need to change the user that the external jobs Windows service runs as ("OracleJobSchedulerSID" service).
OWB uses DBMS_SCHEDULER (create_job, enable etc) package to execute external processes, so when we have issues with the execution, we can try executing using the package manually to narrow down the issue. An example is as below:
job_name => 'TESTJOB',
job_type => 'EXECUTABLE',
job_action => '/tmp/test.sh');
This block should be run under the context of the Control Center Service Owner, In OWB 11.2 release, the owner should be OWBSYS. The view “all_scheduler_job_run_details” can be queried for more details with the job run information.
We can also pass the parameters to the command or leverage the return value with “SCHEDULER”, they are the same as discussed in previous post, and will not be discussed here.