认真就输

Manually Resolving In-Doubt Transactions: Different Scenarios (文档 ID 126069.1)修改时间:2014-4-30类型:BULLETINAPPLIES TO:Oracle Database – Enterprise Edition – Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]Information in this document applies to any platform.PURPOSEThis note is intended to serve as an additional aid for both Analysts and Customers when studying or being confronted with in-doubt transactions.SCOPEThis note provides examples of failing distributed transactions and how to resolve them manually. Failures at different stages of the two phase-commit are identified and studied, together with guidelines to troubleshoot them.Scripts are included at the bottom to setup the environment that will help simulate failing distributed transactions. Please feel free to adjust them to your personal needs.In reality, you should only need to resolve an in-doubt transaction in the following cases:- the in-doubt transaction has locks on critical data or rollback segments- the cause of the machine, network or software failure cannot be repaired quicklyThe RECO background process (Distributed Recovery process) of an Oracle instance automatically resolves failures involving distributed transactions, when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version  of the data to display for a query.Please note that the information here only relates to Oracle distributed transactions. The transaction is done entirely within Oracle or in other words Oracle coordinates the transaction. There are transaction monitors that can be used to coordinate the transactions,instead of Oracle. One  example of those is Tuxedo, where the X/A interface is used. There could be also a situation where a non-Oracle database could be part of a distributed transaction through a means of Transparent Gateway. Both of those are outside the scope of this article.The examples given here are limited to distributed transactions between Two nodes as this is the most common encountered environment.If you are not familiar with the two phase-commit, and handling distributed transactions within Oracle, then you will find useful to consult the additional documentation included at the end of this note. These materials provide background information and terminology related to Oracle Distributed Systems.DETAILSThe Two Phase-CommitThe two phase-commit mechanism is used to ensure data integrity in a distributed transaction. It is automatically used during all distributed transactions and coordinates either commit or roll back of all the changes in the transaction as a single unit.Commit CommentTo intentionally induce a failure during the two-phase commit phases of a distributed transaction, the following is included in the COMMENT parameter (see sample script crash_1.sql, in section 5):COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-n’;where n is one of the following integers:n Effect1 Crash commit point site after collect2 Crash non-commit point site after collect3 Crash before prepare (non-commit point site)4 Crash after prepare (non-commit point site)5 Crash commit point site before commit6 Crash commit point site after commit7 Crash non-commit point site before commit8 Crash non-commit point site after commit9 Crash commit point site before forget10 Crash non-commit point site before forget- Note that you can still examine the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views on all participating sites. This is established by disabling the distributed recovery process (see sample script crash_1.sql, in section 5) before initiating the failing transaction. More detailed info can be found in the related documentation at the end of this note.- distributed transaction consists of DML on remote tableNOTE:Through in this case transaction will occurr on table/site “dept@v817.be.oracle.com”through means of a local synonym “s_dept” and DML on local table “emp”(see script crash_1.sql, in section 5).Stages towards and during the TWO PHASE COMMITSTAGES PHASE CRASH-TEST-NR’s———— ——– —————(02) -> (06) PREPARE 1, 2, 3, 4(07) -> (13) COMMIT 5, 6, 7, 8, 9(14) -> (16) FORGET 10(01) The global coordinator initiates and commits the distributed transaction.During execution of the SQL statements within the transaction, the definition of the session tree is completed (-> dba_2pc_neighbors).(02) The commit point site is determined (commit_point_strength) and the SCNs (System Change Number) of the communicating nodes are coordinated.The highest SCN at all nodes is determined. This will be the commit SCN at the commit point site later on (-> highest global SCN -> global integrity !!!).(03) The global coordinator asks participating(参于) nodes other than the commit point site to promise to commit or roll back (-> prepare message) the transaction, even if there is a failure. If any node cannot prepare, the transaction is rolled back.(04) Every participating node allocates resources it needs to commit or rollback the transaction if data is changed.It saves redo records corresponding to changes made by the transaction to its online redo log. This makes it possible to recover the database back to the prepare state in case of an instance failure.The node guarantees that locks held for the transaction are able to survive a failure.(05) All participating nodes place a distributed lock on modified tables preventing reads/writes.(06) All participating nodes respond with a prepared message to their global/local coordinator and wait until a commit or rollback request is received from the global/local coordinator.After the nodes are prepared, the distributed transaction is said to be in-doubt.Note that all participating nodes need to be prepared for the two phase commit to continue to the next phase (-> commit phase).(07) The global coordinator instructs the commit point site to commit.(08) The commit point site commits with the highest SCN (see step 02).(09) The commit point site informs(通知) the global coordinator of the commit.(10) The global/local coordinator instructs all the participating nodes to commit.(11) Every node commits the local portion of the distributed transaction and releases locks.(12) Every node records an additional redo entry in the local redo log indicating that the transaction has commited.(13) The participating nodes notify the global coordinator that they have committed.On completion of the commit phase, the data on all nodes of the distributed system is consistent with one another.(14) After receiving notice from the global coordinator that all nodes have committed, the commit point site erases status information about this transaction.(15) The commit point site informs the global coordinator that it has erased the status information.(16) The global coordinator erases its own information about the transaction.II Manually Resolving In-Doubt Transactions: Sample Scenarios1. Setup & Introduction to Scenarios———————————The following describes some constants during this note.V817REP.BE.ORACLE.COM- global coordinator ( = origin of distributed transaction)global coordinator is also local coordinator in the examples below commit_point_strength = 5V817.BE.ORACLE.COM- commit point site ( = highest commit_point_strength value)- commit_point_strength = 10DISTRIBUTED TRANSACTION STARTED/* DML remote -> v817.be.oracle.com */insert into s_dept values ();/* DML local -> v817rep.be.oracle.com */insert into emp values ();All scenarios in this document are based on:- one and the same script differing only in the way the transaction is forced to fail. Comments can be included in the COMMENT parameter of the COMMIT statement.Please refer to the Section 5, Scripts, for a copy of the scripts used in this note.2. Sample Scenarios—————-Refer to the scripts provided in Section 5 to work through the scenarios below.In each of the commit crash tests a failure is provoked and methodical investigation and troubleshoot of the transaction is provided. This will involve looking at the errors, the alert.log files and querying the dictionary to understand and resolve the in-doubt transaction given the information gathered.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.1. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-1’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash commit point site after collect-> after step (06) above2.1.1. Observations – Scenario 1ERROR RECEIVED:ORA-02054: transaction 1.8.238 in-doubtORA-02059: ORA-2PC-CRASH-TEST-1 in commit commentORA-02063: preceding line from V817ALERT FILE V817REP.BE.ORACLE.COM:Tue Dec 12 16:23:25 2000Error 2059 trapped in 2PC on transaction 1.8.238. Cleaning up.Error stack returned to user:ORA-02054: transaction 1.8.238 in-doubtORA-02059: ORA-2PC-CRASH-TEST-1 in commit commentORA-02063: preceding line from V817Tue Dec 12 16:23:25 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.1.8.238is local tran 1.8.238 (hex=01.08.ee)insert pending prepared tran, scn=194671 (hex=0.0002f86f)ALERT FILE V817.BE.ORACLE.COM:Tue Dec 12 16:23:25 2000Error 2059 trapped in 2PC on transaction 2.10.176. Cleaning up.Error stack returned to user:ORA-02059: ORA-2PC-CRASH-TEST-1 in commit commentDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-1.8.238 |V817REP.BE.ORACLE.COM.89f6eafb|prepared|no |BE-ORACLE-|194671|.1.8.238 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—1.8.238 |in | |SCOTT |N1.8.238 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.1.2. What do we learn from the Output – Scenario 1V817REP.BE.ORACLE.COM:- state -> prepared (-> dba_2pc_pending)The node has prepared and may or may not have acknowledged this to its global/local coordinator with a prepared message. However, no commit request has been received. The node remains prepared, holding any local resource locks necessary for the transaction to commit.- global coordinator (LOCAL_TRAN_ID = LOCAL_TRAN_ID of GLOBAL_TRAN_ID) – V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out,  INTERFACE=c, DATABASE=v817.be.oracle.com -> dba_2pc_neighbors)V817.BE.ORACLE.COM:- no output from both dba_2pc_pending and dba_2pc_neighbors since  ‘nothing’ happened at this end- did never commit because of crash (never got the instruction from  the global coordinator) (COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-1’)Remark :Note that at this moment there is a distributed lock on table emp@v817rep.be.oracle.com. A select will result in ORA-1591 : lock held by in-doubt distributed transaction 1.8.238.We will decide to manually resolve the in-doubt transaction because  of this lock.2.1.3. Solution – Scenario 1We can force a transaction in either two ways:ROLLBACK FORCE ‘transaction_id’;-OR-COMMIT FORCE ‘transaction_id’,commit#;where transaction_id is either the LOCAL_TRAN_ID or GLOBAL_TRAN_ID column from dba_2pc_pending and commit# is the COMMIT# from the same view.Remark:when specifying a commit# with the COMMIT FORCE, be sure to use the highest for all nodes involved to assure global integrity!Back to our example where a rollback of the local portion of the transaction is needed:rollback force ‘V817REP.BE.ORACLE.COM.89f6eafb.1.8.238’;-OR-rollback force ‘1.8.238’;DBA_2PC_PENDING@v817rep.be.oracle.com after the manual rollback:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-1.8.238 |V817REP.BE.ORACLE.COM.89f6eafb|forced |no |BE-ORACLE-|194671|.1.8.238 |rollback| |NTbel449 |To purge the in-doubt transaction entry:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘1.8.238’);** see NOTE1 at the end of this document if DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with ORA-30019The manual rollback action above can also be found in the alert file of v817rep.be.oracle.com:Tue Dec 12 16:42:13 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.1.8.238is local tran 1.8.238 (hex=01.08.ee)change pending prepared tran, scn=194671 (hex=0.0002f86f)to pending forced rollback tran, scn=194671 (hex=0.0002f86f)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.2. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-2’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash non-commit point site after collect-> after step (05) above2.2.1. Observations – Scenario 2ERROR RECEIVED:ORA-02050: transaction 3.4.270 rolled back, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-2 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Thu Dec 14 11:50:04 2000Error 2059 trapped in 2PC on transaction 3.4.270. Cleaning up.Error stack returned to user:ORA-02050: transaction 3.4.270 rolled back, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-2 in commit commentThu Dec 14 11:50:04 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.4.270is local tran 3.4.270 (hex=03.04.10e)insert pending collecting tran, scn=196918 (hex=0.00030136)ALERT FILE V817.BE.ORACLE.COM:No entriesDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———-|—|———-|——-3.4.270 |V817REP.BE.ORACLE.COM.|collecting|no |BE-ORACLE-|196918|89f6eafb.3.4.270 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—3.4.270 |in | |SCOTT |N3.4.270 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.2.2. What do we learn from the output – Scenario 2At V817REP.BE.ORACLE.COM:- collecting (the node is currently collecting information from other nodes)This node (global coordinator) currently is waiting for a prepared message from a non-commit point site (itself , in this case also global coordinator) which crashed (COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-2’)- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c, DATABASE=v817.be.oracle.com)V817.BE.ORACLE.COM:- commit point site (as indicated above)- no output from both dba_2pc_pending and dba_2pc_neighbors since ‘nothing’ happened (commit/rollback) at this endThe error message we received earlier, tells us the local transaction 3.4.270 rolled back. This means the global coordinator aborted the transaction because it never received a prepared state from the crashing non-commit point site.Note that there are no local distributed locks because of this, since this  site never entered the prepared state and already rolled back the local  portion of the transaction.2.2.3. SOLUTION – Scenario 2A rollback force in this case will not work because this node is still in collecting state. A rollback/commit force will only work for nodes in a prepared state. Note also that the local portion of the transaction was already rolled back.If you try so, you will receive the following error:ORA-02058: no prepared transaction found with ID V817REP.BE.ORACLE.COM.89f6eafb.3.4.270All we can do here is a purge of the in-doubt transaction entry:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘3.4.270’); ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.3. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-3’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash before prepare (non-commit point site) -> after step (03) above2.3.1. Observations – Scenario 3ERROR RECEIVED:ORA-02050: transaction 2.12.254 rolled back, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-3 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Thu Dec 14 14:34:05 2000Error 2059 trapped in 2PC on transaction 2.12.254. Cleaning up.Thu Dec 14 14:34:05 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.2.12.254is local tran 2.12.254 (hex=02.0c.fe)Thu Dec 14 14:34:05 2000Error stack returned to user:ORA-02050: transaction 2.12.254 rolled back, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-3 in commit commentinsert pending collecting tran, scn=199054 (hex=0.0003098e)ALERT FILE V817.BE.ORACLE.COM:No entriesDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———-|—|———-|——-2.12.254 |V817REP.BE.ORACLE.COM.|collecting|no |BE-ORACLE-|199054|89f6eafb.2.12.254 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—2.12.254 |in | |SCOTT |N2.12.254 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.3.2. What do we learn from the output – Scenario 3At V817REP.BE.ORACLE.COM:- collecting (the node is currently collecting information from other nodes) This node (global coordinator) currently is waiting for aprepared message  from a non-commit point site (itself, in this case also the global coordinator) which crashed before it could prepare (COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-3’).- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to  database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c, DATABASE = v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above).- no output from both dba_2pc_pending and dba_2pc_neighbors since ‘nothing’ happened at this end.The error message we received earlier, tells us the local transaction 2.12.254 rolled back. This means the global coordinator aborted the transaction because it never received a prepared state from the crashing non-commit point site.Note that there are no local distributed locks because of this, since this  site never entered the prepared state.2.3.3. Solution – Scenario 3A rollback force in this case will not work because this node is still in collecting state. If you try so, you will receive the following error:ORA-02058: no prepared transaction found with ID V817REP.BE.ORACLE.COM.89f6eafb.2.12.154All we can do here is a purge of the in-doubt transaction entry:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘2.12.254’);~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.4. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-4’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash after prepare (non-commit point site)-> after step (05) above2.4.1. Observations – Scenario 4————————-ERROR RECEIVED:ORA-02054: transaction 1.26.248 in-doubtORA-02059: ORA-2PC-CRASH-TEST-4 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Error 2059 trapped in 2PC on transaction 1.26.248. Cleaning up.Thu Dec 14 14:46:39 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.1.26.248is local tran 1.26.248 (hex=01.1a.f8)insert pending prepared tran, scn=199226 (hex=0.00030a3a)Thu Dec 14 14:46:39 2000Error stack returned to user:ORA-02054: transaction 1.26.248 in-doubtORA-02059: ORA-2PC-CRASH-TEST-4 in commit commentALERT FILE V817.BE.ORACLE.COM:No entriesDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-1.26.248 |V817REP.BE.ORACLE.COM.89f6eafb|prepared|no |BE-ORACLE-|199226|.1.26.248 | | |NTbel449|DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—1.26.248 |in | |SCOTT |N1.26.248 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.4.2. What do we learn from the output – Scenario 4———————————————At V817REP.BE.ORACLE.COM:- prepared (to commit/rollback the local portion of transaction).The non-commit point site crashed after it actually prepared (COMMITCOMMENT ‘ORA-2PC-CRASH-TEST-4’).- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above).- no output from both dba_2pc_pending and dba_2pc_neighbors since ‘nothing’ happened at this end.2.4.3. Solution – Scenario 4———————rollback force ‘V817REP.BE.ORACLE.COM.89f6eafb.1.26.248’;-OR-rollback force ‘1.26.248’;DBA_2PC_PENDING@v817rep.be.oracle.com after the manual rollback:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-1.26.248 |V817REP.BE.ORACLE.COM.89f6eafb|forced |no |BE-ORACLE-|199226|.1.26.248 |rollback| |NTbel449 |To purge the in-doubt transaction entry:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘1.26.248’);The manual rollback action above can also be found in the alert file of v817rep.be.oracle.com:Thu Dec 14 15:06:51 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.1.26.248is local tran 1.26.248 (hex=01.1a.f8)change pending prepared tran, scn=199226 (hex=0.00030a3a)to pending forced rollback tran, scn=199226 (hex=0.00030a3a)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.5. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-5’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash commit point site before commit-> after step (07) above2.5.1. Observations – Scenario 5————————-ERROR RECEIVED:ORA-02054: transaction 3.7.279 in-doubtORA-02059: ORA-2PC-CRASH-TEST-5 in commit commentORA-02063: preceding line from V817ALERT FILE V817REP.BE.ORACLE.COM:Error 2059 trapped in 2PC on transaction 3.7.279. Cleaning up.Error stack returned to user:ORA-02054: transaction 3.7.279 in-doubtORA-02059: ORA-2PC-CRASH-TEST-5 in commit commentORA-02063: preceding line from V817Thu Dec 14 15:14:25 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.7.279is local tran 3.7.279 (hex=03.07.117)insert pending prepared tran, scn=199599 (hex=0.00030baf)ALERT FILE V817.BE.ORACLE.COM:Error 2059 trapped in 2PC on transaction 3.16.186. Cleaning up.Error stack returned to user:ORA-02059: ORA-2PC-CRASH-TEST-5 in commit commentDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-3.7.279 |V817REP.BE.ORACLE.COM.89f6eafb|prepared|no |BE-ORACLE-|199599|.3.7.279 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—3.7.279 |in | |SCOTT |N3.7.279 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.5.2. What do we learn from the output – Scenario 5———————————————At V817REP.BE.ORACLE.COM:- prepared (to commit/rollback the local portion of transaction).The commit point site crashed before it actually committed (COMMITCOMMENT ‘ORA-2PC-CRASH-TEST-5’) although the commit point site did receive the request to commit from the global coordinator.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above).- no output from both dba_2pc_pending and dba_2pc_neighbors since ‘nothing’ happened at this end.2.5.3. Solution – Scenario 5———————rollback force ‘V817REP.BE.ORACLE.COM.89f6eafb.3.7.279’;-OR-rollback force ‘3.7.279’;DBA_2PC_PENDING@v817rep.be.oracle.com after the manual rollback:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-3.7.279 |V817REP.BE.ORACLE.COM.89f6eafb|forced |no |BE-ORACLE-|199599|.3.7.279 |rollback| |NTbel449 |To purge the in-doubt transaction entry:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘3.7.279’);The manual rollback action above can also be found in the alert file ofv817rep.be.oracle.com:Thu Dec 14 15:19:38 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.7.279is local tran 3.7.279 (hex=03.07.117)change pending prepared tran, scn=199599 (hex=0.00030baf)to pending forced rollback tran, scn=199599 (hex=0.00030baf)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.6. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-6’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash commit point site after commit-> after step (08) above2.6.1. Observations – Scenario 6————————-ERROR RECEIVED:ORA-02054: transaction 3.38.281 in-doubtORA-02053: transaction 2.39.179 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-6 in commit commentORA-02063: preceding 2 lines from V817ALERT FILE V817REP.BE.ORACLE.COM:Error 2053 trapped in 2PC on transaction 3.38.281. Cleaning up.Error stack returned to user:ORA-02054: transaction 3.38.281 in-doubtORA-02053: transaction 2.39.179 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-6 in commit commentORA-02063: preceding 2 lines from V817Thu Dec 14 16:09:16 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.38.281is local tran 3.38.281 (hex=03.26.119)insert pending prepared tran, scn=201050 (hex=0.0003115a)ALERT FILE V817.BE.ORACLE.COM:Thu Dec 14 16:09:16 2000Error 2059 trapped in 2PC on transaction 2.39.179. Cleaning up.Error stack returned to user:ORA-02053: transaction 2.39.179 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-6 in commit commentThu Dec 14 16:09:16 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.38.281is local tran 2.39.179 (hex=02.27.b3)insert pending committed tran, scn=201052 (hex=0.0003115c)DBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-3.38.281 |V817REP.BE.ORACLE.COM.89f6eafb|prepared|no |BE-ORACLE-|201050|.3.38.281 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—3.38.281 |in | |SCOTT |N3.38.281 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-2.39.179 |V817REP.BE.ORACLE.COM.89f6eafb|committe|no |BE-ORACLE-|201052|.3.38.281 |d | |NTbel449 |DBA_2PC_NEIGHBORS@v817.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—2.39.179 |in |V817REP.BE.ORACLE.COM |SCOTT |C2.6.2. What do we learn from the Output – Scenario 6———————————————At V817REP.BE.ORACLE.COM:- prepared (to commit/rollback the local portion of transaction).The commit point site crashed after it actually committed(COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-6’). Because of the crash, the commit point site could not inform the global coordinator about this fact.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com)V817.BE.ORACLE.COM:- commit point site (as indicated above).- state ‘committed’. This node already committed the local transaction after which it crashed.- the global coordinator is still waiting for the ‘commit’ response from the commit point site.2.6.3. Solution – Scenario 6———————commit force ‘V817REP.BE.ORACLE.COM.89f6eafb.3.38.281′,’201052’;-OR-commit force ‘3.38.281’,’201052′;Note that we use the commit# of the commit point site(highest global commit#) to assure global integrity!DBA_2PC_PENDING@v817rep.be.oracle.com after the manual commit:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——|—|———-|——-3.38.281 |V817REP.BE.ORACLE.COM.89f6eafb|forced|no |BE-ORACLE-|201052|.3.38.281 |commit| |NTbel449 |To purge the in-doubt transaction entry at v817rep.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘3.38.281’);To purge the in-doubt transaction entry at v817.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘2.4.179’);The manual commit action above can also be found in the alert file ofv817rep.be.oracle.com:Thu Dec 14 16:12:35 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.38.281is local tran 3.38.281 (hex=03.26.119)change pending prepared tran, scn=201050 (hex=0.0003115a)to pending forced commit tran, scn=201052 (hex=0.0003115c)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.7. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-7’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash non-commit point site before commit-> after step (10) above2.7.1. Observations – Scenario 7————————-ERROR RECEIVED:ORA-02054: transaction 2.9.260 in-doubtORA-02059: ORA-2PC-CRASH-TEST-7 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Thu Dec 14 16:21:34 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.2.9.260is local tran 2.9.260 (hex=02.09.104)insert pending prepared tran, scn=201244 (hex=0.0003121c)Thu Dec 14 16:21:34 2000Error stack returned to user:ORA-02054: transaction 2.9.260 in-doubtORA-02059: ORA-2PC-CRASH-TEST-7 in commit commentALERT FILE V817.BE.ORACLE.COM:Thu Dec 14 16:21:34 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.2.9.260is local tran 1.46.176 (hex=01.2e.b0)insert pending committed tran, scn=201246 (hex=0.0003121e)DBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——–|—|———-|——-2.9.260 |V817REP.BE.ORACLE.COM.89f6eafb|prepared|no |BE-ORACLE-|201244|.2.9.260 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—2.9.260 |in | |SCOTT |N2.9.260 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-1.46.176 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|201246|89f6eafb.2.9.260 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—1.46.176 |in |V817REP.BE.ORACLE.COM |SCOTT |C2.7.2. What do we learn from the Output – Scenario 7———————————————At V817REP.BE.ORACLE.COM:- prepared (to commit/rollback the local portion of transaction).The non-commit point site crashed before it actually committed(COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-7’) while the commit point site has alreadycommitted.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above)- state ‘committed’. This node already committed the local transaction as asked by the global coordinator.- the global coordinator is waiting for the ‘commit’ response from the non-commit point site.2.7.3. Solution – Scenario 7———————commit force ‘V817REP.BE.ORACLE.COM.89f6eafb.2.9.260′,’201246’;-OR-commit force ‘2.9.260’,’201246′;Note that we use the commit# of the commit point site to assure global integrity!DBA_2PC_PENDING@v817rep.be.oracle.com after the manual commit:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|——————————|——|—|———-|——-2.9.260 |V817REP.BE.ORACLE.COM.89f6eafb|forced|no |BE-ORACLE-|201246|.2.9.260 |commit| |NTbel449 |To purge the in-doubt transaction entry at v817rep.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘2.9.260’);To purge the in-doubt transaction entry at v817.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘1.46.176’);The manual commit action above can also be found in the alert file ofv817rep.be.oracle.com:DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.2.9.260is local tran 2.9.260 (hex=02.09.104)change pending prepared tran, scn=201244 (hex=0.0003121c)to pending forced commit tran, scn=201246 (hex=0.0003121e)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.8. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-8’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash non-commit point site after commit-> after step (12) above2.8.1. Observations – Scenario 8————————-ERROR RECEIVED:ORA-02053: transaction 3.16.283 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-8 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Thu Dec 14 16:45:52 2000Error 2059 trapped in 2PC on transaction 3.16.283. Cleaning up.Error stack returned to user:ORA-02053: transaction 3.16.283 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-8 in commit commentThu Dec 14 16:45:52 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.16.283is local tran 3.16.283 (hex=03.10.11b)insert pending committed tran, scn=201607 (hex=0.00031387)ALERT FILE V817.BE.ORACLE.COM:Thu Dec 14 16:45:52 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.16.283is local tran 2.44.179 (hex=02.2c.b3)insert pending committed tran, scn=201607 (hex=0.00031387)DBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-3.16.283 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|201607|89f6eafb.3.16.283 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—3.16.283 |in | |SCOTT |N3.16.283 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-2.44.179 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|201607|89f6eafb.3.16.283 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—2.44.179 |in |V817REP.BE.ORACLE.COM |SCOTT |C2.8.2. What do we learn from the Output – Scenario 8———————————————At V817REP.BE.ORACLE.COM:- commited. This node has completed the transaction(COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-8’). The crash does not allow to confirm this towards the global coordinator.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above)- state ‘committed’. This node already committed the local transaction as asked by the global coordinator.2.8.3. Solution – Scenario 8——————–Since all sites committed the transaction already, it will do by purging the in-doubt transaction entries.To purge the in-doubt transaction entry at v817rep.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘3.16.283’);To purge the in-doubt transaction entry at v817.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘2.44.179’);~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.9. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-9’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash commit point site before forget -> after step (13) above2.9.1. Observations – Scenario 9————————-ERROR RECEIVED:ORA-02053: transaction 3.26.284 committed, some remote DBs may be in-doubtORA-02053: transaction 2.12.180 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-9 in commit commentORA-02063: preceding 2 lines from V817ALERT FILE V817REP.BE.ORACLE.COM:Error 2053 trapped in 2PC on transaction 3.26.284. Cleaning up.Error stack returned to user:ORA-02053: transaction 3.26.284 committed, some remote DBs may be in-doubtORA-02053: transaction 2.12.180 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-9 in commit commentORA-02063: preceding 2 lines from V817Fri Dec 15 09:41:56 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.26.284is local tran 3.26.284 (hex=03.1a.11c)insert pending committed tran, scn=202022 (hex=0.00031526)ALERT FILE V817.BE.ORACLE.COM:Fri Dec 15 09:41:55 2000Error 2059 trapped in 2PC on transaction 2.12.180. Cleaning up.Error stack returned to user:ORA-02053: transaction 2.12.180 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-9 in commit commentFri Dec 15 09:41:56 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.3.26.284is local tran 2.12.180 (hex=02.0c.b4)insert pending committed tran, scn=202022 (hex=0.00031526)DBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-3.26.284 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|202022|89f6eafb.3.26.284 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—3.26.284 |in | |SCOTT |N3.26.284 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-2.12.180 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|202022|89f6eafb.3.26.284 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—2.12.180 |in |V817REP.BE.ORACLE.COM |SCOTT |C2.9.2. What do we learn from the Output – Scenario 9———————————————At V817REP.BE.ORACLE.COM:- commited. This node has completed the transaction(COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-9’). The global coordinator is aware of this but cannot communicate this towards the commit point site.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above).- state ‘committed’. This node already committed the local transaction as asked by the global coordinator.2.9.3. Solution – Scenario 9———————Since all sites committed the transaction already, it will do by purging the in-doubt transaction entries.To purge the in-doubt transaction entry at v817rep.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘3.26.284’);To purge the in-doubt transaction entry at v817.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘2.12.180’);~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2.10. COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-10’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Crash non-commit point site before forget-> after step (14) above2.10.1. Observations – Scenario 10————————–ERROR RECEIVED:ORA-02053: transaction 1.10.255 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-10 in commit commentALERT FILE V817REP.BE.ORACLE.COM:Fri Dec 15 09:53:33 2000Error 2059 trapped in 2PC on transaction 1.10.255. Cleaning up.Error stack returned to user:ORA-02053: transaction 1.10.255 committed, some remote DBs may be in-doubtORA-02059: ORA-2PC-CRASH-TEST-10 in commit commentFri Dec 15 09:53:33 2000DISTRIB TRAN V817REP.BE.ORACLE.COM.89f6eafb.1.10.255is local tran 1.10.255 (hex=01.0a.ff)insert pending committed tran, scn=202241 (hex=0.00031601)ALERT FILE V817.BE.ORACLE.COM:No entriesDBA_2PC_PENDING@v817rep.be.oracle.com:LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#————-|———————-|———|—|———-|——-1.10.255 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|202241|89f6eafb.1.10.255 | | |NTbel449 |DBA_2PC_NEIGHBORS@v817rep.be.oracle.com:LOCAL_TRAN_ID|IN_OUT|DATABASE |DBUSER_OWNER |INT————-|——|————————-|—————|—1.10.255 |in | |SCOTT |N1.10.255 |out |V817.BE.ORACLE.COM |SCOTT |CDBA_2PC_PENDING@v817.be.oracle.com:no rows selectedDBA_2PC_NEIGHBORS@v817.be.oracle.com:no rows selected2.10.2. What do we learn from the Output – Scenario 10———————————————-At V817REP.BE.ORACLE.COM:- commited. This node has completed the transaction(COMMIT COMMENT ‘ORA-2PC-CRASH-TEST-10’). The commit point site alreadyforgot about the transaction.- V817.BE.ORACLE.COM (DATABASE column of DBA_2PC_NEIGHBORS) points to database link used to access information on a remote server.- V817.BE.ORACLE.COM is the commit point site (IN_OUT=out, INTERFACE=c,DATABASE=v817.be.oracle.com).V817.BE.ORACLE.COM:- commit point site (as indicated above).- state ‘committed’. This node already committed the local transaction as asked by the global coordinator.2.10.3. Solution – Scenario 10———————-Since all sites committed the transaction already, it will do by Purging the in-doubt transaction entries.To purge the in-doubt transaction entry at v817rep.be.oracle.com:execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘1.10.255’);5. Scripts used in this Note=========================SETUP_REM.SQL – setup remote db users/objects and privilegesSETUP_LOC.SQL – setup local db users/objects and privilegesCRASH_1.SQL – sample crash scenario for CRASHT TEST1COLLECT_INFO.SQL – queries to troubleshoot scenarios–>>>>>>>>>> Begin of setup_rem.sql <<<<<<<<<<–/* Execute at the remote site */connect sys/change_on_install@v817create user scott identified by tigerdefault tablespace userstemporary tablespace temp;grant dba to scott;grant force transaction,force any transaction to scott;/* To be able to crash a distributed transaction withCOMMIT COMMENT ‘ORA-2PC-CRASH-n’; */grant alter system to scott;/* To be able to do ALTER SYSTEM DISABLE/ENABLE DISTRIBUTEDRECOVERY; */grant delete on sys.pending_trans$ to scott;grant delete on sys.pending_sessions$ to scott;grant delete on sys.pending_sub_sessions$ to scott;/* To be able to use DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY */connect scott/tiger@v817create database link v817rep.be.oracle.com connect to scottidentified by tiger using ‘v817rep.be.oracle.com’;SET TERMOUT OFFSET ECHO OFFCREATE TABLE DEPT(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14) ,LOC VARCHAR2(13) ) ;CREATE TABLE EMP(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK’);INSERT INTO DEPT VALUES (20,’RESEARCH’,’DALLAS’);INSERT INTO DEPT VALUES (30,’SALES’,’CHICAGO’);INSERT INTO DEPT VALUES (40,’OPERATIONS’,’BOSTON’);INSERT INTO EMP VALUES (1000,’SMITH’,20);INSERT INTO EMP VALUES (1001,’ALLEN’,30);INSERT INTO EMP VALUES (1002,’WARD’,30);INSERT INTO EMP VALUES (1003,’JONES’,20);COMMIT;CREATE SYNONYM S_DEPT FOR DEPT@v817rep.be.oracle.com;CREATE SYNONYM S_EMP FOR EMP@v817rep.be.oracle.com;SET TERMOUT ONSET ECHO ON–>>>>>>>>>> End of setup_rem.sql <<<<<<<<<<—->>>>>>>>>> Begin of setup_loc.sql <<<<<<<<<<–/* Execute at the local site */connect sys/change_on_install@v817repcreate user scott identified by tigerdefault tablespace userstemporary tablespace temp;grant dba to scott;grant force transaction,force any transaction to scott;/* To be able to crash a distributed transaction with COMMIT COMMENT ‘ORA-2PC-CRASH-n’; */grant alter system to scott;/* To be able to do ALTER SYSTEM DISABLE/ENABLE DISTRIBUTED RECOVERY; */grant delete on sys.pending_trans$ to scott;grant delete on sys.pending_sessions$ to scott;grant delete on sys.pending_sub_sessions$ to scott;/* To be able to use DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY */connect scott/tiger@v817repcreate database link v817.be.oracle.com connect to scottidentified by tiger using ‘v817.be.oracle.com’;SET TERMOUT OFFSET ECHO OFFCREATE TABLE DEPT(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14) ,LOC VARCHAR2(13) ) ;CREATE TABLE EMP(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK’);INSERT INTO DEPT VALUES (20,’RESEARCH’,’DALLAS’);INSERT INTO DEPT VALUES (30,’SALES’,’CHICAGO’);INSERT INTO DEPT VALUES (40,’OPERATIONS’,’BOSTON’);INSERT INTO EMP VALUES (1000,’SMITH’,20);INSERT INTO EMP VALUES (1001,’ALLEN’,30);INSERT INTO EMP VALUES (1002,’WARD’,30);INSERT INTO EMP VALUES (1003,’JONES’,20);COMMIT;CREATE SYNONYM S_DEPT FOR DEPT@v817.be.oracle.com;CREATE SYNONYM S_EMP FOR EMP@v817.be.oracle.com;SET TERMOUT ONSET ECHO ON–>>>>>>>>>> End of setup_loc.sql <<<<<<<<<<—->>>>>>>>>> Begin of crash_1.sql <<<<<<<<<<–/* Crash Scenario 1 *//* Crash commit point site after collect */connect scott/tiger@v817repalter system disable distributed recovery;/* DML remote *//* object s_dept is a synonym for table dept@v817.be.oracle.com */insert into s_dept values (41,’SUPPORT’,’BRUSSELS’);/* DML local */insert into emp values (1041,’MULDER’,10);commit comment ‘ORA-2PC-CRASH-TEST-1’;Related Information===================Oracle8i Documentation – Distributed Database SystemsChapter 4 : Distributed Transactions ConceptsChapter 5 : Managing Distributed TransactionsOracle9i Documentation – Database Administrator’s GuideChapter 31 : Distributed Transactions ConceptsChapter 32 : Managing Distributed TransactionsOracle10g Documentation – Database Administrator’s GuideChapter 32 : Distributed Transactions ConceptsChapter 33 : Managing Distributed TransactionsOracle University Course:Oracle8i Distributed SystemsPart 1 : Distributed Database ImplementationChapter 7 : Monitoring Distributed Transactionsnote:13229.1note:100664.1note:67590.1note:62301.1bug:2191458** NOTE1: If using Oracle 9i or later and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails withORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use thefollowing workaround:In 9i:SQL> alter session set “_smu_debug_mode” = 4;SQL> commit; <– MUST BE ADDED TO PREVENT ORA-1453SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘local_tran_id’); NOTE: From 10g and later, _smu_debug_mode cannot be set at session level, it needs to be set at system level insteaad.Thus in 10g onwards use ALTER SYSTEM for setting _smu_debug_mode and reset this value to its original value afterwards; defaulat value is 0:To reset value to its default, use:SQL> alter system set “_smu_debug_mode” = 0;Still have questions ?To discuss this information further with Oracleexperts and industry peers, we encourage you to review, join or start a discussion via My OracleSupport Streams and Distributed Database CommunityEnjoy a short Video about Oracleôs Support Communities – to quickly understand itôs benefits for you right now (http://bcove.me/tlygjitz)NOTE: Direct or manual DML or dba_2pc* view or sys.pending_* tables is is unsupported and does not resolve any in-doubt transactionREFERENCESNOTE:13229.1 – Distributed Database, Transactions and Two Phase CommitNOTE:62301.1 – ALERT: Distributed Transactions may fail when using COMMIT/ROLLBACK in PLSQLNOTE:100664.1 – Master Note for Troubleshooting Oracle Managed Distributed TransactionsNOTE:67590.1 – PROCEDURE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY Specification