Using Database Replay to Perform Real-World Testing

Purpose

This tutorial shows you how to use the Database Replay feature to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Setting Up Your Environment
 Using Database Replay
 Viewing Database Replay Reports
 Making a Database Change and Replaying the Workload
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What is Database Replay?

The Database Replay feature enables users to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system. The replay on the test system can be done with production characteristics including timing and concurrency. It also provides analysis and reporting to highlight potential problems (for example, errors encountered and divergence in performance) and recommend ways to remedy the problems.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Download and unzip the dbreplay.zip file into a working directory.

Back to Topic List

Setting Up Your Environment

Prior to using Database Replay, perform the following steps to set up your environment:

1.

Open a terminal window. Change to the dbreplay directory and execute the first_time. csh script to set up the files.

                               
./first_time.csh
                            

If you execute first_time.csh a second time you won't receive the error.

 

2.

Execute the wcr_demo_init.csh script to set up the environment.

 

3.

Add the dbreplay directory to your PATH variable and add the $ORACLE_HOME/lib directory to the LD_LIBRARY_PATH variable. From your terminal window, execute the following commands:

                               
export PATH=.:$PATH
e
                                
xport LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/lib
                              
                            

 

Back to Topic List

Using Database Replay

In this section you will capture your production workload and use Database Replay to replay the workload. Enterprise Manager provides an interface to manage each step in the Database Replay process.

A Creating a Directory for the Capture
B. Capturing the Production Workload
C. Processing the Captured Workload
D. Replaying the Workload

Back to Topic List

 

Creating a Directory for the Capture

In the next subtopic, you will perform a capture. Prior to using Enterprise Manager Database Control to perform the capture, you must create a directory in the operating system. Perform the following step to create a directory for your capture:

1.

Open a terminal window and create a directory named capture_dir in your home directory.

 

 

Capturing the Production Workload

To use Database Replay, you must first capture the production system workload that you want to use during the replay. Perform the following steps to capture the production workload:

1.

Using your browser, invoke Enterprise Manager Database Console by enter the following URL: https://<hostname>:1158/em (Note: Specify localhost or your specific hostname in place of <hostname>).

Log in as the SYS user with the SYSDBA password for your database.

 

2.

Select the Software and Support tab.

 

3.

In the Real Application Testing section, select Database Replay.

 

4.

For the Capture Workload task, click the Go to Task icon.

 

5.

Click the Acknowledge check box for each prerequisite and click Next.

 

6.

On the Capture Workload: Options page, select Do not restart the database prior to capture and click Next.

 

7.

On the Capture Workload: Parameters page, accept the default name and click Create Directory Object.

 

8.

On the Create Directory Object page, enter CAPDIR for the name (make sure it is in UPPERCASE). Specify the directory name for the directory you created in the previous substep in the Path field. Click Test File System.

 

9.

On the Host Login page, enter the operating system username and password. Click Login.

 

10.

A confirmation page is displayed. Click Return.

 

11.

You are returned to the Create Directory Object page . Click OK.

 

12.

You are returned to the Capture Workload: Parameters page and an update message is displayed. Click Next to continue with the capture setup.

 

13.

The Capture Workload: Schedule page is displayed. Accept the default Job Name. In the Job Schedule section, accept the default of Immediately in the Start subsection and Not Specified in the End subsection. (In your own environment you should select the capture period based on the nature of your applications and their peak periods.) Scroll down to the bottom of the page.

Enter the host and database credentials. Click Next.

Note: Make sure to run the workload as soon as the capture begin Enterprise Manager screen is displayed. Similarly stop the workload capture as soon as the workload completes otherwise the Elapsed Time of Workload Capture and Replay may not match due to idle activity on the system. DB Time (and not Elapsed Time) should be used for assessing database replays.

 

14.

Review the capture information. Click Submit to start the capture.

The View Workload Capture page is displayed.

 

15.

Return to your terminal window immediately and change to your dbreplay directory.

Execute the wcr_demo_workload.csh script. Monitor the script. When it has completed, return to Enterprise Manager Database Control.

 

16.

In Enterprise Manager Database Control, click Stop Capture.

 

17.

Confirm that you want to stop the capture by clicking Yes.

 

18.

Click Yes to export the Automatic Workload Repository data.

 

19.

The workload summary page is displayed. Review the statistics. Click OK to return to the Workload Capture and Replay page. It may take the export take 1-2 minutes to complete.

 

20.

Return to your terminal window. Change to the capture_dir directory and verify that at least seven files with the .rec extension have been created in the directory.

 

Back to Topic

Processing the Captured Workload

In the last subtopic you captured the workload. Prior to using the captured workload for replay, it must be processed. During the processing, the captured data is transformed into replay files and required metadata is created. Perform the following steps to process the captured workload:

1.

Return to the Database Replay page in Enterprise Manager Database Control. For the Preprocess Captured Workload task, click the Go to Task icon. Note: Preprocessing of the captured workload only needs to be done once on the version that the workload is replayed.

 

2.

Select the directory object you specified for the capture in the previous subtopic in the Capture Directory drop-down menu.

 

3.

Summary information is displayed. Click Preprocess Workload.

 

4.

The Preprocess Captured Workload: Database Version page is displayed. Review the information displayed. Click Next.

 

5.

The Preprocess Captured Workload: Schedule page is displayed. Accept the default Job Name and Start time of Immediately. Enter the host username and password. Click Next.

 

6.

The Preprocess Workload: Review page is displayed. Review the information displayed. Click Submit.

 

7.

A confirmation message is displayed. Click View Job.

 

8.

The Job Run page is displayed. Verify that the job status is "SUCCEEDED." Click the Database tab.

 

Back to Topic

Replaying the Workload

Enterprise Manager is used to replay the workload. In addition, the workload replay is accomplished by using replay clients connected to the replay database. Perform the following steps to replay the workload:

1.

Return to your terminal window. Change to your dbreplay directory. Execute the wcr_demo_init.csh script to restore your database to its original state.

 

2.

Return to Enterprise Manager Database Control. Select the Software and Support tab.

 

3.

Under Real Application Testing, select Database Replay.

 

4.

For the Replay Workload task, click the Go to Task icon.

 

5.

In the Replay Directory drop-down menu, choose the directory object that contains your capture ( CAPDIR).

 

6.

The page is refreshed with information about your capture. Click Set Up Replay.

 

7.

The Replay Workload: Prerequisites page is displayed. For the purposes of this tutorial, no changes are required. Click Continue.

 

8.

The Replay Workload: References to External Systems page is displayed. Click Continue again.

 

9.

The Replay Workload: Choose Initial Options page is displayed. Enter a name in the Replay Name field or accept the default. Click Next.

 

10.

The Replay Workload: Customize Options page is displayed. In this tutorial you do not need to make any changes because you are executing the replay on the same database instance. Click Next.

 

11.

The Replay Workload: Prepare Replay Clients page is displayed. You are alerted to the fact that you need to connect the replay clients. You will connect the client in a couple of steps. Click Next.

 

12.

The Replay Workload: Wait for Client Connections page is displayed.

 

11.

Return to your terminal window and execute the following command to start the replay client. The user specified must have the BECOME USER privilege.

wrc REPLAYDIR=<capture directory path> USERID=system PASSWORD=<system password>

 

12.

Return to Enterprise Manager Database Control. A connection was detected. Click Next on the Replay Workload: Wait for Client Connections page.

 

13.

The Review page is displayed. Click Submit.

 

14.

The View Workload Replay page is displayed. You can watch the progression of the replay.

 

15.

After the workload replay is finished, the status is changed to "COMPLETED." Note: You should assess database replay to capture by checking DB Time, Top wait events, errors, data divergence. In this case, the following screenshots show that there was no data and error divergence.

 

16.

Return to your terminal window and note that the "Replay finished" message is displayed and control is returned to the operating system.

 

Back to Topic

Viewing Database Replay Reports

You can view a number of Database Replay reports by performing the following steps:

1.

Switch back to Enterprise Manager and from the View Workload Replay window, click the Report tab.

 

2.

Under Workload Replay report, click Run Report.

Note: If the report does not display, make sure that your pop-up windows are not blocked in your browser.

 

3.

Review the information on the report. When done, click OK.

 

4.

You can also view an AWR Compare Period Report. Click Run Report.

 

5.

The report is shown. Review the report. When done, click OK.

Note: This OBE illustrates the Database Replay workflow, not so much to compare that the performance characteristics (such as DB Time/Elapsed times)  for workload capture/replay. Due to the extremely short nature of the workload (2-3 minutes) and two AWR snapshots that are taken during this short duration by capture and replay each, the DB replay results may show some performance divergence. For longer workload captures and replays, this generally should not be a problem.

 

6.

Click OK.

 

Back to Topic List

Making a Database Change and Replaying the Workload

This time you will make a change to the database and then perform the replay again. This time, the replay should take more database time because the workload is update intensive and updating the indexes is costly.   However, you might not see any difference because the table the workload is updating is too small and was made for demo purposes. Perform the following steps:

1.

Return to your terminal window. Change to your dbreplay directory. Execute the wcr_demo_init.csh script to restore your database to its original state.

 

2.

Execute the wcr_demo_dbchange.csh script to create some indexes on your tables.

 

3.

For the Replay Workload task, click the Go to Task icon.

 

4.

In the Replay Directory drop-down menu, choose the directory object that contains your capture ( CAPDIR). The page is refreshed with information about your capture. Click Set Up Replay.

 

5.

The Replay Workload: Prerequisites page is displayed. For the purposes of this tutorial, no changes are required. Click Continue.

 

6.

The Replay Workload: References to External Systems page is displayed. Click Continue again.

 

7.

The Replay Workload: Choose Initial Options page is displayed. Enter a name in the Replay Name field or accept the default. Click Next.

 

8.

The Replay Workload: Customize Options page is displayed. In this tutorial you do not need to make any changes because you are executing the replay on the same database instance. Click Next.

 

9.

The Replay Workload: Prepare Replay Clients page is displayed. You are alerted to the fact that you need to connect the replay clients. You will connect the client in a couple of steps. Click Next.

 

10.

The Replay Workload: Wait for Client Connections page is displayed.

 

11.

Return to your terminal window and execute the following command to start the replay client. The user specified must have the BECOME USER privilege.

wrc REPLAYDIR=<capture directory path> USERID=system PASSWORD=<system password>

 

12.

Return to Enterprise Manager Database Control. A connection was detected. Click Next on the Replay Workload: Wait for Client Connections page.

 

13.

The Review page is displayed. Click Submit.

 

14.

After the workload replay is finished, the status is changed to "COMPLETED." Click the Report tab.

 

15.

Under Workload Replay report, click Run Report.

 

17.

Review the information on the report. When done, click OK.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Capture your production workload
 Process the captured workload
 Replay the captured workload

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document