Re: Help Needed External Procs ORA-28575

From: TimKArnold <timkarnold_at_aol.com>
Date: 2000/05/24
Message-ID: <20000523224143.07696.00000843@ng-ba1.aol.com>

Bookmark Fixed font Go to End

Doc ID: Note:70638.1
Subject: External Procedures - Troubleshooting ORA-28575 Errors Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 03-JUN-1999
Last Revision Date: 18-APR-2000
Language: USAENG

External Procedures - Troubleshooting ORA-28575 on UNIX Platforms

Introduction

This article provides a brief overview of the Oracle external procedure architecture and provides a number of suggestions for troubleshooting the ORA-28575 error. Although the emphasis of this bulletin is on the UNIX environment, many of the concepts also apply to Windows and other environments. All concepts apply equally to Oracle 8 and 8i.

This article does not address all aspects of external procedures. It provides just enough background information to provide a context for discussing the ORA-28575 error which is the focus of this article.

Oracle's External Procedure Architecture

A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each.

The database:

Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.)
Initiates the call to the procedure via the listener and external procedure agent
Both the library alias and call specification are stored in the database.

The listener:

Listens for external procedure requests from databases and starts external procedure agent processes (one per session)

The external procedure agent:

Accepts information (arguments, shared library location, etc.) from the database, loads the shared library as required, and executes the corresponding C function
Corresponds to the extproc executable which resides in $ORACLE_HOME/bin

The user-provided shared library:

Contains the user-written function that implements the desired functionality

The library alias:

Records the location of the shared library within the database
Used in the PL/SQL call specification

The external procedure call specification:

Provides a description of the procedure, including its PL/SQL name, arguments and types, and the name of the library alias and C function implementing the procedure

A Typical Sequence of Events

The following is a typical sequence of events:

The user invokes the call specification corresponding to an external procedure via PL/SQL or some other programmatic interface.
The database examines the call specification to find the name of the shared library implementing the function, as well as the name of the function and the number and type of the arguments.
The database uses the extproc_connection_data service name in the tnsnames.ora file to contact the external procedure listener.

Note: This step only occurs the first time the external procedure

           is invoked within a given session.  After that, the existing
           external procedure agent can be contacted directly.

4. The listener starts an external procedure agent process to service

the external procedure execution request and returns the address of the new extproc agent to the requesting database.

5. The database sends the external procedure agent the name of the

shared library, the name of the C function to invoke, and after performing the required conversions from PL/SQL to C datatypes, the actual arguments.

6. The external procedure agent loads the shared library, invokes

the appropriate C function, converts any returned data from C to the corresponding PL/SQL types, and sends the data back to the database.

Why does the ORA-28575 error occur?

The ORA-28575 error indicates that a failure occurred in steps 3-4 above. Either the database was unable to establish a connection to the external procedure listener (step 3), OR the external procedure listener was unable to execute the extproc agent (step 4).

Troubleshooting the ORA-28575 error

Reasons for the ORA-28575 include:

o Issues with extproc

o Database configuration issues

o Listener configuration issues

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:

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.
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.
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:
Log in to the system as the applicable user.
cd to the $ORACLE_HOME/bin directory.
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:

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

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:

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:

Maintenance is simplified since the external procedure listener can be started and stopped independent of database connections.
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:

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.
Modify the listener.ora file as follows:
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:

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.

References

For Oracle8:

"PL/SQL User's Guide and Reference, Release 8.0" (A54654-01) "Net8 Administrator's Guide, Release 8.0" (A51576-01)

For Oracle8i:

"Application Developer's Guide - Fundamentals, Release 8.1.5" (A68003-01) "Net8 Administrator's Guide, Release 8.1.5" (A67440-01)

Additional Search Words

unable to open RPC connection to external procedure agent .