Skip to Main Content
  • Questions

  • Distributed and in-doubt transactions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: March 30, 2020 - 3:06 am UTC

Last updated: April 08, 2020 - 8:13 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello Ask Tom, Team.

I am struggling with distributed transactions. Oracle RAC 18c (18.6) and .NET app.

I want to know a few things:

The RECO background process of an Oracle Database instance automatically resolves failures involving distributed transactions. At exponentially growing time intervals, the RECO background process of a node attempts to recover the local portion of an in-doubt distributed transaction.

1. How can I check if RECO is enabled? (in-doubt transactions are automatically resolved). I know how to enable it but I want to check the current parameter value.

2. How can I easily simulate an in-doubt transaction? I do not get steps in link: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman009.htm#ADMIN12286

3. Do DTP database services work without tnsnames.ora? I am using easy connect and the app connects to the RAC SCAN DTP dabatase service?

4. Does Oracle offer a better way to handle distributed transactions rather than a database service with dtp enable?

Thanks in advanced.

and Chris said...

1. RECO is one of the mandatory background processes.

https://docs.oracle.com/en/database/oracle/oracle-database/20/cncpt/process-architecture.html#GUID-2E691FEA-9027-47E4-A3D0-1B235BBA295A

So if it's NOT running... you're in trouble!

2. Run a transaction over a database link, then commit with the comment shown in the link:

create table t (
  c1 int
);

insert into t@loopback values ( 1 );

commit comment 'ORA-2PC-CRASH-TEST-1';

ORA-02050: transaction 3.21.6128 rolled back, some remote DBs may be in-doubt
ORA-02059: ORA-2PC-CRASH-TEST-1 in commit comment


(loopback is a database link pointing back to the current database. This makes testing easier!)

3. I'm using ezconnect too. So yes!

4. I'm not sure what you're getting at here? What would make it "better"?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up

Geraldo, March 30, 2020 - 4:16 pm UTC

Thanks for the answers.

1. I meant because this command can be executed. What does it do?
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY

2. Got it.

3. Got it.

4. Never mind. It comes with the distributed architecture. I think this is how things get done in this kind of architecture.

Regards,
Connor McDonald
March 31, 2020 - 12:48 am UTC

From the docs:

" For example, you can temporarily disable RECO to force the failure of a two-phase commit and manually resolve the in-doubt transaction.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;"

This was a new discovery for me - I always thought that no RECO = no database instance.

But normal operation would obviously be for reco to be active.

Follow Up

Geraldo, April 08, 2020 - 2:21 am UTC

Thanks for the response.

Is there any way to automatically resolves a in-doubt transaction where the MSDTC is involved?

Thanks in advanced.
Chris Saxon
April 08, 2020 - 8:13 am UTC

I don't know what the MSDTC is, but as the docs say:

No action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a network or system failure. The automatic recovery features of the database transparently complete any in-doubt transaction so that the same outcome occurs on all nodes of a session tree (that is, all commit or all roll back) after the network or system failure is resolved.

But there are situations where you may want to do a manual recovery. Check the docs to see if any of these apply to you: https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-distributed-transactions.html#GUID-297F05FA-6F3E-40E6-8C11-C6B68CFAD9FE
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database