Multiple EXTPROC in 11.2 /EXADATA and 12c PDBs

we know that external procedures or C libraries can interface with oracle database  through extproc. it is defined as follows.

CREATE OR REPLACE  LIBRARY libname as '$APP_V1_HOME/appexe.so' within the database and write a plsql wrapper over it.

There are two ways to set the value for the environment variable APP_V1_HOME defined during library creation. They are

•  extproc.ora
•  SID_LIST_LISTENER.

extproc.ora : The value of APP_VI_HOME used to be stored in listener.ora under SID_LIST_LISTENER until oracle version 11gr2.  However in 11gr2, Oracle introduced the concept of a single file where all the environment variables can be stored. The file is in $ORACLE_HOME/hs/admin/extproc.ora . There are no configuration changes required for either listener.ora or tnsnames.ora.

NOTE: In case this is a RAC or exadata, update the extproc.ora on ORACLE_HOME and not GRID home across all the nodes.

Working with the above example, the information in extproc.ora will be

SET APP_V1_HOME=/home/app/9.3

SET EXTPROC_DLL=ANY

if you had a second app or a newer version of the app in a different directory, all we need to do is add that info in extproc.ora as follows

# Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load.

# Without the EXTPROC_DLLS environment variable, extproc loads DLLs from ORACLE_HOME/lib 

SET EXTPROC_DLL=ANY

# APP version V1

SET APP_V1_HOME=/home/app/9.3

#newer app version V2

SET APP_V2_HOME=/home/app/9.4

and define the library in the database as follows

CREATE OR REPLACE  LIBRARY libname as '$APP_V2_HOME/appexe.so' .

Now the above will work within the database and across different databases in the same ORACLE_HOME.

2) SID_LIST_LISTENER

Until 11.2 , the only way to configure extproc was through listener.ora configuration. 

The steps are

1) configure listener.ora

2) add tnsname.ora entry

3) define library with AGENT clause

1) Configure listener.ora.

Create a  separate listener for extproc and define the individual environments in SID_LIST LISTENER.

In case of RAC /Exadata environment, use the ORACLE_HOME to do this. The GRID_HOME is maintained by OraAgent and does some automatic configuration of LOCAL_LISTENER parameters if the file is not touched. Also, i see no additional advantage starting listener for extproc from GRID_HOME.

Below is the sample listing of listener.ora. Note that the enthronement variable APP_HOME points to different directories for different SID_NAME.

The  SID_NAME NEED NOT correspond to the database names. They are just place holders.

LISTENER_DLIB =
(DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
  )
)

SID_LIST_LISTENER_DLIB =
  (SID_LIST =
   (SID_DESC=
      (SID_NAME=DLIB93)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.3,EXTPROC_DLLS=ANY",APP_HOME=/home/app/9.3)

    )
   (SID_DESC=
      (SID_NAME=DLIB94)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.4,EXTPROC_DLLS=ANY,APP_HOME=/home/app/9.4")

    )
  )

2) configure tnsnames.ora

exaproc_93 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB93)
      (PRESENTATION = RO)
    )
  )

exaproc_94 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB94)
      (PRESENTATION = RO)
    )
  )

to test the validity, you could start your listener as

lsnrctl start listener_dlib

and check the connect string.

tnsping exaproc_93 

3) configure the LIBRARY ...AGENT.

CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'extproc_93';

CREATE OR REPLACE  LIBRARY libname as '$APP_HOME/appexe.so'  agent 'DBLIB_AGENT';

observe the AGENT clause in library definition.

Whenever the library is called, oracle looks at the DBLINK in the AGENT clause. If it exists, then the tnsnames.ora file in the DBLINK is lookedup. The tnsnames.ora will in turn point to the SID information in listener.ora. The environment info for that listener corresponding to the SID is pickup dynamically from listener.

  AGENT --> DB LINK --> tnsnames.ora --> listener.ora --> ENVS for that SID.

So , if you need to point your application to different version of shared library without touching the application code, you can simply change the connect string of the DBLINK or change the ENVS info in listener.ora itself.

eg. DROP   <PUBLIC> DATABASE LINK  DBLIB_AGENT ; -- with 'exaproc_64'

    CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'exaproc_94';