Running Shell Script on Different Linux Server using oracle PL/SQL SP

Question

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 ?

Copy shell script from "Linux Server B" to "Linux Server A". — Littlefoot
– Littlefoot, Commented Aug 22, 2018 at 10:53
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
– Alex Poole, Commented Aug 22, 2018 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
– Rahul, Commented Aug 22, 2018 at 10:56
Create a NFS share pointing at the location of the script and reference the share when calling it. — BriteSponge
– BriteSponge, Commented Aug 22, 2018 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
– Littlefoot, Commented Aug 22, 2018 at 11:02

BriteSponge · Accepted Answer · 2018-08-22 13:12:59Z

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/)

answered Aug 22, 2018 at 13:12

BriteSponge

1,0549 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alex Poole Over a year ago

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?

BriteSponge Over a year ago

@AlexPoole - ahhh, I misunderstood. It could get a bit 'clunky' then. Sounds like 'ssh' will be the answer.

Alex Poole Over a year ago

To be fair, that requirement was buried in a comment when you answered *8-)

Rahul Over a year ago

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"

Collectives™ on Stack Overflow

Running Shell Script on Different Linux Server using oracle PL/SQL SP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related