OCP Blog
Super DBA
The Real Application testing, Database Replay process
by Vazha Mantua Monday, November 8, 2010 12:20 PM

The Real Application testing, Database Replay process

The Real Application testing feature, which consists of two separate tools, Database Replay and the SQL performance Analyzer, is arguably the most significant new feature in the Oracle Database 11.1 Release. With this process we can simulate a real production workload on a test system; we can apply some patches, change data storage and see how our performance changes on test system.
In this post we step by step review Database replay process

1. Restart prod DB and startup restrict mode (this in optional)
2. We can use optional workload filters to restrict the workload capture to only a part of the actual production workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(FNAME => 'FILTER_NAME',FATTRIBUTE => 'USER',FVALUE => 'SCOTT');

END;
We can find filter’s in view DBA_WORKLOAD_FILTERS;
3. Start Workload capture, means start process which collect workload to the some files, which then will apply on test DB:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(NAME => '2010OCT',DIR => 'DATA_PUMP_DIR',DURATION => 1200);
END;

Duration is number of seconds for which workload will be captured this is optional parameter.
DIR is oracle directory which must be empty!
We can manually stop capture process with this statement:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;

We can see status of process in view DBA_WORKLOAD_CAPTURES
4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(CAPTURE_DIR => 'DATA_PUMP_DIR');
END;

5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB
6. Check that oracle directory in test DB link on directory where replay files are located, in this scenario we create directory TEST_DIR
7. Initializing the replay Data means initialize the data, which loads the metadata into tables required by workload process:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(REPLAY_NAME => 'TEST_REPLAY',REPLAY_DIR => 'TEST_DIR');
END;

8. Then we mush remap connections with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(CONNECTION_ID => 111);
END;

9. Prepare the Workload for Replay with statement :
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
END;

There we run procedure with default parameters, for example we can use parameter synchronization, by default true, meaning that the commit order of captured workload will be preserved during the workload replay
10. Set up Reply Clients. The reply driver is a special application that consumes the captured workload by sending replay requests to the test DB. The reply client in essence simulates the production system on the test DB. To Find optimal number of clients we must run this statement on test server in terminal window up to directory where is test_dir
wrc system/a mode=calibrate replay_dir=./test_dir
if we should run with 2 or more clients we must open new terminals and run process in other windows.
Run client with this statement:
wrc system/a mode=replay replaydir=./test_dir
11. Now we can start replay process with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY();
END:

You must start minimum of one wrc client before you can start the workload replay.
You can manually stop Replay process by statement, this is optional, because replay process will finished itself:
BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();
END;

Status and progress of replay process you can see in view : DBA_WORKLOAD_REPLAYS
12. Report and result of replay process you can see with this statement :



DECLARE
CAP_ID NUMBER;
REP_ID NUMBER;
REP_RPT CLOB;
BUFFER VARCHAR2(32767);
BUFFER_SIZE CONSTANT BINARY_INTEGER := 32767;
AMOUNT BINARY_INTEGER;
OFFSET NUMBER(38);
FILE_HANDLE UTL_FILE.FILE_TYPE;
DIRECTORY_NAME CONSTANT VARCHAR2(80) := 'TEST_DIR';
V_FILENAME CONSTANT VARCHAR2(80) := 'REPLAY.HTML';
BEGIN
CAP_ID := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(DIR => 'TEST_DIR');
SELECT MAX(ID)
INTO REP_ID
FROM DBA_WORKLOAD_REPLAYS
WHERE CAPTURE_ID = CAP_ID ;
REP_RPT := DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID => REP_ID,
FORMAT => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
-- WRITE REPORT TO FILE
DBMS_OUTPUT.ENABLE(100000);
-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
FILE_HANDLE := UTL_FILE.FOPEN(LOCATION => 'TEST_DIR',
FILENAME => V_FILENAME,
OPEN_MODE => 'W',
MAX_LINESIZE => BUFFER_SIZE);
AMOUNT := BUFFER_SIZE;
OFFSET := 1;
WHILE AMOUNT >= BUFFER_SIZE
LOOP
DBMS_LOB.READ(LOB_LOC => REP_RPT,
AMOUNT => AMOUNT,
OFFSET => OFFSET,
BUFFER => BUFFER);
OFFSET := OFFSET + AMOUNT;
UTL_FILE.PUT(FILE => FILE_HANDLE,
BUFFER => BUFFER);
UTL_FILE.FFLUSH(FILE => FILE_HANDLE);
END LOOP;
UTL_FILE.FCLOSE(FILE => FILE_HANDLE);
END;







Tags: , ,

Comments (8) -

6/18/2011 1:25:05 AM #

Pearline Frye


Thank you for this wonderfull blog

Pearline Frye United States

6/18/2011 1:25:05 AM #

Latasha Tyler


Interesting point of view, thank you for sharing it.

Latasha Tyler United States

6/18/2011 2:25:26 AM #

Latasha Tyler


Hey, very interesting post, i look forward your next article !

Latasha Tyler United States

9/16/2011 7:54:11 PM #

basil plant

Hi. I needed to drop you a fast be aware to express my thanks. Ive been following your blog for a month or so and have picked up a ton of excellent info and enjoyed the tactic youve structured your site. I am attempting to run my very personal blog nonetheless I think its too general and I must focus on a number of smaller topics. Being all issues to all people will not be all that its cracked up to be.

basil plant United States

9/30/2011 4:50:33 PM #

Advanced Certification

Intrusting informationfrom the developers point of veiw. bookmarked will be back again soon for your next post.

Advanced Certification United Kingdom

6/3/2012 2:23:54 AM #

Directory Submission Service

4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:

Directory Submission Service United Kingdom

6/10/2012 3:26:45 PM #

Iraqi Dinar

5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB

Iraqi Dinar United Kingdom

6/20/2012 2:30:18 PM #

Diminished Value

We can see status of process in view DBA_WORKLOAD_CAPTURES

Diminished Value United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  October 2019  >>
MoTuWeThFrSaSu
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

TextBox