Oracle Database 11g: Database Replay, Part 2 - Page 2
May 30, 2008
Capturing an Actual Workload
To simulate multiple executions of similar code by different users, Ive prepared a simple shell script named RandomLoadGenerator.sh that executes simple queries that do CPU intensive computations, generates complex queries against the AP schema, and also grinds through the insertion of several thousand rows into the new AP schemas tables. Ive also configured my source database environment to use multiple service names, one for each class of users, as shown in SI_Services_tnsnames.ora. Ive added those service names as potential connection aliases in my databases TNSNAMES.ORA configuration file.
I started this workload against my P+0 database environment. Once it was complete, I logged back into EM Database Control and viewed the results of its execution as shown in Figure 2.2.1 below, and then clicked on the Stop Capture button to terminate the workload capture.
Click for larger image
Oracle 11g then asks for confirmation of the capture tasks termination, and displays a stopwatch until the capture has been completed.
Just as capture termination completes, Oracle 11g asks if Id like to capture the corresponding Automatic Workload Repository (AWR) data for the recorded workload. As shown in Figure 2.2.4 below, Ive selected to capture any related AWR snapshot(s) for later comparison reporting.
Once the capture is complete, I can review the Oracle 11gs capture results to determine if the capture was successful and if it contains sufficient data. If I find that its insufficient, the FLASHBACK DATABASE command set allows me to rewind the database to just before capture started and then rerun the capture. I also selected the View Workload Capture Report button and generated a Database Capture Report that summarizes the completed capture.
Phase 2: Preparing for Replay
Even though Ive successfully completed capturing a sufficient workload within my P+0 database environment, I still have some work to do before I can play it back against the P+1 environment.
Resetting the P+0 Environment
Since Im using the same database for both the source and target environments, Ill first need to reset my current P+0 database system to the state immediately before I started workload capture. My database is in flashback logging mode, so Ill simply use Recovery Managers FLASHBACK DATABASE command to rewind the database to its initial state:
$> rman target / RMAN> shutdown immediate; RMAN> startup mount; RMAN> reset database to incarnation 6; RMAN> flashback database to scn= 4162947;
Migrating to the P+1 Environment
My next step is to apply the changes necessary to bring my database environment to the P+1 state. For this rather simple demonstration, Ill make two changes that are almost certain to impact the performance of my database in P+1 mode:
Massaging the Workload
Now that my P+1 environment is in place, Im ready to preprocess the workload for playback. Once again, Ill use EM Database Control to initiate the preprocessing sequence. Figure 2.3.1 shows the results of selecting the Preprocess Workload option from the main Database Replay panel once Ive selected the DBRControl directory object as the target for preprocessing:
Once Ive chosen the desired workload, Oracle 11g reminds me that Ive got to use the same database version to eventually replay the selected workload ...
... and then sets up a new EM scheduled task to complete the preprocessing.
Oracle 11g prompts for a final confirmation before submitting the preprocessing task, and then it fires it off immediately.