Basics of Oracle 12c’s Database Replay - dummies

Basics of Oracle 12c’s Database Replay

By Chris Ruel, Michael Wessler

The Oracle 12c Database Replay feature evolved as a solution for the need to be able to do realistic application testing. Before Database Replay, if you wanted to test any kind of changes against performance or workload, you had to buy a third-party tool or do massive amounts of coding to fake a workload.

In most cases, neither method was truly representative of your real workload. Also, making changes to a production environment without testing them can be risky.

Database Replay is one more tool in your shed to cover all the bases.

In essence, Database Replay allows you to record your workload in real time and then play it back. Furthermore, you could play it against

  • Another database

  • A different version of Oracle

  • A different OS

Database Replay captures the workload at below the SQL level. The workload is stored in binary files. You can then transfer these files to a test environment, run the workload, analyze problems, fix problems, and test again. The same workload is repeatable. In conjunction with a tool like Flashback Database, you can repeatedly test changes in quick succession. It helps reduce the chances of something breaking when environments are changed.

Database Replay provides a mechanism to help with these kinds of situations:

  • Testing

  • Configuration changes

  • Upgrades

  • Downgrades

  • Application changes

  • Debugging

  • Storage, network, and interconnect changes

  • Platform changes

  • OS changes

  • Conversion to Real Application Clusters (RAC)

How to use Database Replay in Oracle 12c

Here’s how to use Database Replay:

  1. Log in to SQL*Plus as a user with the SYSDBA privilege.

    Oracle requires a directory in which to write the replay files.

  2. Create a directory to a location on the OS with plenty of space:

    <create or replace directory capture_dir as

    You see this:

    Directory created.
  3. Start a capture:

    <exec dbms_workload_capture.start_capture ('CAPTURE_DEMO','CAPTURE_DIR');>

    This example uses the name CAPTURE_DEMO.

    Ideally, you restart the database before the capture begins so that you can avoid catching any transactions in the middle. Of course, doing so isn’t always an option when dealing with a production system.

    You see this:

    PL/SQL procedure successfully completed.
  4. Execute your workload.

    If it’s just normal application behavior, let it run for the amount of time you want.

  5. When the workload is complete or your time target has passed, stop the capture process:

    <exec dbms_workload_capture.finish_capture;>

    You see this:

    PL/SQL procedure successfully completed.

According to Oracle documentation, capturing a workload can add up to 4.5 percent of processing overhead to the system as well as 64K of memory overhead for each session. Futhermore, if space runs out in the capture directory, the capture will stop. All the captured data up to that point will still be useful.

The idea is you will use your capture to “replay” the workload. In our experience, the workload is usually replayed against a different database, such as a test environment. However, this is not always the case.

If your database environment is one where lengthy maintenance windows can occur (such as over a weekend), you might find yourself doing these things:

  • Enabling Flashback Database

  • Creating a restore point on Friday morning

  • Starting a workload capture for four hours from 8 a.m. to noon

  • Restricting the system and creating another restore point after the employees go home on Friday evening

  • Restoring the database to the restore point Friday morning

  • Deploying database or application changes

  • Replaying your workload to test the changes

  • Flashing back the workload to Friday evening

  • Deploying database or application changes to take effect when the workers come back Monday morning

How to replay the workload in Oracle 12c

Follow these steps to replay the workload:

  1. Create a directory for the replay capture files:

    <create or replace directory capture_dir as

    You see this:

    Directory created.

    This example assumes the replay is taking place on another database. If it’s on the same database, there is no need to create a directory and move the capture files because they will already be in the correct location.

  2. Move the files from the capture directory on the source system to the directory on the replay system.

  3. Begin the replay process on the database:

    <exec dbms_workload_replay.process_capture ('CAPTURE_DIR');>

    You see this:

    PL/SQL procedure successfully completed.
  4. Initialize a replay session called REPLAY_DEMO:

    <exec dbms_workload_replay.initialize_replay

    You see this:

    PL/SQL procedure successfully completed.
  5. Tell Oracle to prepare the replay files:

    <exec dbms_workload_replay.prepare_replay ;>

    You see this:

    PL/SQL procedure successfully completed.

    Start replay clients, which are processes that execute and manage the workload. These processes are launched from the OS’s command line.

  6. The following example starts a replay client with oracle as the password:

    <wrc system/oracle>

    You see this:

    Workload Replay Client: Release - Production on Fri Aug 16 22:24:44 2013
    Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
    Wait for the replay to start (22:24:44)
  7. Tell the database to start the replay:

    <exec dbms_workload_replay.start_replay;>

    You see this:

    PL/SQL procedure successfully completed.
  8. Check on the status while the replay runs:

    <select id, name, status, duration_secs
     from dba_workload_replays;>

    Basically, you’re querying the DBA_WORKLOAD_REPLAYS table. You see this (or something like it):

---------- -------------------- ----------- -------------
    10 REPLAY_DEMO     IN PROGRESS     369

When everything is done, you should clean up the replay metadata.

  1. Capture ID info on the source system:

    <select id, name
     from dba_workload_captures;>

    You might see something like this:

        ID NAME
    ---------- -----------------------------------
         4 CAPTURE_DEMO
  2. Delete the capture information:

    <exec dbms_workload_capture.delete_capture_info(4);>

    You see this:

    PL/SQL procedure successfully completed.
  3. Find the replay id on the replay system:

    <select id, name
     from dba_workload_replays;>

    You might see something like this:

        ID NAME
    ---------- -----------------------------------
        10 REPLAY_DEMO
  4. Delete the replay information:

<exec dbms_workload_capture.delete_replay_info(10);>