Oracle英文版培训课件之Backup and Recovery：Les-05-flashback

UsingOracleFlashbackTechnologiesObjectivesAftercompletingthislesson,youshouldbeableto:UseFlashbackDroptorecoveradroppedtableUseFlashbackQuerytoviewthecontentsofthedatabaseasofanysinglepointintimeUseFlashbackVersionsQuerytoviewversionsofarowovertimeUseFlashbackTransactionQuerytoviewtransactionhistoryofarowUseFlashbackTabletorestorethecontentsofatabletoaspecificpointinthepastConfigureFlashbackDatabasePerformFlashbackDatabasetoapointintimeUsingOracleFlashbackTechnologyFALSEUndodataTransactionInvestigateseveralhistoricalstatesofdataTxFALSEUndodataVersionCompareversionsofarowFALSEUndodataQueryComparecurrentdatawithdatafromthepastUndodataRecyclebinFlashbacklogsUsesTableDatabaseObjectLevelTRUEDatabaseTruncatetable;UndesiredmultitablechangesmadeTRUETableUpdatewiththewrongWHEREclauseDropFlashback

TechnologyTRUEDroptableAffects

DataScenarioExamplesFlashbackDropandtheRecycleBinDropatableRealizeamistakewasmadeRECYCLEBIN=ONDROPTABLEemployees;FLASHBACKTABLE

employees

TOBEFOREDROP;UnderstandingtheRecycleBinDROPTABLEemployees;BIN$zbjra9wy==$0EMPLOYEES_PKEMPLOYEESRecycle

binBIN$zbjrBdpw==$0USER_OBJECTSBIN$zbjrBdpw==$0EMPLOYEES

BIN$zbjra9wy==$0EMPLOYEES_PKObjectsare:RenamedNotmovedDBA_FREE_SPACERestoringTablesfromtheRecycleBinRestoredroppedtablesanddependentobjects.IfmultipleRecycleBinentrieshavethesameoriginalname:Useunique,system-generatednamestorestoreaparticularversionTherestoredtableislastin,firstout(LIFO)Renametheoriginalnameifthatnameiscurrentlyused.FLASHBACKTABLEtable_name

TOBEFOREDROP[RENAMETOnew_name];AutomaticSpaceReclamationintheRecycleBinBIN$zbjrBdpw==$0

BIN$zbjra9wy==$0

BIN$zbjra9wy==$0BIN$zbjrBdpw==$0123RecyclebinDBA_FREE_SPACE-RECYCLEBINAutoextendManualSpaceReclamationintheRecycleBinPURGE{TABLEtable_name|INDEXindex_name}PURGETABLESPACEts_name[USERuser_name]PURGE[USER_|DBA_]RECYCLEBINBypassingtheRecycleBinDROPTABLESPACEts_name

[INCLUDINGCONTENTS];DROPUSERuser_name[CASCADE];DROPTABLEtable_name[PURGE];QueryingtheRecycleBinSELECTowner,original_name,object_name,type,ts_name,droptime,related,spaceFROMdba_recyclebinWHEREcan_undrop='YES';SELECToriginal_name,object_name,type,ts_name,droptime,related,spaceFROMuser_recyclebinWHEREcan_undrop='YES';SQL>SHOWRECYCLEBINQueryingDatainDroppedTablesSELECT...FROM"BIN$zbjrBdpw==$0"WHERE...Recycle

binUSER_INDEXESYESINDEX_NAMEDROPPEDNOSALES_PKUSER_TABLESTABLE_NAMEDROPPEDYESNOSALESBIN$zbjrBdpw==$0EMPLOYEES

BIN$zbjra9wy==$0EMPLOYEES_PKUsingFlashbackTechnologytoQueryDataFlashbackQueryQueryalldataataspecifiedpointintime.FlashbackVersionsQuerySeeallversionsofarowbetweentwotimes.Seethetransactionsthatchangedtherow.FlashbackTransactionQuerySeeallchangesmade

byatransaction.Tx3Tx1Tx2TimeFlashbackConfiguringUndoforFlashbackSYSTEMtablespaceUNDOTBS1tablespaceUNDO_RETENTIONinitializationparameterFlashbackQuery:Overviewt1t2SELECTemployee_id,salaryFROMemployeesASOFTIMESTAMP<t1>WHEREemployee_id=200EmployeesEmployeesUnwanted

updatesUsetoqueryalldataataspecifiedpointintime.FlashbackQuery:Example11:0011:10UPDATEemployeesSETsalary=(SELECTsalaryFROMemployeesASOFTIMESTAMPTO_TIMESTAMP('2005-05-04

11:00:00',

'yyyy-mm-dd

hh24:mi:ss')WHEREemployee_id=200)WHEREemployee_id=200EmployeesEmployeessalary=4,400Employeessalary=4,400salary=4,840FlashbackVersionsQuery:Overviewt1t2Tx1Tx2SELECTversions_xid,salaryFROMemployeesVERSIONSBETWEENTIMESTAMP<t1>and<t2>WHEREemployee_id=200;Tx0Tx0Tx1Tx2EmployeesEmployeesEmployees200UsingEnterpriseManagertoPerform

FlashbackVersionsQueryFlashbackVersionsQuery:ConsiderationsTheVERSIONSclausecannotbeusedtoquery:ExternaltablesTemporarytablesFixedtablesViewsTheVERSIONSclausecannotspanDDLcommands.Segmentshrinkoperationsarefilteredout.FlashbackTransactionQuery:OverviewUndo

SQLFLASHBACK_TRANSACTION_QUERYDBAUserErroneous

DMLUsingEnterpriseManagertoPerform

FlashbackTransactionQueryFlashbackTransactionQuery:ConsiderationsDDLcommandsareseenasdictionaryupdates.Droppedobjectsappearasobjectnumbers.Droppedusersappearasuseridentifiers.FlashbackTable:OverviewFlashbackTablerecoverstablestoaspecificpointintime.FlashbackTableisanin-placeoperation.Thedatabasestaysonline.UserErroneous

DMLsFlashedback

tablesFlashbackTableUsingFlashbackTable,youcanrecoveratableortablestoaspecificpointintimewithoutrestoringabackup.DataisretrievedfromtheundotablespacetoperformaFlashbackTableoperation.TheFLASHBACK

TABLEprivilegeisrequiredtoflashbackatable.Rowmovementmustbeenabledonthetablethatyouareperformingtheflashbackoperationon.EnablingRowMovementonaTableALTERTABLEemployeesENABLEROWMOVEMENT;PerformingFlashbackTableFLASHBACKTABLEhr.employeesTOTIMESTAMP TO_TIMESTAMP('2005-05-05

05:32:00', 'YYYY-MM-DD

HH24:MI:SS');FlashbackTable:ConsiderationsTheFLASHBACK

TABLEcommandexecutesasasingletransaction,acquiringexclusiveDMLlocks.Statisticsarenotflashedback.Currentindexesanddependentobjectsaremaintained.FlashbackTableoperations:CannotbeperformedonsystemtablesCannotspanDDLoperationsGenerateundoandredodataFlashbackDatabase:OverviewTheFlashbackDatabaseoperation:WorkslikearewindbuttonforthedatabaseCanbeusedincasesoflogicaldatacorruptionsmadebyusersThedatabaseiscorrupted.Thedatabaseis“rewound.”FlashbackDatabase:You“pressthe

rewindbutton.”Usersgenerateerrors.FlashbackDatabaseArchitectureFlashback

bufferRedolog

bufferSGALGWRRVWRFlashback

logsRedo

logs……Buffercache1Logblock

before

images

periodically.Backoutchanges

todatabaseusingbeforeimages.Doforwardmediarecovery.

Every

change2ConfiguringFlashbackDatabase1.ConfiguretheFlashRecoveryArea.3.EnableFlashbackDatabase.2.Settheretention

target.SQL>SHUTDOWNIMMEDIATE;SQL>STARTUPMOUNTEXCLUSIVE;SQL>ALTERSYSTEMSET 2 DB_FLASHBACK_RETENTION_TARGET=2880SCOPE=BOTH;SQL>ALTERDATABASEFLASHBACKON;SQL>ALTERDATABASEOPEN;UsingEnterpriseManagertoConfigure

FlashbackDatabaseEnableflashbackloggingandspecifytheFlashRecoveryArea.FlashbackDatabase:ExamplesRMAN>FLASHBACKDATABASETOTIME=2>"TO_DATE('2004-05-2716:00:00',3>'YYYY-MM-DDHH24:MI:SS')";RMAN>FLASHBACKDATABASETOSCN=23565;RMAN>FLASHBACKDATABASE2>TOSEQUENCE=223THREAD=1;SQL>FLASHBACKDATABASE2TOTIMESTAMP(SYSDATE-1/24);SQL>FLASHBACKDATABASETOSCN53943;SQL>FLASHBACKDATABASETORESTOREPOINTb4_load;UsingEnterpriseManagertoPerform

FlashbackDatabaseSelecttheobjectandoperationtype.UsingEnterpriseManagertoPerform

FlashbackDatabaseStartingtheRecoveryWizard:UsingEnterpriseManagertoPerform

FlashbackDatabaseFlashbackDatabaseConsiderationsWhentheFlashbackDatabaseoperationcompletes,openthedatabase:Inread-onlymodetoverifythatthecorrecttargettimeorSCNwasusedWithaRESETLOGSoperationtoallowforDMLYoucannotuseFlashbackDatabaseinthefollowingsituations:Thecontrolfilehasbeenrestoredorre-created.Atablespacehasbeendropped.Adatafilehasbeenshrunk.UsetheTOBEFORERESETLOGStoflashbacktobeforethelastRESETLOGSoperation.MonitoringFlashbackDatabaseTomonitortheabilitytomeetyourretentiontarget:ViewtheFlashRecoveryAreadiskquota:Determinethecurrentflashbackwindow:

MonitorloggingintheFlashbackDatabaselogs:SQL>SELECTestimated_flashback_size,2flashback_size

3FROMv$flashback_database_log;SQL>SELECT*

2FROMv$flashback_database_stat;SQL>SELECToldest_flashback_scn,2oldest_flashback_time3FROMv$flashback_database_log;MonitoringFlashbackDatabase(continued)

(notesonlyslide)MonitoringFlashbackDatabaseTuningConsiderationsforFlashbackDatabasePerformanceoverheadisapproximately2%.Sequentialandoften,bulkwritesareusedforwritingflashbackdata.Inwell-configuredsystems,noprocessneedstowaitforwritingofflashbacklogs.Bestpractices:UseafastfilesystemfortheFlashRecoveryArea,preferablywithoutoperatingsystemfilecaching.ConfigureenoughdiskspindlesforthefilesystemthatholdstheFlashRecoveryArea.Configurethefilesystemontopofstripedstoragevolumes,witharelativelysmallstripesizesuchas128KB.Forlarge,productiondatabases,settheLOG_BUFFERinitializationparametertoatleast8MB.TuningConside