DBA things that are hard to remember but regret to forget: Oracle Real Application Testing without EM

For some reasons (slow network, console access only etc) you may want to run 'Database Replay' on systems without GUI. There are a lot of docs and blogs explaining this proccess with EM\EM Grid Control. But there is very very few instructions about using packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY that explains it clear.
First of all if capture and replay on different DB versions, check MOS Real Application Testing Now Available for Earlier Releases [ID 560977.1]. Keep in mind that Replay can only be done on Oracle Database 11g and higher. For pre 11g - you must set pre_11g_enable_capture=true. If CAPTURE database < 10.2.0.4 - RAT functionality (Capture) must be installed via Opatch. My test capture & replay both 11.2.0.3 on OEL 5.6.

1. It's best practice to gather system stats before capture, if it's empty (for performance sensitive systems - use interval attribute, for gathering some minutes), and restart database to commit\rollback current transactions:

SQL> exec dbms_stats.gather_system_stats;

PL/SQL procedure successfully completed.

You may check stats in SYS.AUX_STATS$

2. Make full backup for further restoring at capture starting SCN:

RMAN> backup as compressed backupset database plus archivelog;

3. Create a Directory to store the captured load.

Make sure - there is enough space (of course size depends on workload type). In my last expirience there is ~ 2-3gb\per hour.

SQL> create or replace directory CAPTURE as '/home/rat/capture';

Directory created.

SQL> grant read , write on directory CAPTURE to public;

Grant succeeded.

4.* You can add filter to exclude some sessions\users\programs using dbms_workload_capture.add_filter, for example RMAN,agent, etc sessions:

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'GC_AGENT',
fattribute => 'USER',
fvalue => 'DBSNMP');
END;
/
PL/SQL procedure successfully completed.

5. Start the Capture process during 600second.(you can run it without duration argument until you executes dbms_workload_capture.finish_capture)

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'RAT Test Capture',
dir => 'CAPTURE',
duration => 600);
END;
/
PL/SQL procedure successfully completed.

Or stop capture without duration argument:

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;
END;
/

6.Check capture status, get capture_id :

SQL> select id,name,directory,status from dba_workload_captures;

7.Export AWR Data for Workload Capture (need to detailed comparison AWR's):

SQL> BEGIN

DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 3);

END;

/

PL/SQL procedure successfully completed.

8. Copy all CAPTURE dir from CAPTURE server to REPLAY server Replay :

SQL> create or replace directory REPLAY as '/home/rat/replay';

SQL> grant read , write on directory REPLAY to public;

9. Preprocess captured workload (creation metadata on capture structure for replaying). It's very heavy procedure, so don't run it on production system. Once you have preprocessed - capture can be replayed repeatedly on a replay systems.

SQL> BEGIN
dbms_workload_replay.process_capture(capture_dir=>'REPLAY');
END;
/

To monitor preprocessed you can use:

SQL> select sum(bytes_done/1024/1024) from wrr$_processing_progress;

10. Restore REPLAY database to SCN when capture has been done. Script may differ, but this one just example(scn# to which you should recover database you can find in dba_workload_captures):

RMAN> run {
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '/backup/backup_of_ctrl_file';
ALTER DATABASE MOUNT;
CATALOG START WITH '/backup/' NOPROMPT;
RESTORE DATABASE;
RECOVER DATABASE UNTIL SCN scn#;
ALTER DATABASE OPEN RESETLOGS;
}

11. Calibrate clients. Estimate number of replay clients depends on capture and replaying host(s), but default recomedations are :

- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

#wrc mode=calibrate replaydir=$REPLAY_PATH

12. Prepare the replay database:

SQL> BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
replay_name => 'RAT_REPLAY',
replay_dir => 'RAT');
DBMS_WORKLOAD_REPLAY.prepare_replay (
synchronization => TRUE);
END;
/
PL/SQL procedure successfully completed.

13. Roll back system time on REPLAY host on exact time when the CAPTURE was planned, for time-sensitive code could work properly. Then, execute the wrc utility to connect to all clients to database:

SQL> create user rat_user identified by *** default tablespace USERS temporary tablespace TEMP;
user created.
SQL> grant dba, all privileges to rat_user;
grant succeeded.

You may want to use wrc in nohup mode (UNIX only). After this -wait until all wrc client connections approved:

#nohup wrc rat_user/*** mode=replay replaydir=/home/rat/replay &
Wait for the replay to start (13:12:10)

14.Start replay:

SQL> BEGIN

DBMS_WORKLOAD_REPLAY.start_replay;

END;

/

PL/SQL procedure successfully completed.

Wait for the replay to start (13:12:10)

Replay started (13:15:46)

Replay finished (13:25:46)

15.Analyze results:

15.1. Check capture and replay reports(this code is just an example)
Capture:

SQL> DECLARE
cap_id NUMBER;
cap_rpt CLOB;
BEGIN
dbms_output.enable(1000000);
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'REPLAY');
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => 1,
format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);
dbms_output.put_line (cap_rpt);
END;
/

Replay:

SQL> DECLARE
repl_rpt CLOB;
BEGIN
dbms_output.enable(1000000);
repl_rpt := DBMS_WORKLOAD_REPLAY.report(replay_id => 1, format =>
DBMS_WORKLOAD_REPLAY.TYPE_TEXT);
dbms_output.put_line (repl_rpt);
END;
/

15.2 Generate AWR and(or) ASH reports:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR compare period:

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

ASH report:

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

About the way - there are another types of reports availible:

awrrpt.sql -- basic AWR report
awrsqrpt.sql -- Standard SQL statement Report
awrddrpt.sql -- Period diff on current instance
awrrpti.sql -- Workload Repository Report Instance (RAC)
awrgrpt.sql -- AWR Global Report (RAC)
awrgdrpt.sql -- AWR Global Diff Report (RAC)
awrblmig.sql -- AWR Baseline Migrate
awrload.sql -- AWR LOAD: load awr from dump file
awrextr.sql -- AWR Extract
awrddinp.sql -- Get inputs for diff report
awrddrpi.sql -- Workload Repository Compare Periods Report
awrgdinp.sql -- Get inputs for global diff reports
awrgdrpi.sql -- Workload Repository Global Compare Periods Report
awrginp.sql -- AWR Global Input
awrgrpti.sql -- Workload Repository RAC (Global) Report
awrinpnm.sql -- AWR INput NaMe
awrinput.sql -- Get inputs for AWR report
awrsqrpi.sql -- Workload Repository SQL Report Instance

DBA things that are hard to remember but regret to forget

Oracle Real Application Testing without EM