Extproc oracle что это
Oracle Database lets you work in different languages:
C, through the Oracle Call Interface (OCI), as described in the Oracle Call Interface Programmer's Guide
C++, through the Oracle C++ Call Interface (OCCI), as described in the Oracle C++ Call Interface Programmer's Guide
C or C++, through the Pro*C/C++ precompiler, as described in the Pro*C/C++ Programmer's Guide
COBOL, through the Pro*COBOL precompiler, as described in the Pro*COBOL Programmer's Guide
Visual Basic, through Oracle Provider for OLE DB, as described in Oracle Provider for OLE DB Developer's Guide for Microsoft Windows .
Java, through the JDBC and SQLJ client-side application programming interfaces (APIs). See Oracle Database JDBC Developer’s Guide and Oracle Database SQLJ Developer’s Guide .
Java in the database, as described in Oracle Database Java Developer’s Guide . This includes the use of Java stored procedures (Java methods published to SQL and stored in the database), as described in a chapter in Oracle Database Java Developer’s Guide .
The JPublisher utility is also available for generating Java classes to represent database entities, such as SQL objects and PL/SQL packages, in a Java client program; publishing from SQL, PL/SQL, and server-side Java to web services; and enabling the invocation of external web services from inside the database. See Oracle Database JDBC Developer’s Guide .
How can you choose between these different implementation possibilities? Each of these languages offers different advantages: ease of use, the availability of programmers with specific expertise, the need for portability, and the existence of legacy code are powerful determinants.
The choice might narrow depending on how your application must work with Oracle Database:
PL/SQL is a powerful development tool, specialized for SQL transaction processing.
Some computation-intensive tasks are executed most efficiently in a lower level language, such as C.
For both portability and security, you might select Java.
Taking all these factors into account suggests that there might be situations in which you might need to implement your application in multiple languages. For example, because Java runs within the address space of the server, you might want to import existing Java applications into the database, and then leverage this technology by calling Java functions from PL/SQL and SQL.
PL/SQL external procedures enable you to write C procedure calls as PL/SQL bodies. These C procedures are callable directly from PL/SQL, and from SQL through PL/SQL procedure calls. The database provides a special-purpose interface, the call specification, that lets you call external procedures from other languages. While this service is designed for intercommunication between SQL, PL/SQL, C, and Java, it is accessible from any base language that can call these languages. For example, your procedure can be written in a language other than Java or C, and if C can call your procedure, then SQL or PL/SQL can use it. Therefore, if you have a candidate C++ procedure, use a C++ extern "C" statement in that procedure to make it callable by C.
Therefore, the strengths and capabilities of different languages are available to you, regardless of your programmatic environment. You are not restricted to one language with its inherent limitations. External procedures promote reusability and modularity because you can deploy specific languages for specific purposes.
21.2 What Is an External Procedure?
An external procedure is a procedure stored in a dynamic link library (DLL). You register the procedure with the base language, and then call it to perform special-purpose processing.
For example, when you work in PL/SQL, the language loads the library dynamically at runtime, and then calls the procedure as if it were a PL/SQL procedure. These procedures participate fully in the current transaction and can call back to the database to perform SQL operations.
The procedures are loaded only when necessary, so memory is conserved. The decoupling of the call specification from its implementation body means that the procedures can be enhanced without affecting the calling programs.
External procedures let you:
Isolate execution of client applications and processes from the database instance to ensure that problems on the client side do not adversely affect the database
Move computation-bound programs from client to server where they run faster (because they avoid the round trips of network communication)
Interface the database server with external systems and data sources
Extend the functionality of the database server itself
The external library (DLL file) must be statically linked. In other words, it must not reference external symbols from other external libraries (DLL files). Oracle Database does not resolve such symbols, so they can cause your external procedure to fail.
Oracle Database Security Guide for information about securing external procedures
21.3 Overview of Call Specification for External Procedures
You publish external procedures through call specifications , which provide a superset of the AS EXTERNAL function through the AS LANGUAGE clause. AS LANGUAGE call specifications allow the publishing of external C procedures. (Java class methods are not external procedures, but they still use call specifications.)
To support legacy applications, call specifications also enable you to publish with the AS EXTERNAL clause. For application development, however, using the AS LANGUAGE clause is recommended.
In general, call specifications enable:
Dispatching the appropriate C or Java target procedure
Data type conversions
Parameter mode mappings
Automatic memory allocation and cleanup
Purity constraints to be specified, where necessary, for package functions called from SQL.
Calling Java methods or C procedures from database triggers
Location flexibility: you can put AS LANGUAGE call specifications in package or type specifications, or package (or type) bodies to optimize performance and hide implementation details
To use an existing program as an external procedure, load, publish, and then call it.
21.4 Loading External Procedures
To make your external C procedures or Java methods available to PL/SQL, you must first load them.
You can load external C procedures only on platforms that support either DLLs or dynamically loadable shared libraries (such as Solaris . so libraries).
When an application calls an external C procedure, Oracle Database or Oracle Listener starts the external procedure agent, extproc . Using the network connection established by Oracle Database or Oracle Listener, the application passes this information to extproc :
Name of DLL or shared library
Name of external procedure
Any parameters for the external procedure
Then extproc loads the DLL or the shared library, runs the external procedure, and passes any values that the external procedure returns back to the application. The application and extproc must reside on the same computer.
extproc can call procedures in any library that complies with the calling standard used.
The default configuration for external procedures no longer requires a network listener to work with Oracle Database and extproc . Oracle Database now spawns extproc directly, eliminating the risk that Oracle Listener might spawn extproc unexpectedly. This default configuration is recommended for maximum security.
You must change this default configuration, so that Oracle Listener spawns extproc , if you use any of these:
A multithreaded extproc agent
Oracle Database in shared mode on Windows
An AGENT clause in the LIBRARY specification or an AGENT IN clause in the PROCEDURE specification that redirects external procedures to a different extproc agent
CALLING STANDARD for more information about the calling standard
Changing the default configuration requires additional network configuration steps.
To configure your database to use external procedures that are written in C, or that can be called from C applications, you or your database administrator must follow these steps:
21.4.1 Define the C Procedures
Define the C procedures using one of these prototypes:
ISO/ANSI prototypes other than numeric data types that are less than full width (such as float , short , char ); for example:
Other data types that do not change size under default argument promotions.
This example changes size under default argument promotions:
21.4.2 Set Up the Environment
When you use the default configuration for external procedures, Oracle Database spawns extproc directly. You need not make configuration changes for listener . ora and tnsnames . ora . Define the environment variables to be used by external procedures in the file extproc . ora (located at $ORACLE_HOME/hs/admin on UNIX operating systems and at ORACLE_HOME\hs\admin on Windows), using this syntax:
Set the EXTPROC_DLLS environment variable, which restricts the DLLs that extproc can load, to one of these values:
NULL ; for example:
This setting, the default, allows extproc to load only the DLLs that are in directory $ORACLE_HOME / bin or $ORACLE_HOME / lib .
ONLY: followed by a colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:
This setting allows extproc to load only the DLLs named DLL1 and DLL2. This setting provides maximum security.
A colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:
This setting allows extproc to load the DLLs named DLL1 and DLL2 and the DLLs that are in directory $ORACLE_HOME / bin or $ORACLE_HOME / lib .
ANY ; for example:
This setting allows extproc to load any DLL.
Set the ENFORCE_CREDENTIAL environment variable, which enforces the usage of credentials when spawning an extproc process. The ENFORCE_CREDENTIAL value can be TRUE or FALSE (the default). For a discussion of ENFORCE_CREDENTIAL and the expected behaviors of an extproc process based on possible authentication and impersonation scenarios, see the information about securing external procedures in Oracle Database Security Guide .
To change the default configuration for external procedures and have your extproc agent spawned by Oracle Listener, configure your database to use external procedures that are written in C, or can be called from C applications, as follows.
To use credentials for extproc , you cannot use Oracle Listener to spawn the extproc agent.
- Set configuration parameters for the agent, named extproc by default, in the configuration files tnsnames . ora and listener . ora . This establishes the connection for the external procedure agent, extproc , when the database is started.
- Start a listener process exclusively for external procedures.
The Listener sets a few required environment variables (such as ORACLE_HOME , ORACLE_SID , and LD_LIBRARY_PATH ) for extproc . It can also define specific environment variables in the ENVS section of its listener . ora entry, and these variables are passed to the agent process. Otherwise, it provides the agent with a "clean" environment. The environment variables set for the agent are independent of those set for the client and server. Therefore, external procedures, which run in the agent process, cannot read environment variables set for the client or server processes.
It is possible for you to set and read environment variables themselves by using the standard C procedures setenv and getenv , respectively. Environment variables, set this way, are specific to the agent process, which means that they can be read by all functions executed in that process, but not by any other process running on the same host.
In dedicated mode, one "dedicated" agent is launched for each session. In multithreaded mode, a single multithreaded extproc agent is launched. The multithreaded extproc agent handles calls using different threads for different users. In a configuration where many users can call the external procedures, using a multithreaded extproc agent is recommended to conserve system resources.
If the agent is to run in dedicated mode, additional configuration of the agent process is not necessary.
If the agent is to run in multithreaded mode, your database administrator must configure the database system to start the agent in multithreaded mode (as a multithreaded extproc agent). To do this configuration, use the agent control utility, agtctl . For example, start extproc using this command:
where agent_sid is the system identifier that this extproc agent services. An entry for this system identifier is typically added as an entry in the file tnsnames . ora .
If you use a multithreaded extproc agent, the library you call must be thread-safe—to avoid errors such as a damaged call stack.
The database server, the agent process, and the listener process that spawns the agent process must all reside on the same host.
By default, the agent process runs on the same database instance as your main application. In situations where reliability is critical, you might want to run the agent process for the external procedure on a separate database instance (still on the same host), so that any problems in the agent do not affect the primary database server. To do so, specify the separate database instance using a database link.
Figure F-1 in Oracle Call Interface Programmer's Guide illustrates the architecture of the multithreaded extproc agent.
Oracle Call Interface Programmer's Guide for more information about using agtctl for extproc administration
21.4.3 Identify the DLL
In this context, a DLL is any dynamically loadable operating-system file that stores external procedures.
For security reasons, your DBA controls access to the DLL. Using the CREATE LIBRARY statement, the DBA creates a schema object called an alias library, which represents the DLL. Then, if you are an authorized user, the DBA grants you EXECUTE privileges on the alias library. Alternatively, the DBA might grant you CREATE ANY LIBRARY privileges, in which case you can create your own alias libraries using this syntax:
The ANY privileges are very powerful and must not be granted lightly. For more information, see:
Oracle Database Security Guide for information about managing system privileges, including ANY
Oracle Database Security Guide for guidelines for securing user accounts and privileges
Oracle recommends that you specify the path to the DLL using a directory object, rather than only the DLL name. In this example, you create alias library c_utils , which represents DLL utils . so :
where DLL_DIRECTORY is a directory object that refers to '/DLLs' .
As an alternative, you can specify the full path to the DLL, as in this example:
To allow flexibility in specifying the DLLs, you can specify the root part of the path as an environment variable using the notation $ < VAR_NAME >, and set up that variable in the ENVS section of the listener . ora entry.
In this example, the agent specified by the name agent_link is used to run any external procedure in the library C_Utils :
The environment variable EP_LIB_HOME is expanded by the agent to the appropriate path for that instance, such as /usr/bin/dll . Variable EP_LIB_HOME must be set in the file listener . ora , for the agent to be able to access it.
For security reasons, extproc , by default, loads only DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib . Also, only local sessions—that is, Oracle Database client processes that run on the same system—are allowed to connect to extproc .
To load DLLs from other directories, set the environment variable EXTPROC_DLLS . The value for this environment variable is a colon-separated (semicolon-separated on Windows systems) list of DLL names qualified with the complete path. For example:
While you can set up environment variables for extproc through the ENVS parameter in the file listener . ora , you can also set up environment variables in the extproc initialization file extproc . ora in directory $ORACLE_HOME/hs/admin . When both extproc . ora and ENVS parameter in listener . ora are used, the environment variables defined in extproc . ora take precedence. See the Oracle Net manual for more information about the EXTPROC feature.
In extproc.ora on a Windows system, specify the path using a drive letter and using a double backslash ( \\ ) for each backslash in the path. (The first backslash in each double backslash serves as an escape character.)
21.4.4 Publish the External Procedures
You find or write an external C procedure, and add it to the DLL. When the procedure is in the DLL, you publish it using the call specification mechanism described in Publishing External Procedures.
21.5 Publishing External Procedures
Oracle Database can use only external procedures that are published through a call specification, which maps names, parameter types, and return types for your Java class method or C external procedure to their SQL counterparts. It is written like any other PL/SQL stored procedure except that, in its body, instead of declarations and a BEGIN END block, you code the AS LANGUAGE clause.
The AS LANGUAGE clause specifies:
Which language the procedure is written in
For a Java method:
The signature of the Java method
The alias library corresponding to the DLL for a C procedure
The name of the C procedure in a DLL
Various options for specifying how parameters are passed
Which parameter (if any) holds the name of the external procedure agent, extproc , for running the procedure on a different system
You begin the declaration using the normal CREATE OR REPLACE syntax for a procedure, function, package specification, package body, type specification, or type body.
The call specification follows the name and parameter declarations. Its syntax is:
This is then followed by either:
Where java_string_literal_name is the signature of your Java method
Where library_name is the name of your alias library, c_string_literal_name is the name of your external C procedure, and external_parameter stands for:
property stands for:
Unlike Java, C does not understand SQL types; therefore, the syntax is more intricate
This chapter describes how to create an environment on BS2000, where external C procedure calls can operate. External JAVA methods are not supported on BS2000.
This chapter complements the chapter about External Procedures in the Oracle Database Development Guide .
The default configuration for external procedures does not require a network listener to work with Oracle Database and the extproc agent. The extproc agent is spawned directly by Oracle Database and eliminates the risks that the extproc agent might be spawned by Oracle Listener unexpectedly. This default configuration is recommended for maximum security.
You can change the default configuration for external procedures and have the extproc agent spawned by Oracle Listener. To do this, you must perform additional network configuration steps.
Having the extproc agent spawned by Oracle Listener is necessary if you use:
The AGENT clause of the LIBRARY specification or the AGENT IN clause of the PROCEDURE specification such that you can redirect external procedures to a different extproc agent.
14.1 Loading External Procedures
This section complements the corresponding part in Oracle Database Development Guide . It shows how to use an external C procedure stored in a dynamic load library (DLL) with Oracle on BS2000. In this context a DLL is specified as a LMS library in the BS2000 environment or a shared object in the POSIX environment.
Perform the following steps to load external procedures:
14.1.1 Define C Procedures
Define the C procedures using one of the prototypes.
Refer to Oracle Database Development Guide for the prototypes. Compile the C procedures either in the BS2000 environment or in the POSIX program environment.
BS2000 Program Environment
Compile the program using the BS2000 C/C++ compiler. The created LLM object must be stored in a LMS library. You must consider the default settings LOWER-CASE-NAMES=*NO and SPECIAL-CHARACTERS=*CONVERT-TO-DOLLAR of the C/C++ compiler option MODULE-PROPERTIES . These default settings cause the conversion of all lowercase letters in the entry names to uppercase and of all underscores (_) in the entry names to dollar signs ( $ ).
POSIX Program Environment
Use the POSIX installation of the BS2000 C/C++ compiler to compile the program in the POSIX shell. You must consider that on default lower case letters in entry names will be translated to upper case letters and underscores in entry names will be translated to dollar signs. Use the option –K llm_case_lower,llm_keep to retain lower case letters and underscore characters when entry names are generated.
Use the tool genso to generate a shared object. The following example illustrates how to create the shared object C_utils.so for the C procedure, char *concat(ctx, str1, str1_i, str2, str2_i, ret_i, ret_l) in the source file C_concat.c :
Refer to Oracle Database Development Guide for the C code example.
14.1.2 Set Up the Environment
When you use the default configuration for external procedures, Oracle Database spawns extproc directly. You need not make configuration changes for listener.ora and tnsnames.ora . Define the environment variables to be used by external procedures in the file extproc.ora located at $ORACLE_HOME/hs/admin using this syntax:
If the load library is a BS2000 LMS library you must set the variable EXTPROC_DLLS to ANY , For example:
If the load library is a POSIX shared object you can set the variable EXTPROC_DLLS as described in Oracle Database Development Guide . If you have not specified the full qualified file path for the shared object then you must set the variable LD_LIBRARY_PATH . Following is an example of an extproc.ora file for the shared object file C_utils.so , which resides in the /home/oracle/lib directory:
To change the default configuration for external procedures and have your extproc agent spawned by Oracle Listener, configure your listener.ora and tnsnames.ora as follows. The listener.ora file must have the following entries:
Assign the environment variables to be used by external procedures to the parameter ENVS . When extproc.ora is in use, it precedes the same environment variables of ENVS in listener.ora file.
Oracle Database lets you work in different languages:
C, through the Oracle Call Interface (OCI), as described in the Oracle Call Interface Programmer's Guide
C++, through the Oracle C++ Call Interface (OCCI), as described in the Oracle C++ Call Interface Programmer's Guide
C or C++, through the Pro*C/C++ precompiler, as described in the Pro*C/C++ Programmer's Guide
COBOL, through the Pro*COBOL precompiler, as described in the Pro*COBOL Programmer's Guide
Visual Basic, through Oracle Provider for OLE DB, as described in Oracle Provider for OLE DB Developer's Guide for Microsoft Windows .
Java, through the JDBC and SQLJ client-side application programming interfaces (APIs). See Oracle Database JDBC Developer’s Guide and Oracle Database SQLJ Developer’s Guide .
Java in the database, as described in Oracle Database Java Developer’s Guide . This includes the use of Java stored procedures (Java methods published to SQL and stored in the database), as described in a chapter in Oracle Database Java Developer’s Guide .
The Oracle JVM Web Call-Out utility is also available for generating Java classes to represent database entities, such as SQL objects and PL/SQL packages, in a Java client program; publishing from SQL, PL/SQL, and server-side Java to web services; and enabling the invocation of external web services from inside the database. See Oracle Database Java Developer’s Guide .
How can you choose between these different implementation possibilities? Each of these languages offers different advantages: ease of use, the availability of programmers with specific expertise, the need for portability, and the existence of legacy code are powerful determinants.
The choice might narrow depending on how your application must work with Oracle Database:
PL/SQL is a powerful development tool, specialized for SQL transaction processing.
Some computation-intensive tasks are executed most efficiently in a lower level language, such as C.
For both portability and security, you might select Java.
Taking all these factors into account suggests that there might be situations in which you might need to implement your application in multiple languages. For example, because Java runs within the address space of the server, you might want to import existing Java applications into the database, and then leverage this technology by calling Java functions from PL/SQL and SQL.
PL/SQL external procedures enable you to write C procedure calls as PL/SQL bodies. These C procedures are callable directly from PL/SQL, and from SQL through PL/SQL procedure calls. The database provides a special-purpose interface, the call specification, that lets you call external procedures from other languages. While this service is designed for intercommunication between SQL, PL/SQL, C, and Java, it is accessible from any base language that can call these languages. For example, your procedure can be written in a language other than Java or C, and if C can call your procedure, then SQL or PL/SQL can use it. Therefore, if you have a candidate C++ procedure, use a C++ extern "C" statement in that procedure to make it callable by C.
Therefore, the strengths and capabilities of different languages are available to you, regardless of your programmatic environment. You are not restricted to one language with its inherent limitations. External procedures promote reusability and modularity because you can deploy specific languages for specific purposes.
21.2 What Is an External Procedure?
An external procedure is a procedure stored in a dynamic link library (DLL). You register the procedure with the base language, and then call it to perform special-purpose processing.
For example, when you work in PL/SQL, the language loads the library dynamically at runtime, and then calls the procedure as if it were a PL/SQL procedure. These procedures participate fully in the current transaction and can call back to the database to perform SQL operations.
The procedures are loaded only when necessary, so memory is conserved. The decoupling of the call specification from its implementation body means that the procedures can be enhanced without affecting the calling programs.
External procedures let you:
Isolate execution of client applications and processes from the database instance to ensure that problems on the client side do not adversely affect the database
Move computation-bound programs from client to server where they run faster (because they avoid the round trips of network communication)
Interface the database server with external systems and data sources
Extend the functionality of the database server itself
The external library (DLL file) must be statically linked. In other words, it must not reference external symbols from other external libraries (DLL files). Oracle Database does not resolve such symbols, so they can cause your external procedure to fail.
Oracle Database Security Guide for information about securing external procedures
21.3 Overview of Call Specification for External Procedures
You publish external procedures through call specifications , which provide a superset of the AS EXTERNAL function through the AS LANGUAGE clause. AS LANGUAGE call specifications allow the publishing of external C procedures. (Java class methods are not external procedures, but they still use call specifications.)
To support legacy applications, call specifications also enable you to publish with the AS EXTERNAL clause. For application development, however, using the AS LANGUAGE clause is recommended.
In general, call specifications enable:
Dispatching the appropriate C or Java target procedure
Data type conversions
Parameter mode mappings
Automatic memory allocation and cleanup
Purity constraints to be specified, where necessary, for package functions called from SQL.
Calling Java methods or C procedures from database triggers
Location flexibility: you can put AS LANGUAGE call specifications in package or type specifications, or package (or type) bodies to optimize performance and hide implementation details
To use an existing program as an external procedure, load, publish, and then call it.
21.4 Loading External Procedures
To make your external C procedures or Java methods available to PL/SQL, you must first load them.
You can load external C procedures only on platforms that support either DLLs or dynamically loadable shared libraries (such as Solaris . so libraries).
When an application calls an external C procedure, Oracle Database or Oracle Listener starts the external procedure agent, extproc . Using the network connection established by Oracle Database or Oracle Listener, the application passes this information to extproc :
Name of DLL or shared library
Name of external procedure
Any parameters for the external procedure
Then extproc loads the DLL or the shared library, runs the external procedure, and passes any values that the external procedure returns back to the application. The application and extproc must reside on the same computer.
extproc can call procedures in any library that complies with the calling standard used.
The default configuration for external procedures no longer requires a network listener to work with Oracle Database and extproc . Oracle Database now spawns extproc directly, eliminating the risk that Oracle Listener might spawn extproc unexpectedly. This default configuration is recommended for maximum security.
You must change this default configuration, so that Oracle Listener spawns extproc , if you use any of these:
A multithreaded extproc agent
Oracle Database in shared mode on Windows
An AGENT clause in the LIBRARY specification or an AGENT IN clause in the PROCEDURE specification that redirects external procedures to a different extproc agent
CALLING STANDARD for more information about the calling standard
Changing the default configuration requires additional network configuration steps.
To configure your database to use external procedures that are written in C, or that can be called from C applications, you or your database administrator must follow these steps:
21.4.1 Define the C Procedures
Define the C procedures using one of these prototypes:
ISO/ANSI prototypes other than numeric data types that are less than full width (such as float , short , char ); for example:
Other data types that do not change size under default argument promotions.
This example changes size under default argument promotions:
21.4.2 Set Up the Environment
When you use the default configuration for external procedures, Oracle Database spawns extproc directly. You need not make configuration changes for listener . ora and tnsnames . ora . Define the environment variables to be used by external procedures in the file extproc . ora (located at $ORACLE_HOME/hs/admin on UNIX operating systems and at ORACLE_HOME\hs\admin on Windows), using this syntax:
Set the EXTPROC_DLLS environment variable, which restricts the DLLs that extproc can load, to one of these values:
NULL ; for example:
This setting, the default, allows extproc to load only the DLLs that are in directory $ORACLE_HOME / bin or $ORACLE_HOME / lib .
ONLY: followed by a colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:
This setting allows extproc to load only the DLLs named DLL1 and DLL2. This setting provides maximum security.
A colon-separated (semicolon-separated on Windows systems) list of DLLs; for example:
This setting allows extproc to load the DLLs named DLL1 and DLL2 and the DLLs that are in directory $ORACLE_HOME / bin or $ORACLE_HOME / lib .
ANY ; for example:
This setting allows extproc to load any DLL.
Set the ENFORCE_CREDENTIAL environment variable, which enforces the usage of credentials when spawning an extproc process. The ENFORCE_CREDENTIAL value can be TRUE or FALSE (the default). For a discussion of ENFORCE_CREDENTIAL and the expected behaviors of an extproc process based on possible authentication and impersonation scenarios, see the information about securing external procedures in Oracle Database Security Guide .
To change the default configuration for external procedures and have your extproc agent spawned by Oracle Listener, configure your database to use external procedures that are written in C, or can be called from C applications, as follows.
To use credentials for extproc , you cannot use Oracle Listener to spawn the extproc agent.
- Set configuration parameters for the agent, named extproc by default, in the configuration files tnsnames . ora and listener . ora . This establishes the connection for the external procedure agent, extproc , when the database is started.
- Start a listener process exclusively for external procedures.
The Listener sets a few required environment variables (such as ORACLE_HOME , ORACLE_SID , and LD_LIBRARY_PATH ) for extproc . It can also define specific environment variables in the ENVS section of its listener . ora entry, and these variables are passed to the agent process. Otherwise, it provides the agent with a "clean" environment. The environment variables set for the agent are independent of those set for the client and server. Therefore, external procedures, which run in the agent process, cannot read environment variables set for the client or server processes.
It is possible for you to set and read environment variables themselves by using the standard C procedures setenv and getenv , respectively. Environment variables, set this way, are specific to the agent process, which means that they can be read by all functions executed in that process, but not by any other process running on the same host.
In dedicated mode, one "dedicated" agent is launched for each session. In multithreaded mode, a single multithreaded extproc agent is launched. The multithreaded extproc agent handles calls using different threads for different users. In a configuration where many users can call the external procedures, using a multithreaded extproc agent is recommended to conserve system resources.
If the agent is to run in dedicated mode, additional configuration of the agent process is not necessary.
If the agent is to run in multithreaded mode, your database administrator must configure the database system to start the agent in multithreaded mode (as a multithreaded extproc agent). To do this configuration, use the agent control utility, agtctl . For example, start extproc using this command:
where agent_sid is the system identifier that this extproc agent services. An entry for this system identifier is typically added as an entry in the file tnsnames . ora .
If you use a multithreaded extproc agent, the library you call must be thread-safe—to avoid errors such as a damaged call stack.
The database server, the agent process, and the listener process that spawns the agent process must all reside on the same host.
By default, the agent process runs on the same database instance as your main application. In situations where reliability is critical, you might want to run the agent process for the external procedure on a separate database instance (still on the same host), so that any problems in the agent do not affect the primary database server. To do so, specify the separate database instance using a database link.
Figure F-1 in Oracle Call Interface Programmer's Guide illustrates the architecture of the multithreaded extproc agent.
Oracle Call Interface Programmer's Guide for more information about using agtctl for extproc administration
21.4.3 Identify the DLL
In this context, a DLL is any dynamically loadable operating-system file that stores external procedures.
For security reasons, your DBA controls access to the DLL. Using the CREATE LIBRARY statement, the DBA creates a schema object called an alias library, which represents the DLL. Then, if you are an authorized user, the DBA grants you EXECUTE privileges on the alias library. Alternatively, the DBA might grant you CREATE ANY LIBRARY privileges, in which case you can create your own alias libraries using this syntax:
The ANY privileges are very powerful and must not be granted lightly. For more information, see:
Oracle Database Security Guide for information about managing system privileges, including ANY
Oracle Database Security Guide for guidelines for securing user accounts and privileges
Oracle recommends that you specify the path to the DLL using a directory object, rather than only the DLL name. In this example, you create alias library c_utils , which represents DLL utils . so :
where DLL_DIRECTORY is a directory object that refers to '/DLLs' .
As an alternative, you can specify the full path to the DLL, as in this example:
To allow flexibility in specifying the DLLs, you can specify the root part of the path as an environment variable using the notation $ < VAR_NAME >, and set up that variable in the ENVS section of the listener . ora entry.
In this example, the agent specified by the name agent_link is used to run any external procedure in the library C_Utils :
The environment variable EP_LIB_HOME is expanded by the agent to the appropriate path for that instance, such as /usr/bin/dll . Variable EP_LIB_HOME must be set in the file listener . ora , for the agent to be able to access it.
For security reasons, extproc , by default, loads only DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib . Also, only local sessions—that is, Oracle Database client processes that run on the same system—are allowed to connect to extproc .
To load DLLs from other directories, set the environment variable EXTPROC_DLLS . The value for this environment variable is a colon-separated (semicolon-separated on Windows systems) list of DLL names qualified with the complete path. For example:
While you can set up environment variables for extproc through the ENVS parameter in the file listener . ora , you can also set up environment variables in the extproc initialization file extproc . ora in directory $ORACLE_HOME/hs/admin . When both extproc . ora and ENVS parameter in listener . ora are used, the environment variables defined in extproc . ora take precedence. See the Oracle Net manual for more information about the EXTPROC feature.
In extproc.ora on a Windows system, specify the path using a drive letter and using a double backslash ( \\ ) for each backslash in the path. (The first backslash in each double backslash serves as an escape character.)
21.4.4 Publish the External Procedures
You find or write an external C procedure, and add it to the DLL. When the procedure is in the DLL, you publish it using the call specification mechanism described in Publishing External Procedures.
21.5 Publishing External Procedures
Oracle Database can use only external procedures that are published through a call specification, which maps names, parameter types, and return types for your Java class method or C external procedure to their SQL counterparts. It is written like any other PL/SQL stored procedure except that, in its body, instead of declarations and a BEGIN END block, you code the AS LANGUAGE clause.
The AS LANGUAGE clause specifies:
Which language the procedure is written in
For a Java method:
The signature of the Java method
The alias library corresponding to the DLL for a C procedure
The name of the C procedure in a DLL
Various options for specifying how parameters are passed
Which parameter (if any) holds the name of the external procedure agent, extproc , for running the procedure on a different system
You begin the declaration using the normal CREATE OR REPLACE syntax for a procedure, function, package specification, package body, type specification, or type body.
The call specification follows the name and parameter declarations. Its syntax is:
This is then followed by either:
Where java_string_literal_name is the signature of your Java method
Where library_name is the name of your alias library, c_string_literal_name is the name of your external C procedure, and external_parameter stands for:
property stands for:
Unlike Java, C does not understand SQL types; therefore, the syntax is more intricate
Сразу скажу, что теперь можно одновременно использовать как 32 так и 64 разрядные внешние процедуры, а разобравшись в проблеме конфигурирование выполняется весьма несложно.
Столкнувшись с необходимостью организовать работу библиотек используемых ранее на 32 разрядом Oracle обнаружил, что в новой версии Oracle данный аспект сильно изменен в отличие от предшествующих версий.
Конфигурирование было выполнено для следующих компонентов:
• Windows 2008 R2 64-bit
• Oracle 11g R2 64-bit
• Oracle Instant Client 11.2.0.2 32-bit
Вот перечень некоторых отличий настройки от предшествующих версий Oracle:
• extproc32.exe – теперь не используется;
• оснастку Instant Client теперь необходимо устанавливать в отдельный ORACLE_HOME;
• появился новый конфигурационный файл настройки extproc — %ORACLE_HOME%\hs\admin\extproc.ora;
• файлы логов LISTNER размещаются в — %ORACLE_HOME%\diag\tnslsnr\[hostname].
Приступим к настройке Oracle
Первое, что необходимо сделать – выполнить выборочную установку 32-bit Oracle Instant Client. Достаточно выбрать для установки: Oracle Database Utilities и Oracle Net Listener. В качестве пути установки я установил: [диск]:\oracle\product\11.2.0\client_32. В завершении установки автоматически будет запущен Net Configuration Assistant – которым, рекомендуется воспользоваться, что бы избежать впоследствии возможных ошибок, как например использование утилиты netca, которая будет запущена для конфигурирования прослушивателя (LISTNER) из ORACLE_HOME по умолчанию, а он окажется 64-разрядный.
Опишу процесс настройки нового прослушивателя через Net Configuration Assistant:
• В окне конфигурации выбрать пункт: Listener configuration и далее
• Выбрать Add и далее
• Задать имя LISTENER_32 и далее
• Из колонки Selected Protocols убрать протокол TCP и добавить протокол IPC и далее
• Ввести в поле IPC Key value: IPC_EXT32 и далее
• Отказаться от конфигурирования другого прослушивателя и далее, далее, готово
После завершения работы Net Configuration Assistant, можно проверить сформировавшийся файл конфигурации прослушивателя %ORACLE_HOME%\client_32\NETWORK\ADMIN\listener.ora, выглядеть он должен так, как указано в примере, разве что за исключением пути до директории Oracle:
Последнее, о чем стоит упомянуть в описании системной конфигурации – это новый файл конфигурации extproc.ora. Данный файл необходимо сконфигурировать как в 32-bit, так и в 64-bit инстансах Oracle. Конфигурационный файл, оснащен вполне исчерпывающим описанием конфигурации, и я лишь приведу пример настройки. Самое простое, это указать значение параметра EXTPROC_DLLS=ANY; либо указать точный путь, используя опцию ONLY:[MY_PATH\my.dll].
Особенности конфигурирования библиотеки Oracle
Для 64-bit библиотек всё остается, без каких либо изменений, но для указания Oracle, какие библиотеки необходимо запустить, используя 32-bit extproc, необходимо создать символическую ссылку и переконфигурировать скрипт создания библиотеки.
Создадим символическую ссылку:
Используя пример, изменим скрипт создания объекта библиотеки:
На этом особенности конфигурировния библиотек заканчиваются.
Настройка переменных окружения
Я не выполнял тестирования с использованием переменных окружения, таких как TNS_ADMIN, ORACLE_HOME, LD_LIBRARY_PATH и прочих. В моем случае в переменных окружения они отсутствуют, а переменная окружения Path содержит последовательно путь до 64-bit инстанса базы данных, а затем до 32-bit инстанс клиента.
Иногда в конфигурации Java-приложения есть IP-адрес "Primary" сервера базы данных, который может поменяться, например, в следующих случаях:
- Контролируемая смена ролей баз данных. "Primary" становится "Standby" и наоборот, "Standby" становится "Primary". Такая процедура обычно называется "Switchover".
- Аварийная смена роли "Standby" на "Primary". Это обычно называется "Failover".
- MacBook c объемом RAM 16 Гб (для эксперимента нужно более 8 Гб)
- Virtual Box версии 6.1.12
- 2x виртуальные машины (далее VM) c CentOS 7 Minimal, каждая из которых имеет
- 2x vCPU
- 2048 Гб RAM (с временным увеличением до 8Гб, по очереди)
- 40 Гб HDD
- отключенное аудио, чтобы избежать загрузки CPU 100%
Настройка виртуальных машин
Создаем виртуальные машины (далее VMs) с типом Linux Red Hat, стартуем. При запуске Virtual Box предлагает выбрать iso, с которого запустить VM (в эксперименте используется CentOS-7-x86_64-Minimal-1908.iso). Оставляем все по умолчанию, перезагружаем, обновляем, устанавливаем "Virtual Box Guest Additions", отключаем firewalld, чтобы не мешался. "Как очищается политура — это всякий знает", поэтому отметим только, что после обновления VMs переключаем их сетевые интерфейсы с адаптера NAT на "виртуальный адаптер хоста" vboxnet0. Этому адаптеру, который можно создать в инструментах Virtual Box, вручную задаем адрес 192.168.56.1/24 и отключаем DHCP. По сути, это IP-адрес шлюза по умолчанию для VMs и адрес Java-приложения. Просто для наглядности. А если на CentOS все еще нужен интернет, то можно включить NAT на MacOS:
- Переключаемся в пользователя root с помощью команды ’sudo su -′.
- Разрешаем перенаправление трафика с помощью команды ’sysctl -w net.inet.ip.forwarding=1′.
- В файл /var/root/pfnat.conf добавляем содержательную часть файла /etc/pf.conf, в которую на место строки № 3 вставляем правило для NAT:
В /etc/hosts всех VMs добавляем однообразные записи, чтобы они могли "пинговать" друг друга по доменным именам.
Установка Oracle
Выполняем "Software only" установку на oracle1 и oracle2 с помощью rpm-пакетов, директорию с которыми можно расшарить с MacOS через Virtual Box (Machine->Settings->Shared Folder).
Далее, выполняем создание экземпляра СУБД на VM "oracle1". Для этого под пользователем oralce запускаем соответствующий скрипт.
Эта процедура занимает некоторое время. После создания экземпляра на обоих хостах добавляем в файл /home/oracle/.bash_profile вот эти строчки:
Ну и настраиваем ssh для удобства, чтобы можно было сразу подключиться под пользователем oracle. Подключаемся к хосту по ssh и под пользователем oracle подключаемся к БД.
Собственно, Oracle. Для эксперимента нам также нужно настроить репликацию.
Репликация Oracle.
Для настройки репликации воспользуемся немного дополненной инструкцией:
-
Переводим БД на сервере oracle1 в "Archive Mode". Для этого в sqlplus выполняем команды:
SQL> host
[oracle@oracle1 ~]$ mkdir /opt/oracle/recovery_area
[oracle@oracle1 ~]$ exit
SQL> alter system set db_recovery_file_dest_size=2g scope=both;
SQL> alter system set db_recovery_file_dest='/opt/oracle/recovery_area' scope=both;
SQL> ALTER DATABASE FLASHBACK ON;LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:24
Copyright © 1991, 2019, Oracle. All rights reserved.
[oracle@oracle1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 — Production on 15-AUG-2020 08:17:32
Copyright © 1991, 2019, Oracle. All rights reserved.
[oracle@oracle2 ~]$ lsnrctl start
[oracle@oracle2 ~]$ orapwd file=$ORACLE_BASE/product/19c/dbhome_1/dbs/orapwORCLCDB entries=10 password=pa_SSw0rd
[oracle@oracle2 ~]$ echo "*.db_name='ORCLCDB'" > /tmp/initORCLCDB_STBY.ora
[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/pdbseed
[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLCDB/ORCLPDB1
[oracle@oracle2 ~]$ mkdir -p $ORACLE_BASE/admin/ORCLCDB/adump
[oracle@oracle2 ~]$ mkdir /opt/oracle/recovery_area
[oracle@oracle2 ~]$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initORCLCDB_STBY.ora'
[oracle@oracle2 ~]$ rman TARGET sys/pa_SSw0rd@ORCLCDB AUXILIARY sys/pa_SSw0rd@ORCLCDB_STBY
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='ORCLCDB_STBY' COMMENT 'Is standby' NOFILENAMECHECK;[oracle@oracle1 ~]$ dgmgrl sys/pa_SSw0rd@ORCLCDB
DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS ORCLCDB CONNECT IDENTIFIER IS ORCLCDB;
DGMGRL> ADD DATABASE ORCLCDB_STBY AS CONNECT IDENTIFIER IS ORCLCDB_STBY MAINTAINED AS PHYSICAL;
DGMGRL> enable configuration;Итак, в результате создания реплики и включения Data Guard мы имеем следующее состояние:
Это плохое состояние. Давайте подождем немного…
Вот теперь состояние хорошее! Правда, реплика весьма пассивна, она не принимает запросы на чтение (OPEN MODE: MOUNTED).
Давайте сделаем реплику активной, чтобы она принимала запросы на чтение. Тогда мы, в перспективе, сможем разгрузить сервер с "Primary" базой. Это то, что называется "Active Data Guard", либо active standby. На сервере oracle2 выполняем следующую последовательность команд в sqlplus:
Вот теперь можно и почитать с реплики (OPEN MODE: READ ONLY):И в консоли DataGuard на сервере oracle1 все выглядит неплохо:
Несмотря на то, что Data Guard у нас теперь Active, кластер все еще во многом пассивен. Нам и нашему восхитительному Java-приложению он по-прежнему не скажет ни слова о том, что Primary теперь не Primary. Для этого на серверах кластера должна быть запущена еще одна служба, ONS (Oracle Notification Services). И похоже, что для запуска этой службы установки Oracle Database недостаточно, нам потребуется установить Oracle Grid.
Установка и настройка Oracle Grid.
Тут все достаточно просто: как и в процессе установки Oracle Database мы просто будем следовать официальной инструкции.
-
На сервере oracle1 и oracle2 под пользователем root загружаем и распаковываем архив с Oracle Grid, ставим gcc-c++, добавляем пользователя oracle в группу asm. В случае Virtual Box, как при установке Oracle, просто монтируем распакованную директорию Oracle Grid к обеим виртуальным машинам и копируем.
Verifying Physical Memory . FAILED
Required physical memory = 8GB
Verifying Swap Size . FAILED
Required = 2.6924GB (2823138.0KB); Found = 2GB (2097148.0KB)][oracle@oracle1 grid]$ cd /opt/oracle/product/19c/grid/ && ./runcluvfy.sh stage -pre hacfg
Pre-check for Oracle Restart configuration was successful.Содержимое файла grid_configwizard.rsp будет весьма лаконично, потому что нас интересует только oracle restart, без всяких там ASM и прочих восхитительных технологий.
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
INVENTORY_LOCATION=/opt/oracle/oraInventory
oracle.install.option=CRS_SWONLY
ORACLE_BASE=/opt/oracle
oracle.install.asm.OSDBA=oinstall
oracle.install.asm.OSASM=asm
oracle.install.asm.SYSASMPassword=oracle
oracle.install.asm.diskGroup.name=data
oracle.install.asm.diskGroup.redundancy=NORMAL
oracle.install.asm.diskGroup.AUSize=4
oracle.install.asm.diskGroup.disksWithFailureGroupNames=/dev/sdbДемонстрация "Switchover" на тестовом Java приложении
В итоге мы имеем 2 сервера oracle с репликацией в режиме active-standby и службой ons, в которую будут отправляться события о переключениях, и Java-приложение сможет обрабатывать эти события по мере их поступления.
Подключаемся к серверу oracle1 под пользователем oracle и в sqlplus выполняем команду по созданию пользователя и таблицы
После создания тестового пользователя и таблицы, подключаемся к серверу oracle2 и "открываем" экземпляр на чтение, чтобы убедиться, что репликация работает.
Тестовое приложение состоит из одного только класса Main, в цикле пытается добавить запись в тестовую таблицу, а затем эту же запись прочитать (см. методы "putNewMessage()" и "getLastMessage()"). Еще имеется метод "getConnectionHost()", который из объекта "connection" с помощью "Reflection API" получает IP-адрес подключения к БД. Как далее видно в консоли, после "switchover" этот адрес меняется.
Запускаем тестовое приложение и выполняем в консоли Data Guard "switchover".
Видим, как в логе приложения меняется IP-адрес подключения, простой составляет 1 минуту:
Переключаем обратно, для верности.
Также, обратим внимание на TCP-соединения на хостах oralce1 и oracle2. Data-порт занят подключениями только на primary, ONS-порт занят и на primary, и на replica.Заключение
Таким образом, мы смоделировали в лабораторных условиях кластер Data Guard с минимальным количеством настроек и реализовали отказоустойчивое подключение тестового Java-приложения. Теперь мы знаем что разработчик хочет от DBA, а DBA от разработчика. Осталось еще запустить и протестировать Fast Start Failover, но, пожалуй, в рамках отдельной статьи.Читайте также: