How to Capture a Workload in a 10g Database for Replay in an 11g Database Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015

Oracle 11g New Features Tips

At the end of February 2015, Oracle released the 10.2.0.4. patchset. This patchset not only contains bug fixes but also comes with a number of new features for 10gR2. The most important highlight here is the possibility to capture a workload in a 10 database and run it in an 11g database for testing.  Look how this works in a 10g database.

With 10.2.0.4, Oracle has introduced a new parameter which can only be used in 10.2.0.4. to enable resp.  disable workload capturing:

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

LUTZ AS SYSDBA @ orcl10g SQL> show parameter pre_11

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pre_11g_enable_capture               boolean     FALSE

There are two scripts which can be used to modify the parameter:

The package DBMS_WORKLOAD_CAPTURE has been added to the 10g RDBMS. This is the interface to workload capturing and filtering. Also, the data dictionary views DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS can be used to monitor the captures. The Enterprise Manager has a graphical interface for Replay Database integrated found in the Server Pane:

Figure 13:  Database Replay Screen

A workload can only be captured on a 10g database system. The replay must be run on an 11g or higher version.

With this 10g New Feature, it is possible to test the impact of an upgrade to RDBMS Server 11g and the consensus is that this will boost Oracle's license sales dramatically.