Oracle 11g Feature Database Replay


Oracle Database 11g Top New Features for DBA

08-AUG-07 Oracle 11g New Feature Database Replay

“Simulating production load is not possible” , you might have heard these word.

In one project, where last 2 year  management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.

After using these feature of 11g , they will gain confidence and will able to migrate to Linux with full confidence and will know how there system will behave after migration/upgrade.


As per  datasheet given on OTN

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 


                           fname => 'user_scott',
                           fattribute => 'USER',
                           fvalue => 'SCOTT');

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

3)     Create directory make sure enough space is there



AS '/u04/oraout/test/db-replay-capture';



4)     Capture workload   

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

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





# 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 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set will support capture processes.  Is it mean Current patch set ( 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

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


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 - 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

Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 7

- 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 - 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



$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release - 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> SELECT id, name FROM dba_workload_replays;

                            ID NAME
                    ---------- --------------------
                             1 TEST_REPLAY


   v_report  CLOB;
    v_report :=
                     replay_id => 1,


For sample report [ Click Here]



Chapter 22 Database Replay