1. Introduction
Real application testing is a powerful features of Oracle which has been introduced in 11g first and then it was back ported to other version including oracle 9i . In our Eaga specific scenario we have oracle 9.2.0.7 EBS database and we have 11g database with OEM grid cntrol . The real advantage of OEM is to generate report from GUI easily .
Oracle has provided patch for older version 9.2.0.7 the patch number is :- p8542772_92070_SOLARIS64.zip , which needs to apply first in your 9.2.0.7 database to enable RAT data captuing feature .
2. Pre-Requisite :-
RAT feature does not work /capture correct data if there is no active user session in your source (9.2.0.7) database . Apply the oracle supplied patch p8542772_92070_SOLARIS64.zip .
3. Real Application Testing Implementation
Download this patch p8542772_92070_SOLARIS64.zip from metalink.oracle.com
Trasfer this patch to any temporary directory using ftp or winscp . copied this in /ebstst4_bin/db_u01/oracle/patch
$ ls -ltr
total 3720
-rw-r–r– 1 root dba 1899202 Mar 16 04:43 p8542772_92070_SOLARIS64.zip
Extract this patch using below command
Unzip p8542772_92070_SOLARIS64.zip
Read readme.txt of the patch . In case of database EBS (9.2..0.7) There was no need of any pre-req patch.
Shutdown your application services and database .
Login in ebstst4forms as dbaz
Cd /
./ stop_ebstst4
Login in ebstst4db as dbaz
Cd /
./ stop_ebstst4
Apply the patch in EBSTSTS4 (9.2.0.7)
Login as oracle in ebstst4db
. EBSTST4.env (Set the env )
Cd /ebstst4_bin/db_u01/oracle/patch/8542772
$ORACLE_HOME/OPatch/opatch apply (It will take time )
After successful apply of the patch ,do the post patch task as per readme.txt
Startup the database and listener
lsnrctl start EBSTST4
Sqlplus “/as sysdba”
Startup
SQL> @?/rdbms/admin/catwrr.sql
4. Start Data Capturing Process .
Our main target is to capture the data of database EBSTST4 (9.2.0.7)
First figure out/create the direcotry where you want your capture data to store .
mkdir –p /ebstst4_bin/db_u01/oracle/RAT
Connect sqlplus in as sysdba to create directory .
SQL> create or replace directory ebstst4_rat_sample as ‘/ebstst4_bin/db_u01/oracle/RAT’;
Directory created.
SQL>
Check weather directory created or not from sqlplus :-
select directory_name, directory_path
from dba_directories
order by directory_name;
Now you create filters at user /schema level ,so that you can capture the data for specific user . Even you can capture the data for all the users .
In my case I have figured out the user EAGA_GAPS to filter :-
Exec dbms_workload_capture.add_filter(‘sample_test_filter2’, ‘USER’, ‘EAGA_GAPS’);
— To see what filters are in place
/*
select type, name, attribute, status, value from dba_workload_filters;
*/
So I want to capture data for eaga_gaps
Connect sqlplus as sysdba
Sqlplus “/as sysdba”
SQL> exec dbms_workload_capture.start_capture(‘sample_test_rat’, ‘EBSTST4_RAT_SAMPLE’, NULL, ‘EXCLUDE’);
— If you want to capture ALL workload except user ” EAGA_GAPS”
/*
exec dbms_workload_capture.start_capture(‘sample_cap_name’, ‘sample_cap_dirobj’, NULL, ‘INCLUDE’);
*/
Check status of the capture
/*
select name, directory, status, start_time, end_time, duration_secs, errors
from dba_workload_captures;
*/
Finish the capturing of the data using the below command
exec dbms_workload_capture.finish_capture;
Check the status of the captuing
/*
select name, directory, status, start_time, end_time, duration_secs, errors
from dba_workload_captures;
*/
Data captured
$ cd /ebstst4_bin/db_u01/oracle/RAT
$ ls -ltr
total 3456
-rw-rw-r– 1 oracle dba 143 Mar 12 10:53 wcr_scapture.wmd
-rw-rw-r– 1 oracle dba 993 Mar 12 10:53 wcr_4r6hx70002rfm.rec
-rw-rw-r– 1 oracle dba 817479 Mar 12 11:45 wcr_4r6hxf4002rft.rec
-rw-rw-r– 1 oracle dba 903684 Mar 12 11:45 wcr_4r6hx58002rfd.rec
-rw-rw-r– 1 oracle dba 1002 Mar 12 11:48 wcr_4r6ja04002rgr.rec
-rw-rw-r– 1 oracle dba 898 Mar 12 11:52 wcr_4r6jaqn002rgv.rec
-rw-rw-r– 1 oracle dba 898 Mar 14 20:12 wcr_4r7a30w002tsh.rec
-rw-rw-r– 1 oracle dba 1002 Mar 15 04:18 wcr_4r7dnw8002u5r.rec
-rw-rw-r– 1 oracle dba 208 Mar 15 04:19 wcr_fcapture.wmd
-rw-rw-r– 1 oracle dba 4074 Mar 15 04:19 wcr_cr.html
-rw-rw-r– 1 oracle dba 1544 Mar 15 04:19 wcr_cr.text
Now its time to replay the capture in your 11g database , which has got all the features to replay .
Login to sunmanage2 as dbaz
Su – oracle
. OEMTEST.env
Create the replay directory
mkdir –p /oemtest_bin/db/oracle/RAT
Transfer this captured data from ebstst4db to sunmanage2 using ftp to your newly created directory .
/oemtest_bin/db/oracle/RAT
Create directory object in 11g OEMTEST database .
Connect sqlplus as sysdba
Sqlplus “/as sysdba”
create or replace directory “sample_rep_dirobj” as ‘/ebstst4_bin/db_u01/oracle/RAT’
start the capturing process ********
exec dbms_workload_replay.process_capture(‘sample_rep_dirobj’);
Initialize replay ************
exec dbms_workload_replay.initialize_replay(‘sample_rep_name’, ‘sample_rep_dirobj’);
Prepare replay: put DB state into REPLAY mode
— You could specify:
— synchronization – whether or not commit order is preserved
— connect_time_scale – scales the time elapsed between the start of
— the replay and the start of each session
— think_time_scale – scales the time elapsed between two
— successive user calls from the same session
— think_time_auto_correct – Auto corrects the think time between calls
— when user calls takes longer during the
— replay than during the capture
—
exec dbms_workload_replay.prepare_replay(synchronization => TRUE, connect_time_scale => 100, think_time_scale => 100, think_time_auto_correct => FALSE);
exit from the sqlplus
exit
Its time to play from command prompt Use this below command to calibrate
wrc system/password mode=calibrate replaydir=/oemtest_bin/db/oracle/RAT
Start the reply Use this below command
wrc system/password mode=calibrate replaydir=/oemtest_bin/db/oracle/RAT
At the same time take another terminal connect sqlplus as sysdba in 11g database
Sqlplus “/as sysdba”
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
After replay process you can check the status of the file in your file system
Cd /ebstst4_bin/db_u01/oracle/RAT
sunmanage2@oracle# ls -ltr
total 39592
-rw-r–r– 1 oracle dba 903684 Mar 15 06:16 wcr_4r6hx58002rfd.rec
-rw-r–r– 1 oracle dba 993 Mar 15 06:16 wcr_4r6hx70002rfm.rec
-rw-r–r– 1 oracle dba 817479 Mar 15 06:16 wcr_4r6hxf4002rft.rec
-rw-r–r– 1 oracle dba 1002 Mar 15 06:16 wcr_4r6ja04002rgr.rec
-rw-r–r– 1 oracle dba 898 Mar 15 06:16 wcr_4r6jaqn002rgv.rec
-rw-r–r– 1 oracle dba 898 Mar 15 06:16 wcr_4r7a30w002tsh.rec
-rw-r–r– 1 oracle dba 1002 Mar 15 06:16 wcr_4r7dnw8002u5r.rec
-rw-r–r– 1 oracle dba 4074 Mar 15 06:16 wcr_cr.html
-rw-r–r– 1 oracle dba 1544 Mar 15 06:16 wcr_cr.text
-rw-r–r– 1 oracle dba 208 Mar 15 06:16 wcr_fcapture.wmd
-rw-r–r– 1 oracle dba 143 Mar 15 06:16 wcr_scapture.wmd
-rw-r–r– 1 oracle dba 153 Mar 15 06:17 wcr_login.pp
-rw-r—– 1 oracle dba 12288 Mar 15 06:17 wcr_scn_order.extb
-rw-r—– 1 oracle dba 12288 Mar 15 06:17 wcr_seq_data.extb
-rw-r—– 1 oracle dba 12288 Mar 15 06:17 wcr_conn_data.extb
-rw-r–r– 1 oracle dba 35 Mar 15 06:17 wcr_process.wmd
-rw-r–r– 1 oracle dba 633 Mar 15 06:20 wcr_replay.wmd
-rw-r—– 1 oracle dba 18374656 Mar 15 06:23 wcr_ra_2383030089.dmp
-rw-r–r– 1 oracle dba 28278 Mar 15 06:23 wcr_ra_2383030089.log
-rw-r–r– 1 oracle dba 10952 Mar 15 06:23 wcr_rr_2383030089.xml
End of Reply process*****************
5. We need to generate report and analyse the report . We have got 11g database with OEM grid control , now we will login to our OEM and try to generate report from OEM .
Login as OEM
Open internet explorer and type the below url
http://sunmanage2.eaga.co.uk:4889/em/console/logon/logon
Type username :- sysman
Password :- provide password
Below windows will appear
Click on targets
Next click/check your server accordingly
Click on Databases
Click OEMTEST
Click Software and Support for RAT 
Click on Database Replay
Click on Analyze Results on right side 
Choose the directory , where we have captured the Replay
Click Continue
Generate report
*****************Below Sample Report**********
