Oracle Database Replay for a RAC environment

The database Capture/Replay feature of the Real Application Testing (RAT) suite enables you to capture the workload from a production database and replay it on a test or development environment. This allows for realistic testing of database changes, such as upgrades, patches, or hardware migrations.

In this blog, we’ll explore how to accomplish this when both your production and test databases are configured in a two-node RAC environment.

Restrictions :

You should be aware that some workload operations are not supported, such as flashback queries and distributed transactions.

In most cases, Database Replay refrains from capturing these operations, or does not replay them. However you should refer to the oracle documentation bellow for the full list of non-supported operations and related considerations :

Oracle Database Documentation

The Workload Capture

The capture procedure on a RAC database is the same as for a single instance database, you just need to run it on one of the instances, without adding an instance filter :

The path you use to store the raw workload files must be defined as a database directory, and must be empty.

It is recommended, but NOT mandatory, to restart your database before the capture, so that all current processes are finished, and the capture would catch absolutely all the changes from the timestamp of its start.

Let’s take for instance a 4 hours workload capture (14400 seconds) :

This package will capture the workload on both RAC instances for 4 hours and will automatically stop. If you need to stop it earlier, you can do it manually using:

The capture process does not significantly impact performance (it adds about 3-5% CPU overhead). However, it may require substantial storage space. A common reason to stop the capture prematurely is running out of space in the designated directory.

Oracle Support Note Doc ID 1920275.1 provides a method to estimate the space required. For a detailed explanation, please refer to the note:

Doc ID 1920275.1

The general formula for the space needed provided by this note is:

Alternatively, you can perform a short capture, such as for 10 minutes, and extrapolate the estimate based on the size captured during that period.

At the end of the capture, you should generate an html summary, and export related AWR data, for that, you’ll need the ID of you capture :

So the capture id in my case is : 11

– Generating The html report (It is generated in the directory of the capture) :

– Exporting the AWR data (It generates a datapump dump file contaiting all awr data of the period of the capture) :

The Replay

To prepare the replay you need two things :

– To copy all the files from the directory of the capture to the test system (As it is two nodes RAC it should be on a shared location).

– Prepare a test database : The html report of the capture contains the SCN of the start, it’s best practice for instance to duplicate your database up to that specific SCN, in the test environment, before applying the changes you want to evaluate (for instance an upgrade, or parameters changes).

Once your test database is ready, and the captured files are available on a shared location :

– We create the directory of the replay :

The captured raw files must be processed by the database, before the capture could be replayed, this step could take some time depending of the size of the workload raw files :

Then we initiate the replay (just an initiation, the replay is not yet started) :

This step generated an entry in the DBA_WORKLOAD_REPLAYS view, we must retrieve the associated id :

So, here, the ID of the replay is 21.

Next step is to remap the workload connections. To be able to apply the replay on both instances, we must use a TNS entry to a scan host and scan service :

Then we clean and execute the script :

Next we prepare the replay :

synchronization => TRUE : ensures that the replay sessions are synchronized, maintaining the order and dependency between transactions as they occurred during the workload capture.

Checking the status of the replay :

Next we need to calibrate the replay clients, we use the wrc program in calibrate mode. wrc is The Workload Replay Client tool, used to replay database workload captured by Oracle Database.

The calibration mode purpose is to ensures that the workload replay environment has enough replay clients to handle the workload in the capture. After calibration, the wrc tool will output the minimum number of replay clients required.

Based on this calibration, and after checking the cpu and memory available, I will use 56 clients (56 x 152 MB = 8.5G of minimal memory needed for the clients : Ok for me).

And this means using 28 clients per node, as I want to balance the overhead on the two nodes.

The command to start a single client is :

But I prepared a script to start 28 clients at once in background mode, for instance in the first node :

starting the clients on the first node :

We do the same thing on the second node, we just need to change the DB_CONNECT variable in the script.

Finally, we can start the replay :

The replay is finally started, it can be monitored by :

– Querying the views : dba_workload_replays and v$workload_replay_thread.
– Checking the clients log files.

Hope it helps !