Database Replay

Database Replay workload capture of external clients is performed at the database server level. Therefore, Database Replay can be used to assess the impact of any system changes below the database tier level such as below:

 
 

•  Database upgrades, patches, parameter, schema changes, etc.
  
• Configuration changes such as conversion from a single instance to RAC etc.
  
• Storage, network, interconnect changes
  
• Operating system, hardware migrations, patches, upgrades, parameter changes
  

 
 

 
 

DB replay does this by capturing a workload on the production system with negligible performance overhead( My observation is 2-5% more CPU usage ) and replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results; new contentions points or performance regressions. Extensive analysis and reporting ( AWR , ADDM report and DB replay report) is provided to help identify any potential problems, such as new errors encountered and performance divergences. The ability to accurately capture the production workload results in significant cost and timesaving since it completely eliminates the need to develop simulation workloads or scripts. As a result, realistic testing of even complex applications using load simulation tools/scripts that previously took several months now can be accomplished at most in a few days with Database Replay and with minimal effort. Thus using Database Replay, businesses can incur much lower costs and yet have a high degree of confidence in the overall success of the system change and significantly reduce production deployment

 
 

Steps for Database Replay

 
 

1. Workload Capture
   

 
 

Database are tracked and stored in binary files, called capture files, on the file system. These files contain all relevant information about the call needed for replay such as SQL text, bind values, wall clock time, SCN, etc.

 
 

1)     Backup production Database #

2)     Add/remove filter ( if any you want )
By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload. Inclusion filters enable you to specify user sessions that will be captured in the workload. This is useful if you want to capture only a subset of the database workload.
For example , we don't want to capture load for SCOTT user 

    
 

BEGIN   DBMS_WORKLOAD_CAPTURE.ADD_FILTER (                            fname => 'user_scott',                            fattribute => 'USER',                            fvalue => 'SCOTT'); END;

Here filter name is "user_scott" ( user define name)

3)     Create directory make sure enough space is there

    
 

CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u04/oraout/test/db-replay-capture';

 
 

4)     Capture workload   

BEGIN       DBMS_WORKLOAD_CAPTURE.start_capture (        name => capture_testing',dir=>'DB_REPLAY_DIR',       duration => NULL ); END;

Duration => NULL mean , it will capture load till we stop with below mentioned manual SQL command. Duration is optional input to specify the duration (in seconds) , default is NULL

5)     Finish capture

   
 

BEGIN     DBMS_WORKLOAD_CAPTURE.finish_capture; END;

 
 

 
 

# Take backup of production before Load capture, so we can restore database on test environment and will run replay on same SCN level of database to minimize data divergence

 
 

Note as per Oracle datasheet

 
 

The workload that has been captured on Oracle Database release 10.2.0.4 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set 10.2.0.4 will support capture processes.  Is it mean Current patch set (10.2.0.3) not support load capture ??????

 
 

2.      Workload Processing

Once the workload has been captured, the information in the capture files has to be processed preferably on the test system because it is very resource intensive job. This processing transforms the captured data and creates all necessary metadata needed for replaying the workload.                  

exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');

  
 

 
 

1. Workload Replay
   

   1)     Restore database backup taken step one to test system and start Database
   

   2)     Initialize
   

   BEGIN DBMS_WORKLOAD_REPLAY.initialize_replay (   replay_name => 'TEST_REPLAY',   replay_dir => 'DB_REPLAY_DIR'); END;

    
    

   3)     Prepare
   

 
 

exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)

 
 

4)     Start clients

 
 

$ wrc mode=calibrate replaydir=/u03/oradata/test/db-replay-capture Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:41 2007 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Report for Workload in: /u03/oradata/test/db-replay-capture ----------------------- Recommendation: Consider using at least 1 clients divided among 1 CPU(s). Workload Characteristics: - max concurrency: 1 sessions - total number of sessions: 7 Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - think time scale = 100 - connect time scale = 100 - synchronization = TRUE

 

 

$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Wait for the replay to start (00:31:52)

  
 

5)     Start Replay

BEGIN         DBMS_WORKLOAD_REPLAY.start_replay; END; /

  
 

$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Wait for the replay to start (00:31:52) Replay started (00:33:32) Replay finished (00:42:52)

 

1. Analysis and Reporting
   

Generate AWR , ADDM and DB reply report and compare with data gathered on production for same time period when load was captured on Production database. For Database Replay Report  run following command 

                
 

 
 

SQL>  COLUMN name FORMAT A20                 SQL> SELECT id, name FROM dba_workload_replays;                             ID NAME                     ---------- --------------------                              1 TEST_REPLAY     DECLARE    v_report  CLOB; BEGIN     v_report := DBMS_WORKLOAD_replay.report(                      replay_id => 1,                   format=>DBMS_WORKLOAD_CAPTURE.TYPE_HTML                     );     dbms_output.put_line(l_report); END; /