Wednesday, May 2, 2012

Oracle Database Replay in Oracle database 11g

Database Replay and SQL performance analyzer are the two tools of Oracle new Real Application Testing (RAT) feature. If there is a change in database in terms of new code promotion, software or OS then these two tools of RAT can help to predict how the production server is going to cope with that change

Oracle Supplied Packages for Database Replay

1. DBMS_WORKLOAD_CAPTURE
2. DBMS_WORKLOAD_REPLAY

Oracle Supplied Packages for SQL Performance Analyzer

1. DBMS_SQLPA

In this article we are going to discuss "Database Replay"

Database Replay:

Database Replay allows you to capture database workload on the production server and replay it on test server by using the above mentioned database replay oracle supplied packages.Once the testing is done, reports can be produced to analyze and implement changes, if any recommend by database replay.

1. Workload Capture: this is when you record the production database workload.
2. Workload Preprocessing: this is to convert the captured workload into replay files.
3. Workload Replay:after the step above, apply the changes on the test system.
4. Analysis and Reporting: when the step above successfully finishes, you generate report on the errors and performance influence.

Things to consider before capturing the workload:

1.Take production database backup that can be used to create a testing env. for database replay testing
2.Shut-down and restart the database to ensure all pending transactions are finished before the replay
3.Create directory object for storing captured load by connecting as sysdba user as shown below

fname specifies the filter name. This can be any namefattribute specifies the filter attributes such as program, module, action, service, instance_number, and user.fvalue specifies the user name for which the workload has to be captured

below is the SQL to find out the existing filters in the database

SQL> SELECT NAME, ATTRIBUTE, VALUE FROM DBA_WORKLOAD_FILTERS;

In case if you want to remove the above filter then use the below mentioned script

dir specifies the directory object pointing to the workload capture directory created in step 2.

duration specifies the number of seconds for which the workload will be captured.

If you want to specify the duration for the workload capture process then use the AWR (automatic workload repository) or ASH tools to select the appropriate period based on the workload history of the production database

To stop the capture process before ending of duration period, issue the following command:

begin

dbms_workload_capture.finish_capture ;

end;

The workload capture will continue indefinitely, until you stop it with the FINISH_CAPTURE procedure or time specified by the duration parameter is reached.
You can also use the DBA_WORKLOAD_CAPTURES view to see all the workload captures performed by the database.

SQL> SELECT id, name FROM dba_workload_captures;

ID NAME

---------- ------------------------------

1 2012May

Checkout the capture directory to see if there are any files generated

$ cd /u01/app/oracle/db_replay

$ lswcr_cr.html wcr_cr.text wcr_fcapture.wmd wcr_scapture.wmd

we can get the ID of the capture run by passing directory name as below

To be able to replay a Workload Capture, you must preprocess its data. Although it is possible to preprocess in the production database, practically it is done in a test database. Preprocessing includes the following steps:

1. Restore the test database from the backup you made in the production database. The target is to make the same application become in the same state as it has been in the production database.

2. Create a directory object in the test database to hold the workload capture data files.

3. Initialize the Replay Data: this process means metadata will be read from Workload Capture files and loaded into tables. Workload replay process will read from those tables when it operates. Initializing the replay data is done by invoking the procedure INITIALIZE_REPLAY

Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.