oacle ORA1Materialized View Log Younger Than Last Refresh ORACLE导入后物化视图无法使用.docx
- 文档编号:17501947
- 上传时间:2023-07-26
- 格式:DOCX
- 页数:8
- 大小:20.43KB
oacle ORA1Materialized View Log Younger Than Last Refresh ORACLE导入后物化视图无法使用.docx
《oacle ORA1Materialized View Log Younger Than Last Refresh ORACLE导入后物化视图无法使用.docx》由会员分享,可在线阅读,更多相关《oacle ORA1Materialized View Log Younger Than Last Refresh ORACLE导入后物化视图无法使用.docx(8页珍藏版)》请在冰点文库上搜索。
oacleORA1MaterializedViewLogYoungerThanLastRefreshORACLE导入后物化视图无法使用
DiagnosingORA-12034MaterializedViewLogYoungerThanLastRefresh(文档ID204127.1)
转到底部
InthisDocument
Purpose
TroubleshootingSteps
References
APPLIESTO:
OracleDatabase-EnterpriseEdition-Version9.2.0.1andlater
Informationinthisdocumentappliestoanyplatform.
PURPOSE
ThistroubleshootingguideisintendedtointroducethereadertothereasonsandORA-12034canberaisedwhenrefreshingamaterializedview,andtopresentthemostcommoncausesoftheerror. Italsogivessolutionsformanyoftheseproblemsandsometroubleshootingqueries.
TROUBLESHOOTINGSTEPS
1.ErrorDefinitionandDescription
ErrorDefinition
Oracle8iandbelow:
ORA-12034:
"snapshotlogon"%s"."%s"youngerthanlastrefresh"
Oracle9iandabove:
ORA-12034:
"materializedviewlogon"%s"."%s"youngerthanlastrefresh"
Cause:
Thematerializedviewlogwasyoungerthanthelastrefresh.
Action:
Acompleterefreshisrequiredbeforethenextfastrefresh.
Note:
Acompleterefreshcanbedoneusingthecommand:
executedbms_mview.refresh('"CORP"."NM_SV_RANGE"','C');
Liketheerrortextaboveindicates,ORA-12034isatimingissuethatoccurswhenperformingafastrefreshofamaterializedview.Totroubleshootit,it'simportanttogetsomebackgroundinformationregardingtherefreshmechanism.
Whenamaterializedviewlogiscreatedforamastertable,andamaterializedviewhasbeencreatedwiththeREFRESHFASToption,thefollowingtimestampswillbeusedwhenvalidatinglogage.
Atthematerializedviewsite:
-Informationaboutthelastrefreshtimeforeachmaterializedview.Thelastrefreshtimeisrecordedasthetimestampwhenthelastrefreshcompletedsuccessfully.
Atthemastertablesite:
-Informationaboutthelastrefreshtimeforeverymaterializedviewusingamaterializedviewlogonthatsite.
Thetimestampsatthemastersiteareusedfortwopurposes:
-Tomaintaininformationconcerningwhichrowsareneededtofastrefresheachindividualregisteredmaterializedview.
-Tomaintaininformationconcerningwhichrowscanbepurgedfromthematerializedviewlog.
Whenafastrefreshstarts,thelastrefreshtimestampfromthematerializedviewsitefortherefreshingmaterializedviewiscomparedtotheoldesttimestampofANYmaterializedviewusingthesamematerializedviewlogastheonecurrentlybeingrefreshed.Iftheoldesttimestampisnewerthanthematerializedviewsitetimestamp,ORA-12034israised.Bydoingthisitisensuredthatallchangedrowssincethelastrefreshwillberefreshed,andifthiscan'tbeensured,acompleterefreshisforced.There'snoexceptiontothisbehavior,andviolatingthismainrulewillresultORA-12034.
2.PotentialCausesofORA-12034
Thereareanumberofpotentialreasonsthatthelastrefreshtimeforamaterializedviewatthematerializedviewsitecanbeolderthantheoldesttimestampatthemastersite.Thesearedescribedbelow.
2.1Dropping/recreatingthematerializedviewlogonthemastertable.
IfamaterializedviewwascreatedattimeT1andmaterializedviewlogwascreatedattimeT2,wecan'tensurethatallchangesmadebetweenT1andT2willbeinthematerializedviewafterfastrefresh.Thereforecompleterefreshismandatory.
2.2Creatingthematerializedviewbeforethematerializedviewlog.
TheexplanationhereisthesameasinSection2.1.
2.3Thepreviousrefreshforthematerializedviewdidnotcompletesuccessfully.
Whenarefreshstarts,thelastrefreshtimeofthematerializedviewissetto'01-JAN-1950'.Thisguaranteesthatiftherefreshfailsforanyreason,thenanORA-12034errorwillbesignaledandacompleterefreshwillbeforced.Whentherefreshsucceeds,thisdateisupdatedtothepropertime.Ifitdoesn'tgetupdatedbecauseofsomefailureduringtherefresh,thenexttimetherefreshruns,'01-JAN-1950'isusedtovalidatethelogage.
2.4Creatingamaterializedviewtakeslongerthanthetimeittakesallothermaterializedviewscurrentlyusingthematerializedviewlogtorefresh.
Ifthereareothermaterializedviewsusingthematerializedviewlogonthemastertable,andalloftheseothermaterializedviewsstarttheirrefreshesAFTERthenewmaterializedviewcreationhasstartedbutcompletetheirrefreshesBEFOREthenewmaterializedviewcreationhascompleted,thenfastrefresheswillfailwithORA-12034.Materializedviewregistrationisbasedonthestartingtimeofthecreation,butasthelaststepoftheoperation.Ifthatstarttimeisolderthantheoldesttimestampcurrentlyregistered,thenewmaterializedviewwillnotberegistered.Acompleterefreshisrequiredtoregisterthematerializedview,butitmaynotavoidtheORA-12034errorthenexttimeafastrefreshisattempted.
Therearethreewaystoresolvethisproblem:
-Stoptherefreshofatleastoneothermaterializedviewthatisusingthematerializedviewlogbefore
creatingthenewone.
-Inproductionsystemthepreviousoptionmightnotbepossible.Forthissituation,atemporarymaterializedviewcanbecreatedwhichusesthesamelog.Ifthistemporarymaterializedviewisnotrefreshedwhilethenewmaterializedviewiscreated,thenewmaterializedviewcreationcancompletesuccessfully.
-Usedeploymenttemplatestocreatethematerializedviewenvironmentatmaterializedviewsites.Thisproblemwillnotoccurifdeploymenttemplatesareused.SeetheAdvancedReplicationdocumentationforinformationaboutdeploymenttemplates.
2.5CertainDDLchangestothemastertablehavebeenperformed.
DDLchangesthatmodifythemastertabledataarenotrecordedinthematerializedviewlog(truncate,alterpartition,droppartition,truncatepartition,exchangepartition,etc).
Truncationofamaterializedviewmastertableshouldbemadespecifyingthe'purgematerializedviewlog'clausetoforceacompleterefresh,otherwisethedatawillbeinconsistentbetweenthemastertableandthematerializedview.However,specifyingthisclausewillcausetheORA-12034errorifanattemptismadetofastrefreshbeforecompleterefreshing.ThisisOracle'swayofforcingthecompleterefresh.See Note179469.1 foramoredetaileddescriptionofthis.
NotethatforPartitionMaintenanceOperations(PMOPS)suchasalterpartition,droppartition,truncatepartition,etc,ifthematerializedviewisdistributed(i.e.themastertableandthematerializedviewarelocatedondifferentdatabases)thenanORA-12034errorisexpectedafterPMOPStoforceacompleterefresh.ImprovementsinthePCT(PartitionChangeTracking)mechanismsinceOracleserverversion9ihaveallowedmostlocalrefreshestousefastPCTrefreshandthereforeallowafastrefreshtooccurevenafterPMOPS(avoidingtheORA-12034errorandcompleterefresh).PCTisnotsupportedoverdatabaselinks.
2.6Mastertablereorganization.
Asexplainedin Note254593.1,anORA-12034willberaisedwhenattemptingtorefreshaROWID-basedmaterializedviewafteritsmastertablehasbeenreorganized.ThemastertablereorganizationprocessgeneratesnewROWIDswhichcannotbemappedtothosestoredforthematerializedview.ThereforeacompleterefreshisforcedviatheORA-12034.
2.7Materializedviewregistrationfailedatthemastersite.
Thecontentsofthematerializedviewlog(s)dependonothermaterializedviewrefreshes.Thereforeifthematerializedviewwasnotregisteredsuccessfully,thecontentsofthemastertablematerializedviewlog(s)mightnotincludeallthechangesneeded.Acompleterefreshwillregisterthematerializedviewifitisnotalreadyregistered.
2.8IncorrectconversionofamaterializedviewlogfromROWIDtoprimarykey.
WhenconvertingamaterializedviewlogfromROWIDtoprimarykeytype,certainstepshavetobeperformedinordertomaintainthefastrefreshcapability.See Note254593.1 fordetails.
2.9PointinTimeRecovery(PITR)performedonamaterializedviewsite.
PITRorincompleterecoverycanresultinthetimestampsatthematerializedviewsitebeingolderthanthosemaintainedatthemastertablesite.Materializedviewfastrefreshfirstidentifiesthechangesthatoccurredinthemastertablesincethemostrecentrefreshofthematerializedviewandthenappliesthemtothematerializedview.FastrefreshisthereforenotapplicableafteraPITRonthematerializedviewsite.Thematerializedviewlogisyoungerthanthelastfastrefreshsothematerializedviewrequiresacompleterefreshtoresynchronizethematerializedviewlogsandmaterializedviewwiththemastertable.
2.10Cloningofmaterializedviewdatabase.
Ifthematerializedviewdatabaseisclonedandrenamedonthesamenetworkasthemastertabledatabase,andthematerializedviewsaredroppedontheclone,thenrefreshofthematerializedviewsontheoriginalmaterializedviewdatabasecanbeginfailingwithORA-12034.Thisisbecausedroppingthematerializedviewontheclonedsitecausedthematerializedviewtobecomeunregisteredonthemastersite,andacompleterefreshisnecessary.Afteracloningamaterializedviewsite,thedatabaselinktothemastersiteshouldbedroppedpriortodroppinganymaterializedviews.
FastrefreshingamaterializedviewinacloneddatabasebeforeithasbeendroppedandrecreatedcancauseanORA-12034intheoriginaldatabase,astheoriginaldatabaseandcloneddatabasehavethesamesnapshot_id. Oncethematerializedviewhasbeenrecreatedinacloneddatabase,ithasitsownsnapshot_id,anditwillnotaffecttheoriginaldatabase.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oacle ORA1Materialized View Log Younger Than Last Refresh ORACLE导入后物化视图无法使用 ORACLE 导入 物化 视图 无法 使用
链接地址:https://www.bingdoc.com/p-17501947.html