Test Workloads Against an Upcoming Patch

Using the workload auto replay feature you can automatically capture a workload from a production database that is on the regular patch level and replay the workload on a target refreshable clone that is on the early patch level.

This feature allows you to test an upcoming patch by running an existing workload that is in production against a patch, before the patch reaches production.

About Testing Workloads Against an Upcoming Patch

Using the workload auto replay feature you can automate the process of capture-replay to capture a workload that runs on a production database and automatically replay the workload on a target refreshable clone after an upcoming patch is applied on the target.

Autonomous Database provides the ability to provision an instance or create a refreshable clone with the Early patch level option. On instances running at the Early patch level, Autonomous Database applies upcoming maintenance patches a week before the patches are applied to production databases (databases that are provisioned at the Regular patch level). Using the WORKLOAD_AUTO_REPLAY feature you can assure that an upcoming patch is tested against your workload before the patch goes to production. This allows you to verify that the patch either fixes a known issue or does not introduce an issue that affects your workload.

To find information about captures and replays, subscribe to Information events. Information events provide notification for workload capture and reply events and include a PAR URL where you can download the capture file and replay report. See (Optional) Subscribe to Information Events to be Notified of Capture and Replay Details for more information.

When WORKLOAD_AUTO_REPLAY is enabled the source database captures a workload by running for a specified number of minutes. By default the workload capture starts when you enable WORKLOAD_AUTO_REPLAY (optionally you can use parameters to set the capture start day and time). Next, Autonomous Database checks the target database to verify the patching status. After the upcoming weekly patch is applied, Autonomous Database replays the workload on the target database. This capture-replay cycle continues automatically each week with Autonomous Database capturing the workload on the source database, waiting for the upcoming patch to be applied, and replaying the workload on the refreshable clone.

Note the following for enabling WORKLOAD_AUTO_REPLAY:

  • The source database must use the Regular patch level.

  • The target database must use the Early patch level.

  • The target database must be a refreshable clone of the source database, and must be created before you enable WORKLOAD_AUTO_REPLAY.

  • A source database can enable WORKLOAD_AUTO_REPLAY for no more than one refreshable clone (you can enable this feature for a maximum of one refreshable clone, even if you create multiple refreshable clones from the same source database).

  • After you enable WORKLOAD_AUTO_REPLAY, the capture-replay cycle continues every week. Autonomous Database runs a capture on the source database and then replays the workload on the target database, until you disable WORKLOAD_AUTO_REPLAY.

You can find information about workload captures and replays in the DBA_CAPTURE_REPLAY_HISTORY view. See DBA_CAPTURE_REPLAY_HISTORY View for more information.

Autonomous Database automatically applies patches on your database. Oracle provides a service level objective of zero regressions in your production database due to these patches. See Zero-Regression Service Level Objective for more information.

Enable Workload Auto Replay

The WORKLOAD_AUTO_REPLAY feature allows you run a workload from your production database and monitor for any divergence on an instance that is patched one week in advance. This feature allows you to test an upcoming patch by running an existing workload that is in production against a patch before the patch reaches production.

To enable WORKLOAD_AUTO_REPLAY:

  1. Create a refreshable clone of the production database.

    When you create the target refreshable clone, set the patch level to Early.

    See Set the Patch Level and Create a Refreshable Clone for an Autonomous Database Instance for more information.

  2. Run DBMS_CLOUD_ADMIN.ENABLE_FEATURE on the source database.

    For example:

    BEGIN 
       DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
            feature_name => 'WORKLOAD_AUTO_REPLAY',
            params       => JSON_OBJECT(
                              'target_db_ocid' VALUE 'OCID1.autonomousdatabase.REGION..ID1',
                              'capture_duration' VALUE 120,
                              'capture_day' VALUE 'MONDAY',
                              'capture_time' VALUE '15:00'));
    END;
    /

    Where the parameters are:

    • feature_name: the value WORKLOAD_AUTO_REPLAY enables the workload auto replay feature.

    • params: is a JSON Object with the following value pairs:

      • target_db_ocid: accepts a string value. The value specifies the OCID of the target refreshable clone database on which the captured workload is replayed.

        This parameter is mandatory.

      • capture_duration: accepts a number value. The value specifies the duration in minutes for which the workload is captured on the production database. The value must be in the range between 1 and 720 minutes.

        This parameter is mandatory.

      • capture_day: accepts a string value. The value specifies the day of the week the workload capture on the production database should begin.

        This parameter is optional.

      • capture_time: accepts a value in the HH24:MM format. The value specifies the time of the day the workload capture on the production database should begin.

        This parameter is optional.

      By default the workload capture starts when you enable WORKLOAD_AUTO_REPLAY. When the optional capture_day and capture_time are specified, the automatic workload capture and replay happen at the specified timestamp.

      For example if capture_day is Monday and capture_time is 15:00, the first capture on the production database starts at 3PM on the next Monday. The same day of week and time are also used to schedule subsequent captures and replays.

    See ENABLE_FEATURE Procedure for more information.

    A an error value of ORA-20000: Invalid argument for target_db_ocid could indicate that the OCID you supplied is not a refreshable clone. In this case, you need to supply an OCID with a value for a refreshable clone.

  3. Query the DBA_CAPTURE_REPLAY_STATUS view to check the workload replay status.

This example enables WORKLOAD_AUTO_REPLAY on the source Autonomous Database and on the specified target refreshable clone database. With WORKLOAD_AUTO_REPLAY enabled, every week Autonomous Database runs a capture on the source database and replays the workload on the target database, until you disable WORKLOAD_AUTO_REPLAY.

To find information about captures and replays, subscribe to Information events. Information events provide notification for workload capture and reply events and include a PAR URL where you can download the capture file and replay report. See (Optional) Subscribe to Information Events to be Notified of Capture and Replay Details for more information.

You can find information about workload captures and replays in the DBA_CAPTURE_REPLAY_HISTORY view. See DBA_CAPTURE_REPLAY_HISTORY View for more information.

Disable Workload Auto Replay

Run DBMS_CLOUD_ADMIN.DISABLE_FEATURE to disable WORKLOAD_AUTO_REPLAY.

Run DBMS_CLOUD_ADMIN.DISABLE_FEATURE to disable workload auto replay. For example:

BEGIN 
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'WORKLOAD_AUTO_REPLAY');   
END;
/

You must be logged in as ADMIN or have DBMS_CLOUD_ADMIN privileges to run DBMS_CLOUD_ADMIN.DISABLE_FEATURE.

See DISABLE_FEATURE Procedure for more information.