Database replay was introduced in Oracle 11g R1, and it allows you to capture workloads on a Production system and replay them in your Development or test environments while maintaining the unique characteristics of the workload. This enables the user to test a variety of system changes such as hardware/software migration, operating system/ database upgrade, patches, database config changes etc. and eliminate the risks before implementing into production.
There are many performance simulation tools available, but there is a significant cost involved implementing them, especially licensing, procuring and configuration of servers, and time. In order to execute these tools users need to create scripts, develop queries and provide a range of parameters to run the load, but this may only provide a small part of the production work load, which will not simulate your actual production load. It’s a significant cost and a lot of effort for businesses to test the changes before implementing them in production. However, despite such testing many issues often go undetected until deployed into production.
The Database replay has the ability to test real production workload in a cost-effect way. This solution does not require any scripts to be maintained, and as a result the testing effort can be significantly reduced. This allows the enterprise business to quickly adopt new technologies, and changes while eliminating the risk. Database replay will help you to test performance as well as application functionality.
Users can capture the workload either using Enterprise Manager GUI Interface or command line interface provided by DBMS_WORKLOAD_xxxxx packages. Users are able to capture the load on database systems running 10g R2 or higher and workload reply is only supported on databases running 11g R1 and higher.
There are mainly four phases involved in Oracle Database replay:
1.Workload capture
2.Workload pre-processing
3.Workload replay
4.Analysis and reporting
1. Workload capture
In this phase users will capture the production workload from the database. Enabling the workload capture involves tracking and recording all external requests from users, application etc. and changes will be stored in binary files called capture files. These files will have information such as SCN, SQL Text, bind variables etc.
Create a directory where you want to keep all your capture files:
SQL> Create or replace directory db_replay_dir AS ' /home/oracle/tools/db_replay_dir';
Use START_CAPTURE procedure to start workload capture:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘prd_server_capture’,
dir => ‘db_replay_dir ‘,
duration => 900,
capture_sts => TRUE,
sts_cap_interval => 300);
END;
/
If the 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.
Use the FINISH_CAPTURE procedure to stop the workload capture:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/
To add filters to workload use ADD_FILTER procedure and to delete the filter use DELETE_FILTER.
2. Workload pre-processing
In this phase, information in the capture files will be preprocessed and create all the metadata needed for replaying the workload. This step can be resource and time consuming and it’s recommended to perform this step on a test system where you want to replay the database work load.
Copy the capture files from production to test server
It’s recommended to create a restore point, so that changes can be reverted after replay and same database used for other database work load tests:
SQL> CREATE RESTORE POINT before_replay;
Create a directory on the test server where you want to copy the files:
SQL> Create or replace directory db_replay_dir AS ' /home/oracle/tools/db_replay_dir';
Use the PROCESS_CAPTURE procedure to preprocess the captured workload:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'db_replay_dir');
END;
/
It’s important that the preprocessing is done with the same database versions. Users must copy the capture files from production to the test system.
3. Workload replay
In this phase users can replay the workload that’s captured and pre-processed. All the external requests which are recorded will be performed on the test system with same time and concurrency.
Use the INITIALIZE_REPLAY procedure to initialize workload replay:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name=> ‘prd_server_replay’,
replay_dir => ‘db_replay_dir’);
END;
/
Use the 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/tools/db_replay_dir
Workload Replay Client: Release 11.2.0.1.0 - Production on Thu Apr 11 19:24:01 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Report for Workload in: / home/oracle/tools/db_replay_dir
-----------------------
Recommendation:
Consider using at least 2 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 3 sessions
- total number of sessions: 10
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
After determining the necessary clients and hosts needed to replay the workload, start the replay clients in replay mode:
[oracle@server1]$ wrc 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)
Use the START_REPLAY procedure to start a workload replay:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/
4. Analysis and Reporting:
In this phase users will perform detailed analysis of the reports from both the workload capture and workload replay phases. These reports will include information about errors, statistics about database time, average time spent, user call, session info and data divergence.
For advanced analysis and comparison users can use AWR (Automatic work load repository) reports and SQL Performance Analyzer to compare the SQL tuning set from capture and replay.
Query the DBA_WORKLOAD_REPLAYS view to see the information about all the workload replays.
Use DBMS_WORKLOAD_CAPTURE.REPORT function to generate Workload capture report:
DECLARE
V_CAPID NUMBER;
V_REPORT CLOB;
BEGIN
V_CAPID := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => ‘db_replay_dir’);
V_REPORT := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => V_CAPID,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/
Where:
•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
To get the capture_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.
The report contains the information about workload capture such as filters, date, time, SCN of capture and overall statistics such as total database time captures, no of logins and transactions and any limitations due to version etc.
Use DBMS_WORKLOAD_REPLAY.REPORT function to generate Workload replay report:
DECLARE
V_REPOTT CLOB;
V_CAPID NUMBER;
V_REPID NUMBER;
BEGIN
V_CAPID:= DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => ‘db_replay_dir’);
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;
/
Where:
•dir specifies the directory which contains the workload replay files.
•replay_id specifies the ID related to the directory which contains the workload replay files. To get the replay_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.
•Formatspecifies the report format and three formats available.
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
The report contains the information about workload replay and capture such as job name, duration, status, time, path, database information, replay clients and overall statistics like total database time captured and replayed, no of logins, transactions, replay divergence, error divergence etc.
Once the analysis is competed, users are able to restore the database to its original state and can perform other replay tests.
If you have created a restore point use it to restore the database, otherwise use your back to restore.
Shutdown the database and startup in MOUNT state:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Restore the database to restore point and open the database with reset logs:
SQL> FLASHBACK DATBASE TO RESTORE POINT before_replay;
SQL> ALTER DATABASE OPEN RESETLOGS;
The users can use Enterprise manager for Database replay, it’s a user friendly interface and users that don’t have much experience using the command line can use EM for Database replay.
The Database replay can be located under “Software and Support”:
