创建 directory
上传到目标服务器（RAC各节点目录下数据都要传输）
scp -r /home/oracle/capdir/* 192.168.242.3:/home/oracle/replay/
创建directory
mkdir -p /home/oracle/replay
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/home/oracle/replay';
创建过滤器（可以不用）
创建方式：
exec DBMS_WORKLOAD_REPLAY.ADD_FILTER (fname IN VARCHAR2,fattribute IN VARCHAR2,fvalue IN VARCHAR2);
fattribute 可以使用如下：
·
USER
·
MODULE
·
ACTION
·
PROGRAM
·
SERVICE
·
CONNECTION_STRING
按用户创建
exec DBMS_WORKLOAD_REPLAY.ADD_FILTER ('replay_filter1','USER','TPCC');
创建了过滤器，该部分内容执行将被过滤掉，不执行。
加载 capture 日志
使用PROCESS_CAPTURE过程来准备capture logs。
exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE ('DB_REPLAY_CAPTURE_DIR');
执行完毕后，会生成wcr_process.wmd,wcr_login.pp, wcr_seq_data.extb, wcr_scn_order.extb , wcr_conn_data.extb等文件。
|
[oracle@oracle11g pp11.2.0.4.0]$ pwd /home/oracle/replay/pp11.2.0.4.0 [oracle@oracle11g pp11.2.0.4.0]$ ls -l total 1600 -rw-r--r--. 1 oracle oinstall 3522 Feb 25 13:14 wcr_calibrate.xml -rw-r-----. 1 oracle oinstall 12288 Feb 25 13:14 wcr_commits.extb -rw-r-----. 1 oracle oinstall 12288 Feb 25 13:14 wcr_conn_data.extb -rw-r-----. 1 oracle oinstall 12288 Feb 25 13:14 wcr_data.extb -rw-r-----. 1 oracle oinstall 757760 Feb 25 13:14 wcr_dep_graph.extb -rw-r--r--. 1 oracle oinstall 290 Feb 25 13:14 wcr_login.pp -rw-r--r--. 1 oracle oinstall 35 Feb 25 13:14 wcr_process.wmd -rw-r-----. 1 oracle oinstall 12288 Feb 25 13:14 wcr_references.extb -rw-r-----. 1 oracle oinstall 806912 Feb 25 13:14 wcr_scn_order.extb -rw-r-----. 1 oracle oinstall 12288 Feb 25 13:14 wcr_seq_data.extb |
初始化 replay
使用Initializing replay 装载metadata到tables里：
EXEC DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'replay', replay_dir => 'DB_REPLAY_CAPTURE_DIR');
将数据改成PREPARE REPLAY 模式：
exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
默认synchronization参数TRUE，按commit数据的顺序在数据库里重新执行一遍sql。
False ，不按commit顺序执行，跟实际会有数据分歧。
exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => FALSE);
检查replay的状态：
|
SQL> set lin 160 SQL> col name for a20 SQL> col status for a20 SQL> select name,status from dba_workload_replays; NAME STATUS --------------- ---------------------------------------- replay PREPARE |
启动重放客户端
评估客户端需求
wrc MODE=calibrate REPLAYDIR= =/home/oracle/replay
需要多少个客户端，执行多少次
wrc tpcc/tpcc mode=replay replaydir=/home/oracle/replay
可以后台执行
nohup wrc tpcc/tpcc mode=replay replaydir=/home/oracle/replay &
|
$ wrc system/oracle mode=replay replaydir=/home/oracle/replay Workload Replay Client: Release 11.2.0.4.0 - Production on Tue Feb 25 13:29:23 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (13:29:23)-- 等候replay执行 Replay client 1 started (13:30:46) Replay client 1 finished (13:34:39-- 完成replay执行 |
执行之后，replay client 被暂停，并等待start replay 。
执行 replay
执行时间跟生产库获取的时间有关
exec DBMS_WORKLOAD_REPLAY.START_REPLAY;
SQL> select name,status from dba_workload_replays;
|
SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY; PL/SQL procedure successfully completed. SQL> select id, name, status from dba_workload_replays; ID NAME STATUS ---------- --------------- ---------------------------------------- 11 replay COMPLETED |
初始状态：PREPARE
执行状态：IN PROGRESS
完成状态：COMPLETED
停止 replay
如果希望在完成replay 前stop replay过程，调用CANCEL_REPLAY 过程即可。
SQL> exec DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
获取 replay 报告
select id, name, status from dba_workload_replays;
SQL> set pagesize 0 long 30000000 longchunksize 2000
SQL> spool /home/oracle/replay_report.html
SQL> select dbms_workload_replay.report(replay_id => 11,format => 'HTML') from dual;
SQL> spool off
3.9 导入 capture awr 数据
导入Capture AWR data 到测试数据库，可以与replay AWR data做比较，参见
set serveroutput on
declare capid number := 0; BEGIN capid := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir =>' DB_REPLAY_CAPTURE_DIR '); dbms_output.put_line('capid is ' || capid); END; /
|
SQL> set serveroutput on declare capid number := 0; SQL> 2 3 BEGIN 4 capid := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir =>'DB_REPLAY_CAPTURE_DIR'); 5 dbms_output.put_line('capid is ' || capid); END; 6 7 / capid is 21 |
上面获取的为capture ID，通过capture ID可以获取掉awr导入到目标库后的虚dbid，查询如下：
|
-- 查询出导入awr的dbid SQL> select dbms_workload_capture.import_awr(capture_id => 21, staging_schema => 'SYSTEM') from dual; 1247930324 -- 查询出导入awr的snap id SQL> select snap_id,dbid,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=1247930324;
SNAP_ID DBID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ---------- ------------------------------ ------------------------------ 25 1247930324 2020-02-26T15:09:36Z 2020-02-26T15:13:07Z 26 1247930324 2020-02-26T15:13:07Z 2020-02-26T15:15:26Z |
获取 capture 信息
SQL> select id, awr_begin_snap, awr_end_snap from dba_workload_captures;
4 10 11
获取 replay 信息
SQL> select id, awr_begin_snap, awr_end_snap from dba_workload_replays;
11 149 150
比较 capture 和 replay
|
set serveroutput on spool /home/oracle/compare_period_report.html declare v_rlt clob; v_replay_id1 number:=61; v_replay_id2 number:= null; v_snum number:=1; v_length number; v_char varchar2(32767); begin dbms_workload_replay.compare_period_report(replay_id1=>v_replay_id1, replay_id2=>v_replay_id2,format=>'HTML',result=>v_rlt); v_length:=dbms_lob.GETLENGTH(v_rlt); while ( v_snum < v_length ) loop v_char:=dbms_lob.substr(lob_loc=>v_rlt,amount=>32767,offset=>v_snum); v_snum:=v_snum+32767; dbms_output.put_line(v_char); end loop; end; / spool off |
