oracle replay 功能实验

步骤： ======================================= 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