11g新特性之flashback課件

FlashbackTechnology,LogMiner,andDataPumpEnhancements.ObjectivesAftercompletingthislesson,youshouldbeableto:UseFlashbackDataArchivetocreate,protect,andusehistorydataPrepareyourdatabaseCreate,change,anddropaflashbackdataarchiveViewflashbackdataarchivemetadataUseFlashbackTransactionBackoutSetupFlashbackTransactionprerequisitesQuerytransactionswithandwithoutdependenciesChoosebackoutoptionsandflashbacktransactionsUseEnterpriseManagerLogMinerUseDataPumplegacymode.FlashbackDataArchive:OverviewTransparentlytrackshistoricalchangestoallOracledatainahighlysecureandefficientmanner:“Secure”NopossibilitytomodifyhistoricaldataRetainedaccordingtoyourspecificationsAutomaticallypurgedbasedonyourretentionpolicy“Efficient”SpecialkerneloptimizationstominimizeperformanceoverheadofcapturinghistoricaldataStoredincompressedformintablespacestominimizestoragerequirementsCompletelytransparenttoapplicationsEasytosetup.FlashbackDataArchive:OverviewOriginaldatainUndodataDMLoperationsbuffercacheFlashbackdataarchivesstoredintablespaces1year2years5yearsExample:Threeflashbackdataarchiveswithretentionof:ForretentionrequirementsexceedingundoFBDA.Historyorarchivetables:-Compressedstorage-WithautomaticdigitalshreddingFlashbackDataArchive:ArchitectureBuffercacheDMLchangesusedbyFBDAOldvaluesUndoFBDAFlashbackdataarchives123.PreparingYourDatabaseCreateoneormoretablespacesfordataarchivesandgrantQUOTAonthetablespaces.GranttheFLASHBACK

ARCHIVEADMINISTERsystemprivilegetocreateandmaintainflashbackarchivestothearchiveadministrator.GranttheFLASHBACKARCHIVEobjectprivilege(toenablehistorytrackingforspecifictablesinthegivenflashbackarchives)tothearchiveuser.GrantFLASHBACKandSELECTprivilegestoqueryspecificobjectsasnecessary...Configuringundo:Createanundotablespace(default:automaticallyextensibletablespace)EnableAutomaticUndoManagement(11gdefault)Understandautomatictuningofundo:Fixed-sizetablespace:AutomatictuningforbestretentionAutomaticallyextensibleundotablespace:Automatictuningforlongest-runningqueryRecommendationforFlashback:Fixed-sizeundotablespacePreparingYourDatabase..FlashbackDataArchive:WorkflowCreatetheflashbackdataarchive.Optionally,specifythedefaultflashbackdataarchive.Enabletheflashbackdataarchive.Viewflashbackdataarchivedata..UsingFlashbackDataArchive1. Createtheflashbackdataarchive:2. Enablehistorytrackingforatableinthearchive:3. Viewthehistoricaldata:CREATEFLASHBACKARCHIVEfla1TABLESPACEtbs1QUOTA10GRETENTION5YEAR;

ALTERTABLEinventoryFLASHBACKARCHIVEfla1;SELECTproduct_number,product_name,countFROMinventoryASOFTIMESTAMPTO_TIMESTAMP('2007-01-0100:00:00','YYYY-MM-DDHH24:MI:SS');.ConfiguringaDefaultFlashbackDataArchiveUsingadefaultflashbackarchive:1. Createadefaultflashbackdataarchive:2. Enablehistorytrackingforatable:

Note:Thenameoftheflashbackdataarchiveisnotneededbecausethedefaultoneisused.3. Disablehistorytracking:CREATEFLASHBACKARCHIVEDEFAULTfla2TABLESPACEtbs1QUOTA10GRETENTION2YEAR;

ALTERTABLEstock_dataFLASHBACKARCHIVE;ALTERTABLEstock_dataNOFLASHBACKARCHIVE;.FillingtheFlashbackDataArchiveSpaceWhathappenswhenyourflashbackdataarchivegetsfull?90%spaceusageRaisingoferrors:ORA-55623

"FlashbackArchive\"%s\"isblockingandtrackingonalltablesissuspended"ORA-55617"FlashbackArchive\"%s\"runsoutofspaceandtrackingon\"%s\"issuspended"GeneratingalertlogentrySuspendingtracking.MaintainingFlashbackDataArchivesAddingspace:Changingretentiontime:Purgingdata:Droppingaflashbackdataarchive:ALTERFLASHBACKARCHIVEfla1ADDTABLESPACEtbs3QUOTA5G;ALTERFLASHBACKARCHIVEfla1PURGEBEFORETIMESTAMP(SYSTIMESTAMP-INTERVAL'1'day);

ALTERFLASHBACKARCHIVEfla1MODIFYRETENTION2YEAR;DROPFLASHBACKARCHIVEfla1;.FlashbackDataArchive:ExamplesToenforcedigitalshredding:Toaccesshistoricaldata:Torecoverdata:CREATEFLASHBACKARCHIVEtax7_archiveTABLESPACEtbs1RETENTION7YEAR;

SELECTsymbol,stock_priceFROMstock_dataASOFTIMESTAMPTO_TIMESTAMP('2006-12-3123:59:00','YYYY-MM-DDHH24:MI:SS')

INSERTINTOemployeesSELECT*FROMemployeesASOFTIMESTAMPTO_TIMESTAMP('2007-06-1211:30:00','YYYY-MM-DDHH24:MI:SS')WHEREname='JOE';

.FlashbackDataArchive:DDLRestrictionsUsinganyofthefollowingDDLstatementsonatableenabledforFlashbackDataArchivecausesanORA-55610error:ALTERTABLEstatementthatdoesanyofthefollowing:PerformspartitionorsubpartitionoperationsConvertsaLONGcolumntoaLOBcolumnIncludesanUPGRADETABLEclause,withorwithoutanINCLUDINGDATAclauseDROPTABLEstatementTRUNCATETABLEstatement.FlashbackDataArchive:SupportingTransparentSchemaEvolutionAdditionalDDLsupported:DROP,RENAME,andMODIFYcolumnDROPandTRUNCATEpartitionRENAMEandTRUNCATEtableFlashbackqueriesworkacrossDDLchanges:Outputispresentedaccordingly.AllotherDDLnotautomaticallysupportedDropColumnAddColumnAddColumntimeFlashbackVersionQuery11.2.FlashbackDataArchive:SupportingFullSchemaEvolutionDBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBAandDBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBADisablesFlashbackDataArchiveonspecifiedtables,allowingmorecomplexDDL(upgrades,splittables)EnforcesschemaintegrityafterassociationBasetableandhistorytableschemasmustbethesame.RequirestheFLASHBACK

ARCHIVE

ADMINISTERprivilegeALTERSCHEMADISASSOCIATEALTERSCHEMAASSOCIATE11.21243HistorytableHistorytableBasetable.ViewingFlashbackDataArchivesViewingtheresults:ViewNameDescription*_FLASHBACK_ARCHIVEDisplaysinformationaboutflashbackdataarchives*_FLASHBACK_ARCHIVE_TSDisplaystablespacesofflashbackdataarchives*_FLASHBACK_ARCHIVE_TABLESDisplaysinformationabouttablesthatareenabledforflashbackarchiving.GuidelinesandUsageTipsCOMMITorROLLBACKbeforequeryingpastdataUseofcurrentsessionsettingsObtainSCNwiththeDBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERfunction.Computeapasttimewith: (SYSTIMESTAMP-INTERVAL'10'MINUTE)UseSystemChangeNumber(SCN)whereprecisionisneeded(timestampshaveathree-secondgranularity)..QuizWhichofthefollowingarecharacteristicsofaflashbackdataarchive?Aflashbackdataarchiveconsistsofoneormoretablespacesorpartsthereof.Aflashbackdataarchiveconsistsofonlyonetablespace.Youcanhaveonlyoneflashbackdataarchiveineachdatabase.Youcanhavemultipleflashbackdataarchivesineachdatabase..FlashbackTransactionBackoutLogicalrecoveryoptiontorollbackaspecifictransactionandallitsdependenttransactionsUsingredologsandsupplementalloggingCreatingandexecutingcompensatingtransactionsYoufinalizechangeswithcommitorrollback.FasterandeasierthanlaboriousmanualapproachDependenttransactionsinclude:Write-after-write(WAW)andprimarykeyconstraints,butnotforeignkeyconstraints.Prerequisites

…anddatabasemustbeinARCHIVELOGmode11.2update.FlashingBackaTransactionYoucanflashbackatransactionbyusingEnterpriseManagerorthecommandline.EnterpriseManagerusestheFlashbackTransactionWizard,whichcallstheDBMS_FLASHBACK.TRANSACTION_BACKOUTprocedurewiththeNOCASCADEoption.IfthePL/SQLcallfinishessuccessfully,itmeansthatthetransactiondoesnothaveanydependenciesandasingletransaction

is

backedoutsuccessfully..UsingtheFlashbackTransactionWizard.UsingtheDBMS_FLASHBACK.TRANSACTION_BACKOUTProcedureCREATETYPEXID_ARRAYASVARRAY(100)OFRAW(8);CREATEORREPLACEPROCEDURETRANSACTION_BACKOUT(numberOfXIDsNUMBER,--numberoftransactionspassedasinputxidsXID_ARRAY,--thelistoftransactionidsoptionsNUMBERdefaultNOCASCADE,--backoutdependenttxntimeHintTIMESTAMPdefaultMINTIME--timehintonthetxnstart);.ViewingaDependencyReportAfterchoosingyourbackoutoption,thedependencyreportisvisibleintheDBA_FLASHBACK_TXN_STATEandDBA_FLASHBACK_TXN_REPORTviews.Reviewthedependencyreportthatshowsalltransactionswhichwerebackedout.Committhechangestomakethempermanent.Rollbacktodiscardthechanges..ViewingFlashbackTransactionMetadataViewNameDescription*_FLASHBACK_TXN_REPORTDisplaysrelatedXMLinformation*_FLASHBACK_TXN_STATEDisplaysthetransactionidentifiersforbacked-outtransactionsSQL>SELECT*FROMDBA_FLASHBACK_TXN_STATE;COMPENSATING_XIDXIDBACKOUT_MODEDEPENDENT_XIDUSER#-------------------------------------------------------------------050015006905000003000000A905000040

050015006905000005001E0063050000403000000A90500000

.QuizFlashbackTransactionBackoutenablesyoutorollbackaspecifictransactionanddependenttransactionswhilethedatabaseremainsonline.TrueFalse.UsingLogMinerPowerfulaudittoolforOracledatabasesDirectaccesstoredologsUserinterfaces:SQLcommandlineGraphicaluserinterface(GUI)IntegratedwithEnterpriseManager.Review:DataPumpExportandImportexpdp

clientDump

filesetDatabaseDataPump

jobSourceMaster

tableServer

processDump

filesetServer

processTargetDataPump

jobimpdp

clientDatabaseMaster

tableDatabase

link“Networkmode”.MigrationwithDataPumpLegacyModeAssistanceintransitioningfromimpandexputilitiestoimpdpandexpdputilitiesDataPumpinlegacymode:1. Encountersuniqueimporexpparameterandenterslegacymode2. Attemptstomaptheoldsyntaxtothenewsyntax3. Displaysnewsyntax4. ExitslegacymodeBestpracticetip:Oraclestronglyrecommendsthatyouviewthenewsyntaxandmakescriptchangesastimepermits.11.2.DataPumpLegacyModeTheDataPumpexportandimportutilities:ReadandwritefilesonlyinDataPumpformatAcceptexpandimputilitycommandsinlegacymodeLegacymodeparameters:Canbeidenticaltothenewsyntax:FILESIZE=integer[B|K|M|G]Canbesimilar:QUERY=query_clauseAreignored,whenthecommandissupercededbyDataPumpdefaults.BUFFER=integerCOMPRESS={y|n}DIRECT={y|n}Causeanerrorwhenoldandnewsyntaxismixed.11.2.DataPumpLegacyModeLegacymodeparameters(continued):AremappedtoDataPumpparameters,ifpossible:consistent={y|n}->FLASHBACK_TIMEGRANTS=n->EXCLUDE=CONSTRAINTSINDEXES=n->EXCLUDE=INDEXLOG=filename->LOGFILE=filenameFILE=filename->dumpfile=directory-object:filename

Canbesimilar,butnotidentical:FEEDBACK=integer->STATUSCauseanerrorwhenincompatiblewith