Upgrade Case Study: Database Replay, Snapshot Standby

Upgrade Case Study: DatabaseReplay, Snapshot Standby andPlan BaselinesArup Nanda

About MezzzOracle DBA for 16years and countingSpeak at conferences,write articles, 4 booksBrought up the GlobalDatabase Group atStarwood Hotels, inWhite Plains, NYDatabase 11g Upgrade2

What You will LearnzzzzA rehash of our 11g Upgrade ExperienceWhat challenges lay during our upgradeWhat tools are available with OracleHow we used these tools to meet these challengesThe information is for educational purpose only; notprofessional advise or consultation. Starwoodand the speaker make no warranty about theaccuracy of the content and assume no responsibilityfor the consequence of the actions shown in theslides.Database 11g Upgrade3

Must ReadzzzMetaLink Note 429825.1 shows the steps for a manualupgradeMetaLink Note 601807.1 Upgrade Companion for11gR1: a one stop paper for upgrade.z Note 837570.1 for 11gR2A very important step [Never skip it] – check fordangling dictionary objects – MetaLink 579523.1Database 11g Upgrade4

Database DetailszzzA lot of applications; not just onez A lot of business processes; not just a fewVery critical business functionalitiesz A high amount attributed to downtime or slowness(which also translates to downtime since the appstime out)Version 10.2.0.4 was pre-upgradeDatabase 11g Upgrade5

1.0.7No Flash Recovery AreaFlash Recovery AreaFlashback not EnabledFlashback EnabledNon-OMFOracle Managed FilesOlder hardwareNewer hardwareNo partitioningPartitioningNo CompressionCompressionSome parametersChanged paramsLinux RHAS 4Linux RHAS 5Database 11g Upgrade6

The zillion QuestionzzIf it ain’t broken don’t fix it – is generally the mantraMust Have Answersz What will happen – will the database at least performas much as right now, or it might be worse?z How do we know?z How certain are we?Database 11g Upgrade7

Why Worse?zzzzOptimizer Plans could be change for better (or, worse) –performance relatedFunctionality may have changed, producing unexpectedresultsNew bugs may be encountered for which there will be nopatches, at least not immediatelySome new functionality may require further attentionDatabase 11g Upgrade8

The Usual PlanzzzCreate new environment (pre-prod) and run theproduction-like events there and examine theperformanceThe key is it is “production-like”; not actual events thatoccurred in production.Usually synthetic, concoctedDatabase 11g Upgrade9

So, what’s Problem?zzzSynthetic transactions are not faithful reproductions ofthe events that actually happenedThey are mechanized and repeatable, but do not captureproduction dynamicsConcocted ones do not take into account unique datavalues.z Example: name searches are more on “Johnson” inNew York while in Los Angeles, it’s “Lee”Database 11g Upgrade10

The Ugly TruthzzzWill the database work the same way (if not better) afterthe upgrade?Synthetic transactions will not give you the answerTo get that, you must ask the users to redo the activitiesexactly how they did in real productionz In the same order, using the same breaks in between!Database 11g Upgrade11

ChallengeszzzBuilding a test systemz quickly, easily, accurately, repeatedlyDry runs of UpgradesEnsuring performancez Repeating the activities of the productionzzzaccuratelywithout impacting productionImpact of new parametersDatabase 11g Upgrade12

Additional ChallengeszYou want to change something else during the upgrade(since you have an outage)z Convert to RACz Storage to ASMz Change buffer poolsz Change some parameter, such as cursor sharingz Take advantage of new features, e.g. LOBs toSecurefilesDatabase 11g Upgrade13

Tools at your DisposalzzzzzzzDatabase ReplaySQL Performance AnalyzerSQL Tuning AdvisorSQL Plan ManagementEasier Standby BuildingSnapshot StandbySwitching between Physical and LogicalDatabase 11g Upgrade14

Concocting Prod WorkzzzzWorkload generator tools such as Load Runner cansimulate user actions,z Capture clickstream on a webpagez Databank parameters to simulate loadz Coverage for important workflows onlyUpgrade involves only one changed partz Application - App Server - Databasez So, there is no need to test the entire stackCost of QA is not insignificantAvailability of QA is not automaticDatabase 11g Upgrade15

Parts of a SystemApp ServerDatabaseO/SApplicationsThese are theonly parts thatare changingDatabase 11g UpgradeStorage16

Workload Generation ToolsApp ration ToolsUsually Work HereThey may workhere; but in thatcase, it becomes apure SQL “runner”StorageWhat are the SQLs? When to run? Howto sequence?Database 11g Upgrade17

Questions for “SQL Running”zzzzzzWhat SQLs were executedHow often was each one executedz Determines parsing, buffer cache hits, etc.In what order were they executedz Determines buffer hitsHow much was the time between themz Determines buffer hits, parsingWhat optimizer environment was in effectz Someone sets DBFMBRC before running an SQLand then resets it to defaultAre sequence numbers guaranteed?Database 11g Upgrade18

Capturing the WorkzzzzSQL Tracez Captures the SQL statements, in order, with plans10046 Tracez Captures the SQL statements with timestampz Bind variables10053 Tracez Captures the optimizer environmentBut, how will you put the information from all thistogether to produce something that is:z Executablez RepeatableDatabase 11g Upgrade19

Database ReplayzzzzzThis is where Database Replay really shinesIt captures the actual transactions from the productionsystem, in the same order, with the same breaks inbetweenIt’s as if the users are redoing the same activities in frontof the test systemEven sequence numbers are fetched the same way theyoccurred in productionNo primary key violationDatabase 11g Upgrade20

Workload CapturezzzzzzThe package dbms workload capture capturesworkload from current productionThe package exists in 11g, so what about 10g?In 10.2.0.4 it existsFor earlier versions, a patch needs to be appliedz Refer to MetaLink Note 560977.1 for detailsThe easiest is to use Enterprise Manager Grid ControlGrid Control 10.2.0.5 has the toolkitDatabase 11g Upgrade21

StepszzzzzzCapture WorkloadIt produces a set of files with extension *.recMove them to the 11g systemUse Replay feature in command line or EM to replay theactivitiesBoth these activities take AWR snapshots before andafter events. Use AWR Compare Period Report tocompare the performance.Check session S311835 for Database Replay DemoDatabase 11g Upgrade22

Capture from 10gzCreate a directory to hold the rec filescreate directory RAT as ‘/oracle/rat’zAdd a FilterzBEGINdbms workload capture.add filter(fname 'abcd filter',fattribute 'USER',fvalue 'ABCD');END;Allows you to capture only those for the user called ABCD.Database 11g Upgrade23

zStart the Capture ProcessBEGINDBMS WORKLOAD CAPTURE.START CAPTURE (name ‘capture1',dir 'RAT',duration 3600,default action 'EXCLUDE',auto unrestrict TRUE);END;zIt will generate a lot of files in the format wcr *.rec in the/oracle/rat directory.Database 11g Upgrade24

zGet the capture IDselect ID from dba workload captureswhere status ‘COMPLETED’zExport the AWRbegindbms workload capture.export awr(capture id captureid );end;/zzAWR will also be exported as a dumpfile in the/oracle/rat directory.Copy all the files in that directory to the target systemDatabase 11g Upgrade25

Replay Steps1.2.3.4.Create directory on the targetPre-process the captured workloadReplay the workloadFrom the command line wrc system/managerreplaydir /u01/oracle/ratDatabase 11g Upgrade26

During ReplayGives you an ideaabout how much isleftDatabase 11g Upgrade27

Get the ReportsThis “compare”report, aka “Diffdiff Report” is themost important. Itshows thesystem stats onthe target and thesource when thesame activitieswere occurredthere.Database 11g Upgrade28

SQL Performance AnalyzerzzzSome SQLs showed regression, i.e. theyunderperformed compared to 10gYou need to know whyz optimizer environment, bind variables, etc?SPA allows you to run captured SQLs in differingenvironmentsz In the same database butzzzzDifferent optimizer parametersDifferent ways of collecting stats,With pending stats in 11g, can validate on PROD duringmaintenance windows/non-peakDifferent indexes, or MVsDatabase 11g Upgrade29

Source of SQLszzzzShared PoolCaptured from Production during a workloadStored in a SQL Tuning Set (STS)Continuous Capture functionality to capture all SQLsSTSSourceSTSExportAndImportDatabase 11g UpgradeTargetReplay30

Capture from 10gThe following captures the SQL Statements into a SQLTuning Set (STS) in 10g.BEGINdbms sqltune.capture cursor cache sqlset(sqlset name '10GSTS',time limit '3600',repeat interval '300',sqlset owner 'SYS');END;This incrementally captures the SQL statements every 5mins for 10 hours.z You can export this STS and import into 11g.zDatabase 11g Upgrade31

SPA TaskszzzzzCreate an SPA Task on the STS importedReplay with Optimizer 10.2.0.4Replay with Optimizer 11.1.0.7Compare and make adjustmentsRepeat 2 through 4 as neededDatabase 11g Upgrade32

SPA Optimizer ChangeCreate an SPA Task onthe STS importedDatabase 11g Upgrade33

CompareElapsed timesignificantlyreducedMajority ofSQLs didn’tsee their planchanged!Database 11g Upgrade34

Compare Shows the SQL IDs,we can find fromv sqlPlan changed for this SQL, UsingSQL ID, check from v sqlDatabase 11g Upgrade35

Clicking on the SQL ID you cansee the various stats on the SQLSee the SPA Demo at SessionS311324You can call upon SQL TuningAdvisor to suggest possibletuning options on this SQLThe report continues with the plansbefore and after the upgrade, so youcan compare themDatabase 11g Upgrade36

SQL Plan ManagementzzzWhat happens when the plan is actually worse?Perhaps the plan is better when a different optimizerenvironment parameter is used?In that case, we used SQL Plan Management to let theoptimizer pick the right plan from the pool of plansDatabase 11g Upgrade37

SPMzzzzAnalogous to Stored OutlinesBut unlike outlines, baselines:z Calculate the plan anyway; but don’t use it.z The DBA must check and mark a plan good by“accepting” it – a process called “evolving”z Have multiple plans in the baseline and choose thebestSo it is the best of both worldsCheck session S309133 for SPM DemoDatabase 11g Upgrade38

Strategy with SPMzzzzzzIf a plan is “fixed”, that is used, regardless of thepresence of other plansCapture all the plans from 10g to an SQL Tuning SetLoad them to 11g after upgradeMark all of them as fixedz So, the plans will be the same as 10gTurn on capture baselines; the new plans will be storedin the baselinesEvolve them to see if any plan is betterDatabase 11g Upgrade39

Test System Creation10g10gData GuardOriginalSystemNewSystemDatabase 11g Upgrade40

Test System Creation1. Start the DB WorkloadCapture Process10g2. Simultaneously break DataGuard11g3. Convert the Standby tosnapshot standbyXData e 11g Upgrade4. Upgrade the standby to11g41

Converting 10gR2 Standby to RWPrimaryStandby1.2.1.2.alter database recover managed standbydatabase cancel;create restore point gold guaranteeflashback database;alter systemarchivelogcurrent;alter systemlog archive dest state 2 defer;1.2.3.4.5.alter database activate standbydatabase;shutdown/startup mountalter database set standby database tomaximize performance;alter system log archive dest state 2 defer;Database 11g Upgrade42alter database open;

Test System CreationEnableDatabase forFlashback10gOriginalSystem11gNewSystemDatabase 11g Upgrade43

Test System CreationTake playWorkloadNewSystemDatabase 11g Upgrade44

Test System Creation10gThe workload hasFlashback thedatabase to the been captured onlyonce; and replayedRestore Pointseveral times.11gReplayWorkloadRepeat this asoften as neededOriginalSystemNewSystemDatabase 11g Upgrade45

Actual Upgrade1. 10g Æ 10g Standby2. Stop Data Guard3. Upgrade the standby to 11g10g4. This becomes the newproduction11gX5. The old prod is still availableas of that point in timeData e 11g Upgrade46

Post Upgrade Tweaking11g11gData GuardStandbyNewSystemDatabase 11g UpgradePhysicalStandby,Convertedfrom the oldsystem47