Flashback Technology Recovering from Logical CorruptionsWord文件下载.docx

Flashback Technology Recovering from Logical CorruptionsWord文件下载.docx

《Flashback Technology Recovering from Logical CorruptionsWord文件下载.docx》由会员分享，可在线阅读，更多相关《Flashback Technology Recovering from Logical CorruptionsWord文件下载.docx（19页珍藏版）》请在冰豆网上搜索。

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'

at9:

30AM,April4,2003:

SELECT*FROMEMPLOYEEASOFTIMESTAMP

TO_TIMESTAMP（'

2003-04-0409:

30:

00'

'

YYYY-MM-DDHH:

MI:

SS'

）

WHEREname='

;

RestoringJohn'

sinformationtothetableEMPLOYEErequiresthefollowingupdate:

INSERTINTOemployee

（SELECT*FROMemployeeASOFTIMESTAMP

）;

Themissingrowisre-createdwithitspreviouscontents,withminimalimpacttotherunningdatabase.

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

`YYYY-MM-DDHH24:

Theemployeetableisrestoredtoitsstatewhenthedatabasewasatthetimespecifiedbythetimestamp.

YoucanalsospecifythetargetpointintimefortheFLASHBACKTABLEoperationusinganSCN:

FLASHBACKTABLEemployeeTOSCN123456;

ThedefaultforaFLASHBACKTABLEoperationisfortriggersonatabletobedisabled.Thedatabasedisablestriggersforthedurationoftheoperation,andthenreturnsthemtothestatethattheywereinbeforetheoperationwasstarted.Ifyouwishforthetriggerstostayenabled,thenusetheENABLETRIGGERSclauseoftheFLASHBACKTABLEstatement,asshowninthisexample:

FLASHBACKTABLEt1TOTIMESTAMP'

2003-03-0312:

05:

ENABLETRIGGERS;

ThefollowingscenarioistypicalofthekindoflogicalcorruptionwhereFlashbackTablecouldbeused:

At17:

00anHRadministratordiscoversthatanemployee"

JOHN"

ismissingfromtheEMPLOYEEtable.Thisemployeewaspresentat14:

00,thelasttimesheranareport.Someoneaccidentallydeletedtherecordfor"

between14:

00andthepresenttime.SheusesFlashbackTabletoreturnthetabletoitsstateat14:

00,asshowninthisexample:

FLASHBACKTABLEEMPLOYEESTOTIMESTAMP

2003-04-0414:

00:

'

）

OracleDatabaseSQLReferenceforasimpleFlashbackTablescenario

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