Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help! ORA-28575 calling external proc

Re: Help! ORA-28575 calling external proc

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Sun, 21 Nov 1999 06:32:19 -0800
Message-ID: <818s5p$js5$1@inet16.us.oracle.com> 





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:

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


        )





    
  
  1.   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:



    
  
  1.   Maintenance is simplified since the external procedure listener
        can be started and stopped independent of database connections.

  
  2.   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:



    
  
  1.   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.

  
  2.   Modify the listener.ora file as follows:

  
  3.   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
>
>




Received on Sun Nov 21 1999 - 08:32:19 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US