Replay using the DBMS_WORKLOAD_REPLAY Package
The DBMS_WORKLOAD_REPLAY package provides a set of procedures and functions to control the replay process. In order to replay the logs captured on the “ORCL” system, we need to transfers the capture files to our test system. Before we can do this, we need to create a directory on the “catdb” system to put them in. For simplicity we will keep the name the same.
mkdir /u01/app/oracle/paw_db_replay_capture
Transfer the files from the production server to the test server. Next, we create a directory object pointing to the new directory.
It is a good idea to adjust the test system time to match the time when the capture process was started. This way any time-based processing will react in the same way. For this test I have ignored this step.
We can now prepare to replay the existing capture logs using the PROCESS_CAPTURE, INITIALIZE_REPLAY and PREPARE_REPLAY procedures. I’ve named the replay with the same name as the capture process (paw_test_capture_1), but this is not necessary.
[oracle@TestServer cap]$ . oraenv
ORACLE_SID = [ORCL] ? catdb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@TestServer cap]$ sqlplus s/ as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 15:14:03 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 381685712 bytes
Database Buffers 239075328 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SQL> CREATE OR REPLACE DIRECTORY paw_db_replay_capture_dir AS ‘/u01/app/oracle/paw_db_replay_capture/’;
Directory created.
SQL>
BEGIN
DBMS_WORKLOAD_REPLAY.process_capture(‘PAW_DB_REPLAY_CAPTURE_DIR’);
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => ‘paw_test_capture_1_A’,
replay_dir => ‘PAW_DB_REPLAY_CAPTURE_DIR’);
DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/
PL/SQL procedure successfully completed.
Before we can start the replay, we need to calibrate and start a replay client using the “wrc” utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.
[oracle@TestServer cap]$ wrc mode=calibrate replaydir=/u01/app/oracle/paw_db_replay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Fri Feb 26 15:36:55 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Report for Workload in: /u01/app/oracle/paw_db_replay_capture
———————–
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 3
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.
[oracle@TestServer cap]$ wrc system/system@catdb mode=replay replaydir=/u01/app/oracle/paw_db_replay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Fri Feb 26 15:46:52 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (15:46:52)
The replay client pauses waiting for replay to start. We initiate replay with the following command.
SQL>
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
PL/SQL procedure successfully completed.
If you need to stop the replay before it is complete, call the CANCEL_REPLAY procedure.
The output from the replay client includes the start and finish time of the replay operation.
[oracle@TestServer cap]$ wrc system/system@catdb mode=replay replaydir=/u01/app/oracle/paw_db_replay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Fri Feb 26 15:46:52 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (15:46:52)
Replay started (15:47:42)
Replay finished (15:51:13)
Once complete, we can see the PAW_DB_REPLAY_TAB table has been created and populated in the PAW_DB_REPLAY_USER schema.
SQL> CONN sys/sys@catdb AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = ‘PAW_DB_REPLAY_USER’;
TABLE_NAME
——————————
PAW_DB_REPLAY_TAB
SQL> SELECT COUNT(*) FROM paw_db_replay_user.paw_db_replay_tab;
COUNT(*)
———-
500000
Information about the replay processing is available from the DBA_WORKLOAD_REPLAYS view.
SQL> COLUMN name FORMAT A30
SQL> SELECT id, name FROM dba_workload_replays;
ID NAME
————– ————————
2 paw_test_capture_1_A
1 row selected.
In addition, a report can be generated in text or HTML format using the REPORT function.
SQL>
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 2,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/
Thank you for Reading…This is Airy… Enjoy 🙂