Re: Help! ORA-28575 calling external proc

Thanks for the suggestion. We have already gone through this Oracle document several times and have not been able to resolved the problem.

Michael

Yass Khogaly wrote:

> Issues with extproc
> -------------------
>
> You can receive this error if the extproc executable either does
> not exist, OR the listener does not have permissions to execute it,
> OR you cannot execute extproc for any other reason.
>
> Check the following:
>
> 1. Verify that the extproc executable specified in listener.ora
> exists in the bin directory of the specified $ORACLE_HOME.
> See the section on listener configuration below for more information.
>
> 2. Verify that extproc can be executed by the listener. On many
> systems, the listener executes with the effective user id
> of the oracle user, but this can be changed. Verify that the
> permissions are available for whatever user is applicable.
>
> 3. Other problems such as file corruption could also cause the
> extproc program to not be executable. Perform the following
> steps to verify whether the extproc program is valid and executable:
>
> a. Log in to the system as the applicable user.
> b. cd to the $ORACLE_HOME/bin directory.
> c. Execute the following command at the prompt:
>
> $ ./extproc
>
> If all is well, you should see a banner displayed similar to
> the following (the text may differ slightly):
>
> Oracle Corporation --- Wed Jul 19 11:00:04.139352
>
> Heterogeneous Agent based on the following module(s):
> - External Procedure Module
>
> If you do not see such a banner or you receive an error, you
> may need to reinstall and/or rebuild the extproc executable
> or change the permissions to make it executable.
>
> Database Configuration Issues
> -----------------------------
>
> This article does not address database configuration issues other
> than those which result in the ORA-28575 error.
>
> Database configuration issues resulting in an ORA-28575 include:
>
> 1. The database is referencing the wrong copy of tnsnames.ora
>
> In some environments, multiple copies of the tnsnames.ora file
> may exist in the file system. The copy used by the database is
> that which is pointed to by TNS_ADMIN when the database is started.
>
> To ensure that your changes to the tnsnames.ora file are picked
> up by the database, you may need to shut down the database, set
> the TNS_ADMIN environment variable to indicate the directory with
> the appropriate file, and restart the database.
>
> 2. The tnsnames.ora file is improperly configured
>
> The tnsnames.ora file must be configured to contain a special
> service name called extproc_connection_data. This service name
> provides the database with the necessary parameters to connect
> to the external procedure listener. The entry looks like:
>
> extproc_connection_data =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
> (CONNECT_DATA = (SID = extproc_agent))
> )
>
> a. extproc_connection_data cannot be changed except as follows:
>
> If, and only if, the sqlnet.ora file contains the following:
>
> names.default_domain = some.domain (e.g. us.oracle.com)
>
> then you must modify the above tnsnames.ora entry as follows:
>
> extproc_connection_data.some.domain =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
> (CONNECT_DATA = (SID = extproc_agent))
> )
>
> Setting default_domain causes some.domain to be automatically
> appended to all service names that are not already qualified
> with a domain. This is also true for extproc_connection_data.
>
> If default_domain is set, and that domain is not appended to
> the service name in tnsnames.ora, the connection fails.
>
> Note: As was the case for tnsnames.ora, the database uses the
> sqlnet.ora file that was pointed to by TNS_ADMIN at the
> time the database was last started. Make sure you are
> looking at the right copy of the file.
>
> b. extproc_key must be the same as the KEY=extproc_key entry in
> the listener.ora file (see below). Otherwise, it can be any
> string that is not already in use. This uniquely identifies
> which external procedure listener is to be contacted.
>
> c. extproc_agent must be the same as the SID_NAME=extproc_agent
> entry in the listener.ora file (see below). Otherwise, it can
> be any SID that is not already in use. This is the service
> that appears in the list displayed by 'lsnrctl status'.
>
> d. PROTOCOL must be set to IPC. Other protocols (e.g. TCP) are
> currently not supported for external procedures.
>
> 3. The names.directory_path parameter in the sqlnet.ora file does
> not include the tnsnames.ora file as a source of directory info.
>
> If the sqlnet.ora file pointed to the the TNS_ADMIN environment
> variable contains an entry of the following form:
>
> names.directory_path = (tnsnames, onames)
>
> then Net8 looks in both the tnsnames.ora file and Oracle Names
> to resolve service names (including extproc_connection_data). If
> the 'tnsnames' entry is omitted as follows:
>
> names.directory_path = (onames)
>
> then only Oracle Names is searched and the modifications to
> the tnsnames.ora file are not picked up by the database.
>
> Listener Configuration Issues
> -----------------------------
>
> This bulletin does not address listener configuration issues other
> than those which result in the ORA-28575 error.
>
> Listener configuration issues resulting in an ORA-28575 include:
>
> 1. The listener was started with the wrong copy of listener.ora
>
> The external procedure listener reads the listener.ora pointed to
> by TNS_ADMIN when it is started. If the listener is started prior
> to some modification to the listener.ora file, you must shut down
> the listener, set the TNS_ADMIN environment variable to point to
> the modified copy of listener.ora, and restart the listener.
>
> For example:
>
> $ lsnrctl stop callout_listener
> $ export TNS_ADMIN=/u02/etc # assumes Korn shell
> $ lsnrctl start callout_listener
>
> The name callout_listener is not fixed, but is used in this example
> for consistency with the examples that follow.
>
> 2. The listener.ora file is improperly configured
>
> When configuring the listener for external procedures, the following
> two options exist:
>
> o Configure the existing database listener to listen for external
> procedures and database connections
>
> or
>
> o Configure a separate listener specifically for external procedures.
>
> There are several benefits to the second option including:
>
> a. Maintenance is simplified since the external procedure listener
> can be started and stopped independent of database connections.
>
> b. Security is enhanced since a separate listener can be run with
> a different effective user id. Running external procedures as
> the 'oracle' (or equivalent) user can expose the database to
> unnecessary risk since user-written procedures can then have
> unrestricted access to database resources (e.g. datafiles).
>
> For these reasons, we recommend the second method. It is the only
> method addressed in this article.
>
> To properly configure a separate listener for external procedures:
>
> a. Decide on a name for the new listener (callout_listener in the
> examples that follow). This name appears in several places in
> the listener.ora file. Substitute the name you selected for
> callout_listener in the examples that follow.
>
> b. Modify the listener.ora file as follows:
>
> i. Add the following entries:
>
> callout_listener =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = IPC)
> (KEY = extproc_key)
> )
> )
>
> sid_list_callout_listener =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = extproc_agent)
> (ORACLE_HOME = oraclehomedir)
> (PROGRAM = extproc)
> )
> )
>
> where callout_listener is the name of the listener
> extproc_key agrees with the KEY in tnsnames.ora
> extproc_agent agrees with the SID in tnsnames.ora
>
> ii. Remove any entries related to external procedures from
> the default listener that is being used to listen for database
> connections if such entries exist. This is only an issue if
> you have previously attempted to configure a single listener
> for both purposes.
>
> iii. Restart the database listener to make sure it reflects the
> most recent listener.ora changes. Restarting the original
> listener is especially important if it was previously set
> to listen for external procedures.
>
> % lsnrctl stop listener
> % setenv TNS_ADMIN /u02/etc # assumes C shell
> % lsnrctl start listener
>
> Observe and correct any errors that may have resulted from
> modifying the listener.ora file (i.e. typographical errors).
>
> iv. Start the new external procedure listener.
>
> % lsnrctl start callout_listener
>
> Observe and correct any errors that may have resulted from
> modifying the listener.ora file (i.e. typographical errors).
>
> Testing Your Configuration
> --------------------------
>
> The database ships with a sample program which can be used to test your
> external procedure configuration. You can find the following files in
> the $ORACLE_HOME/plsql/demo directory:
>
> extproc.c # the C file implementing the external procedures
>
> extproc.sql # an SQL script for creating and invoking the required
> # packages and procedure/function call specifications.
>
> demo_plsql.mk # a makefile for compiling the extproc demo
>
> The steps for running the demo are:
>
> 1. Compile the extproc.c and create the extproc.so shared library
>
> $ make -f demo_plsql.mk extproc.so
>
> 2. Ensure user scott and requisite tables (e.g. EMP and DEPT) exist.
> Also, ensure that the DBMS_OUTPUT package exists and is valid.
>
> 3. Follow the steps outlined in the comments section of extproc.sql.
> A couple of steps must be completed before you run the extproc.sql
> script, including creating the LIBRARY and granting any privileges
> required.
>
> When creating the LIBRARY, make sure that you specify the correct
> location for your shared library. The example assumes that you put
> it in /tmp, however, this may not be the case.
>
> 4. From SQL*Plus, execute the following to create the PL/SQL package
> and call specifications and execute the demo:
>
> SQL> @extproc.sql
>
> When executed, the demo invokes several examples, each of which produces
> output to the screen via the DBMS_OUTPUT package. You should not see any
> errors. If you receive the ORA-28575, retrace the steps in this article to
> correct the problem. For other errors, refer to the documentation.
>
> "The Views expressed here are my own and not necessarily those of Oracle
> Corporation"
>
> Michael Wilson <mwilson_at_raytheon.com> wrote in message
> news:3835784A.F2C71F1E_at_raytheon.com...
> > We are trying to configure a separate listener to handle the external
> > procedure processing for our system, but we are getting ORA-28575. We have
> > gone through the troubleshooting document several times. Any help is
> greatly
> > appreciated! Here's the listener.ora and tnsnames.ora files:
> >
> > ###########
> > # FILENAME: listener.ora
> > # NETWORK :
> > # SERVICE :
> > # AUTHOR : Martin Yen
> > # DATE : 09/23/99 Init. added plfd01
> > ###########
> > LISTENER =
> > (ADDRESS_LIST =
> > (ADDRESS= (PROTOCOL= IPC)(KEY= plfd01))
> > (ADDRESS= (PROTOCOL= TCP)(Host= explfd)(Port= 1521))
> > )
> > SID_LIST_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (GLOBAL_DBNAME= plfd01.rsc.raytheon.com)
> > (ORACLE_HOME= /apps/oracle/product/8.0.6)
> > (SID_NAME = plfd01)
> > )
> > )
> > STARTUP_WAIT_TIME_LISTENER = 0
> > CONNECT_TIMEOUT_LISTENER = 10
> > TRACE_LEVEL_LISTENER = user
> > #TRACE_LEVEL_LISTENER = admin
> > #TRACE_LEVEL_LISTENER = off
> > TRACE_FILE_LISTENER = listener.trc
> > TRACE_DIRECTORY_LISTENER = /db2/oradata/logs/network
> > LOG_FILE_LISTENER = listener.log
> > LOG_DIRECTORY_LISTENER = /db2/oradata/logs/network
> > ##################################################################
> > ###########
> > # FILENAME: listener.ora
> > # NETWORK :
> > # SERVICE :
> > ###########
> > CALLOUT_LISTENER =
> > (ADDRESS_LIST =
> > (ADDRESS=
> > (PROTOCOL= IPC)
> > (KEY= extproc_key)
> > )
> > )
> > SID_LIST_CALLOUT_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (SID_NAME = extproc_agent)
> > (ORACLE_HOME= /apps/oracle/product/8.0.6)
> > (PROGRAM = extproc)
> > )
> > )
> > STARTUP_WAIT_TIME_CALLOUT_LISTENER = 0
> > CONNECT_TIMEOUT_CALLOUT_LISTENER = 10
> > TRACE_LEVEL_CALLOUT_LISTENER = user
> > #TRACE_LEVEL_CALLOUT_LISTENER = admin
> > #TRACE_LEVEL_CALLOUT_LISTENER = off
> > TRACE_FILE_CALLOUT_LISTENER = callout_listener.trc
> > TRACE_DIRECTORY_CALLOUT_LISTENER = /db2/oradata/logs/network
> > LOG_FILE_CALLOUT_LISTENER = callout_listener.log
> > LOG_DIRECTORY_CALLOUT_LISTENER = /db2/oradata/logs/network
> >
> >
> > TNSNAMES.ORA:
> >
> >
> > #
> > # Installation Generated Net8 Configuration
> > # Version Date: Sep-23-99 Initial Installation
> > # Filename: Tnsnames.ora
> > #
> > # ######################################################################
> > # 11/18/99 Added a new service name
> > # extproc_connection_data for extproc
> > # ######################################################################
> > #
> > plfd01 =
> > (DESCRIPTION =
> > (ADDRESS = (PROTOCOL= TCP)(Host= explfd.rsc.raytheon.com)(Port= 1521))
> > (CONNECT_DATA = (SID = plfd01))
> > )
> > extproc_connection_data =
> > (DESCRIPTION =
> > (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
> > (CONNECT_DATA = (SID = extproc_agent)
> > )
> >
> >
> >
> > --
> > ===== R a y t h e o n S y s t e m s C o m p a n y =====
> > Michael Wilson
> > Enterprise Applications
> > 972-344-3094
> > 972-344-2627 (fax)
> > 888-740-5671 (pager) or 8887405671_at_skytel.com
> >
> >

--
===== R a y t h e o n S y s t e m s C o m p a n y =====   Michael Wilson
  Enterprise Applications

  972-344-3094
  972-344-2627 (fax)
  888-740-5671 (pager) or 8887405671_at_skytel.com