I have used RAT many times in the past and is no doubt the best tool to simulate Oracle database workloads, it empowers us, the DBAs, to make proactive changes while knowing exactly what to expect when a change is implemented and minimizes the likelihood of any negative repercussions.
Once again I’d got a chance to use it during a migration project where we moved to Multitenant 19.15 environment. Database Release 19c, you can capture and replay the workload from within an individual pluggable database (PDB). This enhancement enables you to capture and replay workloads at the PDB level. This leads to better testing, less downtime, and more effective and efficient change control. This was earlier not possible and was only limited to capture and replay multitenant databases at the root multitenant container database (CDB) level.
Todays post is to show how to do that using a short demo on one of my test build.
-- For testing purpose, writing a shell script to capture workload.
[oracle@oracleontario ~]$cat pdbtest_fatdba.sh
while sleep 1; do \
sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> &>/dev/null <<EOF
set head off
set pages 0
select count(*) from dixit.bigtab;
exit
EOF
done
-- Create a capture directory on the source database server. This is where all your capture specific files will reside.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:12 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Nov 19 2022 09:03:41 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> CREATE or replace DIRECTORY mycapture_dir AS '/ontadomain/home/capture_pdb_fatdbatest';
Directory created.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
-- Lets start the test, call the shell script which we prepared in the begining.
[oracle@oracleontario ~]$sh ./pdbtest_fatdba.sh&
[1] 2189
And now capture the workload, use DBMS_WORKLOAD_CAPTURE.start_capture to collect workload data. Once completed, copy all the files from source to target server where they will be replayed.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:41 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Nov 19 2022 09:05:39 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'PDB_666', dir => 'mycapture_dir', duration => 900);
END;
/
PL/SQL procedure successfully completed.
SQL> SQL> col name for a10
col status for a22
SELECT id, name, STATUS FROM dba_workload_captures WHERE name='PDB_666';
ID NAME STATUS
---------- ---------- --------------------
31 PDB_666 IN PROGRESS
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
PL/SQL procedure successfully completed.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
Next we have to create the replay directory structure, process captured files and initialzie the replay.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 1 01:20:18 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Nov 19 2022 09:06:37 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> CREATE or REPLACE DIRECTORY replaydir_fatdbatest1 AS '/ontadomain/home/replay_pdb_fatdbatest';
Directory created.
SQL>
-- Process all files that we have captured during the capture step.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.process_capture('replaydir_fatdbatest1');
END;
/
PL/SQL procedure successfully completed.
SQL>
-- Initialize the replay once above step of capture is sucessfully completed.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'REPLAY_FATDBA1', replaydir_fatdbatest1 => 'replaydir_fatdbatest1');
END;
/
PL/SQL procedure successfully completed.
SQL>
Next we have to remap connections, prepare the replay process and start the replay clients.
SQL> select conn_id,schedule_cap_id,capture_conn,replay_conn from dba_workload_connection_map;
CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN REPLAY_CONN
---------- --------------- -------------------------------------------------- --------------------------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERV
ICE_NAME=<PDB_SERVICE_NAME>)(CID=(PROGRAM=sqlplus)
(HOST=<HOSTNAME>)(USER=oracle)))(A
DDRESS=(PROTOCOL=TCP)(HOST=<IP_ADDRESS>)(PORT=<port>
)(HOSTNAME=<HOSTNAME>)))
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 1,
replay_connection => 'DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxx)(CID=(PROGRAM=sqlplus)(HOST=<HOSTNAME>)(USER=xxxxx)))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=xxxx)(HOSTNAME=xxxx)))');
END;
/
PL/SQL procedure successfully completed.
-- Prepare your replay.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY;
END;
/
PL/SQL procedure successfully completed.
SQL>
-- start the replay client.
[oracle@oracleontario ~]$wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE
Workload Replay Client: Release 19.15.0.0.0 - Production on Sun Mar 1 01:29:11 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (01:29:11)
Start the replay process on the target to fire the workload on the target and once completed generate the replay or any comparison reports.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
PL/SQL procedure successfully completed.
SQL>
-- Verify its status
SQL> COL NAME FOR A10
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = 'REPLAY_FATDBA1';
ID NAME STATUS
---------- --------------- ----------------------------------------
1 REPLAY_FATDBA1 IN PROGRESS
SQL>
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = 'REPLAY_FATDBA1';
ID NAME STATUS
---------- -------------- ----------------------------------------
1 REPLAY_FATDBA1 COMPLETED
SQL>
-- Now when its completed we can generate the REPLAY report or any other reportings like compare report etc.
SQL> set long 50000
set pages 50000
set lines 500SQL> SQL>
SQL> select dbms_workload_replay.report (replay_id => 1, format=> 'TEXT') from dual;
DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID=>1,FORMAT=>'TEXT')
--------------------------------------------------------------------------------
DB Replay Report for REPLAY_FATDBA1
--------------------------------------------------------------------------------
-------------------
--------------------------------------------------------------------------
| DB Name | DB Id | Release | RAC | Replay Name | Replay Status |
--------------------------------------------------------------------------
| FATDBA12 | 1218318131 | 19.15.0.0.0 | NO | REPLAY_FATDBA1 | COMPLETED |
--------------------------------------------------------------------------
... ...
..........
.............
......................
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 