Oracle Database Load Capture

[oracle@orcl01 dbcapture]$ sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 20 10:26:01 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

It looked like a nice addition because, as others have pointed out on their blogs, it could prove very useful for capturing an application workload on 10g to test how it will work on 11g before upgrading. There's a genuine business need for that. Then, having updated our first couple of test instances at work, I was reminded of the great care we take with license management at my current site (thanks, Tam) and, on closer inspection of the documentation :-

The Oracle Real Application Testing license is required on both capture and replay systems for Database Replay and is charged by the total number of CPUs on those systems.

I haven't used workload capture on this instance yet, so I thought I'd give it a quick try. Interesting though it is to try this out for the first time, I'm going to keep this post short by referring you to another blog that shows how to use RAT via OEM DB Console. Actually, my first attempt failed with the following error message.

SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1',
                                       dir      => 'DBCAPTURE',
                                       duration => NULL);
END;
/  2    3    4    5    6
BEGIN
*
ERROR at line 1:
ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799
ORA-06512: at line 2

select name, detected_usages, currently_used,
    first_usage_date, feature_info, last_sample_date,
    sample_interval, description
    from DBA_FEATURE_USAGE_STATISTICS
    where name like 'Database Replay%'
    /

   
    NAME                                                             DETECTED_USAGES                  CURRE FIRST_USA
---------------------------------------------------------------- ---------------                  ----- ---------
FEATURE_INFO                                                                                     LAST_SAMP SAMPLE_INTERVAL
--------------------------------------------------------------------------------                  --------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------                 ------------------------------------------------
Database Replay: Workload Capture                                              0                  FALSE
                                                                                                 18-DEC-13           604800
Database Replay: Workload was ever captured.


you have to reset the value pre_11g_enable_capture to true.

SQL> alter system set pre_11g_enable_capture=true;

System altered.



SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1',
                                       dir      => 'DBCAPTURE',
                                       duration => 900);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.