How to capture a workload in 10g for testing in 11g

Last week the patchset 10.2.0.4 was released by Oracle.

 This patchset not only contains bugfixes but also comes with a number of New Features.

One of the highlights is the possibilty to capture a workload in a 10.2.0.4 database and ship it to an 11g database for testing.

Only with this feature in place the 11g Real Application Testing Feature DATABASE REPLAY makes real sense.
Before this patchset it was only possible to capture a workload in an 11g database and run it for  testing in an also 11g database.

For more detailed evaluation please see my chapter on CHANGE MANAGEMENT in the forthcoming 11g New Features book.

 It will be shipping aroung middle of the year.

Now customers can check in front what an upgrade would bring for them.

Here is a brief desription of the new 1og Feature:

LUTZ AS SYSDBA @ orcl10g SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

In 10.2.0.4 a new paramter is used to enable workload capturing:

LUTZ AS SYSDBA @ orcl10g SQL> show parameter pre_11

NAME                                 TYPE        VALUE
———————————— ———– ——————————
pre_11g_enable_capture               boolean     FALSE
Note:
The PRE_11G_ENABLE_CAPTURE initialization parameter can only be used with Oracle Database 10g Release 2 (10.2). This parameter is not valid in subsequent releases. After upgrading the database, you will need to remove the parameter from the server parameter file (spfile) or the initialization parameter file (init.ora); otherwise, the database will fail to start up.

Oracle even has created the script $ORACLE_HOME/rdbms/admin/wrrenbl.sql which can be used to modity the parameter.  The script to disable workload capture is $ORACLE_HOME/rdbms/admin/wrrdsbl.sql.  

The package DBMS_WORKLOAD_CAPTURE has been added to the 10g RDBMS. This is the interface to workload capturing and filtering:

SYS AS SYSDBA @ orcl10g SQL> desc dbms_workload_capture

PROCEDURE ADD_FILTER
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 FNAME                          VARCHAR2                IN
 FATTRIBUTE                     VARCHAR2                IN
 FVALUE                         VARCHAR2                IN
PROCEDURE ADD_FILTER
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 FNAME                          VARCHAR2                IN
 FATTRIBUTE                     VARCHAR2                IN
 FVALUE                         NUMBER                  IN
PROCEDURE DELETE_CAPTURE_INFO
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CAPTURE_ID                     NUMBER                  IN
PROCEDURE DELETE_FILTER
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 FNAME                          VARCHAR2                IN
PROCEDURE EXPORT_AWR
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CAPTURE_ID                     NUMBER                  IN
PROCEDURE FINISH_CAPTURE
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 TIMEOUT                        NUMBER                  IN     DEFAULT
 REASON                         VARCHAR2                IN     DEFAULT
FUNCTION GET_CAPTURE_INFO RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 DIR                            VARCHAR2                IN
FUNCTION REPORT RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CAPTURE_ID                     NUMBER                  IN
 FORMAT                         VARCHAR2                IN
PROCEDURE START_CAPTURE
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 NAME                           VARCHAR2                IN
 DIR                            VARCHAR2                IN
 DURATION                       NUMBER                  IN     DEFAULT
 DEFAULT_ACTION                 VARCHAR2                IN     DEFAULT
 AUTO_UNRESTRICT                BOOLEAN                 IN     DEFAULT

And the data dictionary views DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS can be used to monitor the captures.

 As in 11g the packages captures all incomming requestes into external files which need to be shipped to a testing system and pre-processed on the testing system. Those files contain logical information to replay client requests in the appropriate sequence and with all contention and waiting as it happened on the capture site. On the testing site we need to start replay clients to run the test workload.

This is the interface for REPLAY DATABASE in the OEM GUI:

The executable $ORACLE_HOME/bin/wrc is only available in 11g.

 So we can now capture in 10g and replay in 11g.