Oracle英文版培訓課件之Data Warehouse：les14

SystemManagementTasks

andTechniquesObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DemonstratetableandpartitioncompressiontechniquesImplementresumablesessionsinyourdatawarehouseImplementaneffectivebackupandrestorestrategyforyourdatawarehousePlanasecuritystrategyforadatawarehouseAreThereExecutionProblems?UseV$PQ_TQSTATtofindoutifthereisdatadistributionskeworbadobjectstatistics:ContainstrafficstatisticsbetweenslavesattablequeuelevelValidonlywhenqueriedfromtheparallelsessionCheckI/OandCPUboundattheoperatingsystemlevelanddecideontheparallelismUsetheAutomaticWorkloadRepositorytoexamineusefulstatisticsPerformanceMonitoringandTuningStatisticsgatheringandmonitoringactivitiesaremainlyautomatic.AdministeranddisplaytheoutputofthegatheringandtuningtoolswithEnterpriseManager:AutomaticWorkloadRepositoryAutomaticDatabaseDiagnosticMonitorSQLAccessAdvisorEndtoEndApplicationTracingStatisticsCollectionTheSTATISTICS_LEVELinitializationparametercontrolsmajorstatisticscollectioninthedatabase.STATISTICS_LEVELcanhaveoneofthreevalues:WhensettoBASIC,noadvisoriesorstatisticsarecollected.TYPICAListhedefaultvalueandensures

collectionforallmajorstatistics.ALLincludesallitemscollectedwith

TYPICALplusoperatingsystemand

rowsourceexecutiondata.EndtoEndApplicationTracingEndtoEndApplicationTracing:SimplifiestheprocessofdiagnosingperformanceproblemsinmultitierenvironmentsHelpstoidentifythesourceofanexcessiveworkloadUsesaclientidentifiertouniquelytraceaspecificendclientthroughalltiersAllowsyoutoidentifywhatauser’ssessionisdoingatthedatabaselevelEndtoEndApplicationTracingUsingEMEMDatabaseControlinterface:CompressionTablecompressionisoptimizedforrelationaldata.Thereisvirtuallynonegativeimpactontheperformanceofqueriesagainstcompresseddata.Therecanbeasignificantpositiveimpactonqueriesaccessinglargeamountsofdata.Thedataiscompressedbyeliminatingduplicatevaluesinadatabaseblock.Alldatabasefeaturesandfunctionsthatworkonregularblocksalsoworkoncompressedblocks.WhatCanBeCompressed?Objectsthatcanbecompressedinclude:TablesPartitionsMaterializedviewsB-treeindexesCompressionworksforalldatatypesexcept:Variantsoflargeobjects(LOBs)DatatypesderivedfromLOBs,suchasVARRAYsstoredoutoflineorXMLdatatypestoredinacharacterlargeobject(CLOB)Tablesthatarefrequentlyupdatedshouldnotbecompressed.Compression:ExamplesSimplecompressedpartitionedtable:Partitionedtablewithonecompressedpartition:CREATETABLEsales_rangesalesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_amountNUMBER(10),sales_dateDATE)COMPRESSPARTITIONBYRANGE(sales_date)...CREATETABLEsales_list(salesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_stateVARCHAR2(20),sales_amountNUMBER(10))PARTITIONBYLIST(sales_state)(PARTITIONsales_westVALUES('California','Hawaii')COMPRESS,PARTITIONsales_eastVALUES('NewYork','Virginia','Florida'),...MaximizingCompressionRatiosLoaddataordered(sorted)tomaximizetheduplicationofvalueswithinadatabaseblock.Ifexternaltablesareusedforloading,addanORDERBYclausetotheSELECTstatement.Uselargerblocksizes.ResumableSessionsResumablestatementsenablelargedatabaseoperationstobesuspendedandresumed.Space-limiterrorscanbecorrected,allowingthehungsessiontoresume.IssueALTERSESSIONENABLERESUMABLEbeforeinitiatingalong-runningoperation:TIMEOUTspecifiesthetimewindowforintervention.NAMEspecifiesthenameoftheresumablesession.ALTERSESSIONENABLERESUMABLETIMEOUT600NAME

'resumable_session_name';

DataWarehouseBackupandRecoveryWhyshouldadatawarehousebackupandrecoverystrategynotbejustlikethatofanOLTPdatabase?Adatawarehouseistypicallymuchlargerthananoperationalsystem.AdatawarehouseoftenhasloweravailabilityrequirementsthananOLTPsystem.Datawarehouseupdatesare

oftenreproduciblefromsources

otherthandatabaselogs.Datawarehousescontain

historicaldataandoften,

largeportionsoftheolder

dataarestatic.RecoveryTimeObjective(RTO)AnRTOisthenumberofhoursinwhichyouwanttobeabletorecoveryourdata.ToformulateanRTO:Understandyourrecoveryreadiness,riskareas,andthebusinesscostsofunavailabledata.Transformtherecoveryrequirementsintobackupandrecoverystrategies.Deployandintegratethesolutionintoyourenvironmenttobackupandrecoveryourdata.Testyourrecoveryplansatregularintervals.Refineitasyourdata,ITinfrastructure,andbusinessprocesseschange.RecoveryPointObjective(RPO)AutomatedrecoveryproceduresManualrecoveryproceduresNobackupofdataRestorebackupDatareinsertedviaETLRPOBackups

runat

scheduledintervals.RTO#1Criticaldataisavailable,databaseisopen.RTO#2Remainderofdataisinserted.0-nnhours+nnhours+nnnhoursNormaloperationsNormaloperationsMoreBackupandRecoveryConsiderationsHardwareisthelimitingfactortofastbackupandrecovery.Moredatameansalongerbackupwindow.RMANcanfullyutilize,inparallel,allavailabletapedevices.Considerbreakingthedatabasebackupoveranumberofdays.RMANbacksupalldatabasefilesthathavenotbeenbackedupinthelast24hours.Thebackupjobislimitedto4hours.RMAN>BACKUPDATABASENOTBACKEDUPSINCE\'sysdate–1'PARTIALDURATION4:00MINIMIZETIME;

BackupandRecoveryToolsRecoveryManagerreducestheadministrationworkassociatedwithyourbackupstrategy.EnterpriseManagerenables

backupandrecoverytobedone

byapoint-and-clickmethod.DataPumpprovideshigh-speed,

parallel,bulkandmetadata

movementofdatabasecontents.User-managedbackupsaredone

usingoperatingsystem–specific

utilities.BackupandRecovery:

BestPracticesUseARCHIVELOGmode.UseRMAN.Leverageread-onlytablespaces.Planfornologgingoperationsinyourbackupandrecoverystrategy.Notalltablespacesinadatawarehouseareequallysignificantfromabackupandrecoveryperspective.NOLOGGINGBackupand

RecoveryStrategiesExtract,transform,load(ETL):UsesseveralOracletoolsandacombinationofmethodstoreloaddataTransportabletablespaces,SQL*Loader,DataPump,externaltablesIncrementalbackups:AreamoreautomatedbackupandrecoverystrategyinthepresenceofnologgingoperationsProvidethecapabilitytobackuponlythechangedblockssincethepreviousbackupArefasterwhenusingtheblockchangetrackingfeatureEmployingIncrementalBackupsIncrementalbackupsofdatafilescapturedatachangesonablock-by-blockbasis.SpeedupbackupsbyusingblockchangetrackingThedatabasetracksthephysicallocationofalldatabasechanges.RMANautomaticallyusesthechangetrackingfiletodeterminewhichblocksneedtoberead.Sizingtheblockchangetrackingfile:Bestpractice:Useblockchangetrackingandmakeanincrementalbackupafternologgingdirectloadoperations.((threads*2)+number_of_old_backups)*(database_size_in_bytes)--------------------------------------------------------------------------------------------------250000DataWarehouseSecurity

Theconsolidatedmodelprovidesseveralbenefitsfordatawarehousesecurity:ConsistentsecuritymodelCentral,expertmanagementFewerpointsofattackSimplersecuritymaintenanceLayeredSecurityModelProvidemultiplesecuritylayerssothatfailureofasinglelayerdoesnotcompromisecriticaldata.ThesecuritylayersavailableinOracleDatabase10ginclude:Role-basedaccesscontrolRow-levelsecurityemploying

VirtualPrivateDatabaseand

OracleLabelSecurityAuditingthedatabaseEncryptionTransparentDataEncryptionTransparentDataEncryption(TDE)isacomponentofOracleAdvancedSecurity.TDEallowsyoutoencryptselectedcolumndatawithouthavingtomanagetheencryptionkey.Encryptsensitivedatasuchascreditcardnumbersbeforeitisstoredinthedatabase.OracleAdvancedSecurityprotectsdatabyusingindustry-standardencryptionmethods:AESTripleDESImplementingTDE1. AsSYSDBA,createtheencryptedwalletfileandsettheinitia