How to test distributed transaction with Oracle 12c pluggable databases

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.

Overview

Distributed transactions require a transaction coordinator and a minimum of 2 different data sources.

In this example I will use 3 Oracle databases:

  • database TXC as transaction coordinator
  • database DS1 as first data source
  • database DS2 as second data source
  • 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.

    Setup

    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.
    

    Test a distributed transaction

    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>
    

    Simulate distributed transaction failure using specific COMMIT COMMENT clause

    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.

    Advertisements
    Post a comment or leave a trackback: Trackback URL.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: