running EXTERNAL_SCRIPT jobs fails with "Unable to find or open libpam.so in $LD_LIBRARY_PATH"

[hal9ccc]

Hi,

I'm trying to run an EXTERNAL_SCRIPT job from XE21c:full but it doesn't start because a binary library is missing.

Using the example from https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#external-script I am getting the following ADDITIONAL_INFO in ALL_SCHEDULER_JOB_RUN_DETAILS:

EXTERNAL_LOG_ID="job_76121_2534",
ORA-27369: Job vom Typ EXTERNAL_SCRIPT nicht erfolgreich mit Exitcode: Device or resource busy
ORA-27369: Job vom Typ EXTERNAL_SCRIPT nicht erfolgreich mit Exitcode: Unable to find or open libpam.so in $LD_LIBRARY_PATH

I've already looked through the install script but couldn't spot a possible reason for this.

Any ideas?

[gvenzl]

Hey @hal9ccc, I think I know what the issue is, the pam package is removed in these images:

oci-oracle-xe/install.2130.sh

Line 1074 in 2c0b7b4

libpwquality libseccomp libtirpc libutempter lm_sensors-libs make pam \

Didn't think it's needed any longer but for external jobs, yeah, they have to authenticate with the OS.

I will need to check the dependencies tree and whether we can just leave it inside the image.
I will also have to check whether inside Docker containers it's actually possible to run external jobs that reauthenticate as an OS user.

[hal9ccc]

I also tested FILE_WATCHER jobs and these just failed with no reasons or output given anywhere - which is strange because I'd expect a similar error message. Probably that'll also be fixed once the pam lib is back...
BTW can we install the pam package via rpm or yum? yum also isn't included but I'd prefer to have it in the image just in case. To be honest, I thought that choosing the "full" flavor would retain most tools...

[gvenzl]

Hey @hal9ccc,

I will check on FILE_WATCHER jobs as well.

BTW can we install the pam package via rpm or yum?

Yeah, you can but have to use microdnf instead, which is a tiny dnf and the successor to yum on Linux 8. Very same syntax though.

To be honest, I thought that choosing the "full" flavor would retain most tools...

It does, just happens to be that I thought that pam was only required for the DB installation itself, not for running external jobs.

[gvenzl]

Hey @hal9ccc, a quick update on this:

Reinstalling pam does indeed solve the original error.
However, it leads to another error Invalid username or password:

2021-11-30T04:44:11.765876+00:00
XEPDB1(3):Errors in file /opt/oracle/diag/rdbms/xe/XE/trace/XE_j000_776.trc:
ORA-12012: error on auto execute of job "SYS"."JOB$_8"
ORA-27369: job of type EXECUTABLE failed with exit code: Argument list too long
ORA-27369: job of type EXECUTABLE failed with exit code: Invalid username or password

This is due to the fact that the oracle OS user does not have a password set inside the container. One can also work around that of course, but that just leads to the next error:

2021-11-30T04:45:55.628235+00:00
XEPDB1(3):Errors in file /opt/oracle/diag/rdbms/xe/XE/trace/XE_j000_808.trc:
ORA-12012: error on auto execute of job "SYS"."JOB$_10"
ORA-27369: job of type EXECUTABLE failed with exit code: No child processes

This one is more difficult to understand what's going on. I do have a hunch that the scheduler is trying to start a new terminal session, which inside a container would not be possible the way it is outside of it. However, that's just as said a hunch and I need to dig deeper to understand what's going on behind the scenes.

[hal9ccc]

I had a similar setup on XE18 using the official images and there I also had to create a new user + password to run the job as well as fire a bunch of chmod / chown statements. The original error on XE18 was "ORA-27369: Job of type EXECUTABLE failed with exit code: Login executable not setuid-root�".

I'll be using this XE21 as a dev database but I do want to have ARCHIVELOG mode enabled and have dbms_scheduler run the daily backup which will be written to an external volume.

So the correct approach is to adapt the dockerfile and create a "dev" flavor of the image, which would then contain all the steps to set this up. I'm just not sure if that is possible.

BTW is there any chance that it will work in docker for desktop on an M1 Mac? I don't have one yet but it seems others were not successful as the installation failed with "ORA-12547: TNS:lost contact".

[gvenzl]

Hi @hal9ccc,

So the correct approach is to adapt the dockerfile and create a "dev" flavor of the image, which would then contain all the steps to set this up. I'm just not sure if that is possible.

Of course, you can always just extend the image altogether and adapt it to your needs.

BTW is there any chance that it will work in docker for desktop on an M1 Mac? I don't have one yet but it seems others were not successful as the installation failed with "ORA-12547: TNS:lost contact".

I doubt it, this seems to be a general limitation with Oracle Database not being available for ARM chips. I personally do not have a Mac with M1 yet, so it's hard for me to debug/test this.

[jesusmorenodiaz]

Fixed compiling our command shell.c with "shared", that is...

1. create C program to execute shell, it will an object "shared library" lib "shell.so"
   ...
   #include
   #include
   #include
   void sh(char *command) {
   int num;
   num = system(command);
   }
   ...

2. install gcc, make
   yum install gcc
   yum install make

3. compile "shell.so"

• check version GCC
  gcc --version
• create shell.so
  cd /opt/oracle
  gcc -G -c shell.c
  (this will a solution)
  ld -shared -o shell.so shell.o
  chmod 775 shell.so

4. set "shell.so" in dir"lib" accesible by oracle
   cp shell.so $ORACLE_HOME/bin
   cp shell.so $ORACLE_HOME/lib

5. create object "library" and procedure
   DROP LIBRARY T24_APX_SHELL;
   CREATE LIBRARY T24_APX_SHELL AS '/opt/oracle/product/21c/dbhomeXE/bin/shell.so';
   CREATE OR REPLACE PROCEDURE T24_APX_COMMAND(command IN char);
   AS EXTERNAL
   NAME "sh"
   LIBRARY T24_APX_shell
   LANGUAGE C
   PARAMETERS (command string);