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

Answered by: Chris Saxon - Last updated: April 08, 2020 - 8:13 am UTC

Category: Database Administration - Version: 18.6

Viewed 100+ 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 we 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"?

and you rated our response

  (2 ratings)

Reviews

Follow Up

March 30, 2020 - 4:16 pm UTC

Reviewer: Geraldo

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

Followup  

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

April 08, 2020 - 2:21 am UTC

Reviewer: Geraldo

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

Followup  

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 to Explore

Administration

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