2pc oracle dba_2pc_pending,关于dba_2pc_pending

PURPOSE-------Thepurposeofthisbulletinistoassistsupportanalystsinunderstandingandresolvingthestrandeddba_2pc_entries.SCOPE&APPLICATION-------------------Theintendedaudiencearesupportanalystshavinggoodexperienceondistributeddatabases.TITLE-----Howtoresolvestrandeddba_2pc_pendingentriesContents========1.ProblemDescription2.Solutions2.1Dba_2pcentrieswithoutacorrespondingtransaction2.2Distributedtransactionwithoutcorrespondingdba_2pcentries1.ProblemDescription:

=======================Asaresultofafailedcommitofadistributedtransaction,someentriescanbeleftindba_2pcviews,i.e.dba_2pc_pendinganddba_2pc_neighbors.TheRECOprocesscheckstheseviewstorecoverthefailedtxn.However,insomecasesRECOcannotperformtherecovery.Onecauseisthatallsitesinvolvedinthetransactionnotbeingaccessibleatthesametime.Anothercauseisdba_2pcviewsbeinginconsistentwiththetransactiontable,whichisthetopicofthisarticle.Thiscausecanfurtherbeclassifiedasfollows:1.dba_2pcviewshaveentriesforanon-existentdistributedtransaction2.Thereisadistributedtransactionforwhichtherearenoentriesindba_2pcviewsSolutionstoeachsubclassisprovidedintherestofthearticle.2.Solutions:

=============2.1Dba_2pcentrieswithoutacorrespondingtransaction-------------------------------------------------------Inthiscasedba_2pcviewsshowdistributedtransactionsbuttherearenotxnsinreality.Ifthestateofthetransactioniscommitted,rollbackforcedorcommitforcedthenthisisnormalanditcanbecleanedupusingdbms_transaction.purge_lost_db_entryHowever,ifthestateofthetransactionisPREPAREDandthereisnoentryinthetransactiontableforitthenthisentrycanbecleanedupmanuallyasfollows:settransactionuserollbacksegmentSYSTEM;deletefromsys.pending_trans$wherelocal_tran_id= ;deletefromsys.pending_sessions$wherelocal_tran_id= ;deletefromsys.pending_sub_sessions$wherelocal_tran_id= ;commit;Example:

--------Thefollowingqueryreportsadist.txn.inpreparedstateselectlocal_tran_id,statefromdba_2pc_pending;LOCAL_TRAN_IDSTATE---------------------- ----------------1.92.66874preparedGiventhatatransactionidiscomposedof triple,'1.92.66874'islocatedinrollbacksegment# 1. To find out the list ofactivetransactionsinthatrollbacksegment,use:SELECTKTUXEUSN,KTUXESLT,KTUXESQN,/* Transaction ID */KTUXESTAStatus,KTUXECFLFlagsFROMx$ktuxeWHEREktuxesta!='INACTIVE'ANDktuxeusn=1; <==thisistherollbacksegment#norowsselectedItisnotpossibletorollbackforceorcommitforcethistransaction.rollbackforce'1.92.66874';ORA-02058:nopreparedtransactionfoundwithID1.92.66874Hence,wehavetomanuallycleanupthattransaction:settransactionuserollbacksegmentSYSTEM;deletefromsys.pending_trans$wherelocal_tran_id='1.92.66874';deletefromsys.pending_sessions$wherelocal_tran_id='1.92.66874';deletefromsys.pending_sub_sessions$wherelocal_tran_id='1.92.66874';commit;2.2Distributedtransactionwithoutcorrespondingdba_2pcentries-----------------------------------------------------------------Inthiscasedba_2pcviewsareemptybutusersarereceivingdistributedtxnrelatederrors,e.g.ORA-2054,ORA-1591.Normallysuchacaseshouldnotappearandifitisreproducibleabugshouldbefiled.Hereisthelistofseveralalternativesolutionsthatcanbeusedinthiscase:a.Performincompleterecoveryb.TruncatetheobjectsreferencedbythattransactionandimportthemrefertoNote76603.1forthis.c.Use_corrupted_rollback_segmentsparametertodropthatrollbacksegmentasdiscussedinNote106638.1.d.Insertdummyentriesintodba_2pcviewsandeithercommitorrollbackforcethedistributedtransactionThefirstthreesolutionsarediscussedinBackupandRecoverymanualsandinthenotesreferredabove.Inthe4thsolutionadummyentryisinsertedintothedictionarysothatthetransactioncanbemanuallycommittedorrolledback.NotethatRECOwillnotbeabletoprocessthistxnanddistributedtxnrecoveryshouldbedisabledbeforeusingthismethod.Furthermore,pleasetakeaBACKUPofyourdatabasebeforeusingthismethod.Thefollowingexampledescribeshowtodiagnoseandresolvethiscase.SupposethatusersarereceivingORA-1591:lockheldbyin-doubtdistributedtransaction1.92.66874andthefollowingqueryreturnsnorows:selectlocal_tran_id,statefromdba_2pc_pendingwherelocal_tran_id='1.92.66874';norowsselectedFurthermorequeryingtherollbacksegmentshowsthat1.92.66874remainsinpreparedstateSELECTKTUXEUSN,KTUXESLT,KTUXESQN,/* Transaction ID */KTUXESTAStatus,KTUXECFLFlagsFROMx$ktuxeWHEREktuxesta!='INACTIVE'ANDktuxeusn=1;/* <== Replace this value with your txn undo seg#

Which is displayed in the first part of

the transaction ID */KTUXEUSNKTUXESLTKTUXESQNSTATUSFLAGS---------- ---------- ---------- ---------------- ------------------------19266874PREPAREDSCO|COL|REV|DEADTryingtomanuallycommitorrollbackthistransactioncommitforce'1.92.66874';ORA-02058:nopreparedtransactionfoundwithID1.92.66874raisesORA-02058sincedba_2pcviewsareempty.Inordertousecommitforceorrollbackforceadummyrecordshouldbeinsertedintopending_trans$asfollows:altersystemdisabledistributedrecovery;insertintopending_trans$(LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)values('1.92.66874',/* <== Replace this with your local tran id */306206,/*                                         */'XXXXXXX.12345.1.2.3',/*  These values can be used without any    */'prepared','P',/*  modification. Most of the values are   */hextoraw('00000001'),/*  constant.                              */hextoraw('00000000'),/*                                         */0,sysdate,sysdate);insertintopending_sessions$values('1.92.66874',/* <==Replace only this with your local tran id */1,hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);commit;commitforce'1.92.66874';Ifcommitforceraisesanerrorthennotetheerrormessageandexecutethefollowing:deletefrompending_trans$wherelocal_tran_id='1.92.66874';deletefrompending_sessions$wherelocal_tran_id='1.92.66874';commit;altersystemenabledistributedrecovery;Otherwiserunaltersystemenabledistributedrecovery;andpurgethedummyentryfromthedictionary,usingconnect/assysdbaaltersessionset"_smu_debug_mode"=4;/* if automatic undo management

is being used */commit;/* this is to prevent the ORA-01453 in purge_lost_db_entry call */execdbms_transaction.purge_lost_db_entry('1.92.66874')http://solo.bloghome.cn/posts/182666.htmlhttp://www.oraclefans.cn/forum/showtopic_tree.jsp?boardcode=o5&hit=1026&rootid=7153http://www.oraclefans.cn/forum/showtopic_tree.jsp?boardcode=o5&hit=908&rootid=7179