Oracle Distributed TRANSACTION Summary-REPRINT

Basic concept Local Coordinator: In a distributed transaction, you must refer to the data on other nodes to complete the site of this part of your operation. Global Coordinator: The initiator of the distributed transaction, responsible for coordinating this distributed transaction. Commit Point site: In a distributed transaction, the site that first performs a commit or rollback operation. In general, the site where the critical data is stored should be the commit point site. Because commit point site is different from other sites, it never enters the prepared state, so there is no in-doubt transaction. You can set the initialization parameter Commit_point_strength, in a distributed transaction, the commit point Site is determined based on the size of the value, and the state information for the distribution of things exists in the database. In general, the key database as a commit point site, the Commit_point_strength value of the database is a commit point site, in the distribution of things first submitted distributed commit the 3 phases of distribution of things in the two-phase commit three processes: 1. Preparation phase (PREPARE PHASE) • Local database Global Coordinator commit notifications to other databases • Compare the SCN number for all databases and use the highest SCN number as the global SCN number for the distribution • All databases write online logs · Add a distributed lock to a table that distributes things modified to prevent being read and written • Each database sends a prepared notification to global coordinator that all databases participating in the distribution must be prepared to enter the next phase. 2. Commit phase (commit PHASE) • Local Database Global Coordinator Notify Commit point site to commit first. After commit point site commits, releases its occupied resources, notifies global coordinator to complete the submission • Local Database global coordinator notifies other database submissions • Submit node appends a message to the log, Indicates that the distribution has completed the submission and informs global coordinator. At this point, the data for all databases remains consistent. 3. Logoff phase (Forget PHASE) • Local database Global Coordinator notifies the commit point site that all databases have been committed commit point site clears records and status information for distributed things and informs Global Coordinator Global COordinator clears records and status information for locally distributed things at this point the two-phase commit of distributing things is complete.   If the database or network is abnormal before the two-phase commit is completed, the application will report an error and the distribution is in the IN_DOUBT state. Once the database or network is back to normal, the system (RECO process) automatically handles the distribution of the IN_DOUBT state. Some situations require the administrator to manually handle the distribution of the IN_DOUBT state: · In_doubt State of the distribution of things, the key table is locked, resulting in the application does not work properly two important views Dba_2pc_pending: List all the outstanding transactions, this view is empty before the end of the pending transactions, resolved this is also emptied.

Column Name	Description
local_tran_id	The local transaction identity, in the format Integer.integer.ingeger. When the local_tran_id and global_tran_id of a connection are the same, the node is the global coordinator for the transaction.
global_tran_id	The global transaction identity, in the form of:global_db_name.db_hex_id.local_tran_id, where db_hex_id is the hexadecimal number used to identify the database eight characters, and the common thing ID is the same for each node of the distributed transaction.
State	Table for instructions
MIXED	"YES" means that some of the transactions have been committed on one node and are rolled back on the other node.
Tran_comment	Transaction, or if a transaction name is used, the name of the transaction appears here when the party is committed
Host	Host Name
commit#	The number of global commits for committed transactions

Description of the State column of the dba_2pc_pending If a transaction is manually rolled back by the DBA on the local node.

column value	description
connecting	usually Case, only the global coordinator and the local coordinator use this entry, and the node collects information from other database services before it can determine if it is ready.
prepared	node is ready, possibly or may not have prepared message Notifies the local coordinator, but at this point, the node has not received the submitted request, remains in a state of readiness, and controls any local resources necessary to commit the transaction.
commited	node (any type) has committed a transaction, but the firm contains Other nodes may not be committed, that is, the transaction is still pending on one or more other nodes.
forced commit	dba after judgment, you can forcibly submit the pending Transaction, this item is generated when a transaction is manually committed by the DBA on the local node
forced Abor (rollback)	DBA is judged, you can force rollback of the pending transaction, resulting in the project

       dba_2pc_neighbors: Lists all the outstanding transactions (from the remote client) and the sent (to the remote server) Also indicates whether the local node is the commit point site of the transaction.   TD valign= "Top" width= "279", ibid.

local_tran_id
in_out	Get transaction for in, send out transaction for out
database	pair won The name of the client database that refers to the local node information, and the name of the database link that is used to access information on the remote server for the sent transaction
dbuser_owner
interface	' C ' represents the commit information, ' N ' represents the prepared state A message or a request that requests read-only submissions. When ' In_out ' is out, ' C ' indicates that the remote site of the connection is the submission point site and knows whether to commit or break. ' N ' indicates that the local node is notifying the remote node that it is ready.   When ' in_out ' is in, ' C ' indicates that a local node or a remote database that is being sent is the commit point site, ' N ' indicates that the local node is notifying the remote node that it is ready.

  General steps for handling suspended transactions 1,   Check alert file, found similar to the following error:       ORA-1591 "lock held by In-doubt Distributed transaction%s "       ORA-2062" distributed recovery received dbid X, Expected y "       ORA-2068" following severe error from%s%s "2,   Verify that the network is normal, Dblink whether the distributed transaction is currently being used in valid, V$dblink, and Gv$dblink queries. 3,   query view dba_2pc_pending, query hanging transaction information:    select local_tran_id, global_tran_id, State, MIXED, HOST, commit#       from dba_2pc_pending       WHERE LOCAL_ tran_id = '??. ';        If there is no record, the RECO process has automatically processed the transaction. 4,   Query the view on all nodes dba_2pc_neighbors5,   get all the commit_point_strength value of the node, the value of the largest is the commit point site, that is, the earliest committed points, If a hanging transaction occurs at commit point site, its state determines the status of the entire distributed transaction. Whether the hanging transaction should commit force or rollback force is determined by this node. 6,   Check the State column of dba_2pc_pending, if it is commited, it means that the local database submission has been successful. Other nodes need to be forced to commit according to the local transaction number and the largest commit#. Usage is as follows:        svrmgr> COMMIT Force ' your local TransactionID in this node ', ' highest SCN from already Commi tted site ';       svrmgr> commit force ' 1.13.5197 ', ' 88123887 '; 7,   if commit A state of point site other than commited indicates that the commit point site did not commit successfully and the distributed transaction requires a forced rollback. The maximum commit# of all nodes is no longer required here. Use the following:       svrmgr> ROLLBACK force ' your local TransactionID on this node ';        svrmgr> ROLLBACK force ' 1.13.5197 '; 8,   clear dba_2pc_pending and dba_2pc_ Neighbers related records. After a general distributed transaction is automatically restored, the view content is automatically cleared, and if it is a manually committed transaction, it needs to be manually purged with the Dbms_transaction package, as shown in the following table: determining when to use dbms_transaction

State column	Global transaction State	Local transaction state	The usual action	Selectable actions
Collecting	Rollback	Rollback	No	Purge_lost_db_entry (only if the automatic reply does not resolve the transaction)
Committed	Committed	Committed	No	Purge_lost_db_entry (only if the automatic reply does not resolve the transaction)
Prepared	Unknown	Prepared	No	Force commit or rollback
Forcedcommit	Unknown	Committed	No	Purge_lost_db_entry (only if the automatic reply does not resolve the transaction)
Forced rollback	Unknown	Rollback	No	Purge_lost_db_entry (only if the automatic reply does not resolve the transaction)
Forced commit	Mixed	Committed	Remove inconsistencies manually, and then use purge_mixed
forced rollback	mixed	rollback	remove inconsistencies manually, then use purge_mixed	 

   Test record ¡        set DB1 commit_point_strength to 1,db2 Commit_point_ Strength is 2,DB2 for commit point site. Perform 100 insert loops on ¡        DB1, DB2, and each loop inserts a test table in DB1 and DB2 with a distributed transaction. Intermediate reboot DB2 server. At this point db1 the query for the test table appears with the following error:sql> select COUNT (1) from Temp.my_table;select count (1) from Temp.my_table*error on line 1:o Ra-01591:lock held by In-doubt Distributed transaction 7.30.7415 [[email protected] bdump]$ tail-f alert_ntes Pay.logtue mar  4 14:14:28 2008DISTRIB TRAN 1234.4f000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000  is local tran 7.30.7415 (HEX=07.1E.1CF7)   Insert pending prepared Tran, scn=934346533 (hex=0.37b0 FF25) The 2 views related to distributed transactions in    DB1 are as follows: Select A.* from Dba_2pc_pending a where local_tran_id= ' 7.30.7415 ';          local_tran_id    global_tran_id STATE  &NBsp;    mixed     advice    tran_comment  FAIL_TIME        force_time         retry_time   os_user  os_terminal         host         Db_user commit#1       7.30.7415          4660.4f000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000  prepared    no                         2008-3-4 14:14:28                  2008-3-4 14:22:56         zhenxingzhai       zhaizhenxing         netease\zhaizhenxing                934346533 where state has the following states: Collecting, prepared, committed, forced commit, Or forced rollbackmixed indicates whether partial commit, partial rollback advice:cfor commit,rfor rollback, Elsenull select a.* from Dba_2pc_neighbors a where local_tran_id= ' 7.30.7415 ';      local_tran_id    in_out    database       dbuser_owner     INTERFACE       dbid         sess#         branch1       7.30.7415   in      nulljavaxa.oracle.com        temp       N       JAVAXA_ORCL 1         01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 This view illustrates the data source 1 Enter the connection information. Because data source 2 is not connected through Dblink, it does not have a record of it.  ¡        DB2 Restart Query my_tab:sql> select COUNT (1) from my_tab;   COUNT (1)----------        75 ¡         Because there is no record in dba_2pc_pending and Dba_2pc_neighbers in DB2, and DB2 is a commit point site, no record means no action, so DB1 should be like DB2, To enforce rollback. sql> conn/as sysdbaconnected.sql> rollback force ' 7.30.7415 ';  rollback complete. sql> Select count ( From Temp.my_table;  count (----------)         75   The rollback process for suspicious transactions is shown in the alert log of DB1: Tue mar  4 15:14:31 2008DISTRIB TRAN 1234.4f000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000  is local tran 7.30.7415 (HEX=07.1E.1CF7)   Change pending PREpared Tran, scn=934346533 (HEX=0.37B0FF25)   to     pending forced rollback Tran, scn= 934346533 (HEX=0.37B0FF25)  ¡        rollback, the status in two views is changed to the following:  select a.* From dba_2pc_pending a where local_tran_id= ' 9.33.5992 ';              local_tran_id    global_tran_id state       MIXED      advice    tran_comment  fail_time       FORCE_TIME          retry_time   os_user  os_terminal          host        Db_user commit#1        7.30.7415         4660.4f000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000&nbsP Forced rollback  no                         2008-3-4 14:14:28         2008-3-4 15:14:31        2008-3-4 15:20:07         zhenxingzhai         zhaizhenxing       netease\zhaizhenxing               934346533 select a.* from Dba_2pc_neighbors a where local_tran_id= ' 9.33.5992 ';      LOCAL_ tran_id    in_out    database       DBUSER_OWNER      interface      dbid          sess#        branch1       7.30.7415&NBsp;  in      nulljavaxa.oracle.com        TEMP        n      JAVAXA_ORCL 1          0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000  ¡        removal of records in Dba_2pc_pending and Dba_2pc_ Neighbors: (1) DISABLE Distributed Recovery sql> ALTER SYSTEM DISABLE distributed RECOVERY; System altered.  (2) puege (empty) in-doubt transaction entry: sql> exec dbms_transaction. Purge_lost_db_entry (' 7.30.7415 ');P L/sql procedure successfully completed. (3) Commit; (4) then enable distributed recovery:sql> ALTER SYSTEM ENABLE distributed recovery;  Distributed transaction related information note:1012842.102ora-2019 ORA-2058 ORA-2068 ora-2050:failed Distributed transactions note:100664.1how to troubleshoot distributed transactions note:274321.1while Trying to Commit or Rollback a PenDing Transaction Getting Errors ora-02058,ora-01453,ora-06512 note:126069.1manually resolving in-doubt Transactions:different scenarios [url]http://www.itk.ilstu.edu/docs/oracle/server.101/b10739/ds_txns.htm# I1007721[/url]

This article is from "Handsome Boy's Blog" blog, please make sure to keep this source http://zhaizhenxing.blog.51cto.com/643480/134750

Oracle Distributed TRANSACTION Summary-REPRINT