仓酷云

标题: MYSQL教程之ORA-01591妨碍处置 [打印本页]

作者: 只想知道    时间: 2015-1-16 22:43
标题: MYSQL教程之ORA-01591妨碍处置
表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。
凌晨到办公室听同事说表被锁了,一试,发明表中某字段为1111111的行都被锁了,SELECT都不可。报毛病ORA-01591,翻开TOAD的KnowledgeeXpert,形貌很少,只是说因为散布式事件毛病而形成锁定。扣问同事,今天经由过程一个存储历程挪用另外一个存储历程出了毛病,尔后者经由过程通明网关insert一些数据到SQlServer数据库。
当即想到翻开OEM,谁晓得事与愿违,进进锁,基本没发明相干的工具被锁定,入手下手有点忧郁。转而反省会话,该用户有5个会话,都是INACTIVE,不论三七二十一,全体杀失落。了局仍旧,而且锁也没有呈现。远程上岸上主机,发明CPU和历程都一般,也没有发明通明网关历程挂逝世(之前曾发明TG4SQL在无营业量时也会呈现25%摆布的CPU,挂逝世)。
俄然想到看看alert.log,经由细心搜刮,终究发明:

WedNov1700:00:042004
Errorsinfiled:oracleadminxdcjudumpxdcj_j006_3020.trc:
ORA-12012:主动实行功课82堕落
ORA-01591:锁定已被有成绩的分派事件处置6.5.887985挂起
ORA-06512:在line6

这恰是堕落的中央,往前追溯:

TueNov1617:35:042004
Error28500trappedin2PContransaction6.5.887985.Cleaningup.
Errorstackreturnedtouser:
ORA-02054:事件处置6.5.887985有成绩
ORA-28500:毗连ORACLE到非Oracle体系时前往此信息:
[TransparentgatewayforMSSQL]
ORA-02063:紧接着2lines(源于ZSMOS_CRM)
TueNov1617:35:042004
DISTRIBTRANQDCJ.US.ORACLE.COM.5ae32328.6.5.887985
islocaltran6.5.887985(hex=06.05.d8cb1)
insertpendingpreparedtran,scn=6606197672830(hex=602.2010cb7e)
TueNov1617:35:072004
Errorsinfiled:oracleadminxdcjdumpxdcj_reco_3024.trc:
ORA-28500:connectionfromORACLEtoanon-Oraclesystemreturnedthismessage:
[TransparentgatewayforMSSQL][Microsoft][ODBCSQLServerDriver][SQLServer]用户RECOVER登录失利。(SQLState:28000;SQLCode:18456)
ORA-02063:preceding2linesfromZSMOS_CRM

TueNov1617:35:122004
Errorsinfiled:oracleadminxdcjdumpxdcj_reco_3024.trc:
ORA-28500:connectionfromORACLEtoanon-Oraclesystemreturnedthismessage:
[TransparentgatewayforMSSQL][Microsoft][ODBCSQLServerDriver][SQLServer]用户RECOVER登录失利。(SQLState:28000;SQLCode:18456)
ORA-02063:preceding2linesfromZSMOS_CRM

这就是事发地址了。看来是今天下战书远程事件失利,可是又没有前往形成散布式事件挂逝世,从而锁定了行。终究找到了具体的毛病ORA-02054,进进TOAD一查,说是要守候大概提交该事件,但是怎样操纵呢。仍是翻开官方文档搜刮相干内容,在AdminstratorGuide中发明以下内容:
DiscoveringProblemswithaTwo-PhaseCommit
Theuserapplicationthatcommitsadistributedtransactionisinformedofaproblembyoneofthefollowingerrormessages:

ORA-02050:transactionIDrolledback,
someremotedbsmaybein-doubt
ORA-02051:transactionIDcommitted,
someremotedbsmaybein-doubt
ORA-02054:transactionIDin-doubt


Arobustapplicationshouldsaveinformationaboutatransactionifitreceivesanyoftheaboveerrors.Thisinformationcanbeusedlaterifmanualdistributedtransactionrecoveryisdesired.

Noactionisrequiredbytheadministratorofanynodethathasoneormorein-doubtdistributedtransactionsduetoanetworkorsystemfailure.TheautomaticrecoveryfeaturesofOracletransparentlycompleteanyin-doubttransactionsothatthesameoutcomeoccursonallnodesofasessiontree(thatis,allcommitorallrollback)afterthenetworkorsystemfailureisresolved.

Inextendedoutages,however,youcanforcethecommitorrollbackofatransactiontoreleaseanylockeddata.Applicationsmustaccountforsuchpossibilities.

DeterminingWhethertoPerformaManualOverride
Overrideaspecificin-doubttransactionmanuallyonlywhenoneofthefollowingsituationsexists:

Thein-doubttransactionlocksdatathatisrequiredbyothertransactions.ThissituationoccurswhentheORA-01591errormessageinterfereswithusertransactions.
Anin-doubttransactionpreventstheextentsofarollbacksegmentfrombeingusedbyothertransactions.Thefirstportionofanin-doubtdistributedtransactionslocaltransactionIDcorrespondstotheIDoftherollbacksegment,aslistedbythedatadictionaryviewsDBA_2PC_PENDINGandDBA_ROLLBACK_SEGS.
Thefailurepreventingthetwo-phasecommitphasestocompletecannotbecorrectedinanacceptabletimeperiod.Examplesofsuchcasesincludeatelecommunicationnetworkthathasbeendamagedoradamageddatabasethatrequiresalongrecoverytime.
Normally,youshouldmakeadecisiontolocallyforceanin-doubtdistributedtransactioninconsultationwithadministratorsatotherlocations.Awrongdecisioncanleadtodatabaseinconsistenciesthatcanbedifficulttotraceandthatyoumustmanuallycorrect.

Iftheconditionsabovedonotapply,alwaysallowtheautomaticrecoveryfeaturesofOracletocompletethetransaction.Ifanyoftheabovecriteriaaremet,however,consideralocaloverrideofthein-doubttransaction.

看来是倡议差未几,前面Oracle老是试图登录SQlServer就是要主动恢复,但是总不乐成。观察视图DBA_2PC_PENDING的确发明了该事件的陈迹。要如何操纵呢?

ManuallyCommittinganIn-DoubtTransaction
Beforeattemptingtocommitthetransaction,ensurethatyouhavetheproperprivileges.Notethefollowingrequirements:

Ifthetransactionwascommittedby...Thenyoumusthavethisprivilege...
You
FORCETRANSACTION

Anotheruser
FORCEANYTRANSACTION


CommittingUsingOnlytheTransactionID
ThefollowingSQLstatementcommitsanin-doubttransaction:

COMMITFORCEtransaction_id;


Thevariabletransaction_idistheidentifierofthetransactionasspecifiedineithertheLOCAL_TRAN_IDorGLOBAL_TRAN_IDcolumnsoftheDBA_2PC_PENDINGdatadictionaryview.

Forexample,assumethatyouqueryDBA_2PC_PENDINGanddeterminethatLOCAL_TRAN_IDforadistributedtransactionis1:45.13.

YouthenissuethefollowingSQLstatementtoforcethecommitofthisin-doubttransaction:

COMMITFORCE1.45.13;

CommittingUsinganSCN
Optionally,youcanspecifytheSCNforthetransactionwhenforcingatransactiontocommit.Thisfeatureallowsyoutocommitanin-doubttransactionwiththeSCNassignedwhenitwascommittedatothernodes.

Consequently,youmaintainthesynchronizedcommittimeofthedistributedtransactionevenifthereisafailure.SpecifyanSCNonlywhenyoucandeterminetheSCNofthesametransactionalreadycommittedatanothernode.

Forexample,assumeyouwanttomanuallycommitatransactionwiththefollowingglobaltransactionID:

SALES.ACME.COM.55d1c563.1.93.29

First,querytheDBA_2PC_PENDINGviewofaremotedatabasealsoinvolvedwiththetransactioninquestion.NotetheSCNusedforthecommitofthetransactionatthatnode.SpecifytheSCNwhencommittingthetransactionatthelocalnode.Forexample,iftheSCNis829381993,issue:

COMMITFORCESALES.ACME.COM.55d1c563.1.93.29,829381993;

SeeAlso:
Oracle9iSQLReferenceformoreinformationaboutusingtheCOMMITstatement


ManuallyRollingBackanIn-DoubtTransaction
Beforeattemptingtorollbackthein-doubtdistributedtransaction,ensurethatyouhavetheproperprivileges.Notethefollowingrequirements:

Ifthetransactionwascommittedby...Thenyoumusthavethisprivilege...
You
FORCETRANSACTION

Anotheruser
FORCEANYTRANSACTION


ThefollowingSQLstatementrollsbackanin-doubttransaction:

ROLLBACKFORCEtransaction_id;


Thevariabletransaction_idistheidentifierofthetransactionasspecifiedineithertheLOCAL_TRAN_IDorGLOBAL_TRAN_IDcolumnsoftheDBA_2PC_PENDINGdatadictionaryview.

Forexample,torollbackthein-doubttransactionwiththelocaltransactionIDof2.9.4,usethefollowingstatement:

ROLLBACKFORCE2.9.4;

因而上岸数据库
COMMITFORCE6.5.887985;
然后检察DBA_2PC_PENDING发明形态已改成COMMITFORCE,SELECT该表相干行,统统一般。至此,妨碍办理。
整体来看,间接INSERT...TABLENAME@SQLDBLK仍是很伤害的,赶上不克不及一般前往就出成绩了。Oracle的文档是保举利用包大概存储历程来办理,今后倡议同事改用此办法,今朝已测试经由过程。
与数据库相关的流程的逐渐标准化,使得解决方案提供商能以更便捷的方式提供服务、部署应用程序、规划容量和管理资源。DBaaS模式还有助于减少数据和数据库的冗余度并提升整体服务质量。
作者: 精灵巫婆    时间: 2015-1-19 21:45
我是新手,正在学习数据库和操作系统,深感理论的泛广,唯有一步一步来,但是又感觉时间不够,收集了很多资料却总是没能认真的看完,希望有一个讨论板块,大家共同解决,共同分享,共同努力
作者: 活着的死人    时间: 2015-1-26 23:31
对于数据库来说,查询是数据库的灵魂,那么SQL查询效率究竟效率如何呢?下文将带对SQL查询的相关问题进行讨论,供您参考。
作者: 只想知道    时间: 2015-2-4 22:20
对递归类的树遍历很有帮助。个人感觉这个真是太棒了!阅读清晰,非常有时代感。
作者: 透明    时间: 2015-2-10 21:37
从底层原理到表层引用,书籍多的很。个人认为没有什么那本书好?这样的说法。主要看和个人的学习方法是否适合。
作者: 柔情似水    时间: 2015-3-1 14:39
如果你是从“学习某一种数据库应用软件,从而获得应聘的资本和工作机会”的角度来问的话。
作者: 愤怒的大鸟    时间: 2015-3-10 18:49
而SQLServer如果能像Oracle一样可以为登陆分配如:5%的cpu,10%的内存。就可以解决这个漏洞。
作者: 再现理想    时间: 2015-3-17 09:10
不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?
作者: 再见西城    时间: 2015-3-24 05:16
对于数据库来说,查询是数据库的灵魂,那么SQL查询效率究竟效率如何呢?下文将带对SQL查询的相关问题进行讨论,供您参考。




欢迎光临 仓酷云 (http://ckuyun.com/) Powered by Discuz! X3.2