The following picture shows the overall architecture from the logical point of view.
The configurations explained in this section was developed using different software versions:
Oracle Database 10g Release 2 and 11g Release 2 Express Edition using a local connection
Oracle Database 12c Release 1 (12.1) Standard Edition using a remote (SqlNet) connection
If you use a different configuration, you will need to fix some of the details shown by these examples.
Note
If you have not yet installed the software provided by Oracle, please refer to the official Oracle site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to Oracle technology: it is assumed that you have already installed and configured the database.
Important
The LIXA software must be configured, compiled and installed to support the Oracle Database Server resource manager as explained in the section called “Linking third party resource managers”.
The following example is based on Oracle XE 10.2 and 11.2, but there shouldn't be too much differences with other Oracle versions.
If the database server was not running, you can start it with these commands
tiian@ubuntu:~/tmp$ sudo /etc/init.d/oracle-xe enable tiian@ubuntu:~/tmp$ sudo /etc/init.d/oracle-xe start Starting Oracle Net Listener. Starting Oracle Database 10g Express Edition Instance.
on some systems, like Ubuntu 10.04, you use somethig like this:
tiian@ubuntu:~/tmp$ sudo service oracle-xe enable tiian@ubuntu:~/tmp$ sudo service oracle-xe start Starting Oracle Net Listener. Starting Oracle Database 11g Express Edition Instance.
First of all you must be able to connect as “SYSDBA”
from a terminal session; the commands below show what happens when
I connect to the Oracle server using the user
sys with password
“oracle”
[58]
[59]:
tiian@ubuntu:~$ sudo su - oracle oracle@ubuntu:~$ echo $ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server oracle@ubuntu:~$ sqlplus "sys/oracle as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 7 22:23:56 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
You must check the file xaview.sql:
oracle@ubuntu:~$ ls -la $ORACLE_HOME/rdbms/admin/xaview.sql -rw-r--r-- 1 oracle dba 1754 2006-02-24 06:18 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/admin/xaview.sql
It contains the SQL instructions necessary to create two specific system views that could be not defined in your database; the following commands are related to a database that contains the desired views:
oracle@ubuntu:~$ sqlplus "sys/oracle as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 7 22:32:45 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from v$pending_xatrans$; no rows selected SQL> select * from v$xatrans$; no rows selected SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
If the command failed the views would be not defined and you would get something like this:
oracle@ubuntu:~$ sqlplus "sys/oracle as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 29 22:20:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select * from v$pending_xatrans$;
select * from v$pending_xatrans$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from v$xatrans$;
select * from v$xatrans$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
you can create them with a command like this:
oracle@ubuntu:~$ cat $ORACLE_HOME/rdbms/admin/xaview.sql | sqlplus "sys/oracle as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 29 22:25:48 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> DROP VIEW v$xatrans$ * ERROR at line 1: ORA-00942: table or view does not exist SQL> DROP VIEW v$pending_xatrans$ * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> SQL> 2 3 4 5 6 7 8 View created. SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 View created. SQL> SQL> SQL> Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
The example programs supplied by the LIXA project are designed
to use the
hr user; you
must grant
the necessary privileges to all the users you want to use for
your Application Programs. The below commands show how to grant
the necessary privileges to
hr:
oracle@ubuntu:~$ sqlplus "sys/oracle as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 7 22:44:44 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> grant select on dba_pending_transactions to hr; Grant succeeded. SQL> grant select on v$pending_xatrans$ to hr; Grant succeeded. SQL> grant select on v$xatrans$ to hr; Grant succeeded. SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Note
If the user
hr did not exist and
the above commands failed,
you should read Oracle documentation and pick-up the necessary
information to create it.
The example programs supplied by the LIXA project are designed
to use the
hr user; it might be
locked after Oracle software installation.
The below commands show how to unlock it:
oracle@ubuntu:~$ sqlplus "sys/oracle as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 7 22:44:44 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> ALTER USER hr ACCOUNT UNLOCK; User altered. SQL> ALTER USER hr IDENTIFIED BY hr; User altered. SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
You may perform the same operation using the graphical (web based) interface.
Note
Newer Oracle database versions set a default password limit: after such a limit, Oracle will start to issue ORA-28002 messages and XA functions will return with an error like below:
215229.2063.1888967424.1: ORA-28002: the password will expire within 6 days 215229.2063.1888967424.1: xaolgn: XAER_RMERR; logon failed. ORA-28002. 215229.2063.1888967424.1: xaoopen: return -3
To avoid this potential error, configure your users in the right way.
Execute the below commands to check the existence of table
countries and to create a new table with
name authors:
tiian@ubuntu:~$ sqlplus "hr/hr"[60] SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 14 22:04:55 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> CREATE TABLE hr.authors ( 2 id INTEGER NOT NULL UNIQUE, 3 last_name VARCHAR2(20), 4 first_name VARCHAR2(20)); Table created. SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
That's OK because the table does not contain the row we are going to insert.
If you experience this type of error: “ ORA-02049: time-out: distributed transaction waiting for lock ”, especially after a JDBC thin client used by XTA for Java crashed, you can change the system parameter to reduce the number of caught exceptions:
[oracle@centos7-oracle12 ~]$ sqlplus "sys/oracle as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 19 16:41:54 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> SELECT name,value FROM v$parameter where NAME='distributed_lock_timeout' ; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- distributed_lock_timeout 60 SQL> ALTER SYSTEM SET distributed_lock_timeout=120 scope=spfile; System altered. SQL> commit; Commit complete.
The database instance must be restarted to activate the new value.
Warning
The parameter has system wide scope: please check official documentation and/or contact support before proceding.
Note
This example has been tested using Ubuntu Server LTS 14.04 and Oracle Instant Client 12.1
Using a remote configuration instead of a local configuration introduces two differences:
the Oracle Database Server must be configured to be accessible from another system: a listener must be configured
the Oracle Instant Client software must be installed and configured in the system that will connect to the database
The first part of the Oracle Database Server is pretty the same described in the section called “Local configuration (Server) and OCI”. All the statements remain valid for Oracle 12c Standard Edition with the exception of the paths. Here's a default path installation example:
[oracle@centos7-oracle12 ~]$ ls -la $ORACLE_HOME/rdbms/admin/xaview.sql -rw-r--r--. 1 oracle oinstall 1941 Apr 21 2011 /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/xaview.sql
Warning
Many recent Linux distributions, CentOS 7.x for example, automatically enable an internal firewall to prevent undesired accesses. If your firewall configuration does not allow Oracle traffic, you have no way to reach your database instance from a different system.
Here's a very basic example that accepts incoming connection from any network interface (IP address 0.0.0.0):
[oracle@centos7-oracle12 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
You can check that the listener is accepting connection with this command:
[oracle@centos7-oracle12 ~]$ netstat -unta | grep 1521 | grep LISTEN tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN
and you can check a connection from a different system with something like this (adjust your IP address, port and Global Database Name):
tiian@ubuntu1404-64:~$ sqlplus hr/hr@192.168.122.81:1521/orcl.brenta.org SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 21:50:46 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Fri Jan 13 2017 21:36:05 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
Oracle Instant Client must be retrieved from Oracle portal. These are the suggested packages for version 12.1 if you want to use LIXA:
instantclient-basic-linux.x64-12.1.0.2.0.zip instantclient-precomp-linux.x64-12.1.0.2.0.zip instantclient-sdk-linux.x64-12.1.0.2.0.zip instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
For the Linux operating system you can choose between ".rpm"
packages and ".zip" archives: feel free to install the packages
that you prefer.
Just as an example, here's the final layout of a tested
configuration using /opt/oracle as a prefix
tiian@ubuntu1404-64:~/lixa$ find /opt/oracle/ | sort
/opt/oracle/
/opt/oracle/instantclient_12_1
/opt/oracle/instantclient_12_1/adrci
/opt/oracle/instantclient_12_1/BASIC_README
/opt/oracle/instantclient_12_1/cobsqlintf.o
/opt/oracle/instantclient_12_1/genezi
/opt/oracle/instantclient_12_1/glogin.sql
/opt/oracle/instantclient_12_1/libclntshcore.so.12.1
/opt/oracle/instantclient_12_1/libclntsh.so
/opt/oracle/instantclient_12_1/libclntsh.so.12.1
/opt/oracle/instantclient_12_1/libipc1.so
/opt/oracle/instantclient_12_1/libmql1.so
/opt/oracle/instantclient_12_1/libnnz12.so
/opt/oracle/instantclient_12_1/libocci.so
/opt/oracle/instantclient_12_1/libocci.so.12.1
/opt/oracle/instantclient_12_1/libociei.so
/opt/oracle/instantclient_12_1/libocijdbc12.so
/opt/oracle/instantclient_12_1/libons.so
/opt/oracle/instantclient_12_1/liboramysql12.so
/opt/oracle/instantclient_12_1/libsqlplusic.so
/opt/oracle/instantclient_12_1/libsqlplus.so
/opt/oracle/instantclient_12_1/network
/opt/oracle/instantclient_12_1/network/admin
/opt/oracle/instantclient_12_1/network/admin/tnsnames.ora
/opt/oracle/instantclient_12_1/ojdbc6.jar
/opt/oracle/instantclient_12_1/ojdbc7.jar
/opt/oracle/instantclient_12_1/precomp
/opt/oracle/instantclient_12_1/precomp/admin
/opt/oracle/instantclient_12_1/precomp/admin/pcbcfg.cfg
/opt/oracle/instantclient_12_1/precomp/admin/pcscfg.cfg
/opt/oracle/instantclient_12_1/PRECOMP_README
/opt/oracle/instantclient_12_1/sdk
/opt/oracle/instantclient_12_1/sdk/admin
/opt/oracle/instantclient_12_1/sdk/admin/oraaccess.xsd
/opt/oracle/instantclient_12_1/sdk/demo
/opt/oracle/instantclient_12_1/sdk/demo/cdemo81.c
/opt/oracle/instantclient_12_1/sdk/demo/demo.mk
/opt/oracle/instantclient_12_1/sdk/demo/demo_proc_ic.mk
/opt/oracle/instantclient_12_1/sdk/demo/demo_procob_ic.mk
/opt/oracle/instantclient_12_1/sdk/demo/occidemod.sql
/opt/oracle/instantclient_12_1/sdk/demo/occidemo.sql
/opt/oracle/instantclient_12_1/sdk/demo/occidml.cpp
/opt/oracle/instantclient_12_1/sdk/demo/occiobj.cpp
/opt/oracle/instantclient_12_1/sdk/demo/occiobj.typ
/opt/oracle/instantclient_12_1/sdk/demo/oraaccess.xml
/opt/oracle/instantclient_12_1/sdk/demo/procdemo.pc
/opt/oracle/instantclient_12_1/sdk/demo/procobdemo.pco
/opt/oracle/instantclient_12_1/sdk/demo/setuporamysql.sh
/opt/oracle/instantclient_12_1/sdk/include
/opt/oracle/instantclient_12_1/sdk/include/ldap.h
/opt/oracle/instantclient_12_1/sdk/include/nzerror.h
/opt/oracle/instantclient_12_1/sdk/include/nzt.h
/opt/oracle/instantclient_12_1/sdk/include/occiAQ.h
/opt/oracle/instantclient_12_1/sdk/include/occiCommon.h
/opt/oracle/instantclient_12_1/sdk/include/occiControl.h
/opt/oracle/instantclient_12_1/sdk/include/occiData.h
/opt/oracle/instantclient_12_1/sdk/include/occi.h
/opt/oracle/instantclient_12_1/sdk/include/occiObjects.h
/opt/oracle/instantclient_12_1/sdk/include/oci1.h
/opt/oracle/instantclient_12_1/sdk/include/oci8dp.h
/opt/oracle/instantclient_12_1/sdk/include/ociap.h
/opt/oracle/instantclient_12_1/sdk/include/ociapr.h
/opt/oracle/instantclient_12_1/sdk/include/ocidef.h
/opt/oracle/instantclient_12_1/sdk/include/ocidem.h
/opt/oracle/instantclient_12_1/sdk/include/ocidfn.h
/opt/oracle/instantclient_12_1/sdk/include/ociextp.h
/opt/oracle/instantclient_12_1/sdk/include/oci.h
/opt/oracle/instantclient_12_1/sdk/include/ocikpr.h
/opt/oracle/instantclient_12_1/sdk/include/ocixmldb.h
/opt/oracle/instantclient_12_1/sdk/include/ocixstream.h
/opt/oracle/instantclient_12_1/sdk/include/odci.h
/opt/oracle/instantclient_12_1/sdk/include/oraca.h
/opt/oracle/instantclient_12_1/sdk/include/oratypes.h
/opt/oracle/instantclient_12_1/sdk/include/orid.h
/opt/oracle/instantclient_12_1/sdk/include/ori.h
/opt/oracle/instantclient_12_1/sdk/include/orl.h
/opt/oracle/instantclient_12_1/sdk/include/oro.h
/opt/oracle/instantclient_12_1/sdk/include/ort.h
/opt/oracle/instantclient_12_1/sdk/include/sql2oci.h
/opt/oracle/instantclient_12_1/sdk/include/sqlapr.h
/opt/oracle/instantclient_12_1/sdk/include/sqlca.h
/opt/oracle/instantclient_12_1/sdk/include/sqlcpr.h
/opt/oracle/instantclient_12_1/sdk/include/sqlda.h
/opt/oracle/instantclient_12_1/sdk/include/sqlkpr.h
/opt/oracle/instantclient_12_1/sdk/include/sqlucs2.h
/opt/oracle/instantclient_12_1/sdk/include/xa.h
/opt/oracle/instantclient_12_1/sdk/ott
/opt/oracle/instantclient_12_1/sdk/ottclasses.zip
/opt/oracle/instantclient_12_1/sdk/proc
/opt/oracle/instantclient_12_1/sdk/procob
/opt/oracle/instantclient_12_1/sdk/rtsora
/opt/oracle/instantclient_12_1/sdk/SDK_README
/opt/oracle/instantclient_12_1/sqlplus
/opt/oracle/instantclient_12_1/SQLPLUS_README
/opt/oracle/instantclient_12_1/uidrvci
/opt/oracle/instantclient_12_1/xstreams.jar
If your installation layout is different, adjust the following steps as necessary.
Oracle Instant Client does not provide
oracle_env.sh, but some
environment variables can be very handy and it is suggested you to
create your own oracle_env.sh as below:
#!/bin/sh export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1:$LD_LIBRARY_PATH export PATH=/opt/oracle/instantclient_12_1:/opt/oracle/instantclient_12_1/sdk:$PATH export ORACLE_HOME=/opt/oracle/instantclient_12_1
and to put it inside Oracle Instant Client base directory [61]:
tiian@ubuntu1404-64:~/lixa$ ls -la /opt/oracle/instantclient_12_1/oracle_env.sh -rwxr-xr-- 1 root root 216 mar 10 21:57 /opt/oracle/instantclient_12_1/oracle_env.sh
Use it when it's needed with shell sourcing:
tiian@ubuntu1404-64:~$ . /opt/oracle/instantclient_12_1/oracle_env.sh tiian@ubuntu1404-64:~$ echo $LD_LIBRARY_PATH /opt/oracle/instantclient_12_1: tiian@ubuntu1404-64:~$ echo $PATH /opt/oracle/instantclient_12_1:/opt/oracle/instantclient_12_1/sdk:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games tiian@ubuntu1404-64:~$ echo $ORACLE_HOME /opt/oracle/instantclient_12_1
A possible option to configure a remote database is based on the
"tnsnames.ora" file. It should be put at path
$ORACLE_HOME/network/admin/tnsnames.ora
Here's an example:
lixa_ora_db=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=centos7-oracle12.brenta.org)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=orcl.brenta.org)))
Note
Configuring Oracle networking feature requires a little bit of experience: be patient and consult official documentation and user group forums to obtain a working configuration.
Important
This example configuration uses lixa_ora_db
as the name understood by the Instant Client to reach the
database server.
The final step is the configuration check. If everything is OK, you should obtain something like this:
tiian@ubuntu1404-64:~$ sqlplus hr/hr@lixa_ora_db SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 21:36:05 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Fri Jan 13 2017 21:35:16 +01:00 Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
See the instructions reported in the section called “Check the data table before execution” to configure the tables necessary for examples execution.
[58]
I put this line (Oracle 10.2 32 bit)
. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
or this line (Oracle 11.2 64 bit)
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
in the file $HOME/.profile of the
oracle user to set-up
the default administration environment; it complains about
two shell errors, but for the sake of our example it's
safe.
[59] If you got some errors like these:
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found
you could edit the file /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh and substitute
#!/bin/sh with
#!/bin/bash in the first row as explained
here:
http://forums.oracle.com/forums/thread.jspa?messageID=1542334
[60] use
sqlplus hr/hr@lixa_ora_db
in the event that you are using a remote connection as explained above
[61] LIXA test suite, based on autotest looks for it: if it's not available, Oracle's tests are skipped.