How to Use Oracle Real Application Testing Database Replay for a Safe 11g‑to‑19c Migration

The customer’s core system ran on an IBM AIX server with Oracle 11g R2 for nearly a decade and needed to migrate to an x86 platform with Oracle 19c. Simple functional tests were insufficient, so the team employed Oracle Real Application Testing (RAT) Database Replay to simulate real‑world load and uncover migration, compatibility, and performance issues.

Background

RAT, introduced in Oracle 11g R2, provides tools to measure the impact of hardware upgrades, software upgrades, and architectural changes on production workloads. It offers two main features: Database Replay and SQL Performance Analyzer (SPA). This article focuses on Database Replay.

Test Environment

Source host: IBM 880, AIX, Oracle 11.2.0.4.16, RAC

Target host: domestic x86 server, Red Hat 7, Oracle 19.7, RAC

RAT Testing Process

1. Capture (Recording)

Prepare directories : create an NFS shared directory on both RAC nodes, e.g., mkdir -p /datadump/rat_test, then create an Oracle DIRECTORY object and grant permissions.

SQL> create directory DBRAT_0218 as '/home/oracle/rat_test';
SQL> grant read,write on directory DBRAT_0218 to public;

Set capture filter (optional) to limit workload, e.g., capture only user NGCRM_ST:

exec dbms_workload_capture.ADD_FILTER(fname=>'FILTER_SYS',fattribute=>'USER',fvalue=>'NGCRM_ST');

Start capture for a defined period (7200 s = 2 h):

BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture(name=>'DBREPLAY_0218', dir=>'DBRAT_0218', duration=>7200);
END;/

Monitor capture status :

select id,name,status,start_time,end_time,connects,user_calls,dir_path from dba_workload_captures;

Issue encountered: capture sometimes stopped after 1–2 minutes despite a duration set, likely due to shared‑directory problems. Switching to a different NFS directory resolved it.

2. Load Target Database

Export relevant schemas from the source with Data Pump and import them into the target. Detailed steps are omitted for brevity.

3. Preprocess Captured Workload

Create a DIRECTORY on the target, grant rights, then run:

SQL> create directory DBRAT_0218 as '/ACFS01/rat_0218';
SQL> grant read,write on directory DBRAT_0218 to public;
SQL> exec dbms_workload_replay.process_capture('DBRAT_0218');

Problem: preprocessing hung and reported corrupt *.rec files. Oracle MOS suggested applying one‑off patches 21117072 (target) and 17373277 (source) to fix the bug.

4. Initialize Replay

Initialize with the capture name and directory:

exec dbms_workload_replay.initialize_replay('DBREPLAY_0218','DBRAT_0218');

5. Connection Mapping

Map source connection IDs to target connection strings, e.g.:

exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id=>1, replay_connection=>'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxx)))');

6. Prepare Replay

Run with default scaling (100 %):

exec dbms_workload_replay.prepare_replay(synchronization=>FALSE);

To increase pressure, adjust connect_time_scale and think_time_scale (e.g., 50 % to halve intervals).

7. Execute Replay

Determine required client count (e.g., at least 30 clients for 8 CPUs) using wrc mode=calibrate replaydir=/ACFS01/rat_0218 . Launch clients, for example:

for ((i=1;i<=30;i++))
do
nohup wrc system/oracle@NGCRM_ST mode=replay replaydir=/ACFS01/rat_0218 > /ACFS01/wrc_log/wrc_$i.log &
done

Start replay: exec dbms_workload_replay.start_replay; Control replay with: DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY(); DBMS_WORKLOAD_REPLAY.RESUME_REPLAY(); DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();

8. Monitoring and Reporting

Query divergence calls via DBA_WORKLOAD_REPLAY_DIVERGENCE or GET_DIVERGENT_STATEMENT . Generate HTML report: select dbms_workload_replay.report(139,'HTML') from dual; Additional useful views: DBA_WORKLOAD_CAPTURES, DBA_WORKLOAD_FILTERS, DBA_WORKLOAD_REPLAYS, DBA_WORKLOAD_REPLAY_DIVERGENCE, DBA_WORKLOAD_CONNECTION_MAP, V$WORKLOAD_REPLAY_THREAD.

Conclusion

The Oracle 11g‑to‑19c migration spanned several months and involved multiple test cycles. Using RAT Database Replay early uncovered issues in parameters, schema changes, and SQL code, allowing timely remediation before the final cut‑over.