2

I want to run a shell script placed on Linux server when a stored procedure is called.

The below code works like a charm if the script is placed on the same server where database is installed, say "Linux Server A".

Database Version: Oracle Database 12c Linux Version: Red Hat Linux 7

begin
  dbms_scheduler.create_credential
  (
    credential_name => 'my_credential',
    username        => 'user',
    password        => 'pass'
  );
end;
/
create or replace procedure RunShell
as
begin
  dbms_scheduler.create_job
  (
    job_name             => 'shell_scripts_job',
    job_type             => 'executable',
    number_of_arguments  => 1,
    job_action           => '/usr/bin/sh',
    auto_drop            => true,
    credential_name      => 'my_credential'
  );
  dbms_scheduler.set_job_argument_value(job_name=>'shell_scripts_job', argument_position=>1, argument_value=>'/u01/Script.sh');
  dbms_scheduler.enable('shell_scripts_job');
  dbms_scheduler.run_job(job_name=>'shell_scripts_job');
END;
/
EXECUTE RunShell;

The issue is, my DB is installed on "Linux Server A" and the shell script I want to run is on "Linux Server B".

This script performs some operations which has to be done "Linux Server B". One of the main functions is that it starts/stops another application residing on "Linux Server B".

How can I achieve this ?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Rahul
  • 191
  • 1
  • 11
  • Copy shell script from "Linux Server B" to "Linux Server A". – Littlefoot Aug 22 '18 at 10:53
  • 1
    Can you ssh from A to B to call the script remotely, as part of the job? (Implies key access already configured, with no passphrase...) – Alex Poole Aug 22 '18 at 10:54
  • @Littlefoot: But after copying, how would PL/SQL Stored Proc RunShell would know to run /u01/Script.sh on "Linux Server B" but not on "Linux Server A" ? – Rahul Aug 22 '18 at 10:56
  • 1
    Create a NFS share pointing at the location of the script and reference the share when calling it. – BriteSponge Aug 22 '18 at 11:02
  • As you said: PL/SQL procedure runs a script which is located on the database server (which is "Linux Server A"). So, if you copy a script from "Linux Server B" to "Linux Server A", it will be accessible to your PL/SQL code and will be executed. Exactly what you've already said: "code works like a charm if the script is placed on the same server where database is installed" – Littlefoot Aug 22 '18 at 11:02
  • @Alex: I agree that one way is to ssh from "Linux Server B" to "Linux Server A" ,when script will be called on "Linux Server A". But i am looking to directly run it on "Linux Server B". How I can direct the Stored Proc or any other method like this to achieve the required functionality. – Rahul Aug 22 '18 at 11:04
  • @Littlefoot: But then after copying script from "Linux Server B" to "Linux Server A" , finally it will be executed on "Linux Server A".. That's not what I want, because the script performs some operations which has to be done "Linux Server B". – Rahul Aug 22 '18 at 11:10
  • @BriteSponge: Thanks for the idea but could you please add some more details like how I can pass that Network Shared Path in my code here. – Rahul Aug 22 '18 at 11:12
  • "... script performs some operations which has to be done "Linux Server B"" - that's a new info; you didn't say that earlier. There's nothing I'd like to add (another people have said it already), except: do you *really* have to include your database into performing tasks at the operating system level? Can't you schedule that task from your operating system scheduling application? – Littlefoot Aug 22 '18 at 11:18
  • @Littlefoot: My bad on not adding that in Question..Will do that...Can't you schedule that task from your operating system scheduling application? --> No Actually... I'm basically using Oracle Apex UI to trigger these scripts and the only option I got from searching is that it has to be done throgh PL/SQL SP. – Rahul Aug 22 '18 at 11:23
  • What does that shell script do? – Littlefoot Aug 22 '18 at 11:48
  • @Littlefoot: There are lot many functionality in that.. One of the main functionality is that it Start/Stops another application residing on "Linux Server B" – Rahul Aug 22 '18 at 11:52
  • I see ... well, that's enough complex on operating system level (privileges and stuff), so adding the database into equation doesn't help (on the contrary!). Sorry, I'm afraid I can't help. Hopefully, someone else will. – Littlefoot Aug 22 '18 at 12:04
  • No worries Littlefoot, I appreciate your time and ideas to explore solutions for my problem :) – Rahul Aug 22 '18 at 12:07
  • You could set up a script on the remote server that checks for the existence of a flag file and is run by CRON at certain intervals. When it finds the file it can run your script.sh. The job on server A can simply call a local script that writes the flag file to an NFS share. This is a bit clunky and there isn't an easy way for Server A to know that the service actually started on Server B but it might be a possibility for you. – BriteSponge Aug 23 '18 at 08:44

1 Answers1

1

You can create a NFS share on the remote machine and then mount that share on the local machine. That remote directory that is being shared can then be referred to as though it was a local directory.

As an example if you create a share on the remote directory '/u01' and call that share 'remote_scripts' you would mount that at /mnt/remote_scripts.

The argument_value in your code would then be 

argument_value=>'/remote_scripts/Script.sh'

There is a fair bit more detail to setting up the share and the permissions need to be thought about but in principle this works.

The following page gives a good rundown of the set-up but get your SYSADMIN involved as they know your network. (https://www.tecmint.com/how-to-setup-nfs-server-in-linux/)

BriteSponge
  • 1,034
  • 9
  • 15
  • The script will exist on the remote server (B), but will still execute on the local server (A); so it can't perform operations on the remote server (unless they directly affect files on the NFS share); it can't start/stop another application residing on the remote server - can it? – Alex Poole Aug 22 '18 at 14:31
  • 1
    @AlexPoole - ahhh, I misunderstood. It could get a bit 'clunky' then. Sounds like 'ssh' will be the answer. – BriteSponge Aug 23 '18 at 08:22
  • To be fair, that requirement was buried in a comment when you answered *8-) – Alex Poole Aug 23 '18 at 08:24
  • Thanks guys for helping me out, I think the only way now is to let my Stored Proc run a Script on "Linux Server A", which in turn will do ssh to "Linux Server B" and will call the script on "Linux Server B" – Rahul Aug 23 '18 at 09:05