11g Database Replay

It allows you to capture workload on a production system and replay them exactly as they happened on a test system.

This provides an accurate method to test the impact of a variety of system change including: 

1.    Steps to Capture

Create a directory at OS level to keep the capture files

CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u01/app/oracle/db_replay_capture/';

Shutdown the database

Shutdown immediate

Startup

Shutting down the database is optional; it is a good way to make sure any outstanding processes are complete before starting the capture process.

BEGIN

  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1', 

                                       dir      => 'DB_REPLAY_CAPTURE_DIR',

                                       duration => NULL);

END;

/

Once the work is complete you can finish the capture by below command.



BEGIN

  DBMS_WORKLOAD_CAPTURE.finish_capture;

END;

SELECT id, name FROM dba_workload_captures;

    ID NAME

---------- ------------------------------

        21 test_capture_1

We can generate the report with the help of below procedure

DECLARE

  l_report  CLOB;

BEGIN

  l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 21,

                                           format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

END;

Print l_report;

/

The capture ID can be used to export the AWR snapshots associated with the specific capture run.

BEGIN

  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 21);

END;

/

We can transfer all the files from the capture process to the test machine and create a similar directory on test machine

BEGIN

  DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'testreplay’

                                          replay_dir  => 'DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);

END;

/

Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.

$ wrc mode=calibrate replaydir=/u01/app/oracle/db_replay_capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:33:42 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Report for Workload in: /u01/app/oracle/db_replay_capture

-----------------------

Recommendation:

Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:

- max concurrency: 1 sessions

- total number of sessions: 3

Assumptions:

- 1 client process per 50 concurrent sessions

- 4 client process per CPU

- think time scale = 100

- connect time scale = 100

- synchronization =The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.

$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Wait for the replay to start (09:34:14)

The replay client pauses waiting for replay to start. We initiate replay with the following command.

BEGIN

  DBMS_WORKLOAD_REPLAY.start_replay;

END;

/

Once complete, we can see the DB_REPLAY_TEST_TAB table has been created and populated in the DB_REPLAY_TEST schema.

SELECT id, name FROM dba_workload_replays;

        ID NAME

---------- ------------------------------

        11 testreplay