在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;