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