步骤：
=======================================
Capture Server: 源端
=======================================
--创建目录来存放workload trace文件.
sysdba连接
create or replace directory DB_REPLAY as '/home/oracle/dbreplay';
--启动capture
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1',
dir =>'DB_REPLAY');
END;
/
先创建用户(源端和目标端均要创建)：
create user scott identified by sysdba;
grant CONNECT, RESOURCE to scott;
grant CREATE SESSION, ALTER SESSION to scott;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to scott;
grant ALTER ANY TABLE to scott;
grant FLASHBACK ANY TABLE to scott;
grant EXECUTE on DBMS_FLASHBACK to scott;
--在另一窗口使用业务用户连接到数据库，执行如下操作
CREATE TABLE DB_REPLAY_TEST_TAB3 (
id NUMBER,
description VARCHAR2(50));
CREATE TABLE DB_REPLAY_TEST_TAB (
id NUMBER,
description VARCHAR2(50));
BEGIN
FOR i IN 1 .. 20000 LOOP
INSERT INTO DB_REPLAY_TEST_TAB (id, description)
VALUES (i, 'Description for' || i);
END LOOP;
COMMIT;
END;
/
BEGIN
FOR i IN 1 .. 20000 LOOP
update DB_REPLAY_TEST_TAB set description='Description for' || i ||'test' where id=i;
COMMIT;
END LOOP;
COMMIT;
END;
/
select count(*) from DB_REPLAY_TEST_TAB3;
select count(*) from DB_REPLAY_TEST_TAB3;
select count(*) from DB_REPLAY_TEST_TAB3;
select count(*) from DB_REPLAY_TEST_TAB3;
（可选）可以在sysdba的窗口，查看捕获进度
col name format a10
col status format a20
col sqlset_name format a25
set linesize 120
select id,name,status,duration_secs,awr_begin_snap,awr_end_snap,user_calls from dba_workload_captures;
--结束捕获，可选，因为我们捕获的时候已经指定了duration 600秒
begin
dbms_workload_capture.finish_capture(timeout => 30, reason => null);
end;
/
以上，捕获过程已经结束
=======================================
Replay Server: 目标端执行
=======================================
--将Source Database的/home/oracle/dbreplay目录中所有的文件全部复制到Replay Server主机，
--为了方便起见，Replay Server主机的目录也为/home/oracle/dbreplay
sysdba连接
create or replace directory DB_REPLAY as '/home/oracle/dbreplay';
--处理捕获的内容
begin
dbms_workload_replay.process_capture(capture_dir => 'DB_REPLAY',
parallel_level => null);
end;
/
--初始化replay
begin
dbms_workload_replay.initialize_replay(replay_name => 'replay_1',
replay_dir => 'DB_REPLAY');
end;
/
----（可选）可以确认replay的状态
col name format a30
set linesize 120
select id,name,status from dba_workload_replays;
ID NAME STATUS
---------- ------------------------------ ----------------------------------------
61 replay_1 INITIALIZED
--查看connection map
col replay_conn format a15
select replay_id,conn_id,capture_conn,replay_conn from dba_workload_connection_map;
REPLAY_ID CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN REPLAY_CONN
61 1 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.208.40)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=R10205)(CID=(PROGRAM=sqlplus@nascds5)(HOST=nascds5)(USER=oracle))))
查看自己的replay_id 对应几个conn_id ,全部更改，按照下面的格式修改HOST=？,server_name=?
? 代表目标端自身的信息
-----修改 《〈〈此处有两个也要改两个
begin
dbms_workload_replay.remap_connection(--capture_number =>,
connection_id => 1,
replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=KING1PDB)))');
end;
/
begin
dbms_workload_replay.remap_connection(--capture_number =>,
connection_id => 2,
replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=KING1PDB)))');
end;
/
begin
dbms_workload_replay.remap_connection(--capture_number =>,
connection_id => 3,
replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=KING1PDB)))');
end;
/
上面是 自己试验的修改方式
---确认
col replay_conn format a15
select replay_id,conn_id,capture_conn,replay_conn from dba_workload_connection_map;
REPLAY_ID CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN REPLAY_CONN
61 1 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.208.40)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=R10205)(CID=(PROGRAM=sqlplus@nascds5)(HOST=nascds5)(USER=oracle))))
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.208.40)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=R11204)))
--准备replay
--将数据改成PREPARE REPLAY 模式：
SQL>exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => FALSE);
--发起模拟连接 如果是ＰＤＢ这里使用ｓｙｓｔｅｍ用户 （在另外的会话执行 ）
wrc system/sysdba@CDB$ROOT mode=replay replaydir=/home/oracle/dbreplay
如果上面报错，执行下面的语句
wrc system/sysdba mode=replay replaydir=/home/oracle/dbreplay CONNECTION_OVERRIDE=TRUE
--开始replay
begin
dbms_workload_replay.start_replay;
end;
/
生成replay 报告：
set echo off head off feedback off linesize 200 pagesize 1000
set long 1000000 longchunksize 10000000
VARIABLE rep_id number;
BEGIN
SELECT max(id) INTO :rep_id FROM dba_workload_replays;
END;
/
spool /home/oracle/replay_report_single_pdb.html
select dbms_workload_replay.report( :rep_id, 'HTML') from dual;
spool off
--其他参考补充
-- 查询dba_workload_replays
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_replays;
--删除重放信息
exec dbms_workload_replay.delete_replay_info(&i);
-- 查询dba_workload_captures
select id, name from dba_workload_captures;
-- 删除捕获信息
exec dbms_workload_capture.delete_capture_info(&i);
输入上面查询dba_workload_captures的id值进行删除。
参考文档：
How to Setup and Run a Database Testing Replay in an Oracle Multitenant Environment (Real Application Testing - RAT) ( Doc ID 1937920.1 )
Using Real Application Testing Functionality in Earlier Releases ( Doc ID 560977.1 )
set echo off head off feedback off linesize 200 pagesize 1000
set long 1000000 longchunksize 10000000
VARIABLE rep_id number;
BEGIN
SELECT max(id) INTO :rep_id FROM dba_workload_replays;
END;
/
spool /home/oracle/replay_report_single_pdb.html
select dbms_workload_replay.report( :rep_id, 'HTML') from dual;
spool off
alter session set container=KING1PDB;
grant become user to scott container=all;
wrc system/sysdba mode=replay replaydir=/home/oracle/dbreplay