SQL Replay: A new feature of Oracle 11g

SQL Replay is a new feature introduced in Oracle 11g. SQL Replay features is used to capture the commands executed in a database and then replay them elsewhere. You can use the capture and replay features to perform diagnostic operations or to test applications under production conditions.

Steps to create SQL Replay:

Step 1: Creating a Workload Directory

The log file for the SQL workload will be written to a directory on the source server. To specify the physical location for that directory, you must create a directory object within the database.

Use the create directory command:

create directory workload_dir as '/u01/workload'

Step 2: Starting the Capture of Source Database

To start a capture, execute the START_CAPTURE procedure of  DBMS_WORKLOAD_CAPTURE package.

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE
        (name => 'practice_capture',
          dir => 'workload_dir',
     default_action => 'EXCLUDE');
END;
/

Note:
1. The name parameter is the name you assign to the workload capture.
2. dir is the name of the directory object you created to store the workload-capture files.

Stopping the Capture:

If you have not specified a duration, you must stop the capture manually. To stop the capture, execute the FINISH_CAPTURE procedure, as shown here:

BEGIN
  DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

Step 3: Processing the Workload Logs:

Workload Logs are transformed into Replay Files and metadata is created. It can be done by PROCESS_CAPTURE procedure of DBMS_WORKLOAD_REPLAY package.

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE
   (capture_dir => 'workload_dir');
END;
/

The capture_dir variable value must be the name of a directory object within the database that points to the physical directory where the workload logs are stored.

Step 4: Replaying the Workload on Target Database

To start the replay, use the START_REPLAY procedure of DBMS_WORKLOAD_REPLAY package, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

To stop the replay, use the CANCEL_REPLAY procedure, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
END;
/

Precautions while Replaying SQL

1. Before starting the capture on the source database, you should make sure there are no active transactions within the database. Best practice is to restart the database.

2. For best results during the replay, you should alter / reset the system time on the target system.

3. The target database should run the same version of Oracle as the source system.

4. Target database should be separate from the production database and should be as isolated as possible from the external structures accessed by the production database.
Note: Replay is best suited for uses in which the target system is a testing environment, rather than generating the replay in a testing environment and then executing it in a production database