Oracle英文版培训课件之Backup and Recovery：Les-09-monitor

MonitoringandTuningRMANObjectivesAftercompletingthislesson,youshouldbeableto:MonitorRMANjobsTroubleshootRMANoperationsTuneRMANbackupperformanceMonitoringRMANSessionsQueryV$SESSIONandV$PROCESStoidentifytherelationshipbetweenserversessionsandRMANchannels.Ifyouaremonitoringmultiplesessions,usetheSET

COMMAND

IDcommandtocorrelateaprocesswithachannelduringabackup.SQL>COLUMNCLIENT_INFOFORMATa30SQL>COLUMNSIDFORMAT999SQL>COLUMNSPIDFORMAT9999SQL>SELECTs.sid,p.spid,s.client_info2FROMv$processp,v$sessions3WHEREp.addr=s.paddr4ANDCLIENT_INFOLIKE'rman%';MonitoringRMANSessions(notesonly)MonitoringRMANJobProgressMonitortheprogressofbackupandrestoreoperationsbyqueryingV$SESSION_LONGOPS.SQL>SELECTSID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,2ROUND(SOFAR/TOTALWORK*100,2)"%_COMPLETE"3FROMV$SESSION_LONGOPS4WHEREOPNAMELIKE'RMAN%'5ANDOPNAMENOTLIKE'%aggregate%'6ANDTOTALWORK!=07ANDSOFAR<>TOTALWORK;SIDSERIAL#CONTEXTSOFARTOTALWORK%_COMPLETE-------------------------------------------1375194701536061.6512811158712816056.36MonitoringRMANJobProgress(notesonly)MonitoringRMANJobPerformanceThefollowingviewscanbeusedtomonitorbackupandrestoreperformance:V$BACKUP_SYNC_IOV$BACKUP_ASYNC_IOThefollowingrowswillexistforabackuporrestore:OnerowforeachdatafileOneaggregatedatafilerowOnerowforeachbackuppieceWhetherornotI/Oissynchronousdependsonhowthecontrollingprocessviewsit.InterpretingRMANMessageOutputRMANtroubleshootinginformationcanbefoundinthe:RMANcommandoutputRMANtracefileAlertlogOracleservertracefilesbtio.logfileUsingtheDEBUGOptionTheDEBUGoptionisusedto:ViewthePL/SQLthatisgeneratedDeterminepreciselywhereanRMANcommandishangingorfaultingTheDEBUGoptionisspecifiedattheRMANpromptorwithinarunblock.TheDEBUGoptioncreatesanenormousamountofoutput,soredirecttheoutputtoatracefile:$ rmantarget/catalogrman/rmandebugtracetrace.logUnderstandingRMANCodeLayerErrorNumbersCatalogpackagediagnostics12000-12999InterphaseerrorsbetweenPL/SQLandRMAN11000-11999Executionmanagerserver-sideerrors10000-10999Low-levelkeywordanalyzer9000-9999GeneratedPL/SQLerrorsandmessages8000-8999Generalexecutionmanager7000-7999RESTOREorRECOVERcompilationerrors5000-5499Generalcompilationerrors6000-69995500-5999ErrorRangeDUPLICATEcompilationerrorsCauseInterpretingRMANErrorStacksReadthestackfrombottomtotop.LookforAdditionalinformation.RMAN-03009identifiesthefailedcommand.RMAN-00571:===========================================RMAN-00569:=======ERRORMESSAGESTACKFOLLOWS=======RMAN-00571:===========================================RMAN-03009:failureofbackupcommandonc1channelat

09/04/200113:18:19ORA-19506:failedtocreatesequentialfile,

name="07d36ecp_1_1",parms=""ORA-27007:failedtoopenfileSVR4Error:2:NosuchfileordirectoryAdditionalinformation:7005Additionalinformation:1ORA-19511:Errorfrommediamanagerlayer,errortext:TuningRMANRMANBACKUPandRESTOREoperationsperformthe

followingtasks:Readorwritedata.Processdatabycopyingandvalidatingblocks.Theslowestofthesetasksisreferredtoasabottleneck.TuningRMANrequiresthatthebottlenecksbeidentifiedandaddressed.RMANMultiplexing1MBbuffersareallocatedsothatthetotalbuffersizeforalltheinputfilesis16MB.Lessthanorequalto4RMANallocatesfour128KBdiskbuffersforeachfile,sothatthetotalsizeis512KBforeachfile.Greaterthan8Greaterthan4butlessthanorequalto8MultiplexingLevel512KBareallocatedsothatthetotalbuffersizeforallthefilesislessthan16MB.AllocationRuleAllocatingDiskBuffer:ExampleDatafiles1MB1MB1MB1MBInputdiskbuffersChannelFILESPERSET=4MAXOPENFILES=41MB1MB1MB1MB1MB1MB1MB1MB1MB1MB1MB1MBAllocatingTapeBuffersIfBACKUP_TAPE_IO_SLAVESisTRUE,tapebuffersareallocatedfromtheSGA.IfBACKUP_TAPE_IO_SLAVESisFALSE,tapebuffersareallocatedfromthePGA.256KB256KB256KB256KBTapebuffersChannelComparingSynchronousandAsynchronousI/OServerprocessSynchronousI/O010010001001001243Tapeprocesssignalsfinish.Serverprocesswritesdatatonewbuffer.Serverprocesswritesdatatobuffer.Serverprocesswaits;

tapeprocesswritesdata.Tape

buffersComparingSynchronousandAsynchronousI/OTape

buffersServerprocessAsynchronousI/O01001000100100010010012Serverprocesswritesdatatobuffer.Tapeprocesswritesdata.3Serverprocesswritestonewbufferwhilestep2completes.MonitoringRMANJobPerformanceThefollowingviewscanbeusedtomonitorbackupandrestoreperformance:V$BACKUP_SYNC_IOV$BACKUP_ASYNC_IOThefollowingrowswillexistforabackuporrestore:OnerowforeachdatafileOneaggregatedatafilerowOnerowforeachbackuppieceWhetherornotI/Oissynchronousdependsonhowthecontrollingprocessviewsit.AsynchronousI/OBottlenecksUseV$BACKUP_ASYNC_IOtomonitorasynchronousI/O.IfLONG_WAITS+SHORT_WAITSisalargepartofIO_COUNT,thenthefileisprobablyabottleneck.LONG_WAITS:Numberoftimesthebackup/restoreprocesstoldtheOStowaituntilI/OwascompleteSHORT_WAITS:Callsthebackup/restoreprocessmadepollingforI/Ocompletioninnonblockingmode.WAITStimesshouldbezerotoavoidbottlenecks.SynchronousI/OBottlenecksSynchronousI/Oisconsideredtobeabottleneck.QuerytheDISCRETE_BYTES_PER_SECONDcolumnfromV$BACKUP_SYNC_IOtoviewtheI/Orate.Comparethisratewiththedevice’smaximumrate.Iftherateislowerthanwhatthedevicespecifies,thisisatuningopportunity.TapeBackupSpeedThefollowingfactorsaffectthespeedofthebackuptotape:NativetransferrateTapecompressionTapestreamingPhysicaltapeblocksizeTapeSubsystemPerformanceRulesIncreasingthenumberoftapedrivesorthespeedoftapedrivesprovidesforfasterbackupandrestore.Allocateonechannelperphysicaldevice:Ifmorechannelsthanphysicaldrivesareused,thenthebackupsetswillbeintermingled.Thisissimilartomultiplexingdatafiles.Iftapedriveisnotstreaming,thenincreasethenumberoffilesthataremultiplexed.Backupthefilesthatarelikelytoberestoredtogetherinthesamebackupset.ControllingTapeBufferSizewithBLKSIZEAdjustthesizeofthetapebuffertokeepthetapestreaming.UsetheBLKSIZEoptionofPARMStosetthedesiredbuffersize:SetBLKSIZEto1

MBtocorrespondtothebuffersize.RMAN>CONFIGURECHANNELDEVICETYPESBT_TAPE2>PARMS="BLKSIZE=1048576";ChannelTuningUsetheCONFIGURE

CHANNELandALLOCATE

CHANNELcommandsto:LimitthesizeofbackuppiecesPreventRMANfromconsumingtoomuchdiskbandwidthDeterminethelevelofmultiplexingforeachchannelTuningtheBACKUPCommandMAXPIECESIZElimitsthesizeofeachbackuppiece.FILESPERSETpreventsRMANfromreadingfromtoomanydisksatonce.TuningRMANBackupPerformanceFollowthesestepstotuneRMANbackupperformance:RemoveRATEsettingsfromconfiguredandallocatedchannels.SetDBWR_IO_SLAVESifyouusesynchronousdiskI/O.SetLARGE_POOL_SIZE.TuneRMANtapestreamingperformancebottlenecks.QueryV$viewstoidentifybottlenecks.SettingLARGE_POOL_SIZEIfLARGE_POOL_SIZEisnotset,theOracleservertriestogetmemoryfromthesharedpool.IfLARGE_POOL_SIZEisnotbigenough,theserverdoesnotallocatebuffersfromtheshar