During the migration of an application from an older AIX onto modern Linux based database, I stumbled across a little issue: The application uses external procedure calls. This is not an issue in itself as extproc is supported and nowadays doesn't even require a listener to run the code anymore. Even security is covered if the extproc agent is run as a different user, which can be enforced in extproc.ora.
But - as the application is quite old - the application responsible team could not provide any linux libraries up until now. All the oracle documenation always shows a local extproc configuration only. either using an agent process spawned directly from the instance, or a listener, addressed by (PROTOCOL=ipc).
Still, there is hope!
In the listener-config, basically it's just a listener which listens to some protocol and provides the extproc binary as a SID_LIST. This could enable me to run the extproc libraries on a dedicated AIX host, and leave the poor RDBMS nodes for RDBMS stuff.
I did not find any such configuration on the internet but was made aware by a colleague about this article:
How to call external Procedure from PL/SQL on remote Host ?
If it was promoted for Oracle 8 some years ago, let's give it a try!
Sitting on my sandbox I only have one linux node with 23c binaries - but I'll just use a different os user to show connectivity is using SQL*Net.
To keep the setting simple (and avoid some additional work) I loosely follow the MOS document 10 Steps to Create and Run a Sample External Procedure Program on Unix (Doc ID 312564.1).
Beside the oracle user, I create a new user extproc_runner (group extproc_runner) and copied the full ORACLE_HOME there.
my first step is to create the proper shared object file. In /home/extproc_runner
I create a very simple shell.c
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
void sh(char *command) {
int num;
num = system(command);
}
gcc -fPIC -c shell.c
ld -shared -o shell.so shell.o
chmod 755 shell.so
LISTENER_EXTPROC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555))
)
)
sid_list_listener_extproc =
(sid_list=
(sid_desc=
(sid_name=extproc_run)
(oracle_home=/home/extproc_runner/product/23c/dbhomeFree)
(program=extproc)
)
)
[extproc_runner@localhost admin]$ lsnrctl start listener_extproc
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 23-DEC-2023 17:57:25
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Starting /home/extproc_runner/product/23c/dbhomeFree/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
System parameter file is /home/extproc_runner/product/23c/dbhomeFree/network/admin/listener.ora
Log messages written to /home/extproc_runner/base/diag/tnslsnr/localhost/listener_extproc/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1555)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1555)))
STATUS of the LISTENER
------------------------
Alias listener_extproc
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
Start Date 23-DEC-2023 17:57:26
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/extproc_runner/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File /home/extproc_runner/base/diag/tnslsnr/localhost/listener_extproc/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1555)))
Services Summary...
Service "extproc_run" has 1 instance(s).
Instance "extproc_run", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
extproc_run_alias =
(description=
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555))
(connect_data=(sid=extproc_run))
)
[oracle@localhost admin]$ tnsping extproc_run_alias
TNS Ping Utility for Linux: Version 23.0.0.0.0 - Developer-Release on 23-DEC-2023 18:05:54
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555)) (connect_data=(sid=extproc_run)))
OK (0 msec)
[oracle@localhost admin]$ sqlplus x/x@extproc_run_alias
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Dec 23 18:05:58 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error
To keep it simple, I use SYSTEM in my PDB, but any schema with proper permissions should do.
CREATE DATABASE LINK extproc_agent_link USING 'extproc_run_alias';
Database link EXTPROC_AGENT_LINK created.
CREATE LIBRARY shell_lib is '/home/extproc_runner/shell.so' agent 'extproc_agent_link';
Library SHELL_LIB compiled
CREATE OR REPLACE PROCEDURE shell(command IN char)
AS EXTERNAL
NAME "sh"
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (command string);
/
Procedure SHELL compiled
set serveroutput on
exec shell('/usr/bin/env >/tmp/bla');
ORA-28595: Extproc agent: Invalid DLL Path
ORA-06512: at "SYSTEM.SHELL", line 1
ORA-06512: at line 1
28595. 00000 - "Extproc agent : Invalid DLL Path"
*Cause: The path of DLL supplied for the extproc execution is invalid.
*Action: Check if the DLL path is set properly using the EXTPROC_DLLS
environment variable.
Some more research & reading of
ORA-28595: Extproc&nsbp;Agent&nsbp;: Invalid DLL Path Error in 12c With Listener&nsbp;Configuration (Doc&nsbp;ID&nsbp;1669896.1)
External Procedure Calls and ORA-28595 In Versions 9.2 through&nsbp;10gR2 (Doc&nsbp;ID&nsbp;198523.1)
and some check of tracefiles in /home/extproc_runner/product/23c/dbhomeFree/hs/log
(at least I kenw the extproc was somehow involved already) led to an extended listener.ora for extproc_runner:
LISTENER_EXTPROC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1555))
)
)
sid_list_listener_extproc =
(sid_list=
(sid_desc=
(sid_name=extproc_run)
(oracle_home=/home/extproc_runner/product/23c/dbhomeFree)
(program=extproc)
(ENVS="EXTPROC_DLLS=ANY,
LD_LIBRARY_PATH=/home/extproc_runner/product/23c/dbhomeFree/lib:/home/extproc_runner")
)
)
With this settings, I can call my external procedure through SQL*Net:
exec shell('/usr/bin/env >/tmp/bla');
PL/SQL procedure successfully completed.
-rw-rw-r--. 1 extproc_runner extproc_runner 4841 Dec 24 20:23 /tmp/bla
ORACLE_SID=extproc_run
ORACLE_BASE=/home/extproc_runner/base
MODULES_RUN_QUARANTINE=LD_LIBRARY_PATH LD_PRELOAD
_=/usr/bin/env
LANG=en_US.UTF-8
HISTCONTROL=ignoredups
EXTPROC_DLLS=ANY
ORACLE_HOME=/home/extproc_runner/product/23c/dbhomeFree
HOSTNAME=localhost.localdomain
JAVA_HOME=/var/opt/jdk-20.0.1
...
USER=extproc_runner
PWD=/home/extproc_runner/product/23c/dbhomeFree/hs/admin
HOME=/home/extproc_runner
LD_LIBRARY_PATH=/home/extproc_runner/product/23c/dbhomeFree/lib:/home/extproc_runner
ORA_NET2_DESC=18,21
LOADEDMODULES=
...
SHELL=/bin/bash
SHLVL=2
MANPATH=:
MODULEPATH=/etc/scl/modulefiles:/usr/share/Modules/modulefiles:/etc/modulefiles:/usr/share/modulefiles
LOGNAME=extproc_runner
MODULEPATH_modshare=/usr/share/Modules/modulefiles:2:/etc/modulefiles:2:/usr/share/modulefiles:2
PATH=/home/extproc_runner/product/23c/dbhomeFree/bin:/home/extproc_runner/.local/bin:/home/extproc_runner/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/var/opt/jdk-20.0.1/bin:/opt/oracle/product/sqlcl/bin
MODULESHOME=/usr/share/Modules
...
This should be sufficient as a proof of concept.
Keine Kommentare:
Kommentar veröffentlichen