在11g中，很多功能oracle都引导用户通过OEM来操作，但喜欢用API的dba，照样可以写脚本来完成自己的工作，下面给大家介绍一下database replay的配置方法，database replay可以捕捉整个数据库的负载，并且传递到需要进行测试数据库上，然后重演负载以测试系统调优后的效果，当然也可以为升级硬件进行压力测试。

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
Sys@ORA11G> 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 进准备重演状态
Sys@ORA11G> 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 执行重演
Sys@ORA11G> BEGIN
2 DBMS_WORKLOAD_REPLAY.START_REPLAY ();
3 END;
4 /
PL/SQL procedure successfully completed.

J 结束重演
Sys@ORA11G> BEGIN
2 DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
3 END;
4 /

D 监控database replay的重演情况
select * from DBA_WORKLOAD_REPLAYS;