Flashback Technology Recovering from Logical Corruptions.docx
《Flashback Technology Recovering from Logical Corruptions.docx》由会员分享,可在线阅读,更多相关《Flashback Technology Recovering from Logical Corruptions.docx(19页珍藏版)》请在冰豆网上搜索。
FlashbackTechnologyRecoveringfromLogicalCorruptions
FlashbackTechnology:
RecoveringfromLogicalCorruptions
ThischapterdescribeshowtousetheflashbackfeaturesofOracletoretrievelostdataindatarecoveryscenarios.Thischapterincludesthefollowingsections:
∙OracleFlashbackTechnology:
Overview
∙OracleFlashbackQuery:
RecoveringattheRowLevel
∙OracleFlashbackTable:
ReturningIndividualTablestoPastStates
∙OracleFlashbackDrop:
UndoaDROPTABLEOperation
∙OracleFlashbackDatabase:
AlternativetoPoint-In-TimeRecovery
∙UsingOracleFlashbackFeaturesTogetherinDataRecovery:
Scenario
OracleFlashbackTechnology:
Overview
OracleFlashbackTechnologyprovidesasetoffeaturesthatsupportviewingandrewindingdatabackandforthintime.Theflashbackfeaturesofferthecapabilitytoquerypastversionsofschemaobjects,queryhistoricaldata,analyzedatabasechanges,orperformself-servicerepairtorecoverfromlogicalcorruptionswhilethedatabaseisonline.
∙OracleFlashbackQueryfeatureletsyouspecifyatargettimeandthenrunqueriesagainstyourdatabase,viewingresultsastheywouldhaveappearedatthattime.Torecoverfromanunwantedchangelikeanerroneousupdatetoatable,ausercouldchooseatargettimebeforetheerrorandrunaquerytoretrievethecontentsofthelostrows.
∙OracleFlashbackVersionQueryletsyouviewalltheversionsofalltherowsthateverexistedinoneormoretablesinaspecifiedtimeinterval.Youcanalsoretrievemetadataaboutthedifferingversionsoftherows,includingstarttime,endtime,operation,andtransactionIDofthetransactionthatcreatedtheversion.Thisfeaturecanbeusedbothtorecoverlostdatavaluesandtoauditchangestothetablesqueried.
∙OracleFlashbackTransactionQueryletsyouviewchangesmadebyasingletransaction,orbyallthetransactionsduringaperiodoftime.
∙OracleFlashbackTablereturnsatabletoitsstateatapreviouspointintime.Youcanrestoretabledatawhilethedatabaseisonline,undoingchangesonlytothespecifiedtable.
∙OracleFlashbackDropreversestheeffectsofaDROPTABLEstatement.
∙OracleFlashbackDatabaseprovidesamoreefficientalternativetodatabasepoint-in-timerecovery.Whenyouuseflashbackdatabase,yourcurrentdatafilesreverttotheircontentsatapasttime.Theresultismuchliketheresultofapoint-in-timerecoveryusingdatafilebackupsandredologs,butyoudonothavetorestoredatafilesfrombackupandyoudonothavetore-applyasmanyindividualchangesintheredologsasyouwouldhavetodoinconventionalmediarecovery.
FlashbackTable,FlashbackQuery,FlashbackTransactionQueryandFlashbackVersionQueryallrelyonundodata,recordsoftheeffectsofeachupdatetoanOracledatabaseandvaluesoverwrittenintheupdate.UsedprimarilyforsuchpurposesasprovidingreadconsistencyforSQLqueriesandrollingbacktransactions,theseundorecordscontaintheinformationrequiredtoreconstructdataasitstoodatapasttimeandexaminetherecordofchangessincethatpasttime.
SeeAlso:
∙OracleDatabaseConceptsandOracleDatabaseAdministrator'sGuideformoreinformationonundodataandautomaticundomanagement
∙"OracleFlashbackDrop:
UndoaDROPTABLEOperation"formoreinformationonFlashbackDropandtherecyclebin
∙OracleDatabaseApplicationDeveloper'sGuide-FundamentalsformoreinformationonFlashbackQuery,FlashbackTransactionQueryandFlashbackVersionQuery
OracleFlashbackQuery:
RecoveringattheRowLevel
Inadatarecoverycontext,itisusefultobeabletoquerythestateofatableataprevioustime.If,forinstance,youdiscoverthatat12:
30PM,anemployee'JOHN'hadbeendeletedfromyourEMPLOYEEtable,andyouknowthatat9:
30AMthatemployee'sdatawascorrectlystoredinthedatabase,youcouldquerythecontentsofthetableasofatimebeforethedeletiontofindoutwhatdatahadbeenlost,and,ifappropriate,re-insertthelostdatainthedatabase.
QueryingthepaststateofthetableisachievedusingtheASOFclauseoftheSELECTstatement.Forexample,thefollowingqueryretrievesthestateoftheemployeerecordfor'JOHN'at9:
30AM,April4,2003:
SELECT*FROMEMPLOYEEASOFTIMESTAMP
TO_TIMESTAMP('2003-04-0409:
30:
00','YYYY-MM-DDHH:
MI:
SS')
WHEREname='JOHN';
RestoringJohn'sinformationtothetableEMPLOYEErequiresthefollowingupdate:
INSERTINTOemployee
(SELECT*FROMemployeeASOFTIMESTAMP
TO_TIMESTAMP('2003-04-0409:
30:
00','YYYY-MM-DDHH:
MI:
SS')
WHEREname='JOHN');
Themissingrowisre-createdwithitspreviouscontents,withminimalimpacttotherunningdatabase.
SeeAlso:
∙OracleDatabaseApplicationDeveloper'sGuide-FundamentalsforamoreextensivediscussionoftheuseoftheSELECT...ASOFSQLstatementandextensiveexamplesofitsuse.
∙OracleDatabaseSQLReferenceformoredetailsonthesyntaxoftheSELECT...ASOFformoftheSELECTstatement.
OracleFlashbackTable:
ReturningIndividualTablestoPastStates
OracleFlashbackTableprovidestheDBAtheabilitytorecoveratableorsetoftablestoaspecifiedpointintimeinthepastveryquickly,easily,andwithouttakinganypartofthedatabaseoffline.Inmanycases,FlashbackTableeliminatestheneedtoperformmorecomplicatedpoint-in-timerecoveryoperations.FlashbackTablerestorestableswhileautomaticallymaintainingassociatedattributessuchascurrentindexes,triggersandconstraints,andnotrequiringtheDBAtofindandrestoreapplication-specificproperties.UsingFlashbackTablecausesthecontentsofoneormoreindividualtablestoreverttotheirstateatsomepastSCNortime.
FlashbackTableusesinformationintheundotablespacetorestorethetable.Thisprovidessignificantbenefitsovermediarecoveryintermsofeaseofuse,availabilityandfasterrestorationofdata.
FormoreinformationonAutomaticUndoManagement,seeOracleDatabaseAdministrator'sGuide.
PrerequisitesforUsingFlashbackTable
TheprerequisitesforperformingaFLASHBACKTABLEoperationareasfollows:
∙YoumusthavebeengrantedtheFLASHBACKANYTABLEsystemprivilegeoryoumusthavetheFLASHBACKobjectprivilegeonthetable.
∙YoumusthaveSELECT,INSERT,DELETE,andALTERprivilegesonthetable.
∙UndoinformationretainedintheundotablespacemustgofarenoughbackintimetosatisfythespecifiedtargetpointintimeorSCNfortheFLASHBACKTABLEoperation.
∙RowmovementmustbeenabledonthetableforwhichyouareissuingtheFLASHBACKTABLEstatement.YoucanenablerowmovementwiththefollowingSQLstatement:
∙ALTERTABLEtableENABLEROWMOVEMENT;
PerformingFlashbackTable
ThefollowingSQL*PlusstatementperformsaFLASHBACKTABLEoperationonthetableemployee:
FLASHBACKTABLEemployeeTOTIMESTAMP
TO_TIMESTAMP('2003-04-0409:
30:
00',`YYYY-MM-DDHH24:
MI:
SS');
Theemployeetableisrestoredtoitsstatewhenthedatabasewasatthetimespecifiedbythetimestamp.
YoucanalsospecifythetargetpointintimefortheFLASHBACKTABLEoperationusinganSCN:
FLASHBACKTABLEemployeeTOSCN123456;
ThedefaultforaFLASHBACKTABLEoperationisfortriggersonatabletobedisabled.Thedatabasedisablestriggersforthedurationoftheoperation,andthenreturnsthemtothestatethattheywereinbeforetheoperationwasstarted.Ifyouwishforthetriggerstostayenabled,thenusetheENABLETRIGGERSclauseoftheFLASHBACKTABLEstatement,asshowninthisexample:
FLASHBACKTABLEt1TOTIMESTAMP'2003-03-0312:
05:
00'ENABLETRIGGERS;
ThefollowingscenarioistypicalofthekindoflogicalcorruptionwhereFlashbackTablecouldbeused:
At17:
00anHRadministratordiscoversthatanemployee"JOHN"ismissingfromtheEMPLOYEEtable.Thisemployeewaspresentat14:
00,thelasttimesheranareport.Someoneaccidentallydeletedtherecordfor"JOHN"between14:
00andthepresenttime.SheusesFlashbackTabletoreturnthetabletoitsstateat14:
00,asshowninthisexample:
FLASHBACKTABLEEMPLOYEESTOTIMESTAMP
TO_TIMESTAMP('2003-04-0414:
00:
00','YYYY-MM-DDHH:
MI:
SS')
ENABLETRIGGERS;
SeeAlso:
OracleDatabaseSQLReferenceforasimpleFlashbackTablescenario
OracleFlashbackDrop:
UndoaDROPTABLEOperation
OracleFlashbackDropreversestheeffectsofaDROPTABLEoperation.Theintentionbehindthisfeatureistoprovideuserswitharecoverymechanismforanaccidentaldropofatable.FlashbackDropissubstantiallyfasterthanotherrecoverymechanisms(suchaspoint-in-timerecovery)andalsodoesnotleadtoanylossofrecenttransactions.
Whenyoudropatable,thedatabasedoesnotimmediatelyremovethespaceassociatedwiththetable.Instead,thetableisrenamedand,alongwithanyassociatedobjects,itisplacedintheRecycleBinofthedatabase.TheFlashbackDropoperationrecoversthetablefromtherecyclebin.
TounderstandhowtouseOracleFlashbackDrop,youmustalsounderstandhowtherecyclebinworks,andhowtoaccessandmanageitscontents.
Thissectioncoversthefollowingtopics:
∙WhatistheRecycleBin?
∙HowTablesandOtherObjectsArePlacedintheRecycleBin
∙NamingConventionforObjectsintheRecycleBin
∙ViewingandQueryingObjectsintheRecycleBin
∙RecycleBinCapacityandSpacePressure
∙PurgingObjectsfromtheRecycleBin
WhatistheRecycleBin?
Therecyclebinisalogicalcontainerforalldroppedtablesandtheirdependentobjects.Whenatableisdropped,thedatabasewillstorethetable,alongwithitsdependentobjectsintherecyclebinsothattheycanberecoveredlater.Dependentobjectswhicharestoredintherecyclebinincludeindexes,constraints,triggers,nestedtables,LOBsegmentsandL