11g可以将生产库的一些负载replay到测试库上，这就便于我们在测试上进行真实的重演现网的状态。otn上介绍是用em，其实很多dba还是喜欢用命令行吧，在这里简单介绍下replay的架构和其主要操作的步骤：
replay的架构：
前期准备：
创建和生产库逻辑结构类似的测试库：可以用rman、standby snapshot、exp/imp
一、捕获数据库负载
1.重启数据库（非必要选项）
2.建立负载过滤器
3.建立负载文件存储目录
4.开始捕获负载
5.停止捕获负载
6.导出捕获的负载AWR数据
二 预处理负载
建立重演的存储文件路径，传输文件到此路径。
将捕获文件转换为重演文件，此步骤耗时耗资源，请在需要重演的测试库上进行，不要在生产库上进行！！
三、重演负载
wrc用于模拟将原生产库上的客户端的负载请求，模拟到测试库上
1.将测试库的系统时间调整为和开始捕获生产库的时间（非必要选项）
2.关闭测试机上来自外界的影响：通过dblink的连接、外部表、目录对象、URL、email
3.建立wrc（Workload Replay Client）：wrc用于模拟将原生产库上的客户端的负载请求，模拟到测试库，可以先校准wrc
4.初始化重演
5.重新配置连接
6.设置负载重演的选项
7.开启wrc重演
8.开始负载重演
9.结束负载重演
10.导出负载重演的AWR数据
四、分析重演的负载
下面我们就来进行实际的演练（AWR的相关知识和重演之后的分析不在此处介绍）：
在生产库上：
1.建立负载过滤器
建立过滤器filter_test用于过滤掉user为system的规则：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> --create capture filter SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.ADD_FILTER ( 3 fname => 'filter_test', 4 fattribute => 'USER', 5 fvalue => 'SYSTEM'); 6 END; 7 / PL/SQL procedure successfully completed. |
2.建立负载文件存储目录
|
1 2 3 4 |
SQL> --create workload dir SQL> create directory test_dir as '/oradata/dir_for_testfile'; Directory created. |
3.开始捕获负载
历时120秒，如果没有指定历时，需要使用FINISH_CAPTURE存储过程来停止。
注意2点：
（1）.目录不能有其他文件!!!不然会报错15505：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'peak_120s', 3 dir => 'TEST_DIR', 4 duration => 120); 5 END; 6 / BEGIN * ERROR at line 1: ORA-15505: cannot start workload capture because instance 1 encountered errors while accessing directory "/oradata/dir_for_testfile" ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799 ORA-06512: at line 2 |
（2）.dir => ‘DIR_NAME’此处需要大写，教材中是小写，会报错20222：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'peak_120s', 3 dir => 'test_dir', 4 duration => 120); 5 END; 6 / BEGIN * ERROR at line 1: ORA-20222: Invalid DB State or Input. Input "test_dir" is not a valid DIRECTORY object! ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 783 ORA-06512: at line 2 |
正确的执行应该为：
|
1 2 3 4 5 6 7 8 |
SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'peak_120s', 3 dir => 'TEST_DIR', 4 duration => 120); 5 END; 6 / PL/SQL procedure successfully completed |
4. 执行负载脚本test.sql
|
1 2 3 |
SQL> conn hr/hr Connected. SQL> @/oradata/test.sql |
其中 test.sql为：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@localdb oradata]$ cat test.sql declare l_stmt varchar2(2000); begin for ctr in 1..10000 loop l_stmt := 'insert into xxx values (''''|| lpad(round(dbms_random.value(1,9999999999999999999)),20,0)||'''')'; dbms_output.put_line(l_stmt); execute immediate l_stmt; commit; end loop; end; / |
5.可等待历时过后，或手工stop捕获：
|
1 2 3 |
SQL> exec dbms_workload_capture.FINISH_CAPTURE(); PL/SQL procedure successfully completed. |
6.导出捕获的负载AWR数据
（略）
——————————————————————-
在测试库上：
1.建立负载文件存储目录
|
1 2 3 |
SQL> create directory test_dir as '/oradata/dir_for_testfile'; Directory created. |
将生产库上的负载文件传输到测试库此目录
2.预处理负载。
注意要让SYSAUX有足够的空间剩余，不然会报错1658：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'TEST_DIR'); 3 END; 4 / BEGIN * ERROR at line 1: ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 1138 ORA-06512: at line 2 |
我们增大空间后再次处理：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select file_name from dba_data_files where tablespace_name='SYSAUX'; FILE_NAME -------------------------------------------------------------------------------- /oradata/dfile/oralocal/sysaux01.dbf SQL> alter database datafile '/oradata/dfile/oralocal/sysaux01.dbf' resize 800m; Database altered. SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'TEST_DIR'); 3 END; 4 / PL/SQL procedure successfully completed. |
3.wrc校准：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[oracle@localdb dir_for_testfile]$ wrc mode=calibrate replaydir=/oradata/dir_for_testfile Workload Replay Client: Release 11.1.0.6.0 - Production on Fri Dec 14 02:52:09 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Report for Workload in: /oradata/dir_for_testfile ----------------------- 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 |
4.初始化重演
|
1 2 3 4 5 6 7 |
SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'peak_120s', 3 replay_dir => 'TEST_DIR'); 4 END; 5 / PL/SQL procedure successfully completed. |
5.设置负载重演的选项
|
1 2 3 4 5 6 |
SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE); 3 END; 4 / PL/SQL procedure successfully completed. |
6. wrc重演，在DBMS_WORKLOAD_REPLAY.START_REPLAY之前，会提示Wait for the replay to start
|
1 2 3 4 5 6 7 8 |
[oracle@localdb dir_for_testfile]$ wrc system/oracle mode=replay replaydir=/oradata/dir_for_testfile Workload Replay Client: Release 11.1.0.6.0 - Production on Fri Dec 14 02:56:44 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (02:56:44) |
7.开始负载重演（由于wrc会一直等待，我们需要另开一个窗口执行）
|
1 2 3 4 5 6 7 8 |
SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.START_REPLAY (); 3 END; 4 / PL/SQL procedure successfully completed. SQL> |
此时另一端wrc会出现：
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@localdb dir_for_testfile]$ [oracle@localdb dir_for_testfile]$ wrc system/oracle mode=replay replaydir=/oradata/dir_for_testfile Workload Replay Client: Release 11.1.0.6.0 - Production on Fri Dec 14 02:56:44 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (02:56:44) Replay started (02:58:05) |
8.结束负载重演
|
1 2 3 4 5 6 7 |
SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY (); 3 END; 4 / PL/SQL procedure successfully completed. |
9.导出负载重演的AWR数据
（略）
四、分析重演的负载
（略）
检查测试库上，我们再检查一下是否10000行记录已经重演：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[oracle@localdb dir_for_testfile]$ sqlplus hr/hr SQL*Plus: Release 11.1.0.6.0 - Production on Fri Dec 14 03:01:04 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from xxx; COUNT(*) ---------- 10000 |
确实发现已经重演，重演时的负载如何，就需要通过后续的分析了，在这里不展开。
其他：
重演的注意点：
1.注意生产库和测试库的数据库版本要一致
2.有些客户端的请求不会被捕获，因此不能让重演：
■ Direct path load of data from external files using utilities such as SQL*Loader
■ Shared server requests (Oracle MTS)
■ Oracle Streams
■ Advanced replication streams
■ Non-PL/SQL based Advanced Queuing (AQ)
■ Flashback queries
■ Oracle Call Interface (OCI) based object navigations
■ Non SQL-based object access
■ Distributed transactions (any distributed transactions that are captured will be
replayed as local transactions)
■ Remote DESCRIBE and COMMIT operations