Capture and Replay Between Autonomous Database Instances

You can Capture and Replay from an Autonomous Database instance into another Autonomous Database instance.

Capture-Replay between Autonomous Databases consists of the following steps (you either cancel or finish a workload capture, not both):

(Optional) Subscribe to Information Events to be Notified of Capture and Replay Details

Subscribe to com.oraclecloud.databaseservice.autonomous.database.information Information events to be notified at the start and completion of a capture and replay.

Note:

This step is optional. You can also find status and historical information for a workload capture in the DBA_CAPTURE_REPLAY_STATUS and DBA_CAPTURE_REPLAY_HISTORY views.

See DBA_CAPTURE_REPLAY_STATUS View and DBA_CAPTURE_REPLAY_HISTORY View for more information.

Information events provide notifications about begin and end times of capture and replay and contain a PAR URL to access the capture and replay reports.

Autonomous Database Information events include the following:

  • WorkloadCaptureBegin: This event is triggered when a workload capture is initiated.
  • WorkloadCaptureEnd: This event is triggered when a workload capture completes successfully and generates a pre-authenticated (PAR) URL to download the capture file.
  • WorkloadReplayBegin: This event is triggered when a workload replay is initiated.
  • WorkloadReplayEnd: This event is triggered when a workload replay completes successfully and generates a pre-authenticated (PAR) URL to download the replay reports.

See Information Events on Autonomous Database for more information.

Capture a Workload on an Autonomous Database Instance

The first step in using Database Replay is to capture a production workload.

Note:

You can capture a workload in an Autonomous Database instance and replay it in another Autonomous Database instance. You can replay the captured workload on a full clone or on a refreshable clone. The capture and replay targets must be in a consistent logical state. So, you must provision a refreshable clone or a full clone of the Autonomous Database instance on which you want to capture the workload.

See Clone, Move, or Upgrade an Autonomous Database Instance for more information.

When you begin workload capture on a production system, all requests from external clients directed to Oracle Database are tracked and stored in binary files called capture files.

A workload capture results in the creation of two subdirectories, cap and capfiles, which contain the capture files. The capture files provide all pertinent information about the client request, including transaction details, bind values, and SQL text. These capture files are platform independent and can be transported to another system.

Run DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE to initiate workload capture on an Autonomous Database instance.

See Clone, Move, or Upgrade an Autonomous Database Instance for more information.

To initiate a workload capture on your Autonomous Database instance you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

Example to initiate a workload capture:

BEGIN 
   DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE(
        capture_name => 'test',
        duration     => 60);
   END;
/

This starts the workload capture on your Autonomous Database instance.

The parameters are:

  • capture_name: is the name of the workload capture.

  • duration: is the duration (in minutes) for which you need to capture the workload. This parameter is optional.

See START_WORKLOAD_CAPTURE Procedure for more information.

Workload Capture Events

You can subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified at the start of START_WORKLOAD_CAPTURE. See (Optional) Subscribe to Information Events to be Notified of Capture and Replay Details for more information.

Workload Capture and Replay Views

You can find information about workload capture and replay in the DBA_CAPTURE_REPLAY_STATUS and DBA_CAPTURE_REPLAY_HISTORY views. See DBA_CAPTURE_REPLAY_STATUS View and DBA_CAPTURE_REPLAY_HISTORY View for more information.

Cancel a Workload Capture on an Autonomous Database Instance

Run DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE to cancel the current workload capture on your Autonomous Database instance.

To cancel a workload capture, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

Example:

BEGIN
    DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE;
END;
/

This cancels the current workload capture and performs a refresh on the refreshable clone.

You can query the DBA_CAPTURE_REPLAY_STATUS view to check the cancel workload status.

See DBA_CAPTURE_REPLAY_STATUS View for more information.

See CANCEL_WORKLOAD_CAPTURE Procedure for more information.

Finish a Workload Capture on Autonomous Database Instance

Run DBMS_CLOUD_ADMIN.FINISH_WORKLOAD_CAPTURE to complete a workload capture on your Autonomous Database instance.

Example to finish a workload capture on your Autonomous Database instance:

BEGIN
    DBMS_CLOUD_ADMIN.FINISH_WORKLOAD_CAPTURE;
END;
/

To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN. When you run this procedure a workload capture file is uploaded to Object Store as a zip file.

See FINISH_WORKLOAD_CAPTURE Procedure for more information.

Workload Capture Events

You can subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about a workload capture, that includes:

  • The completion of FINISH_WORKLOAD_CAPTURE.

  • The captureDownloadURL field that contains the PAR URL to access the capture and reports in Object Store. The capture and reports are valid for seven (7) days from the date when the PAR URL is generated.

See (Optional) Subscribe to Information Events to be Notified of Capture and Replay Details for more information.

Workload Capture and Replay Views

You can query the DBA_CAPTURE_REPLAY_STATUS view to check the status of a completed workload capture. See DBA_CAPTURE_REPLAY_STATUS View for more information.

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

You can query the ID, NAME, START_TIME, and END_TIME columns of the DBA_WORKLOAD_CAPTURES view to retrieve the details of your workload capture. See DBA_WORKLOAD_CAPTURES for more information.

Prepare a Refreshable Clone for Workload Replay

Provides steps to prepare a refreshable clone for a workload replay.

Note:

This step is not applicable when you are replaying a workload on a full clone.

You have two options to prepare a refreshable clone to replay a workload capture. You can run DBMS_CLOUD_ADMIN.PREPARE_REPLAY to automatically prepare a refreshable clone for a workload replay. This procedure refreshes the refreshable clone to the start time of the capture and disconnects the refreshable clone. You also have the option to manually prepare a refreshable clone to replay a workload capture.

Automatically Prepare a Refreshable Clone for Workload Replay

Example to automatically prepare a refreshable clone for a workload replay:

BEGIN
 DBMS_CLOUD_ADMIN.PREPARE_REPLAY (
    capture_name    'test'
END;
/

To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

DBMS_CLOUD_ADMIN.PREPARE_REPLAY does the following:

  • Refreshes the refreshable clone to the capture start timestamp.

  • Disconnects the refreshable clone.

Optionally, at this point before you replay a capture, you can make changes to the a refreshable clone. For example, changing parameter values and turning certain features on/off to see the impact on the replay.

Manually Prepare a Refreshable Clone for Workload Replay

These manual refreshable clone steps are not required when you automatically prepare a refreshable clone by running DBMS_CLOUD_ADMIN.PREPARE_REPLAY.

Perform the following steps to manually prepare for a workload replay:

  1. Find the capture start timestamp by querying the DBA_WORKLOAD_CAPTURES view. See DBA_WORKLOAD_CAPTURES for more information.

  2. Refresh the refreshable clone to the capture start timestamp. See Refresh a Refreshable Clone on Autonomous Database for more information.

  3. Manually disconnect the refreshable clone. See Disconnect a Refreshable Clone from the Source Database for more information.

  4. Optionally, before you replay a capture, you can make changes to the refreshable clone. For example, changing parameter values, turning certain features on/off to see the impact on the replay.

Replay a Workload on an Autonomous Database Instance

After you complete a workload capture you can replay it on a test system. Oracle replays the actions recorded during workload capture with the same timing, concurrency, and transaction dependencies of the production system.

Run the procedure DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD to initiate workload replay on your database. You must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN to run DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD.

You can replay a captured workload on a refreshable clone or on a full clone of the Autonomous Database instance from which the workload was captured. The capture and replay targets must be in a consistent logical state.

Replay Workload On a Refreshable Clone

The following example downloads the capture files from Object Storage, replays the captured workload, and uploads a replay report to Object Storage.

BEGIN 
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
      capture_name => 'CAP_TEST1');
END;
/

The CAPTURE_NAME parameter specifies the name of the workload capture. This parameter is mandatory.

Replay Workload On a Full Clone

This following example downloads capture files from the Object Storage, replays the captured workload on the clone, and uploads a replay report to Object Storage.

BEGIN 
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
       capture_name                => 'CAP_TEST1',        
       capture_source_tenancy_ocid => 'OCID1.TENANCY.REGION1..ID1',         
       capture_source_db_name      => 'ADWFINANCE');
END;
/

Note:

If there are multiple captures with the same capture name, the REPLAY_WORKLOAD procedure uses the latest capture. Oracle recommends that you use a unique capture name for each capture to prevent confusion on which capture you are replaying.

The CAPTURE_NAME parameter specifies the name of the workload capture. This parameter is mandatory.

The CAPTURE_SOURCE_TENANCY_OCID parameter specifies the source tenancy OCID of the workload capture. This parameter is mandatory when running the workload capture in a full clone.

The CAPTURE_SOURCE_DB_NAME parameter specifies the source database name of the workload capture. This parameter is mandatory when running the workload capture in a full clone.

See REPLAY_WORKLOAD Procedure for more information.

Workload Replay Events

Subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the following

  • The start and completion of a REPLAY_WORKLOAD.

  • The Object Store link to download replay reports. The event provides a PAR URL to access the reports in the replayDownloadURL field. The reports are valid for seven (7) days from the date when the PAR URL is generated.

See Information Events on Autonomous Database for more information.

Workload Capture and Replay Views

You can query the DBA_CAPTURE_REPLAY_STATUS view to check the workload replay status.

See DBA_CAPTURE_REPLAY_STATUS View for more information.

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