ORA-02054 ORA-03150 ORA-02063 On Distributed Transactions with Oracle Text index configured to sync-on commit (Doc ID 2482643.1)

Last updated on JULY 14, 2020

Applies to:

Symptoms

When attempting to insert data to a remote database that has a text index with sync set to on commit, errors are raised and the dml fails. 

SQL> INSERT INTO z_pg_citations_text (cit_icd,cd,aa,title,length,version,sort_cd,cit_aa) VALUES (503675,'ΠΕΡΙΛΗΨΗ',0,'640/2012 ΕΣ (Τμ.ΙΙ) (696072)',7,0,'20150230',0);

1 row created.

SQL> INSERT INTO z_dd_citations_text@dbsync SELECT * FROM z_pg_citations_text WHERE cit_icd = 503675;

1 row created.

SQL> commit;

the following error occurs.

ERROR
-----------------------
SQL> INSERT INTO z_pg_citations_text (cit_icd,cd,aa,title,length,version,sort_cd,cit_aa) VALUES (503675,'ΠΕΡΙΛΗΨΗ',0,'640/2012 ΕΣ (Τμ.ΙΙ) (696072)',7,0,'20150230',0);

1 row created.

SQL> INSERT INTO z_dd_citations_text@dbsync SELECT * FROM z_pg_citations_text WHERE cit_icd = 503675;

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 10.3.16580 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from DBSYNC
SQL>

ALERT
-------
2018-11-22T17:40:58.128007+02:00
Error 3150 trapped in 2PC on transaction 10.3.16580. Cleaning up.
Error stack returned to user:
ORA-02054: transaction 10.3.16580 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from DBSYNC

2018-11-22T17:40:58.156427+02:00
DISTRIB TRAN LAW.70da8639.10.3.16580
 is local tran 10.3.16580 (hex=0a.03.40c4)
 insert pending prepared tran, scn=90762433 (hex=0x000000000568ecc1)
2018-11-22T17:41:40.034808+02:00
DISTRIB TRAN LAW.70da8639.10.3.16580
 is local tran 10.3.16580 (hex=0a.03.40c4)
 change pending prepared tran, scn=90762433 (hex=0x000000000568ecc1)
 to pending collecting tran, scn=90762433 (hex=0x000000000568ecc1)
DISTRIB TRAN LAW.70da8639.10.3.16580
 is local tran 10.3.16580 (hex=0a.03.40c4))
 delete pending collecting tran, scn=90762433 (hex=0x000000000568ecc1)

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.