How to Use Database Replay Feature to Help With The Upgrade From 10.2.0.4 to 11g (文件 ID 748895.1)

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 25-Jun-2012***

Goal

The purpose behind creating this article is to give a detailed information about using the Database Replay feature before upgrading from 10.2.0.4 to 11g, supported by a life example.

Before making changes, such as hardware or software upgrades, companies typically conduct extensive testing to validate the changes. However, when the change is made on the production system, unexpected problems are often encountered because the testing was not performed with a workload that was a good representation of the production environment. Tools are available in the market to construct "synthetic workloads" involving multiple users. However, they are not able to adequately simulate the complexities of a real-world workload, such as interactions between concurrent activities and unpredictability of the workload profile.

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

The ability to replay the workload, however, has been introduced in Oracle Database 11g (11.1). This has been done so that when you upgrade from Oracle Database release 10.2.0.4 to 11.1 or 11.2, you can use the Database Replay feature to help with the upgrade.

Actually, the Capture feature has started to be included starting from 10.2.0.4 (DBMS_WORKLOAD_CAPTURE) but the Database Replay complete functionality started in 11.1.0.6 and up. Thus you can use the capture process to capture the workload in 10.2.0.4 and then on a test environment replay the captured workload into 11g database before doing the upgrade.

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 it simply mean NEW patch set 10.2.0.4 will support capture processes.

NOTE:
The database capture functionality was introduced with 10.2.0.4. However, patches are available for some earlier versions.
Please refer to:
Using Real Application Testing Functionality in Earlier Releases (Doc ID 560977.1)

Note also that Database Capture and Replay is part of the Real Application Testing (RAT) option which needs a separate license.

Solution

Steps for Database Replay

1. Workload Capture For the 10.2.0.4 Database.

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.
a) Backup production Database #

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

c) Create directory make sure enough space is there

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

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

Note: In 10.2.0.4 make sure that the PRE_11G_ENABLE_CAPTURE parameter should be set to true otherwise if you try to use the capture feature you will get the following error;

ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled.

You can use the following if it is set to FALSE.

SQL> alter system set pre_11g_enable_capture=true;

e) 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.

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');

3. Workload Replay

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

b) Initialize

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

c) Prepare

exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)

d) Start clients

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

$ wrc 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 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)

Example for Capturing 10.2.0.4 database and Replaying it into 11.1.0.6 database:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'user_scott',
fattribute => 'USER',
fvalue => 'SCOTT');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir';  2

Directory created.

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (
name => 'capture_testing',dir=>'DB_REPLAY_DIR',
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

SQL>
SQL> show parameter pre_11g

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pre_11g_enable_capture               boolean     FALSE
SQL> alter system set pre_11g_enable_capture=true;

System altered.

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (
name => 'capture_testing',dir=>'DB_REPLAY_DIR',
duration => NULL );
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/  2    3    4

PL/SQL procedure successfully completed.

SQL>exit

/*-----------------------------------------------------------------*/
/*Then connecting the the 11.1.0.6 database located in the same box*/
/*-----------------------------------------------------------------*/

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir';  2

Directory created.

SQL> exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
replay_name => 'TEST_REPLAY',
replay_dir => 'DB_REPLAY_DIR');
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE);

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/   2    3    4
BEGIN
*
ERROR at line 1:
ORA-20223: No replay clients have connected yet! Please issue START_REPLAY()
after one or more WRC replay clients have been started!
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 1469
ORA-06512: at line 2

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[rmtdcsol3]/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir> wrc replaydir=/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir  mode=calibrate

Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Nov 11 06:58:52 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Report for Workload in: /emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir
-----------------------

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

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

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

[rmtdcsol3]/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir> wrc replaydir=/emea/bde/64bit/app/oracle/product/10.2.0/db_replay_dir

Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Nov 11 07:00:57 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Wait for the replay to start (07:00:57)
Replay started (07:04:16)
Replay finished (07:09:22)

SQL> BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

PL/SQL procedure successfully completed.

SQL> select name,status from  dba_workload_replays;

NAME
--------------------------------------------------------------------------------
STATUS
----------------------------------------
TEST_REPLAY
COMPLETED

References

NOTE:445116.1 - Using Workload Capture and Replay in 11G
NOTE:463263.1 - Database Capture And Replay Common Errors And Reasons
NOTE:560977.1 - Using Real Application Testing Functionality in Earlier Releases