11g新特性-重演(replay)
· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接:
http://www.oracleblog.cn/study-note/11g-new-feature-replay/ ·
--------------------------------------------------------------------------------
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的规则：
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.建立负载文件存储目录
SQL> --create workload dir
SQL> create directory test_dir as '/oradata/dir_for_testfile';
Directory created.
3.开始捕获负载
历时120秒，如果没有指定历时，需要使用FINISH_CAPTURE存储过程来停止。
注意2点：
（1）.目录不能有其他文件!!!不然会报错15505：
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：
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
正确的执行应该为：
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
SQL> conn hr/hr
Connected.
SQL> @/oradata/test.sql
其中 test.sql为：
[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捕获：
SQL> exec dbms_workload_capture.FINISH_CAPTURE();
PL/SQL procedure successfully completed.
6.导出捕获的负载AWR数据
（略）
——————————————————————-
在测试库上：
1.建立负载文件存储目录
SQL> create directory test_dir as '/oradata/dir_for_testfile';
Directory created.
将生产库上的负载文件传输到测试库此目录
2.预处理负载。
注意要让SYSAUX有足够的空间剩余，不然会报错1658：
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
我们增大空间后再次处理：
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校准：
[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.初始化重演
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.设置负载重演的选项
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
[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会一直等待，我们需要另开一个窗口执行）
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.START_REPLAY ();
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
此时另一端wrc会出现：
[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.结束负载重演
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
3 END;
4 /
PL/SQL procedure successfully completed.
9.导出负载重演的AWR数据
（略）
四、分析重演的负载
（略）
检查测试库上，我们再检查一下是否10000行记录已经重演：
[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
[
本帖最后由 jimmyhe1981 于 2007-12-16 23:33 编辑 ]
