Oracle Database Replay feature for test workload in testing environment

Oracle Database Replay for your Workload Test

Capture workloads on a Production system and replay on your Development or Test environments for testing
workload of any patch deployment, upgradation and any change in coding.

Four phases involved in Oracle Database replay:
Workload capture
Workload pre-processing
Workload replay
Analysis and reporting

1. Workload Capture: ( Production environment)

Create or replace directory db_replay_load AS '/home/oracle/loads/';BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'prd_server_load_capture',
dir => 'db_replay_load',
duration => 900,
capture_sts => TRUE,
sts_cap_interval => 300);
END;
/

If DURATION parameter value is not specified then workload capture will continue until the FINISH_CAPTURE procedure is used.
Use capture_sts parameter to capture a SQL tuning set in parallel with the workload capture.
The default value of STS_CAP_INTERVAL is 300 and this parameter specifies the duration of the SQL tuning set capture.

Finish the capture:

BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

2. Workload pre-processing:
Copy the capture files from production to test server

Create restore point in test server:

CREATE RESTORE POINT before_replay;create directory in test server:
Create or replace directory db_replay_load AS '/home/oracle/loads/';

PROCESS_CAPTURE procedure to preprocess the captured workload:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'db_replay_load');
END;
/

3. Workload replay
INITIALIZE_REPLAY procedure to initialize workload replay:

BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name=> 'prd_server_load_capture',
replay_dir => 'db_replay_load');
END;
/

--PREPARE_REPLAY procedure to prepare workload replay on the test database system:
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE,
capture_sts => TRUE,
sts_cap_interval => 300);
END;
/

The synchronization parameter default value is SCN this means the COMMIT order will be preserved and replay actions will be executed only after all dependent COMMIT actions are complete.

Before you run the replay procedures make sure to estimate the number of clients & hosts necessary to replay the captured workload using wrc.exe (workload replay client):

[oracle@server1]$ wrc mode=calibrate replaydir=/home/oracle/load/
[oracle@server1]$ rc system/xxxx@test_db mode=replay replaydir= /home/oracle/tools/db_replay_dir

Workload Replay Client: Release 11.2.0.1.0 - Production on Thu Apr 11 19:28:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (19:28:32)

BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

4.Analysis and Reporting:

DECLARE
V_CAPID NUMBER;
V_REPORT CLOB;
BEGIN
V_CAPID := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'db_replay_load');
V_REPORT := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => V_CAPID,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/

dir specifies the directory which contains the workload capture files
capture_id specifies the ID related to the directory which contains the workload capture files

DECLARE
V_REPOTT CLOB;
V_CAPID NUMBER;
V_REPID NUMBER;BEGIN
V_CAPID:= DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'db_replay_load');
SELECT MAX (id) INTO V_REPID
FROM dba_workload_replays
WHERE capture_id = V_CAPID;
V_REPORT:= DBMS_WORKLOAD_REPLAY.REPORT(
replay_id => V_REPID
format =>
DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/

To Generate a TEXT report use DBMS_WORKLOAD_REPLAY.TYPE_TEXT
To Generate a HTML report use DBMS_WORKLOAD_REPLAY.TYPE_HTML
To Generate a XML report use DBMS_WORKLOAD_REPLAY.TYPE_XML

Rollback the test database to flashback time:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO RESTORE POINT before_replay;
SQL> ALTER DATABASE OPEN RESETLOGS;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.