Monitoring Oracle 10g RAC with Quest Spotlight on RAC.docx

Monitoring Oracle 10g RAC with Quest Spotlight on RAC.docx

《Monitoring Oracle 10g RAC with Quest Spotlight on RAC.docx》由会员分享，可在线阅读，更多相关《Monitoring Oracle 10g RAC with Quest Spotlight on RAC.docx（20页珍藏版）》请在冰豆网上搜索。

MonitoringOracle10gRACwithQuestSpotlightonRAC

January24,2006

MonitoringOracle10gRACwithQuestSpotlightonRAC–PartI

ByTarrySingh

Youhaveheardthehypearoundaparticulartechnology.It'scoolandeveryonewantsapieceofit.Thenit'sthepost-hypeeraforatechnologywhereitisgettingseriousattentionfromtheBigPlayersandeventuallyitscommoditization.Breakthroughtechnologies,likeOracleRACorServerPartitioning（fondlyknownasVirtualization）,willeventuallybecommonplace.Therefore,itistimeforyourDBAtomonitorseveraloracleRACs.ThescenariocouldrangefromaRACfarm,asmassiveas36-NodeOracleRAC,toa2-NodeOracleRAC.BeingconfrontedwithquestionsliketheseisverynormaltoacuriousDBA:

∙Howismyclusterperforming?

∙HowismyDatabaseperformanceasawhole?

∙Contentionissuesdisk,io,cluster,interconnect?

∙LatencyproblemswithinHSI（HighSpeedInterconnects）?

Aretherepoorsettings?

AremyNIC'saresetproperly?

OrisitapoorlyconfiguredRAC?

∙Inter-nodecommunicationissues?

Aretoomany"HotBlocks"beingtransferredacrossnodesduetopoordatabasedesign?

∙Poorloadbalancing?

Asyoucansee,thelistisendless.YouhavemigratedtoaheavydutyOracleRACandyouseeperformancedrop.Unfortunately,youcannotseeexactlywhatmightbegoingwrong.

IntroducingSpotlightonRAC（SoRAC）

ItistimetosayhellotoSoRAC（SpotlightonRACbyQuestSoftware）whichiswelloutofbetaphaseandverysoontobeGA.Ihadthepleasureofparticipatinginthebetaprogramandwasratherpleasedwiththeresults（eventhoughitwasstillinbetaphase）.

SoRACprovidesdetaileddiagnosticinformationbydrillingdownindividualinstancesandmergingtheappropriatecalculationsintooneinterfacecalled"SoRACHomepage."Andit'salllive!

OverviewofSpotlightonOracleRAC

Whatisthistoolcapableof?

ItprovidesthreeclassesoffunctionalitytoaRACDBA.…

∙Yougettoseethephysicalarchitecture.Asyouseebelow...

Clickforlargerimage

Youcanclearlyseealloftheessentialcomponentsofacluster:

theindividualinstances,theinterconnectandtheI/Osubsystemaredisplayed,includingtheessentialdataflowsbetweenthem.ThegreatthingaboutthistoolisthatyoucanpaintapictureofyourRACinyourhead,enhanceyourunderstandingofRAC,andlocalizeproblems.Theaboveexamplerunsagainstmy2-nodeRHEL4.2Oracle10gR2RAConVMwareGSXServer.

∙Calculationofdiagnosticsandpresentingthembacktotheinterfaceasawhole.Forinstance,youmighthavelatchcontentionononenodeona6-nodeRACandtheaggregateofallthelatchesmightjustbefineforyourRACtofunctionadequately.

∙SeveralRACspecificalarmssuchas:

1.UnevenLoadDistributiontotheinstances.

2.Overheadduetoclustermaintenance.

3.HighlatencyproblemswithintheHSI（HighSpeedInterconnects）.

4.ExcessiveHSIcommunication（mightpointtopartitioningdataacrossnodes,forinstanceatypicalhybrid,meaningOLTP（OnlineTransactionProcessing）/DSS（DecisionSupportSystem）mightrequireadatapartitioningontheDSSpartofthatRACdatabase）.

5.BlockCorruption/Orphanedblocksacrossinstances.

6.OCFSproblems/SharedDisk（WhetheronSAN/NAS）contention.

WhatPlatformdoesitsupport?

TheinitialreleasewillstillprovidesupporttoaRACsrunningonUnix/LinuxbasedsystemssuchasRHEL/Solaris.Thereistheoreticalsupportforatypical36-nodeclusterbutformally,youmonitora16-nodeclusterwithease.

Youwill,however,needanOracleClientsoftwareinstallationandyoucangetitherealthoughyouwillneedtoadjustyourTNSNAMES.orafile,asthiswillbeusedtomakeaconnectiontoyourRACService.

GettingSpotlightonRAC

SpotlightonRACshouldbeavailableverysoon.（SoIheardfromtheMarketingManager）andthenyoushouldbeabletodownloadanevaluationfromhere.

January24,2006

MonitoringOracle10gRACwithQuestSpotlightonRAC–PartI

ByTarrySingh

InstallingandSettingup

Installationisrathersimple.

∙InstallyourOracleClientSoftware.

∙InstallSoRAC

∙SettinguptheTNSNAMES.ora

Clickforlargerimage

∙ConnectingtoyourRAC

Clickforlargerimage

Upondoubleclickingyourconnection,yougettofillintheMyclusterPropertiesfile:

Clickforlargerimage

Above,youwillseetheconnectionstringdropdownforbothyourOracleRACServiceandASMinstances.TheminimumthatyouneedistheOracleConnectionString.SoRACconnectstotheinstanceandqueriesviewgv$instanceforallRACinstancesanditqueriestheviewgv$active_instancestodisplaytheactiveinstancesinthecluster.

TheOracleUsernameandPasswordisthesameasthatrequiredforSpotlightonOracle–SoRACandSoOsharethesameOracleuser.Youwillbepromptedtocreateauseroruseanexistinguser.Followthestepsbelowtocreateanewuser.

Step1:

Wizardopensup

Step2:

Itconnectstoaninstancetoupgradetheuser

Step3:

Createanewuser

Step4:

Wewillcallthenewuserspotrac.

Step5:

Ipicktablespaces"Users"and"TEMP",youcanhowevercreateyourowntablespaceaswell.

Step6:

Creatinguserandconfiguring...

Step7:

Done.

Allotherfieldsareoptional.IftheclusterusesASM,thenyoucanfilltheMonitorASMsection.ASMismanagedviaanOracleinstance,whichmustbeenteredhere.Itonlysupportstheuser"SYS"asweknow.ThissectionmustbefilledouttopopulatetheASMspinner,associateddrilldownandASMServiceTimealarm.

Connecting/VerifyingtheCluster

Step1:

Doubleclickingtheconnectionwegetourinstancespresentedtous...

Clickforlargerimage

Step2:

ClicktestandthenOK

Step3:

CalibrationSettings

Conclusion:

Inthenextandthefinalarticleof"MonitoringOracleRACwithSpotlightonRAC"（SoRAC）,wewilldiscussindetailhowtheAlarm（OracleAggregatedAlarms,ClusterBalanceAlarms,ClusterLatencyandOverheadAlarms,GlobalCacheAlarms,ASMAlarms）andDrilldownswork.HereyouhaveseenthatthehomepageorthemonitoringpaneofSoRACisveryintuitive.YouasabusyDBAwillspendalotlesstimeoncomplexproblems,asagraphicaloverviewwillpointyouintherightdirection.

February17,2006

MonitoringOracle10gRACwithQuestSpotlightonRAC–PartII

ByTarrySingh

Inthissecondarticleoftheseries,wewilldiscusssomeoftheissuesthatwelaiddownlasttime.IreceivedanemailfromanotherbetatesterwhocomplainedaboutsomefeaturesthatdidnotexistinSoRAC.Hesaiditisjustlivedata.Iaskedhim"AndwhatdoyouthinkaDBAdoesmostofthetime?

Keepfixingproblems?

"Certainlynot!

Theywanttomonitortheirdatabaseandwhatbetterwaytomonitorthanwatchingitlive!

Althoughwewillnotdelveintothe"goodtohave"or"nicetohaveutilities"here,youcansendfeedbacktoQuestSoftware.IalsoputupasimpleAVIfileonmyOracleblog;downloadittoseeitinactionhere.Whiledoingtherecording,Ididnotmouseoverallofmovingpartsasitcausedtherecordingtofreeze.However,ifyoumoveyourmouseoveryourownSoRACwhenyourunit,youwillseefulldetailedinformationoftheactivity.

It'sallaboutthoseringingAlarms...

...anddrilldowns.Wewillcheckoutsometypicalalarms.Noticethatonlyusefulandcrucialalarmshavebeenputonhighpriority;lowpriorityalarmshavebeenadjustedwitha"lowloadfilter,"sothatyouaren'talarmedallthetime.

Ifthereislittleactivityinyourcluster,thealarmwillnotbetriggered.Sincethedefaultvalueforlowloadis"（TotalLogicalReads/sec）=500",ifthereisnoactivitybelowthisvaluethenthealarmwillnotbefired.

OracleAggregatedAlarms

SoRACtreatsanOracleRACasasingledatabaseandaggregatesalloftheSpotlightonOracle（SoO）metrics（tocreateclusterwidemetrics）tosetalarmthresholds,whichinturnarescaledaspertheloadoneachnode.

Forinstance,the"enqueuewaitpercentage"forasinglenodewouldbecalculatedthisway:

AndSoRACaggregatesitasfollows:

Yougettheidea.If,forinstance,node2complainsofbeingblockedbynode1,thenthealarmconditionisnotreallyimpactingyourOracleRACinstanceasawhole.Thewholeideaoftheaggregatedmetricsisnottostarthyperventilatingifonenodehappenstohavecontention.

Asinyouseeinbelowscenario,thealarmisraisedeventhoughthereisnoproblemonRACasawhole.Hereyoucanseethatinstance1createdalockandinstance2islockedandmustwait.Wedonotknowwhythesecondinstanceisblocked.

ThisisaperfectexampleofatotalRACclustercentricmetricsthatnootherdiagnostictoolprovides.Youcangetfulldetailsonthesessionifyouclickonasessionwiththesubdrilldown.

Iflockcontentionbecomessufficientlywidespread,SpotlightonRACwillraiseacluster-widealarm;otherwise,onlythoseinstancesufferingfromlockcontentionwillshowanalarm.Thesamelogicisusedforotherpotentialproblemssuchaslatches,bufferbusy,poorIOresponsetimes,etc.

BalanceAlarm

AnideallyloadbalancedclusteriscrucialforthefunctioningofahealthyandproductiveOracleRAC.Manyhavefaceddefeatandshamewhenmovingfromasinglenodetoaclusteredsituation,inthehopeofsavingface.

AsQuest'sTechnicalManualstates:

"SoRACcalculatesbalancebyrecordingauser-definablemetricofloadovertimeoneachinstance（thedefaultisLogicalReads,buttheusercanswitchthistoCPUusageorPhysicalWritesviatheSpotlightonRACOptions）,andcalculatingtherelativebalanceofloadacrossthecluster.Thisbalanceisrepresentedasapercentagewhere100%representsperfectlybalanced（eachinstanceintheclusterisdoingthesameamoun