Database Replay:
In critical production systems, before making changes to the environment( Hardware or software related), lots of testing is required in test systems to know the effect of those changes and this usually takes lots of time to test for the stability and effects of the change.
In Oracle 11g this thing is simplified and a new feature “database replay” is added which can be used to simulate the production workload on a test system.
Following are the three stages of DATABASE REPLAY:
1. Workload Capture
2. Workload Preprocessing
3. Workload Replay
1. Workload Capture:
Before starting the capture process you need to take care of following things:
- Login as sys user
- It is always recommened (not mandatory) to shutdown the database and then start the database in restricted mode. By doing so, you will be making sure that no transactions are running on the database before capture process is actually started. It will guarantee the complete workload capture and you will be running full transactions during database replay on test system.
If you do not follow this step then you have to be prepared for some divergance of results during database replay on test system.
<span style="font-family: andale mono,times; font-size: small;">SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.</span> <span style="font-family: andale mono,times; font-size: small;">SQL> startup restrict ORACLE instance started.</span> <span style="font-family: andale mono,times; font-size: small;">Total System Global Area 313860096 bytes Fixed Size 1299624 bytes Variable Size 184552280 bytes Database Buffers 121634816 bytes Redo Buffers 6373376 bytes Database mounted. Database opened. SQL > select LOGINS from v$instance; LOGINS ---------- RESTRICTED</span>
Important thing to note here is that, after starting the capture process the database will automatically be switched into UNRESTRICTED mode and the users will be able to connect normally.
After capturing the initial database information, the capture process will internally fire a statement to disable restricted sessions, this can be verified by setting 10046 trace before starting the capture process as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > alter session set events '10046 trace name context forever, level 4';</span>
- Now start the capture process.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > alter session set events '10046 trace name context off'</span>
You will see following information:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">PARSING IN CURSOR #21 len=39 dep=1 uid=0 oct=49 lid=0 tim=121695902847 hv=14656808 ad='0' sqlid='7shphjbg23h8' alter system disable restricted session END OF STMT PARSE #21:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=121695384843</span>
Now check the information from database:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > select LOGINS from v$instance; LOGINS ---------- ALLOWED</span>
- Create a directory to hold the workload information.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > create directory DIR as '/tmp/ss';</span>
Check the permission of directory ss at OS level and make sure that oracle is able to write to this directory.
Also make sure that the directory is empty, before starting the capture process.
- Define Workload Filters
By adding filters we can restrict the Workload Capture process to be specific to certain user sessions. By default all the sessions will be captured.
There are two types of filters i.e INCLUDE and EXCLUDE.
If INCLUDE is used then the filter will be active and workload for the objects mentioned in the filter will not be captured rather all other user’s workload Will be captured i.e the objects defined in the filter will be FILTERED OUT from capture process.
If EXCLUDE is used, only the workload done by objects defined in the filter will be captured, no other client request to database will be captured.
EXAMPLE:
========
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > exec dbms_workload_capture.ADD_FILTER( fname IN VARCHAR2, fattribute IN VARCHAR2,fvalue IN VARCHAR2);</span>
fname=Name of the filter.
fattribute=Attribute on which the filter will be applied i.e USER, PROGRAM, INSTANCE_NUMBER etc.
fvalue= value for the corresponding attribute.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > exec dbms_workload_capture.ADD_FILTER( fname =>'FILTER_SCOTT',fattribute => 'USER',fvalue => 'SCOTT');</span>
- Starting workload capture:
To start the workload capture START_CAPTURE procedure is used as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > exec dbms_workload_capture.start_capture(NAME => 'PEAK_LOAD_TEST',DIR => 'DIR', DURATION => 600);</span>
Name,Dir are mandatory parameters while duration (in seconds) is optional, here the capture will run for 600 seconds, if the duration is not specified then you have to finish the capture manually as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > dbms_workload_capture.finish_capture();</span>
- While the capture process is runnig it will generate two files : wcr_scapture.wmd and wcr_cap_000xx.start
- After using the finish_capture procedure you will get folloing files also: wcr_cr.html and wcr_cr.text
wcr_cr.html is similar to the AWR report generated by awrrpt.sql
During capture process database initiate a AWR report for the duration of capture period.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures;</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">ID AWR_BEGIN_SNAP AWR_END_SNAP ---------- -------------- ------------ 1 20 21 2 29 30</span>
- Exporting AWR data for the workload capture:
It will enable detailed analysis of the workload:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> exec DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 2);</span>
After this you can see two more files generated under the specified directory:
wcr_ca.dmp and wcr_ca.log
2. Workload Preprocessing:
Once the workload is captured it can be replayed on the test system. For this you need to copy the capture files to the test system. Preprocessing a workload will convert the captured files into REPLAY files and create the necessary metadata for this. Preprocessing will put load on the system and is resource intensive and should be performed only on test systems and on the same version of oracle database.
- Create a new directory for the capture files placed on the test system.
Let us suppose that the capture files are placed under /tmp/ss on test system, now create a directory as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > create directory REPLAY as '/tmp/ss';</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> EXEC DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir => 'REPLAY');</span>
After preprocessing followings files will be generated:
wcr_process.wmd, wcr_login.pp, wcr_seq_data.extb, wcr_scn_order.extb , wcr_conn_data.extb
3. Workload Replay:
You have to use an instance which is logically identical to the instance whose workload is captured and need to setup Replay Client (WRC).
- wrc executable has to be run in calibrate mode to estimate the number of replay clients.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">$ wrc mode=calibrate replaydir=/tmp/sood</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Workload Replay Client: Release 11.1.0.6.0 - Production on Sat Jul 26 18:49:29 2008</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Copyright (c) 1982, 2007, Oracle. All rights reserved.</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Report for Workload in: /tmp/sood -----------------------</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Recommendation: Consider using at least 1 clients divided among 1 CPU(s).</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Workload Characteristics: - max concurrency: 1 sessions - total number of sessions: 5</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - think time scale = 100 - connect time scale = 100 - synchronization = TRUE</span>
- Initialize Replay Data:
Initializing replay loads the metadata into tables required for database replay.
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'REPLAY_1', REPLAY_DIR=> 'REPLAY');</span>
- Put the database in “PREPARE REPLAY” mode as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY()</span>
To check the status of replay use:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > select name,status from dba_workload_replays; NAME STATUS -------------------- ---------------------------------------- REPLAY_1 PREPARE</span>
– Start the replay client:
<span style="font-family: arial,helvetica,sans-serif;"><span style="font-size: small;">$ wrc replaydir=/tmp/sood</span> </span><span style="font-family: arial,helvetica,sans-serif; font-size: small;">Workload Replay Client: Release 11.1.0.6.0 - Production on Sat Jul 26 22:12:35 2008</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Copyright (c) 1982, 2007, Oracle. All rights reserved.</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Wait for the replay to start (22:12:35)</span>
- Start the replay as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY ();</span>
Check the status of the replay as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL> select name,status from dba_workload_replays;</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">Initially it will show :</span> <span style="font-family: arial,helvetica,sans-serif; font-size: small;">NAME STATUS -------------------- ---------------------------------------- REPLAY_1 IN PROGRESS</span>
And after that it will be shown as completed.
- Once the status is completed finish the replay as:
<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL > exec DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();</span>
To generate the report of the replay in HTML format you need to run :
<span style="font-family: andale mono,times; font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">DECLARE My_Report CLOB; BEGIN My_Report := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => 1, format => DBMS_WORKLOAD_REPLAY.TYPE_HTML); END; /</span><code><ins datetime="2008-07-26T18:07:29+00:00"></ins></code></span>
One Comment for “11g New Feature: Database Replay”