Before restarting the database, determine an appropriate time to shut down the production database before the workload capture when it is the least disruptive. For example, you may want to capture a workload that begins at 8:00 a.m. However, to avoid service interruption during normal business hours, you may not want to restart the database during this time. In this case, you should consider starting the workload capture at an earlier time, so that the database can be restarted at a time that is less disruptive.

Once the database is restarted, it is important to start the workload capture before any user sessions reconnect and start issuing any workload. Otherwise, transactions performed by these user sessions will not be replayed properly in subsequent database replays, because only the part of the transaction whose calls were executed after the workload capture is started will be replayed. To avoid this problem, consider restarting the database in RESTRICTED mode using STARTUP RESTRICT, which will only allow the SYS user to login and start the workload capture. By default, once the workload capture begins, any database instance that are in RESTRICTED mode will automatically switch to UNRESTRICTED mode, and normal operations can continue while the workload is being captured.

Only one workload capture can be performed at any given time. If you have a Oracle Real Application Clusters (Oracle RAC) configuration, workload capture is performed for the entire database. Once you enable capture for one of the Oracle RAC nodes, workload capture is started on all database instances (the workload capture process is Oracle RAC aware). Although it is not required, restarting all instances in a Oracle RAC configuration before workload capture is recommended to avoid capturing ongoing transactions.

To restart all instances in a Oracle RAC configuration before workload capture:

To monitor an active workload capture:

To stop an active workload capture:

To view a completed workload capture:

To add filters to a workload capture:

• Use the ADD_FILTER procedure:

  BEGIN
    DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
                             fname => 'user_ichan',
                             fattribute => 'USER',
                             fvalue => 'ICHAN');
  END;
  /
  

  In this example, the ADD_FILTER procedure adds a filter named user_ichan, which can be used to filter out all sessions belonging to the user name ICHAN.

  The ADD_FILTER procedure in this example uses the following parameters:

  • The fname required parameter specifies the name of the filter that will be added.

  • The fattribute required parameter specifies the attribute on which the filter will be applied. Valid values include PROGRAM, MODULE, ACTION, SERVICE, INSTANCE_NUMBER, and USER.

  • The fvalue required parameter specifies the value for the corresponding attribute on which the filter will be applied. It is possible to use wildcards such as % with some of the attributes, such as modules and actions.

To remove filters from a workload capture:

• Use the DELETE_FILTER procedure:

  BEGIN
    DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (fname => 'user_ichan');
  END;
  /
  

  In this example, the DELETE_FILTER procedure removes the filter named user_ichan from the workload capture.

  The DELETE_FILTER procedure in this example uses the fname required parameter, which specifies the name of the filter to be removed. The DELETE_FILTER procedure will not remove filters that belong to completed captures; it only applies to filters of captures that have yet to start.

It is important to have a well-defined starting point for the workload so that the replay system can be restored to that point before initiating a replay of the captured workload. To have a well-defined starting point for the workload capture, it is preferable not to have any active user sessions when starting a workload capture. If active sessions perform ongoing transactions, those transactions will not be replayed properly in subsequent database replays, since only that part of the transaction whose calls were executed after the workload capture is started will be replayed. To avoid this problem, consider restarting the database in restricted mode using STARTUP RESTRICT before starting the workload capture. Once the workload capture begins, the database will automatically switch to unrestricted mode and normal operations can continue while the workload is being captured. For more information about restarting the database before capturing a workload, see "Restarting the Database".

To start a workload capture:

• Use the START_CAPTURE procedure:

  BEGIN
    DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'dec10_peak', 
                             dir => 'dec10',
                             duration => 600,
                             capture_sts => TRUE,
                             sts_cap_interval => 300,
                             plsql_mode => 'extended'
                             encryption => 'AES256');
  END;
  /
  

  In this example, a workload named dec10_peak will be captured for 600 seconds and stored in the file system defined by the database directory object named dec10. A SQL tuning set will also be captured in parallel with the workload capture.

  The START_CAPTURE procedure in this example uses the following parameters:

  • The name required parameter specifies the name of the workload that will be captured.

  • The dir required parameter specifies a directory object pointing to the directory where the captured workload will be stored.

  • The duration parameter specifies the number of seconds before the workload capture will end. If a value is not specified, the workload capture will continue until the FINISH_CAPTURE procedure is called.

  • The capture_sts parameter specifies whether to capture a SQL tuning set in parallel with the workload capture. If this parameter is set to TRUE, you can capture a SQL tuning set during workload capture, then capture another SQL tuning set during workload replay, and use SQL Performance Analyzer to compare the SQL tuning sets without having to re-execute the SQL statements. This enables you to obtain a SQL Performance Analyzer report and compare the SQL performance—before and after the change—while running Database Replay. You can also export the resulting SQL tuning set with its AWR data using the EXPORT_AWR procedure, as described in "Exporting AWR Data for Workload Capture".

    This feature is not supported for Oracle RAC. Workload capture filters that are defined using DBMS_WORKLOAD_CAPTURE do not apply to the SQL tuning set capture. The default value for this parameter is FALSE.

  • The sts_cap_interval parameter specifies the duration of the SQL tuning set capture from the cursor cache in seconds. The default value is 300. Setting the value of this parameter below the default value may cause additional overhead with some workloads and is not recommended.

  • The optional plsql_mode parameter determines how PL/SQL is handled by DB Replay during capture and replays.

    These two values can be set for the plsql_mode parameter:

    • top_level: Only top-level PL/SQL calls are captured and replayed, which is how DB Replay handled PL/SQL prior to Oracle Database 12c Release 2 (12.2.0.1). This is the default value.

    • extended: Both top-level PL/SQL calls and SQL called from PL/SQL are captured. When the workload is replayed, the replay can be done at either top-level or extended level, but not both.

  • The encryption parameter specifies the algorithm used for encrypting capture files.

    The following encryption standards can be used for the encryption parameter:

    • NULL - Capture files are not encrypted (the default value)

    • AES128 - Capture files are encrypted using AES128

    • AES192 - Capture files are encrypted using AES192

    • AES256 - Capture files are encrypted using AES256

    Note:

    To run START_CAPTURE with encryption, you must set the password using the oracle.rat.database_replay.encryption (case-sensitive) identifier. The password is stored in a software keystore. For more information about creating a software keystore, see Oracle Database Advanced Security Guide.

Example: Setting up a password-based software keystore:

The following statement creates a password-based software keystore:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'MYKEYSTORE' IDENTIFIED BY password;

The following statements open the password-based software keystore and create a backup of the password-based software keystore:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP;

The following statement adds secret secret_key, with the tag DBREPLAY, for client 'oracle.rat.database_replay.encryption' to the password-based software keystore. It also creates a backup of the password-based software keystore before adding the secret:

ADMINISTER KEY MANAGEMENT ADD SECRET secret_key FOR CLIENT 'oracle.rat.database_replay.encryption' USING TAG 'DBREPLAY' 
IDENTIFIED BY password WITH BACKUP;

The following statement closes the password-based software keystore:

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY password;

To stop a workload capture:

• Use the FINISH_CAPTURE procedure:

  BEGIN
    DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE (); 
  END;
  /
  

  In this example, the FINISH_CAPTURE procedure finalizes the workload capture and returns the database to a normal state.

To export AWR data:

• Use the EXPORT_AWR procedure:

  BEGIN
    DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 2);
  END;
  /
  

  In this example, the AWR snapshots that correspond to the workload capture with a capture ID of 2 are exported, along with any SQL tuning set that may have been captured during workload capture.

  The EXPORT_AWR procedure uses the capture_id required parameter, which specifies the ID of the capture whose AWR snapshots will be exported. The value of the capture_id parameter is displayed in the ID column of the DBA_WORKLOAD_CAPTURES view.

  Note:

  This procedure works only if the corresponding workload capture was performed in the current database and the AWR snapshots that correspond to the original capture time period are still available.

To import AWR data:

• Use the IMPORT_AWR function, as shown in the following example:

  CREATE USER capture_awr
  SELECT DBMS_WORKLOAD_CAPTURE.IMPORT_AWR (capture_id => 2,
                                           staging_schema => 'capture_awr')
    FROM DUAL;
  

  In this example, the AWR snapshots that correspond to the workload capture with a capture ID of 2 are imported using a staging schema named capture_awr.

  The IMPORT_AWR procedure in this example uses the following parameters:

  • The capture_id required parameter specifies the ID of the capture whose AWR snapshots will be import. The value of the capture_id parameter is displayed in the ID column of the DBA_WORKLOAD_CAPTURES view.

  • The staging_schema required parameter specifies the name of a valid schema in the current database which can be used as a staging area while importing the AWR snapshots from the capture directory to the SYS AWR schema.