Not complete clear as to why we need all of this
Lonnie Cumberland, March 23, 2001 - 9:12 am UTC
I can follow what you are saying, but it raises many more questions such as:
"do we need to modify this each time that we want to add some external proceedures?"
A more detailed description of "WHY this is need and important" would be helpful.
ENVS
A reader, October 21, 2002 - 11:58 am UTC
Hi Tom,
Please tell me where exactly to we need to provide ENVS parameter in the listener.ora. I need to provide LD_LIBRARY_PATH. Should various directories in the LD_LIBRARY_PATH be separated by comma or colon. I am on DYNIX 4.48 environment.
If dynamic linking does not work is it possible to convert dynamic library in static?
Thanks
October 21, 2002 - 1:08 pm UTC
like this in the listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /export/home/ora817)
(ENVS=LD_LIBRARY_PATH=/ora817/ctx/lib:/ora817/lib)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = ora817dev.world)
(ORACLE_HOME = /export/home/ora817)
(SID_NAME = ora817dev)
)
)
you would use the same syntax (colon, comma) as you would for setting this in the ENVIRONMENT itself -- typically using a :
Simple Test
Marcio, July 15, 2003 - 8:42 am UTC
Tom, I have this environment and seen doesn't work. I've been worked on this last all week and got nothing. I've tried many things. Would you see any issue on this?
Box:
C:\Oracle\Ora81\network\ADMIN>sqlplus /
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Jul 15 09:31:12 2003
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
JServer Release 8.1.6.3.0 - Production
ops$t_mp00@MRP816> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
Files:
C:\Oracle\Ora81\network\ADMIN>type sqlnet.ora
NAMES.DEFAULT_DOMAIN = aquarius.cpqd.com.br
SQLNET.AUTHENTICATION_SERVICES= (nts)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)
automatic_ipc = off
C:\Oracle\Ora81\network\ADMIN>type listener.ora
LISTENER =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=extproc_chave))
(ADDRESS=(PROTOCOL=TCP)(Host=cpqd020496)(Port=1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=mrp816)
(ORACLE_HOME= c:\oracle\ora81)
(SID_NAME = mrp816)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = c:\oracle\ora81)
(PROGRAM = extproc)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
C:\Oracle\Ora81\network\ADMIN>type tnsnames.ora
extproc_connection_data.aquarius.cpqd.com.br =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc_chave))
(CONNECT_DATA = (SID = extproc))
)
mrp816.aquarius.cpqd.com.br =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cpqd020496)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = MRP816))
)
MRP9I1.aquarius.cpqd.com.br =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = CPQD020496)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mrp9i1))
)
My test:
C:\Oracle\Ora81\network\ADMIN>sqlplus /@extproc_connection_data
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Jul 15 09:33:56 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
ERROR:
ORA-03113: end-of-file on communication channel
Enter user-name:
Here extproc.exe has generated a error unloged. But I've tried in 8.1.7, 9.2.0 in another box (same OS) -- like my notebook.
I don't know if this way is correct to test as simple as possible, because i have no C compiler here. Is there easy test to know if ipc is up.
Seen to me the extproc is up -- doesn't?
ops$t_mp00@MRP816> host lsnrctl status
LSNRCTL for 32-bit Windows: Version 8.1.6.3.0 - Production on 15-JUL-2003 09:37:03
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=extproc_chave))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 8.1.6.3.0 - Production
Start Date 14-JUL-2003 18:17:08
Uptime 0 days 15 hr. 19 min. 54 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File c:\oracle\ora81\network\admin\listener.ora
Listener Log File c:\Oracle\Ora81\network\log\listener.log
Services Summary...
extproc has 1 service handler(s)
mrp816 has 1 service handler(s)
mrp816 has 1 service handler(s)
mrp9i1 has 2 service handler(s)
mrp9i1 has 2 service handler(s)
The command completed successfully
After all, I would like to know if exist a easy test to ipc.
Thanks,
Marcio
July 15, 2003 - 10:07 am UTC
what are you trying to do? you cannot sqlplus to the extproc service.
it is running, yes the extproc is there. no clue as to why you think you can "sqlplus" to it tho.
SQLPLUS IPC
Marcio, July 15, 2003 - 1:24 pm UTC
Sorry, I didn'd know that. I'd just want to test my ipc (extproc) connection as simple as possible, just it.
July 15, 2003 - 1:36 pm UTC
tnsping it, beyond that, you need an "extproc" to run
Thanks
Marcio, July 15, 2003 - 1:58 pm UTC
One more!
ops$t_mp00@MRP816> host tnsping extproc_connection_data
TNS Ping Utility for 32-bit Windows: Version 8.1.6.3.0 - Production on 15-JUL-2003 14:56:41
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=IPC)(KEY=extproc0))
OK (40 msec)
instance status UNKNOWN
Pushparaj A, May 24, 2004 - 4:35 pm UTC
Tom,
I am able to start the listener but the status is
UNKNOWN and please help me in correcting this problem.
Services Summary...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Please see the listener and the tnsnames files.
listener.ora file
-----------------
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=am001mn)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc.oracledb.abm.com))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/app/oracle/product/9.2)
(PROGRAM=extproc)))
LOG_DIRECTORY_LISTENER = /home/users/raak/demo/new
tnsnames.ora file
-----------------
EXTPROC_CONNECTION_DATA.ORACLEDB.ABM.COM=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc.oracledb.abm.com))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
(CONNECT_DATA=
(SID=plsextproc)))
When I try to execute the PL/SQL program that calls
the C function then I get the following error message.
DECLARE
*
ERROR at line 1:
ORA-28575: unable to open RPC connection to external procedure agent
Thanks
Pushparaj
May 24, 2004 - 6:55 pm UTC
have you followed my setup here step by step? before I review it -- just want to make sure you have.
(things cannot start in column 1 except for the lead lines, are you using the tool to do this, it'll format the files correctly for you)
A reader, May 28, 2004 - 12:29 pm UTC
I hope I have set up the listener.ora, tnsnames.ora and sqlnet.ora files properly. However, I am getting Error - ORA-28575: unable to open RPC connection to external procedure agent code = -28575 frequently.
If I tnsping to extproc, it says successful.
I am not sure what is the problem.
I am pasting all the three .ora files. Can you please help me regarding this.
Thanks,
tnsnames.ora
EXTPROC_CONNECTION_DATA.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = hake)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /ux165/ora_adm8/orasoft/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)
sqlnet.ora
sqlnet.authentication_services=(none)
names.directory_path = (LDAP,TNSNAMES, HOSTNAME)
names.default_domain = world
name.default_zone = world
automatic_ipc = off
Oracle version 8.1.7
May 28, 2004 - 1:25 pm UTC
please give the configuration I used above a try and make sure your oracle_home is set properly (that the path is done up right)
A reader, May 28, 2004 - 2:07 pm UTC
I tried creating a new listner in the lines you have mentioned in the beginning of this thread. Still same problem.
On unix, If I connect to the database without specifying oracle sid (like sqlplus user/password), run the external proc program, I am not getting the ora error. If I connect giving the oracle sid (user/password@orasid) then it bombs.
I am pretty sure Oracle_home is correct.
Do I need to check any libraries or any other thing?
My surprise is if I tnsping extproc it says Ok.
May 28, 2004 - 2:54 pm UTC
Ahh, your environment is going to be discovered to be "botched"
when you connect directly, the dedicated server used YOUR environment to do things like find the tnsnames.ora.
when you connected via the listener, the dedicated server used ITS environment to do the same.
You'll find on that server you have more than one tnsnames.ora file. Look to see if
a) you have TNS_ADMIN (env variable) set or if you have a file ~/.tnsnames.ora.
b) same for the listener
if one of you does and the other does not, you are using totally different configurations.
A reader, May 28, 2004 - 4:43 pm UTC
I checked the tnsnames and listener.ora files and there were no other files with that name in it.
We thought to bounce the database from where we were trying to run the extranal proc. After rebooting the instance, things seems to be working.
I don't know what was the exact problem.
Thanks
May 28, 2004 - 8:02 pm UTC
(your environment was messed up, thats about the only thing that could cause this, do a
find / -name tnsnames.ora
and you'll find you have more than one and your listener was pointing to it and it was the wrong one.
A reader, June 01, 2004 - 11:17 am UTC
Yes I found more than one tnsnames.ora file. But all the extra files are in users defualt home directory (not in $ORACLE_HOME/network/admin folder).
How do we know which tnsnames.ora file listener is listening to?
Thanks a lot.
June 01, 2004 - 2:58 pm UTC
the listener will either use the default ($ORACLE_HOME/network/admin) or the one pointed to by the TNS_ADMIN environment variable.
Ask your dba how they have it setup.
A reader, June 01, 2004 - 3:36 pm UTC
Well in our case, we have tnsnames.ora and listener.ora under $ORACLE_HOME/network/admin. The TNS_ADMIN env variable is also pointing to the same path.
The problem of external proc was resolved after bouncing the database. I don't know how it got resolved.
June 01, 2004 - 4:00 pm UTC
the tns_admin might *not* have been pointing there for the listener that was running.
under solaris, i use
/usr/ucb/ps -auxwwe | grep ....
to see the environment of running processes -- maybe your OS has the same capability. that is useful for diagnosing this.
A reader, June 01, 2004 - 4:18 pm UTC
What should I put in the place of "...." after grep?
I got an error while trying to run your command
$ps -auxwwe | grep tns
ps: cannot find user: "xwwe"
Our Unix environment is
$uname -a
OSF1 V5.1 1885 alpha
June 01, 2004 - 4:58 pm UTC
you'll need to man ps and see what options work on your system.
On solaris or red hat linux:
[tkyte@tkyte-pc-isdn tkyte]$ ps -auxwwe | grep tns
ora9ir2 22313 0.0 0.2 14392 4388 ? S May31 0:00 /home/ora9ir2/bin/tnslsnr LISTENER -inherit
tkyte 24737 0.0 0.0 3680 656 pts/1 S 16:42 0:00 grep tns SSH_AGENT_PID=3852 HOSTNAME=tkyte-pc-isdn SHELL=/bin/bash TERM=xterm HISTSIZE=1000 NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 GTK_RC_FILES=/etc/gtk/gtkrc:/home/tkyte/.gtkrc-1.2-gnome2 WINDOWID=44040262 USER=tkyte LD_LIBRARY_PATH=/home/ora9ir2/lib ORACLE_SID=ora9ir2 SSH_AUTH_SOCK=/tmp/ssh-ILtq3817/agent.3817 SESSION_MANAGER=local/tkyte-pc-isdn:/tmp/.ICE-unix/3817 MAIL=/var/spool/mail/tkyte PATH=/home/tkyte/bin:/home/ora9ir2/bin:/home/tkyte/bin:/home/ora9ir2/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/tkyte/bin:/home/tkyte/binpriv:/sbin:/usr/java/j2sdk1.4.2_04/bin:/home/tkyte/bin:/home/tkyte/bin:/home/tkyte/binpriv:/sbin:/usr/java/j2sdk1.4.2_04/bin INPUTRC=/etc/inputrc PWD=/home/tkyte XMODIFIERS=@im=none LANG=en_US.UTF-8 SQLPATH=/home/tkyte/src/sqlstuff GDMSESSION=Default HOME=/home/tkyte SHLVL=2 GNOME_DESKTOP_SESSION_ID=Default LOGNAME=tkyte ORACLE_PROC_MAKEFILE=/home/ora9ir2/precomp/demo/proc/demo_proc.mk CLASSPATH=.:/home/ora9ir2/jdbc/lib/classes12.zip NO_TIME_PROXY=YES DISPLAY=:0.0 ORACLE_HOME=/home/ora9ir2 COLORTERM=gnome-terminal XAUTHORITY=/tmp/.gdmmNn0vA _=/bin/grep
for example -- on OSF1 -- no idea, you'd have to hunt around and see if the option exists
A reader, June 01, 2004 - 5:19 pm UTC
I got it. I have to say ps auxwwe | grep tns. (No "-" )
The output I got is
ora_adm8 539945 0.0 0.1 16.0M 6.2M ?? S May 28 0:08.84 /ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/bin/tns
lsnr LISTENER_8I -inherit TMPDIR=/ux168/tmp TEMP=/ux168/tmp SHLIB_PATH=/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/lib:/usr/lib
:/usr/dt/lib PATH=/usr/bin:/usr/lib:/etc:/usr/ucblib:/usr/openwin/bin:/usr/dt/lib:/usr/local/bin:/usr/ccs/bin:/sbin:/usr/sbin:/usr/u
sers/ora_adm8/common/scripts:/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/bin:/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/p
recomp/demo/proc:/usr/local/bin ORACLE_BASE=/ux165/ora_adm8/orasoft/app/oracle EDITOR=vi LOGNAME=ora_adm8 TMP=/ux168/tmp ORACLE_SID=
adams_po GWSOURCE=telnet USER=ora_adm8 TNS_ADMIN=/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/network/admin DISPLAY=pgh2267.penn
1.pc.msais.com:0 SHELL=/usr/bin/ksh HOME=/usr/users/ora_adm8 LD_LIBRARY_PATH=/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7/lib:/u
sr/lib:/usr/dt/lib TERM=vt320 ORACLE_HOME=/ux165/ora_adm8/orasoft/app/oracle/product/8.1.7 PWD=/ux165/ora_adm8/orasoft/app/oracle/pr
oduct/8.1.7/network/admin ORA_NET2_DESC=4,7
However, the external proc is working fine now(after restarting the database). Probably I should have done this when I was getting the error.
If TNS_ADMIN path is the real problem, What could case this to happen? Can it happen again?
June 01, 2004 - 5:54 pm UTC
if someone logs in, doesn't set (or sets) the TNS_ADMIN and restarts the listener, the listener will pick up the tnsnames.ora from potentionally the wrong place.
You need to make sure the environment is set properly when you start the database pieces....
external procedure
Alex Smith, November 03, 2004 - 4:46 pm UTC
currently we have external procedure in C on unix box working fine with oracle 8.1.7(32 bit). now we have installed the oracle 9i R2(64bit) on the same box (AIX - 64 bit).
But when I compile the code to make *.so file it says the following message
XCOFF32 object files are not allowed in 64-bit mode.
make: The error code from the last command is 8.
Stop.
I do not understand what is the reason. Can you please help.
Thanks
November 05, 2004 - 11:13 am UTC
so you have an experienced C programmer?
you need to compile in 64bit mode -- it is saying "nope, you don't have 64bit code and are trying to link it with 64bit code -- that'll not work"
Both 32 bit and 64 bit
A reader, March 01, 2005 - 12:22 pm UTC
Hi Tom,
In 9iR2, we can call 32 bit libraries using extproc32, or extproc for 64 bit libraries.
Can we configure listener for 32 bit and 64 bit libraries both? How can this be done, I understand this will have something to do with EXTPROC_EXTERNAL_DATA.
I'll appreciate any help in pointing me to the right direction.
Regards
March 01, 2005 - 12:36 pm UTC
not that I can think of (at least not for the same session)
Session/ Database?
A reader, March 02, 2005 - 8:10 am UTC
Hi Tom,
By session you mean for a Database?
If a database is configured to use 32bit libraries and all external procedures in that database must refer to 32bit libraries. OR is it the session, which I didn't understand? Please can you point me to some more information on this?
Regards
March 02, 2005 - 8:12 am UTC
external procedures are run by another process -- the extproc process. it runs in an address space separate and distinct from your dedicated/shared server. It passes information back and forth via sqlnet (and sqlnet is word size independent).
A single database session will have at most one extproc process.
This single extproc process can be running on that machine or even (in 9i up) on another machine elsewhere in the network.
That single extproc process does not have to be the same as the dedicated server bit wise since it is a totally separate process started by the tns listener (fork/exec for example on unixes)
A reader, March 02, 2005 - 8:36 am UTC
Hi Tom,
So we can have two different Database Sessions with one using 32 bit extproc and another using 64 bit extproc.
For this how do we configure the listerner, please?
Regards
March 02, 2005 - 8:39 am UTC
it would be very shaky -- it would rely on a "trick", the extproc_connection_data would have two different domains -- two different listener entries and two different tnsnames entries. I cannot suggest it for real, it would be very hard to support (if even supported, i have not tried it, it would only work in theory).
there should be one extproc service registered for the database only.
Thanks
A reader, March 02, 2005 - 12:14 pm UTC
Hi Tom,
Looks like I am begining to understand this, two more questions, please.
1. If a machine has two different databases, then can one database listen to 32 bit libraries and the another 64 bit?
2. With 64 bit RDBMS, what specific advantages will there be to change all the other interface softwares also to 64 bit?
Thanks very much for your time and efforts.
Regards
March 02, 2005 - 12:35 pm UTC
1) sure
2) the clients? none really, they should use whatever word size is best for them. sqlnet is word size agnostic in that respect.
Extproc Issue............
A reader, November 21, 2005 - 2:39 am UTC
Hi Tom,
Please find the error messages and the explanations as follows:
LISTENER.ORA:
*************
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2481))
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ExtProc)
(ORACLE_HOME = C:\oracle\ora81)
(PROGRAM = EXTPROC)
)
(SID_DESC =
(GLOBAL_DBNAME = db)
(ORACLE_HOME = C:\oracle\ora81)
(SID_NAME = db)
)
(SID_DESC =
(GLOBAL_DBNAME = oradb)
(ORACLE_HOME = C:\oracle\ora81)
(SID_NAME = oradb)
)
)
TNSNAMES.ORA:
*************
EXTPROC_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA = (SID = ExtProc))
)
SQLPLUS:
********
SQL=> CONN SCOTT/TIGER
SQL=> CREATE LIBRARY TESTING2 AS 'C:\TESTING2\DEBUG\TESTING2.DLL';
SQL=> CREATE OR REPLACE FUNCTION Plscallsmultiply_c (
x BINARY_INTEGER,
y BINARY_INTEGER)
RETURN BINARY_INTEGER
AS LANGUAGE C
LIBRARY testing2
NAME "multiply_c";
SQL=> VARIABLE V NUMBER
SQL=> EXEC :V:= Plscallsmultiply_c(1,2)
BEGIN :V:= Plscallsmultiply_c(1,2); END;
*
ERROR at line 1:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SCOTT.PLSCALLSMULTIPLY_C", line 0
ORA-06512: at line 1
I have done exactly stated by you, yet getting the error.
The function calls a .dll which has the multiply_c C++ program.
Oracle 8i Rel 3 on Win2k.
Also how to configure the listener for multiple external procedures.
Please help to resolve this issue.
Thanks as always
November 21, 2005 - 8:40 am UTC
did you read through the original answer? what is in your sqlnet.ora?
Recover the SYS Schema
Krishnan Kutty, January 05, 2006 - 9:04 am UTC
Hi Tom,
I made a mistake by deleting objects in sys Schema by querying from user_objects. Please tell me the way to recover this. Plsssss...
January 05, 2006 - 10:56 am UTC
first option: contact support
second option: restore your backup, point in time recover the database to the point in time right before you did this "thing"
that is my first answer, you don't give sufficient information to say anything further.
How to regenrate extproc
Ranjan, May 05, 2006 - 4:50 pm UTC
In out instance listener process are working for extproc as well as for database instance. But whenver extproc invoke it throws ora-28575. I try to execute the executables
$ORACLE_HOME/bin/extptoc and it returns nothing. Based on a metalink note , it should return me a banner. Does that mean extproc is corrupted , if yes how can we regenrate the extproc .
May 05, 2006 - 5:02 pm UTC
please use support - which I know you have - for this one.
if it were corrupt, it would "fail outright" I would think.