Oracle Database 11g Workload Replay.

Database replay feature can be used to capture workload from the 
production system, and replay it on a test system, with exact
timing and concurrency.

Its like you can emulate the transaction workload on the test system
as it was happening on the production system.

Main benefit of this is that you can test new settings on 
the test system before implementing them on the production system.



Illustration:


Production Database Name: orcl

Test Database Name: orclb



Create a directory for workload capture and a directory object.

conn / as sysdba

SQL> ! mkdir /u01/test_replay


SQL> create or replace directory test_replay as '/u01/test_replay';

Directory created.

SQL> shutdown immediate
SQL> startup



The above shutdown and startup is not necessary but oracle recommends
it. So that any outstanding processes are complete before the workload
capture begins.


There are many filters available for workload capture, so that you
can capture workload on a specific level

Example:

1) INSTANCE_NUMBER
2) USER
3) MODULE
4) ACTION
5) PROGRAM
6) SERVICE


CAPTURE

But for the reason of simplicity i am going to capture the full
workload.

SQL> begin
     dbms_workload_capture.start_capture (name => 'my_test_capture',
                                          dir  => 'TEST_REPLAY',
                                          duration => null);
     end;
     /
 

PL/SQL procedure successfully completed.

Now we are going to create a user JAMES and give him appropriate
priviledges.

SQL> create user james identified by bond
     default tablespace users
     temporary tablespace temp
     quota unlimited on users;


SQL> grant connect, create table to james;

Grant succeeded.


Now connect to james create a test table and insert some data.

SQL> conn james/bond
Connected.

SQL> create table test (id int,
  2   description varchar2(50),
  3  constraint test_tab_pk primary key (id)
  4  );

SQL> begin
  2   for i in 1 .. 67000 loop
  3    insert into test (id, description)
  4    values (i, 'Description for ' || i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Finish workload capture.

SQL> begin
  2   dbms_workload_capture.finish_capture;
  3  end;
  4  /

PL/SQL procedure successfully completed.


Check the directory for contents.

SQL> ! ls -l /u01/test_replay
total 8
drwxr-xr-x 2 oracle oinstall 4096 Jan 27 19:44 cap
drwxr-xr-x 3 oracle oinstall 4096 Jan 27 19:34 capfiles


To get information about captures previously run execute:


SQL> column name format a25

SQL> select id, name from dba_workload_captures;


      ID NAME
---------- -------------------------
         1 my_test_capture


REPLAY

Now its time to replay capture on the test system.

Copy all the files from the test_replay folder to the test system
and create a directory object for it.

I have kept both the directory structure and directory object names
the same as that of the production system.

SQL> ! mkdir /u01/test_replay

SQL> create or replace directory test_replay as '/u01/test_replay';

Directory created.

Preprocess the replay.

SQL> begin
  2   dbms_workload_replay.process_capture('TEST_REPLAY');
  3   dbms_workload_replay.initialize_replay (replay_name => 'my_test_capture',
  4                                           replay_dir => 'TEST_REPLAY');
  5  dbms_workload_replay.prepare_replay (synchronization => true);
  6  end;
  7  /


Prepare the replay client.


[oracle@canada ~]$ export ORACLE_SID=orclb
[oracle@canada ~]$ wrc mode=calibrate replaydir=/u01/test_replay

Workload Replay Client: Release 11.2.0.1.0 - Production on Fri Jan 27
20:38:24 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Report for Workload in: /u01/test_replay
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.

Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 3

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE



As per the recommendation we have to create atleast 1 client.

[oracle@canada ~]$ export ORACLE_SID=orclb
[oracle@canada ~]$ wrc system/sys mode=replay replaydir=/u01/test_replay

Workload Replay Client: Release 11.2.0.1.0 - Production on Fri Jan 27 20:43:15 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Wait for the replay to start (20:43:15)

# open another terminal log in as "SYS" user and start the replay
# by issuing the following command.

SQL> begin
     dbms_workload_replay.start_replay;
     end;


Replay started (20:45:14)
Replay finished (20:52:51)

Check whether the database has completed the replay and did all the
modifications.


SQL> set linesize 130
SQL> set pagesize 100
SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
JAMES
OUTLN
MGMT_VIEW
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
DBSNMP
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER
HR
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

37 rows selected.

SQL> select count(*) from james.test;

  COUNT(*)
----------
     67000

Replays made in this system can be seen by issuing the following
command.

SQL> column name format a25
SQL> select id, name from dba_workload_replays;

        ID NAME
---------- -------------------------
         1 my_test_capture