在11g中,很多功能oracle都引导用户通过OEM来操作,但喜欢用API的dba,照样可以写脚本来完成自己的工作,下面给大家介绍一下database replay的配置方法,database replay可以捕捉整个数据库的负载,并且传递到需要进行测试数据库上,然后重演负载以测试系统调优后的效果,当然也可以为升级硬件进行压力测试。
[@more@]
A 创建目录
create directory sqlplay as /home/oracle11g/worksh/sqlplay';
B 启动捕获进程
SQL> BEGIN
2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
3 name => 'capture_by_yekai',
4 dir => 'sqlplay',
5 duration => 600);
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-20222: Invalid DB State or Input. Input "sqlplay" is not a valid DIRECTORY object!
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 783
ORA-06512: at line 2
说明:这个地方dir参数必须是大写的,否则将会报错。
SQL> BEGIN
2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
3 name => 'capture_by_yekai',
4 dir => 'SQLPLAY',
5 duration => 600);
6 END;
7 /
PL/SQL procedure successfully completed.
C 如果在启动捕获进程时,没有指定duration的话,将必须调用finish_capture过程结束捕获workload.
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;
/
D 监控database replay的捕获情况
select * from DBA_WORKLOAD_CAPTURES;
E 处理捕获信息
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (
3 capture_dir => 'SQLPLAY');
4 END;
5 /
PL/SQL procedure successfully completed.
F 创建replay目录
create or replace directory sqlreplay as '/home/oracle/worksh/sqlplay'
说明:在初始化前,需要把捕获在sqlplay目录内的文件都拷贝到新的sqlreplay目录内
G 初始化replay
> BEGIN
2 DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
3 replay_name => 'replay_by_yekai',
4 replay_dir => 'SQLREPLAY');
5 END;
6 /
PL/SQL procedure successfully completed.
H 进准备重演状态
> BEGIN
2 DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
3 END;
4 /
PL/SQL procedure successfully completed.
I 启动database replay客户端
$wrc system/oracle mode=replay replaydir=./replay
I 执行重演
> BEGIN
2 DBMS_WORKLOAD_REPLAY.START_REPLAY ();
3 END;
4 /
PL/SQL procedure successfully completed.
J 结束重演
> BEGIN
2 DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
3 END;
4 /
D 监控database replay的重演情况
select * from DBA_WORKLOAD_REPLAYS;