|
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 编辑 ] |
|