Tuesday, January 30, 2024

Database Replay - Real Application Testing (RAT)


=> 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 


SQL> BEGIN
        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


SQL> 

col NAME           format a15
col ATTRIBUTE    format a10
col VALUE          format a10

SELECT NAME, ATTRIBUTE, VALUE FROM DBA_WORKLOAD_FILTERS;

NAME            ATTRIBUTE  VALUE
--------------- ---------- ----------
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>


SQL> BEGIN
        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 NAME        format a15
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> 


BEGIN
        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 


SELECT 
'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

--------------------------------------------------------------------------------


=> Notice that client worker process is started in other terminal window. Wait for replay to complete.


[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)
Replay client 1 started (05:17:13)
Replay client 1 finished (05:24:41)
[oracle@dba01~]$


=> 

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 


SQL> set pagesize 0 long 30000000 longchunksize 1000
SQL> VARIABLE v_rpt CLOB;
SQL> DECLARE
        v_cap_id NUMBER;
        v_rep_id NUMBER;
BEGIN
        v_cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(REPLAY_DIR=>'WORKLOAD_DIR');
        /* Get the latest replay */
        SELECT MAX(ID) INTO v_rep_id FROM DBA_WORKLOAD_REPLAYS WHERE CAPTURE_ID=v_cap_id;
        :v_rpt := DBMS_WORKLOAD_REPLAY.REPORT( REPLAY_ID => v_rep_id, FORMAT => DBMS_WORKLOAD_REPLAY.TYPE_HTML); 
end;
/   

PL/SQL procedure successfully completed.

SQL>

SQL> spool /home/oracle/reports//replay_report.html
SQL> print :v_rpt

.

.

    </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   ################