=> Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system.
=> Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload.
=> Below are high level steps to perform Real Application Testing
. Capture Database Workload in Production
. Set Up the Test Environment
. Process the Captured Database Workload
. Replay the Captured Database Workload
. Generate and Analyze the Replay Report
=> Capture Database Workload
=> Create a directory to store workload files
[oracle@dba01 ~]$ mkdir /home/oracle/workload
[oracle@dba01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 30 09:08:16 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE DIRECTORY WORKLOAD_DIR AS '/home/oracle/workload';
Directory created.
SQL>
=> Create a filter to capture changes under schema HR
DBMS_WORKLOAD_CAPTURE.ADD_FILTER
(
FNAME => 'INCLUDE_HR',
FATTRIBUTE => 'USER',
FVALUE => 'HR'
);
END;
/
PL/SQL procedure successfully completed.
SQL>
=> Verify if the filter is created
col ATTRIBUTE format a10
col VALUE format a10
SELECT NAME, ATTRIBUTE, VALUE FROM DBA_WORKLOAD_FILTERS;
--------------- ---------- ----------
INCLUDE_HR USER HR
=> Start the database is restricted mode and start the replay process. Replay will convert the database to unrestricted mode
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 1744830464 bytes
Fixed Size 8621616 bytes
Variable Size 536871376 bytes
Database Buffers 1191182336 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL>
DBMS_WORKLOAD_CAPTURE.START_CAPTURE
(
NAME => 'HR_CAPTURE',
DIR => 'WORKLOAD_DIR',
DEFAULT_ACTION => 'EXCLUDE',
DURATION => 300
);
END;
/
PL/SQL procedure successfully completed.
=> Monitor the capture process
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
col ID format 99
col STATUS format a10
SELECT
ID,
NAME,
STATUS,
START_TIME,
END_TIME,
CONNECTS,
USER_CALLS
FROM
DBA_WORKLOAD_CAPTURES
WHERE
ID = (SELECT MAX(ID) FROM DBA_WORKLOAD_CAPTURES);
/
ID NAME STATUS START_TIME END_TIME CONNECTS USER_CALLS
--- --------------- -------------------- ------------------ ------------------ ---------- ----------
1 HR_CAPTURE IN PROGRESS 30-JAN-24 05:17:10 30-JAN-24 05:22:10 11 141524
SQL> /
ID NAME STATUS START_TIME END_TIME CONNECTS USER_CALLS
--- --------------- -------------------- ------------------ ------------------ ---------- ----------
1 HR_CAPTURE COMPLETED 30-JAN-24 05:17:10 30-JAN-24 05:22:10 11 175862
SQL>
=> Extract the capture report
SQL> set pagesize 0 long 30000000 longchunksize 1000 linesize 200
SQL> spool /home/oracle/reports/capture.html
SQL> SELECT DBMS_WORKLOAD_CAPTURE.REPORT(&Enter_ID,'HTML') FROM DUAL;
Enter value for enter_id: 1
.
.
End of Report
</body></html>
SQL> spool off
=> Edit the html report and remove unnecessary lines in the beginning and at end of the file
vi /home/oracle/reports/capture.html
=> Verify the capture files
ls -alh /home/oracle/workload/capfiles/inst1/aa
total 56M
drwxr-xr-x. 2 oracle oinstall 4.0K Jan 30 09:17 .
drwxr-xr-x. 12 oracle oinstall 4.0K Jan 30 09:17 ..
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000000.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000001.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000002.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000003.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000004.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000005.rec
-rw-r--r--. 1 oracle oinstall 5.7M Jan 30 09:22 wcr_bj1kvh0000006.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000007.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000008.rec
-rw-r--r--. 1 oracle oinstall 5.6M Jan 30 09:22 wcr_bj1kvh0000009.rec
On Test Instance :
=> Perform the change like database upgrade or OS or hardware changes
=> Transfer the capture directory to TEST instance and create a directory object in test database
=> Pre-process the replay and verify pre-process files
SQL> exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('WORKLOAD_DIR')
PL/SQL procedure successfully completed.
ls -altr /home/oracle/workload
total 20
drwxr-xr-x. 3 oracle oinstall 4096 Jan 30 09:17 capfiles
drwxr-xr-x. 2 oracle oinstall 4096 Jan 30 09:22 cap
drwx------. 25 oracle oinstall 4096 Jan 30 09:37 ..
drwxr-xr-x. 5 oracle oinstall 4096 Jan 30 09:42 .
drwxr-xr-x. 3 oracle oinstall 4096 Jan 30 09:42 pp12.2.0.1.0
=> Initialize the replay
SQL>
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY
(
REPLAY_NAME =>'HR_CAPTURE',
REPLAY_DIR =>'WORKLOAD_DIR'
);
END;
/
PL/SQL procedure successfully completed.
SQL>
=> Verify and update connection strings used in the replay capture
SQL> SELECT REPLAY_ID, CONN_ID, CAPTURE_CONN, REPLAY_CONN FROM DBA_WORKLOAD_CONNECTION_MAP ORDER BY 1,2;
SQL> exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION ( CONNECTION_ID =>1, REPLAY_CONNECTION => 'dba01:1521/ORADB' )
=> Prepare the replay
SQL> exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(SYNCHRONIZATION => 'SCN', CONNECT_TIME_SCALE=>100, THINK_TIME_SCALE => 100, THINK_TIME_AUTO_CORRECT => TRUE)
=> set the time of database server to the start the time of replay capture to replay time dependent transactions on test database
=> Run workload capture client process (wrc) in calibrate mode to identify the number of client process required to start to perform replay
[oracle@dba01 ~]$ wrc replaydir='/home/oracle/workload' mode=calibrate
Workload Replay Client: Release 12.2.0.1.0 - Production on Tue Jan 30 09:45:43 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Report for Workload in: /home/oracle/workload
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 37 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 10 sessions
- total number of sessions: 10
Assumptions:
- 1 client process per 100 concurrent sessions
- 4 client processes per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
[oracle@dba01 ~]$
=> start the replay client process a separate terminal
[oracle@dba01 ~]$ date
Tue Jan 30 05:16:31 +04 2024
[oracle@dba01 ~]$ wrc system/oracle@ORADB replaydir=/home/oracle/workload
Workload Replay Client: Release 12.2.0.1.0 - Production on Tue Jan 30 05:16:42 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (05:16:42)
=> start the replay process
SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY()
PL/SQL procedure successfully completed.
SQL> Monitor the replay
'ID: ' || ID || CHR(10) ||
'CAPTURE_ID: ' || CAPTURE_ID || CHR(10) ||
'STATUS: ' || STATUS || CHR(10) ||
'PREPARE_TIME: ' || PREPARE_TIME || CHR(10) ||
'START_TIME: ' || START_TIME || CHR(10) ||
'END_TIME: ' || END_TIME || CHR(10) ||
'DURATION_SECS: ' || DURATION_SECS || CHR(10) ||
'NUM_CLIENTS: ' || NUM_CLIENTS || CHR(10) ||
'NUM_CLIENTS_DONE: ' || NUM_CLIENTS_DONE || CHR(10) ||
'USER_CALLS: ' || USER_CALLS || CHR(10) ||
'DBTIME: ' || DBTIME || CHR(10) ||
'NETWORK_TIME: ' || NETWORK_TIME || CHR(10) ||
'THINK_TIME: ' || THINK_TIME || CHR(10) ||
'PLSQL_CALLS: ' || PLSQL_CALLS || CHR(10) ||
'PLSQL_SUBCALLS: ' || PLSQL_SUBCALLS || CHR(10) ||
'PLSQL_DBTIME: ' || PLSQL_DBTIME || CHR(10) ||
'ELAPSED_TIME_DIFF: ' || ELAPSED_TIME_DIFF || CHR(10) ||
'AWR_BEGIN_SNAP: ' || AWR_BEGIN_SNAP || CHR(10) ||
'AWR_END_SNAP: ' || AWR_END_SNAP || CHR(10) ||
'AWR_EXPORTED: ' || AWR_EXPORTED || CHR(10) ||
'ERROR_CODE: ' || ERROR_CODE || CHR(10) ||
'ERROR_MESSAGE: ' || ERROR_MESSAGE INFO
FROM
DBA_WORKLOAD_REPLAYS
ORDER BY
ID;
INFO
--------------------------------------------------------------------------------
ID: 1
CAPTURE_ID: 1
STATUS: COMPLETED
PREPARE_TIME: 30-JAN-24 05:45:14
START_TIME: 30-JAN-24 01:17:13
END_TIME: 30-JAN-24 01:21:58
DURATION_SECS: 285
NUM_CLIENTS: 1
NUM_CLIENTS_DONE: 1
USER_CALLS: 175862
DBTIME: 326189435
INFO
--------------------------------------------------------------------------------
NETWORK_TIME: 14250826
THINK_TIME: 48064736
PLSQL_CALLS: 58629
PLSQL_SUBCALLS: 0
PLSQL_DBTIME: 184310890
ELAPSED_TIME_DIFF: 114287558
AWR_BEGIN_SNAP: 5
AWR_END_SNAP: 6
AWR_EXPORTED: YES
ERROR_CODE:
ERROR_MESSAGE:
INFO
--------------------------------------------------------------------------------
=> Generate replay report
.
.
</body>
</html>
SQL> spool off
=> Edit the file and remove unnecessary lines at beginning and at the end of the file
vi /home/oracle/reports//replay_report.html
=> Generate and review the AWR report between snap id's during replay
SQL> SELECT ID, AWR_BEGIN_SNAP, AWR_END_SNAP FROM DBA_WORKLOAD_REPLAYS ORDER BY ID;
=> You can create Guaranteed Restore Point on target database before starting replay to perform multiple iterations of replay after fixing issues
################ END ################