This article shows how to create distributed transactions and to simulate failure of these distributed transactions without using an application server like Weblogic or Websphere.
Distributed transactions require a transaction coordinator and a minimum of 2 different data sources.
In this example I will use 3 Oracle databases:
Up to Oracle Database 11G release 2 you need to use 3 different database for this purpose (without using an application server or another executable). As of Oracle Database 12C release 1 we can use a single container database with 3 different pluggable databases.
For this article I have used Oracle 12.1.0.1 under Windows and the container database is named CDB12C. You first need to download Oracle 12.1.0.1 from OTN, to install it and to create a container database (for Windows see how to do it in silent mode).
Then you need to create the 3 specific pluggable databases:
SQL> set linesize 100
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
SQL> --
SQL> alter pluggable database txc close;
Pluggable database altered.
SQL> alter pluggable database ds1 close;
Pluggable database altered.
SQL> alter pluggable database ds2 close;
Pluggable database altered.
SQL> -- to avoid ORA-01265/ORA-27056/OSD-04024 Windows limitation
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1920757760 bytes
Fixed Size 2404072 bytes
Variable Size 603980056 bytes
Database Buffers 1308622848 bytes
Redo Buffers 5750784 bytes
Database mounted.
Database opened.
SQL> drop pluggable database txc including datafiles;
Pluggable database dropped.
SQL> drop pluggable database ds1 including datafiles;
Pluggable database dropped.
SQL> drop pluggable database ds2 including datafiles;
Pluggable database dropped.
SQL> --
SQL> whenever sqlerror exit failure;
SQL> --
SQL> create pluggable database txc
2 admin user txca identified by txca
3 roles=(dba)
4 default tablespace users datafile 'c:\oradata\cdb12c\txc\users01.dbf' size 100M
5 file_name_convert = ('c:\oradata\cdb12c\pdbseed', 'c:\oradata\cdb12c\txc');
Pluggable database created.
SQL> alter pluggable database txc open;
Pluggable database altered.
SQL> --
SQL> create pluggable database ds1
2 admin user ds1a identified by ds1a
3 roles=(dba)
4 default tablespace users datafile 'c:\oradata\cdb12c\ds1\users01.dbf' size 100M
5 file_name_convert = ('c:\oradata\cdb12c\pdbseed', 'c:\oradata\cdb12c\ds1');
Pluggable database created.
SQL> alter session set container=ds1;
Session altered.
SQL> alter pluggable database ds1 open;
Pluggable database altered.
SQL> alter user ds1a quota unlimited on users;
User altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> --
SQL> create pluggable database ds2
2 admin user ds2a identified by ds2a
3 roles=(dba)
4 default tablespace users datafile 'c:\oradata\cdb12c\ds2\users01.dbf' size 100M
5 file_name_convert = ('c:\oradata\cdb12c\pdbseed', 'c:\oradata\cdb12c\ds2');
Pluggable database created.
SQL> alter pluggable database ds2 open;
Pluggable database altered.
SQL> alter session set container=ds2;
Session altered.
SQL> alter user ds2a quota unlimited on users;
User altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> --
SQL> exit
Add in <ORACLE_HOME>/network/admin/tnsnames.ora following Oracle Net aliases:
# tnsnames.ora
DS1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DS1)
)
)
DS2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DS2)
)
)
Create database links from TXC database to DS1 and DS2 databases:
SQL> connect txca/txca@localhost:1521/txc Connected. SQL> drop database link ds1; Database link dropped. SQL> drop database link ds2; Database link dropped. SQL> whenever sqlerror exit failure; SQL> create database link ds1 connect to ds1a identified by ds1a using 'ds1'; Database link created. SQL> create database link ds2 connect to ds1a identified by ds1a using 'ds2'; Database link created. SQL> -- SQL> exit
Create database tables in DS1 and DS2 databases:
SQL> connect ds1a/ds1a@localhost:1521/ds1 Connected. SQL> drop table t1 purge; Table dropped. SQL> create table t1(x int); Table created. SQL> connect ds2a/ds2a@localhost:1521/ds2 Connected. SQL> drop table t2 purge; Table dropped. SQL> create table t2(x int); Table created.
SQL> connect txca/txca@localhost:/txc
Connected.
SQL> insert into t1@ds1 values(1);
1 row created.
SQL> insert into t2@ds2 values(2);
1 row created.
SQL> select globalid, con_id, coupling, state from v$global_transaction;
GLOBALID
--------------------------------------------------------------------------------
CON_ID COUPLING STATE
---------- --------------- --------------------------------------
5458432E31663033366330362E372E32382E33323439
0 TIGHTLY COUPLED [ORACLE COORDINATED]ACTIVE
SQL> commit;
Commit complete.
SQL> select globalid, con_id, coupling, state from v$global_transaction;
no rows selected
SQL> select * from t1@ds1;
X
----------
1
SQL> select * from t2@ds2;
X
----------
2
SQL>
We first need to disable RECO background process (otherwise RECO will automatically fix the pending transaction):
SQL> connect / as sysdba Connected. SQL> alter system disable distributed recovery; System altered.
Then we use COMMIT COMMENT to crash distributed transaction:
SQL> connect txca/txca@localhost:/txc Connected. SQL> insert into t1@ds1 values(11); 1 row created. SQL> insert into t2@ds2 values(22); 1 row created. SQL> commit comment 'ORA-2PC-CRASH-TEST-1'; commit comment 'ORA-2PC-CRASH-TEST-1' * ERROR at line 1: ORA-02054: transaction 10.23.4633 in-doubt ORA-02059: ORA-2PC-CRASH-TEST-1 in commit comment ORA-02063: preceding line from DS1
Database instance alert log says:
Fri Apr 25 19:54:06 2014 Error 2059 trapped in 2PC on transaction 17.32.102. Cleaning up. Error stack returned to user: ORA-02059: ORA-2PC-CRASH-TEST-1 in commit comment Fri Apr 25 19:54:06 2014 Error 2059 trapped in 2PC on transaction 10.23.4633. Cleaning up. Error stack returned to user: Fri Apr 25 19:54:06 2014 DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 10.23.4633 (hex=0a.17.1219) insert pending prepared tran, scn=5298429 (hex=0.0050d8fd) DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 6.15.3614 (hex=06.0f.e1e) insert pending prepared tran, scn=5298427 (hex=0.0050d8fb) ORA-02054: transaction 10.23.4633 in-doubt ORA-02059: ORA-2PC-CRASH-TEST-1 in commit comment ORA-02063: preceding line from DS1
DBA_2PC_PENDING view has one row in TXC database:
SQL> select local_tran_id, state, fail_time from dba_2pc_pending; LOCAL_TRAN_ID STATE FAIL_TIME ---------------------- ---------------- --------- 10.23.4633 prepared 25-APR-14
You can query involved data in DS1 database but you get ORA-01591 when accessing involved data in DS2:
SQL> select * from t1@ds1;
X
----------
1
SQL> select * from t2@ds2;
select * from t2@ds2
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 6.15.3614
ORA-02063: preceding line from DS2
SQL>
In DS1 database we have no pending transaction for this error:
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
DS1
SQL> select local_tran_id, state, fail_time from dba_2pc_pending;
no rows selected
SQL>
In DS2 database we have one pending transaction for this error:
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
DS2
SQL> select local_tran_id, state, fail_time from dba_2pc_pending;
LOCAL_TRAN_ID STATE FAIL_TIME
---------------------- ---------------- ---------
6.15.3614 prepared 25-APR-14
SQL>
To fix automatically this issue we need to enable again RECO background process:
SQL> connect / as sysdba Connected. SQL> alter system enable distributed recovery; System altered. SQL>
Database instance alert log says:
Fri Apr 25 20:03:53 2014 DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 10.23.4633 (hex=0a.17.1219) change pending prepared tran, scn=5298429 (hex=0.0050d8fd) to pending collecting tran, scn=5298429 (hex=0.0050d8fd) Fri Apr 25 20:03:53 2014 DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 6.15.3614 (hex=06.0f.e1e) change pending prepared tran, scn=5298427 (hex=0.0050d8fb) to pending collecting tran, scn=5298427 (hex=0.0050d8fb) DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 6.15.3614 (hex=06.0f.e1e)) delete pending collecting tran, scn=5298427 (hex=0.0050d8fb) Fri Apr 25 20:03:53 2014 DISTRIB TRAN TXC.1f036c06.10.23.4633 is local tran 10.23.4633 (hex=0a.17.1219)) delete pending collecting tran, scn=5298429 (hex=0.0050d8fd)
In TXC database the pending transaction has disappeared because it has been rolled back by RECO:
SQL> connect txca/txca@localhost:/txc
Connected.
SQL> select local_tran_id, state, fail_time from dba_2pc_pending;
no rows selected
SQL> select * from t1@ds1;
X
----------
1
SQL> select * from t2@ds2;
X
----------
2
SQL>
You can read more about distributed transaction using My Oracle Support 100664.1 also published on blogs.oracle.com
Oracle Support Master Note for Troubleshooting Managed Distributed Transactions (Doc ID 100664.1) last updated in 2010.