OracleBackupandRecoveryD22057GC10_Les_09_monitor

Copyright©2006,Oracle.Allrightsreserved.MonitoringandTuningRMANCopyright©2006,Oracle.Allrightsreserved.9-2ObjectivesAftercompletingthislesson,youshouldbeableto:•MonitorRMANjobs•TroubleshootRMANoperations•TuneRMANbackupperformanceCopyright©2006,Oracle.Allrightsreserved.9-3MonitoringRMANSessions•QueryV$SESSIONandV$PROCESStoidentifytherelationshipbetweenserversessionsandRMANchannels.•Ifyouaremonitoringmultiplesessions,usetheSETCOMMANDIDcommandtocorrelateaprocesswithachannelduringabackup.SQLCOLUMNCLIENT_INFOFORMATa30SQLCOLUMNSIDFORMAT999SQLCOLUMNSPIDFORMAT9999SQLSELECTs.sid,p.spid,s.client_info2FROMv$processp,v$sessions3WHEREp.addr=s.paddr4ANDCLIENT_INFOLIKE'rman%';Copyright©2006,Oracle.Allrightsreserved.9-5MonitoringRMANJobProgressMonitortheprogressofbackupandrestoreoperationsbyqueryingV$SESSION_LONGOPS.SQLSELECTSID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,2ROUND(SOFAR/TOTALWORK*100,2)%_COMPLETE3FROMV$SESSION_LONGOPS4WHEREOPNAMELIKE'RMAN%'5ANDOPNAMENOTLIKE'%aggregate%'6ANDTOTALWORK!=07ANDSOFARTOTALWORK;SIDSERIAL#CONTEXTSOFARTOTALWORK%_COMPLETE-------------------------------------------1375194701536061.6512811158712816056.36Copyright©2006,Oracle.Allrightsreserved.9-7MonitoringRMANJobPerformance•Thefollowingviewscanbeusedtomonitorbackupandrestoreperformance:–V$BACKUP_SYNC_IO–V$BACKUP_ASYNC_IO•Thefollowingrowswillexistforabackuporrestore:–Onerowforeachdatafile–Oneaggregatedatafilerow–Onerowforeachbackuppiece•WhetherornotI/Oissynchronousdependsonhowthecontrollingprocessviewsit.Copyright©2006,Oracle.Allrightsreserved.9-8InterpretingRMANMessageOutputRMANtroubleshootinginformationcanbefoundinthe:•RMANcommandoutput•RMANtracefile•Alertlog•Oracleservertracefile•sbtio.logfileCopyright©2006,Oracle.Allrightsreserved.9-9UsingtheDEBUGOption•TheDEBUGoptionisusedto:–ViewthePL/SQLthatisgenerated–DeterminepreciselywhereanRMANcommandishangingorfaulting•TheDEBUGoptionisspecifiedattheRMANpromptorwithinarunblock.•TheDEBUGoptioncreatesanenormousamountofoutput,soredirecttheoutputtoatracefile:$rmantarget/catalogrman/rmandebugtracetrace.logCopyright©2006,Oracle.Allrightsreserved.9-10UnderstandingRMANCodeLayerErrorNumbersCatalogpackagediagnostics12000-12999InterphaseerrorsbetweenPL/SQLandRMAN11000-11999Executionmanagerserver-sideerrors10000-10999Low-levelkeywordanalyzer9000-9999GeneratedPL/SQLerrorsandmessages8000-8999Generalexecutionmanager7000-7999RESTOREorRECOVERcompilationerrors5000-5499Generalcompilationerrors6000-69995500-5999ErrorRangeDUPLICATEcompilationerrorsCauseCopyright©2006,Oracle.Allrightsreserved.9-11InterpretingRMANErrorStacks•Readthestackfrombottomtotop.•LookforAdditionalinformation.•RMAN-03009identifiesthefailedcommand.RMAN-00571:===========================================RMAN-00569:=======ERRORMESSAGESTACKFOLLOWS=======RMAN-00571:===========================================RMAN-03009:failureofbackupcommandonc1channelat09/04/200113:18:19ORA-19506:failedtocreatesequentialfile,name=07d36ecp_1_1,parms=ORA-27007:failedtoopenfileSVR4Error:2:NosuchfileordirectoryAdditionalinformation:7005Additionalinformation:1ORA-19511:Errorfrommediamanagerlayer,errortext:Copyright©2006,Oracle.Allrightsreserved.9-12TuningRMAN•RMANBACKUPandRESTOREoperationsperformthefollowingtasks:–Readorwritedata.–Processdatabycopyingandvalidatingblocks.•Theslowestofthesetasksisreferredtoasabottleneck.•TuningRMANrequiresthatthebottlenecksbeidentifiedandaddressed.Copyright©2006,Oracle.Allrightsreserved.9-13RMANMultiplexing1MBbuffersareallocatedsothatthetotalbuffersizeforalltheinputfilesis16MB.Lessthanorequalto4RMANallocatesfour128KBdiskbuffersforeachfile,sothatthetotalsizeis512KBforeachfile.Greaterthan8Greaterthan4butlessthanorequalto8MultiplexingLevel512KBareallocatedsothatthetotalbuffersizeforallthefilesislessthan16MB.AllocationRuleCopyright©2006,Oracle.Allrightsreserved.9-14AllocatingDiskBuffer:ExampleDatafiles1MB1MB1MB1MBInputdiskbuffersChannelFILESPERSET=4MAXOPENFILES=41MB1MB1MB1MB1MB1MB1MB1MB1MB1MB1MB1MBCopyright©2006,Oracle.Allrightsreserved.9-15AllocatingTapeBuffers•IfBACKUP_TAPE_IO_SLAVESisTRUE,tapebuffersareallocatedfromtheSGA.•IfBACKUP_TAPE_IO_SLAVESisFALSE,tapebuffersareallocatedfromthePGA.256KB256KB256KB256KBTapebuffersChannelCopyright©2006,Oracle.Allrightsreserved.9-16ComparingSynchronousandAsynchronousI/OServerprocessSynchronousI/O010010001001001243Tapeprocesssignalsfinish.Serverprocesswritesdatatonewbuffer.Serverprocesswritesdatatobuffer.Serverprocesswaits;tapeprocesswritesdata.TapebuffersCopyright©2006,Oracle.Allrightsreserved.9-17ComparingSynchronousandAsynchronousI/OTapebuffersServerprocessAsynchronousI/O01001000100100010010012Serverprocesswritesdatatobuffer.Tapeprocesswritesdata.3Serverprocesswritestonewbufferwhilestep2completes.Copyright©2006,Oracle.Allrightsreserved.9-18MonitoringRMANJobPerformance•Thefollowingviewscanbeusedtomonitorbackupandrestoreperformance:–V$BACKUP_SYNC_IO–V$BACKUP_ASYNC_IO•Thefollowingrowswillexistforabackuporrestore:–Onerowforeachdatafile–Oneaggregatedatafilerow–Onerowforeachbackuppiece•WhetherornotI/Oissynchronousde