Oracle 11G database replay options

Oracle Database 11G is providing a new future, database replay. What database replay in basics is doing is capturing all what is done in a database and provide you with a replay file which can be executed on a other database instance. So, even do this is a nice feature from a technical point of view, where can it be used for. A couple of examples:

(1) Tuning a Oracle database and the applications running on it can be a challenging job. Even do we have all kind of applications where we can tune SQL for example we always come to a point where we have to ask ourselves how will it be behave under production environment load. In most cases the new code is checked on bugs and placed on production to have a impression on the new speed of the code. In some cases test scripts are build to simulate a production load. With Oracle Database 11G database replay you now have the ability to capture real-life data for some time and run it on your development instance again and again until you are satisfied with the speed of your code.

(2) Building installation and setup scripts. We all know cases where we have to create tables, change some things, import some data and then redo the entire thing on a other server(s) for testing. In some cases you know that this is only a test and you are not yet ready to create official installation scripts, you just want to have it on multiple servers for testing. Now you can simply capture the steps and replay it on a second database instance.

(3) Something is wrong on a database instance and users are complaining that they have a issue. You now can capture the session and export it to a testing instance where you can use the data from the users session and play it over and over again until you found and solved the bug.You can think of numerous other useful options of Oracle database replay on Oracle 11G databases. So, how is it working. The concept is shown in the picture below:


1. You start a capture process that records activities against the database.
2. The process writes the activities to special files called "capture files" in a directory called /capture directory/.
3. After a while you stop the capture process and move these capture files to a test system in a directory called /replay directory/.
4. You start a replay process and several replay clients to replay all these capture files.
5. The capture files are applied against the test database. So how is it done. First of all we have to make sure we have a location to store the capture files somewhere on the fi


somewhere a new directory where we write access. In our case we will be creating /u01/app/oracle/replayfiles

mkdir /u01/app/oracle/replayfiles

Now we have to make it a directory in the oracle database:

CREATE OR REPLACE DIRECTORY db_replay
AS '/u01/app/oracle/replayfiles/';


Ok, if all is setup correctly we can start capturing for this we will be using dbms_workload_capture procedure, namely the dbms_workload_capture.start_capture and the dbms_workload_capture.finish_capture procedures. To start a capture use:

BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'capture_run',
dir => 'DB_REPLAY',
duration => NULL);
END;
/

And to stop the capture we use:

BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;

Even do it are some simple commands which can easily be invoked you can also use the Oracle Enterprise Manager GUI for this.