Oracle7 Server Distributed Systems Manual, Vol. 1 | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Oracle's CDE tools assist you in all phases of application development from design through implementation. It also simplifies the task of porting existing applications to different operating systems and platforms
Additional Information: See the CASE tool set documentation for full explanations of how to use CASE*Designer, CASE*Dictionary, and CASE*Generator to develop your applications.
In a distributed system, each database must have a unique global name. The global name is composed of the database name and the network domain that contains the database. Each object in the database then has a global object name consisting of the object name and the global database name. Because Oracle ensures that the object name is unique within a database, you can ensure that it is unique across all databases by assigning unique global database names. You should coordinate with your database administrator on this task, as it is usually the DBA who is responsible for assigning database names. See "Global Naming Issues," , for more information on object naming.
The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application (or user) explicitly terminates them. Terminating remote connections established using database links is useful for disconnecting high cost connections (such as long distance phone connections) that are no longer required by the application.
The application developer or user can close (terminate) a remote connection and session using the ALTER SESSION command with the CLOSE DATABASE LINK parameter. For example, assume you issue the following query:
SELECT * FROM emp@sales; COMMIT;
The following statement terminates the session in the remote database pointed to by the SALES database link:
ALTER SESSION CLOSE DATABASE LINK sales;
If partial global database names are specified, the local Oracle expands the name using the network domain of the local database.
Note: Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.
To close a database link connection in your user session, you must have the ALTER SESSION system privilege.
Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table.
For example, assume that the child table is in the SALES database and the parent table is in the HQ database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the HQ database.
If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a rollback or rollback to savepoint is issued.
You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.
EXCEPTION WHEN ZERO_DIVIDE THEN /* ...handle the exception */
Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:
... null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, -20101); ... RAISE_APPLICATION_ERROR(-20101, 'salary is missing'); ... WHEN null_salary THEN ...
When calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.
Additional Information: For information on library linking filenames, see your Oracle operating system- specific documentation.
The Oracle XA library conforms to the X/Open Distributed Transaction Processing (DTP) software architecture's XA interface specification.
For a general overview of XA, including basic architecture, see X/Open CAE Specification - Distributed Transaction Processing: The XA Specification.
You can obtain a copy of this document by requesting X/Open Document No. XO/CAE/91/300 or ISBN 1 872630 24 3 from:
X/Open Company, Ltd. 1010 El Camino Real, Suite 380 Menlo Park, CA 94025 U.S.A.
Figure 7 - 1 illustrates a possible X/Open DTP model.
Figure 7 - 1. One Possible DTP Model
A resource manager (RM) controls a shared, recoverable resource that can be returned to a consistent state after a failure. For example, Oracle7 Server is an RM and uses its redo log and undo segments to return to a consistent state after a failure. An RM provides access to shared resources such as a database, file systems, printer servers, and so forth.
A transaction manager (TM) provides an application program interface (API) for specifying the boundaries of the transaction and manages the commit and recovery procedures.
Normally, Oracle7 Server acts as its own TM and manages its own commit and recovery. However, using a standards-based TM allows Oracle7 Server to cooperate with other heterogeneous RMs in a single transaction.
A TM is usually a component provided by a transaction processing monitor (TPM) vendor. The TM assigns identifiers to transactions, and monitors and coordinates their progress. It uses Oracle XA library subroutines to tell Oracle7 Server how to process the transaction, based on its knowledge of all RMs in the transaction. You can find a list of the XA subroutines and their descriptions later in this section.
An application program (AP) defines transaction boundaries and specifies actions that constitute a transaction. For example, an AP can be a precompiler or OCI program. The AP operates on the RM's resource through the RM's native interface, for example SQL. However, it starts and completes all transaction operations via the transaction manager through an interface called TX. The AP itself does not directly use the XA interface.
Note: The naming conventions for your TX interface and associated subroutines are vendor-specific, and may differ from those used here. For example, you may find that the tx_open call is referred to as tp_open on your system. To check on terminology, see the documentation supplied with your transaction processing monitor.
In phase one, the prepare phase, the TM asks each RM to guarantee the ability to commit any part of the transaction. If this is possible, then the RM records its prepared state and replies affirmatively to the TM. If it is not possible, the RM may roll back any work, reply negatively to the TM, and forget any knowledge about the transaction. The protocol allows the application, or any RM, to roll back the transaction unilaterally until the prepare phase is complete.
In phase two, the commit phase, the TM records the commit decision. Then the TM issues a commit or rollback to all RMs which are participating in the transaction. Note that a TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.
There are several other tasks the TM instructs the RMs to do. These include among others:
xa_open
connect to the resource manager
disconnect from the resource manager
start a new transaction and associate it with the given transaction ID (XID), or associate the process with an existing transaction
disassociate the process from the given XID
roll back the transaction associated with the given XID
prepare the transaction associated with the given XID. This is the first phase of the two-phase commit protocol.
commit the transaction associated with the given XID. This is the second phase of the two-phase commit protocol.
retrieve a list of prepared, heuristically committed or heuristically rolled back transactions
forget the heuristic transaction associated with the given XID
wait for completion of an asynchronous operation
In general, the AP does not need to worry about these subroutines except to understand the role played by the xa_open string.
The TPM synchronizes any commits and rollbacks required to complete a distributed transaction. The transaction manager (TM) portion of the TPM is responsible for controlling when distributed commits and rollbacks take place. Thus, if a distributed application program is written to take advantage of a TPM, the TM portion of the TPM is responsible for controlling the two-phase commit protocol. The RMs enable the TMs to do this.
Because the TM controls distributed commits or rollbacks, it must communicate directly with Oracle (or any other resource manager). It communicates through the Oracle XA library interface.
xa_switch_t structure
The Oracle Server xa_switch_t structure name is xaosw. This structure contains entry points and other information for the resource manager.
xa_switch_t resource mgr
The Oracle Server resource manager name within the xa_switch_t structure is Oracle_XA.
close string
The close string used by xa_close () is ignored and may be null.
open string
The format of the open string used by xa_open () is described in detail in the section titled "Developing and Installing Applications that use the XA Libraries" .
libraries
Libraries needed to link applications using Oracle XA have operating system-specific names. In general, it is similar to linking an ordinary precompiler or OCI program except with one additional library ($ORACLE_HOME/lib/libxa.a), and any TPM-specific libraries.
See your Oracle operating system-specific documentation and the Oracle XA README.doc file to find the correct library name.
Use of the Oracle XA library restricts the use of SQL to connect to and disconnect from the database and to perform transaction operations. These restrictions are described in "Interfacing to Precompilers and OCIs" .
requirements
You must have purchased and installed the distributed database option.
DDL statements
Oracle applications in global transactions may not perform DDL statements (like CREATE TABLE, DROP TABLE, CREATE INDEX) because they force a commit of pending work.
global and local transactions
When an application uses the XA library to connect to the Oracle Server, that application can only operate on global transactions. The Oracle Server does not allow local transactions when it is using XA.
transaction branches
Oracle Server transaction branches within the same global transaction share locks in a tightly coupled manner. However, if the branches are on different instances when running Oracle Parallel Server, then they are loosely coupled.
read-only
Oracle Server supports the read-only optimization.
association migration
Oracle Server does not support association migration (a means whereby a transaction manager may resume a suspended branch association in another branch).
dynamic registration
The optional XA feature dynamic registration is not supported.
asynchronous calls
The optional XA feature asynchronous XA calls is not supported.
Oracle_XA{+required_fields...} [+optional_fields...]
where required_fields are:
Acc=P//
or
Acc=P/user/password
SesTm=session_time_limit
and where optional_fields are:
DB=db_name
GPwd=P/group_password
LogDir=log_dir
MaxCur=maximum_#_of_open_cursors
SqlNet=connect_string
Note the following:
Acc=P//
or
Acc=P/user/password
Acc
Specifies user access information.
P
Indicates that explicit user and password information is provided.
P//
Indicates that no explicit user or password information is provided and that the operating system authentication form will be used.
For more information see "Managing Users and Resources," in Chapter 12 of the
Oracle7 Server Administrator's Guide.
user
A valid Oracle Server account.
password
The corresponding current password.
For example, Acc=P/scott/tiger indicates that user and password information is provided. In this case, the user is scott and the password is tiger.
As previously mentioned, make sure that scott has the SELECT privilege on the V$XATRANS$ table.
Acc=P// indicates that no user or password information is provided, thus defaulting to operating system authentication.
SesTm=session_time_limit
SesTm
Specifies the maximum length of time a transaction can be inactive before it is automatically deleted by the system.
session_time_limit
This value should be the maximum time allowed in a transaction between one service and the next, or a service and the commit or rollback of the transaction.
For example, if the TPM uses remote procedure calls between the client and the servers, then SesTM applies to the time between the completion of one RPC and the initiation of the next RPC, or the
tx_commit, or the tx_rollback.
The unit for this time limit is in seconds. The value of 0 indicates no limit, but entering a value of 0 is strongly discouraged. For example, SesTM=15 indicates that the session idle time limit is 15 seconds.
DB=db_name
DB
Specifies the database name
db_name
Indicates the name used by Oracle precompilers to identify the database.
Application programs that use only the default database for the Oracle precompiler (that is, they do not use the AT clause in their SQL statements) should omit the DB=db_name clause in the open string.
Applications that use explicitly named databases should indicate that database name in their DB=db_name field.
OCI programs need to call the sqlld2() function to obtain the correct connection. See "Using OCIs with the Oracle XA Library," .
The db_name is not the sid and is not used to locate the database to be opened. Rather, it correlates the database opened by this open string with the name used in the application program to execute SQL statements. The sid is set from either the environment variable ORACLE_SID of the TPM application server or the sid given in the SQL*Net clause in the open string. The SQL*Net clause is described later in this section.
Some TPM vendors provide a way to name a group of servers that use the same open string. The DBA may find it convenient to choose the same name both for that purpose and for db_name.
For example, DB=payroll indicates that the database name is "payroll", and that the application server program will use that name in AT clauses.
For more information about precompilers (specifically Pro*C/C++), see "Interfacing to Precompilers and OCIs" .
GPwd=P/group_password
GPwd
Specifies the server security group password.
P
Indicates that an explicit server security group password is currently provided.
group_password
Indicates the actual server security group password.
Server security groups provide an optional extra layer of protection between different applications running against the same Oracle Server instance. If no server security group option is specified, then the application using this open string will be part of an Oracle Server-defined server security group.
A transaction must be executed wholly within a server security group. For example, if a debit application specifies a different Oracle Server security group than a credit application, then the two may not be used in the same transaction.
For example, GPwd=P/banking indicates that the current server group password is "banking".
LogDir=log_dir
LogDir
Specifies the directory on a local machine where the Oracle XA library error and tracing information may be logged.
log_dir
Indicates the pathname of the directory where the tracing information should be stored. The default is $ORACLE_HOME/rdbms/log if ORACLE_HOME is set, otherwise it is the current directory.
For example, LogDir=/xa_trace indicates that the error and tracing information is located under the /xa_trace directory.
Note: Ensure that the directory you specify for logging exists and the application server can write to it, otherwise useful trace files may be lost without any error indication.
MaxCur=maximum_#_of_open_cursors
MaxCur
Specifies the number of cursors to be alocated when the database is opened. It serves the same purpose as the precompiler option maxopencursors.
maximum_#_of_ open_cursors
Indicates the number of open cursors to be cached.
For example, MaxCur=5 indicates that the precompiler should try to keep five open cursors cached.
Note: This parameter overrides the precompiler option maxopencursors that you might have specified in your source code or at compile time.
For more information on maxopencursors, see Chapter 6, Running the Oracle Precompilers," in the Programmer's Guide to the Oracle Precompilers.
SqlNet=db_link
SqlNet
Specifies the SQL*Net database link.
db_link
Indicates the string to use to log on to the system. The syntax for this string is the same as that used to set the TWO-TASK environment variable.
See your SQL*Net documentation for information about SQL*Net database links.
For example, SqlNet=hqfin@NEWDB indicates the database with sid=NEWDB accessed at host hqfin by TCP/IP.
The SqlNet parameter can be used to specify the ORACLE_SID in cases where you cannot control the server environment variable. It must also be used when the server needs to access more than one Oracle Server database. To use the SQL*Net string without actually accessing a remote database, use the Pipe driver.
For example:
SqlNet=localsid1
where:
localsid1
is an alias defined in the SQL*net tnsnames.ora file.
Make sure that all databases to be accessed with a SQL*Net database link have an entry in /etc/oratab.
There are two options to choose from when interfacing with precompilers:
The following is an example of an open string identifying a default Pro*C/C++ connection.
ORACLE_XA+SqlNet=host@MAIL+ACC=P/scott/tiger+GPwD=P/mailgrp +SesTM=10+LogDir=/usr/local/logs
Note that the DB=db_name is absent, indicating an empty database ID string.
The syntax of a SQL statement would be:
EXEC SQL UPDATE EMP SET SAL = sal*1.5;
An application may include the default database, as well as one or more named databases, as shown in the following examples.
For example, suppose you want to update an employee's salary in one database, her department number (DEPTNO) in another, and her manager in a third database. You would configure the following open strings in the transaction manager:
ORACLE_XA+DB=MANAGERS+SqlNet=hqfin@SID1+ACC=P/scott/tiger +GPwd+P/pay+SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+DB=PAYROLL+SqlNet=SID2+ACC=P/scott/tiger +GPwd=P/mgr+SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+SqlNet=hqemp@SID3+ACC=P/scott/tiger+GPwd=P/mgr +SesTM=10+LogDir=/usr/local/xalog
Note that there is no DB=db_name field in the last open string.
In the application server program, you would enter declarations such as:
EXEC SQL DECLARE PAYROLL DATABASE;
EXEC SQL DECLARE MANAGERS DATABASE;
Again, the default connection (corresponding to the third open string that does not contain the db_name field) needs no declaration.
When doing the update, you would enter statements similar to the following:
EXEC SQL AT PAYROLL UPDATE EMP SET SAL=4500 WHERE EMPNO=7788;
EXEC SQL AT MANAGERS UPDATE EMP SET MGR=7566 WHERE EMPNO=7788;
EXEC SQL UPDATE EMP SET DEPTNO=30 WHERE EMPNO=7788;
There is no AT clause in the last statement because it is referring to the default database.
In Oracle precompilers version 1.5.3 or later, you can use a character host variable in the AT clause, as the following example shows:
EXEC SQL BEGIN DECLARE SECTION;
DB_NAME1 CHARACTER(10);
DB_NAME2 CHARACTER(10);
EXEC SQL END DECLARE SECTION;
.
.
SET DB_NAME1 = 'PAYROLL'
SET DB_NAME2 = 'MANAGERS'
.
.
EXEC SQL AT :DB_NAME1 UPDATE...
EXEC SQL AT :DB_NAME2 UPDATE...
Additional Information: For more information on concurrent logons, see the Programmer's Guide to the Oracle Precompilers.
Note: Applications using XA should not create Oracle Server database connections of their own. Any work performed by them would be outside the global transaction, and may confuse the connection information used by the Oracle XA library.
Because an application server can have multiple concurrent open Oracle Server resource managers, it should call the function sqlld2() with the correct arguments to obtain the correct lda structure.
If DB=db_name is not present in the open string, then execute:
sqlld2(lda, NULL, 0);
to obtain the lda for this resource manager.
Alternatively, if DB=db_name is present in the open string, then execute:
sqlld2(lda, db_name, strlen(db_name));
to obtain the lda for this resource manager.
Additional Information: For more information about using the sqlld2() function, see the Programmer's Guide to the Oracle Call Interface.
When the XA library is used, transactions are not controlled by the SQL statements which commit or roll back transactions. Rather, they are controlled by an API accepted by the TM which starts and stops transactions. Most of the TMs use the TX interface for this. It includes the following functions:
tx_open
logs into the resource manager(s)
logs out of the resource manager(s)
starts a new transaction
commits a transaction
rolls back the transaction
Most TPM applications are written using a client-server architecture where an application client requests services and an application server provides services. The examples that follow use such a client-server model. A service is a logical unit of work, which in the case of the Oracle Server as the resource manager, comprises a set of SQL statements that perform a related unit of work.
For example, when a service named "credit" receives an account number and the amount to be credited, it will execute SQL statements to update information in certain tables in the database. In addition, a service might request other services. For example, a "transfer fund" service might request services from a "credit" and "debit" service.
Usually application clients request services from the application servers to perform tasks within a transaction. However, for some TPM systems, the application client itself can offer its own local services.
You can encode transaction control statements within either the client or the server; as shown in the examples.
To have more than one process participating in the same transaction, the TPM provides a communication API that allows transaction information to flow between the participating processes. Examples of communications APIs include RPC, pseudo-RPC functions, and send/receive functions.
Because the leading vendors support different communication functions, the examples that follow use the communication pseudo-function tpm_service to generalize the communications API.
X/Open has included several alternative methods for providing communication functions in their preliminary specification. At least one of these alternatives is supported by each of the leading TPM vendors.
The first example shows a transaction started by an application server, and the second example shows a transaction started by an application client.
Example 1: Transaction started by an application server.
Client:
tpm_service("ServiceName"); /* Request Service*/
Server:
ServiceName()
{
<get service specific data>
tx_begin(); /* Begin transaction boundary */
EXEC SQL UPDATE ....;
/*This application server temporarily becomes a client and requests *other service.*/
tpm_service("AnotherService");
tx_commit(); /* Commit the transaction */
<return service status back to the client>
}
Example 2 Transaction started by an application client.
Client:
tx_begin(); /* Begin transaction boundary */
tpm_service("Service1");
tpm_service("Service2");
tx_commit(); /* Commit the transaction */
Server:
Service1()
{
<get service specific data>
EXEC SQL UPDATE ....;
<return service status back to the client>
}
Service2()
{
<get service specific data>
EXEC SQL UPDATE ....;
...
<return service status back to client>
}
Regular Oracle Commands | TPM Functions |
CONNECT user/password | tx_open (possibly implicit) |
implicit start of transaction | tx_begin |
SQL | service that executes the SQL |
COMMIT | tx_commit |
ROLLBACK | tx_rollback |
disconnect | tx_close (possibly implicit) |
SAVEPOINT savepoint | illegal |
SET TRANSACTION READ ONLY | illegal |
A thread of control (or thread) refers to the set of connections to resource managers. In an unthreaded system, each process could be considered a thread of control, since each process has its own set of connections to resource managers and each process maintains its own independent resource manager table.
In a threaded system, each thread has an autonomous set of connections to resource managers and each thread maintains a private resource manager table. This private resource manager table must be allocated for each new thread and deallocated when the thread terminates, even if the termination is abnormal.
Note that, in an Oracle system, once a thread has been started and establishes a connection, only that thread can use that connection. No other thread can make a call to that connection.
The name of the trace file is:
xa_db_namedate.trc
where db_name is the database name you specified in the open string field DB=db_name, and date is the date when the information is logged to the trace file.
If you do not specify DB=db_name in the open string, it automatically defaults to the name "NULL".
The trace file can be placed in one of the following locations:
The example, xa_NULL040292.trc, shows a trace file that was created on April 2, 1992. Its DB field was not specified in the open string when the resource manager was opened.
The example, xa_Finance121591.trc, shows a trace file was created on December 15, 1991. Its DB field was specified as "Finance" in the open string when the resource manager was opened.
Note that multiple Oracle XA library resource managers with the same DB field and LogDir field in their open strings log all trace information that occurs on the same day to the same trace file.
Each entry in the trace file contains information that looks like this:
1032.12345.2: xa_switch rtn ORA-22
where "1032" is the time when the information is logged, "12345" is the process ID (PID), "2" is the resource manager ID, xa_switch is the module name, and ORA-22 is the Oracle Server information that was returned.
Generally, the transaction manager provided by the TPM system should resolve any failure and recovery of in-doubt or pending transactions. However, the DBA may have to override an in-doubt transaction in certain circumstances, such as when the in-doubt transaction is:
Additional Information: For more information on pending transactions, see Chapter 5.
For transactions generated by Oracle XA applications, the following column information applies specifically to the DBA_2PC_NEIGHBORS table.
For example, you could use the SQL statement below to obtain more information about in-doubt transactions generated by Oracle XA applications.
SELECT * FROM DBA_2PC_PENDING p, DBA_2PC_NEIGHBORS n WHERE p.LOCAL_TRAN_ID = n.LOCAL_TRAN_ID AND n.DBID = 'xa_orcl';
Warning: If these restrictions are not satisfied, when you use database links within an XA transaction, it creates an O/S network connection in the Oracle Server that is linked to the TPM server process. Since this O/S network connection cannot be moved from one process to another, you cannot detach from this server, and when you complete a service or RPC, you will receive an ORA#23 message.
If using the Multi-Threaded Server configuration is not possible then, access the remote database through the Pro*C/C++ application using EXEC SQL AT syntax.
Additional Information: For more information about the Parallel Server and the Oracle XA library, see your Oracle operating system-specific documentation.
Do not use EXEC SQL ROLLBACK WORK for precompiler applications. Similarly, an OCI application should not execute orol(). You can roll back a transaction by the initiator by calling tx_rollback().
Similarly, a precompiler application should not have the EXEC SQL COMMIT WORK statement. An OCI application should not execute ocom(). Instead, use tx_commit() or tx_rollback() to end a transaction.
EXEC SQL SAVEPOINT savepointname.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |