Distributed Transaction (In-doubt Transaction Errors)

In-doubt Transaction Errors:

A transaction is In-Doubt when there is a failure during aspect of the two-phase commit. The following ways Distributed

Transaction Errors occurs.

• A network connection failure or slow between one or more oracle database.

• A server machine running Oracle Software crash.

• An un-handule software error.

You need to force commit or rollback of local, In-doubt transaction; this operation can generate some consistency

problems; you can perform it as per following conditions.

Problem with Two-Phase Commit:

A user application that commits a differed transaction is informed problem area.

ORA-02050: transaction ID rolled back, some remote dbs may be in-doubt

ORA-02051: transaction ID committed, some remote dbs may be in-doubt

ORA-02054: transaction ID in-doubt

Action:

No action required by DBA of any node that has one or more in-doubt transaction due to System Failure or Network

Failures.

Oracle automatically fixes those errors once network or system failure problem resolved.

Transaction Time-outs;

A DML statement that require locks on remote database can be blocked by another transaction own locks on the request

data. If the locks continue to block transaction following reason we should be face below error message.

1. Timeout occurs

2. Oracle roll-back the statement

3. Oracle returns the error message.

ORA-02049: time-out: distributed transaction waiting for lock

Action:

As a DBA; should be check any DEAD LOCK occurs in application or any DML locks occurs in database.

Locks Perform In-doubt Transaction:

A query DML statement that require locks on local database can be blocked indefinitely due to in-doubt transaction then
only these errors occurs.



ORA-01591: lock held by in-doubt distributed transaction identifier

Action:

A user should be inform to DBA; because oracle rollback SQL statement immediately;

Manual Commit in In-doubt Transaction:

DBA_2PC_PENDING data dictionary view to gain information about the in-doubt transaction.

Replicaton Tuning Tool

The primary tools used to perform the FAST REFRESH analysis were:

1. Oracle provided DBMS_MVIEW.explain_mview procedure
2. MV_CAPABILITIES_TABLE table

The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE:

1. Lists the general capabilities of materialized view or materialized view query
2. Specifies whether each capability is possible (Y or N)
3. If a capability is not possible the reason (why) is written to the msgtxt column of the MV_CAPABILITIES_TABLE

The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

SQL> @ $ORACLE_HOME/rdbms/admin/utlxmv.sql

Example:

After creating utlxmv.sql script execute the below command.

SQL> exec dbms_mview.explain_mview( 'GENERALISSUEARNO');


SQL> SELECT CAPABILITY_NAME,POSSIBLE,SUBSTR(MSGTXT,1,60) MSGTXT FROM MV_CAPABILITIES_TABLE WHERE CAPABILITY_NAME LIKE '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REFRESH_FAST_PCT N the materialized view is upgraded from a previous version th

6 rows selected.