What is the critical concern when we need to make a change in the production database ?  The biggest concern is the risk of the change – “mess up” something. Even the most minor changes can have an awful effect, eventually producing a visible impact. In Oracle Database 11g, We have a new feature called Real Application Testing. Here are few experiments I performed with Real Application Testing, I used APIs instead of DB console/ EM – same can be performed with DB console also.

Some bullets – before moving to the try-out ->

Oracle RAT – Real Application Testing

  • Introduced in Oracle 11.1
  • Licensable option
  • Includes
    • Database Replay
    • SQL Performance Analyzer
  • Database Replay
    • Captures database workload on production system
    • Replays captured workload on test system
    • Optionally includes concurrency and timing characteristics
    • Generates reports and recommends changes
  • SQL Performance Analyzer
    • Identifies performance divergence between workloads on source and target platforms
    • Generates tuning recommendations

Here are my try-out with Database Replay.

Database Replay

Requires the following steps:

  • Capture workload into capture files
  • Copy files to test system and pre-process them
  • Replay files on test system
  • Perform detailed analysis of workload capture and replay using reports generated by Database Replay

Potential scenarios where database replay may help:

  • Testing operating system upgrades
  • Testing database upgrades and migrations
  • Testing parameter changes
  • Evaluate migration from single-instance to RAC
  • Testing storage changes
  • Debugging
  • Testing database manageability features

Capture Workload

  • Create an operating system directory
[oracle@orachat]$ mkdir /home/oracle/rat/test1
  • Create an Oracle directory
SQL> CREATE DIRECTORY dir1 AS ‘/home/oracle/rat/test1′;
  • Start the capture process
dbms_workload_capture.start_capture
(
name => ‘TEST4′,dir => ‘DIR4′
);
  • Run the workload – Do some random works and transactions etc.
  • Finish the capture process
dbms_workload_capture.finish_capture;

Before a workload can be replayed, the application data state should be logically equivalent to that of the capture system at the start time of workload capture.If RMAN was used to back up the capture system, you can use RMAN DUPLICATE capabilities to create the test database.

Replay Preparation

  • Copy the capture files to the target test database server
  • On the target database – Pre process the captured workload
dbms_workload_replay.process_capture
(
capture_dir => ‘DIR1′)
);
  • Initialize the replay
dbms_workload_replay.initialize_replay
(
replay_name => ‘REPLAY1′,
replay_dir => ‘DIR1′
);

  • Prepare the replay
dbms_workload_replay.prepare_replay
(
synchronization => FALSE
);

Workload Replay

  • Copy the workload files to the client system. This could be the same target system or any other machine where 11g client is installed For eg:
/home/oracle/rat/test1
  • On the client , run the workload client to calibrate the replay
$ wrc orachat/orachat@cluster1-tns mode=CALIBRATE
\ replaydir=/home/oracle/rat/test1
  • The calibration process recommends the number of client processes required to perform the replay
  • Replay the workload
$ wrc orachat/orachat@cluster1-tns mode=REPLAY
\replaydir=/home/oracle/rat/test1
  • wrc – is a multithreaded program located in the $ORACLE_HOME/bin directory, where each thread submits a workload from a captured session.
  • The client will not start to replay the workload until the replay has been started on the target database
  • On the target database, start the replay process
  • SQL> exec dbms_workload_replay.start_replay;
  • When the replay process completes on the client, run the replay report
SET SERVEROUTPUT ON TRIMSPOOL ON LONG 500000 LINESIZE 200
VAR v_rep_rpt CLOB;
DECLARE
l_cap_id NUMBER;  l_rep_id NUMBER;
BEGIN
l_cap_id := dbms_workload_replay.get_replay_info (dir => ‘DIR4′);
SELECT MAX (id) INTO l_rep_id FROM dba_workload_replays
WHERE capture_id = l_cap_id;
:v_rep_rpt := dbms_workload_replay.report
(
replay_id => l_rep_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
);
END;
/
PRINT :v_rep_rpt
Reference:
Real Application testing – Oracle 11g Library
PrintFriendly

Viewers of this post also viewed...