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 Cloning and Moving an Autonomous Database 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.

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.

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.

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.

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 follwowing 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;
/

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.

When you begin workload capture on the 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.

See Workload Capture to capture a workload on an on-premises database.

Run 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 REPLAY_WORKLOAD.

Example to replay on an Autonomous Database instance a workload captured from an on-premises database:

BEGIN 
   DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
      location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
      credential_name => 'CRED_TEST',   
      synchronization => TRUE,
      process_capture => TRUE);    
END;
/

This downloads the capture files contained in the Object Storage location specified in the location_uri parameter, and replays the workload capture from the capture files. The replay generates and uploads the replay and Automatic Workload Repository reports to the Object Storage location specified in the location_uri parameter.

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

See Upload Files to Your Oracle Cloud Infrastructure Object Store Bucket for more information on uploading files to Object Storage.

See Navigate to Oracle Cloud Infrastructure Object Storage and Create Bucket for more information on Object Storage.

The credential_name parameter specifies the credential to access the object storage bucket. The credential that you supply must have the write privileges to write into the Object Storage bucket. The write privileges are required to upload the replay report to the bucket.

If you do not specify a credential_name value, then DEFAULT_CREDENTIAL is used.

You don't need to create a credential to access Oracle Cloud Infrastructure Object Store if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

The synchronization parameter specifies the synchronization method used during workload replay. A TRUE value indicates that the synchronization is SCN based.

The process_capture specifies whether you need to specify process_capture value or not. A TRUE value indicates that the replay includes process_capture.

See REPLAY_WORKLOAD Procedure for more information.

Workload Replay Events

You can subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the WorkloadReplayBegin and WorkloadReplayEnd events. These events provide information about:

• Start and completion of the REPLAY_WORKLOAD.

• The replayDownloadURL field that contains the PAR URL to access the reports in Object Store. The PAR URL is valid for 7 days from the date of generation.

See Information Events on Autonomous Database for more information.

Workload Capture and Replay Views

You can query the DBA_CAPTURE_REPLAY_STATUS and DBA_CAPTURE_REPLAY_HISTORY views to check the workload replay status.

See DBA_CAPTURE_REPLAY_STATUS View and DBA_CAPTURE_REPLAY_HISTORY View for more information.

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.

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.

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.