Real Application Testing Oracle

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

OEM1

Click on targets  

OEM2

Next click/check  your server accordingly

OEM3

Click on Databases

OEM4

 Click  OEMTEST

OEm6 Click Software and Support for RAT OEM7 

Click on Database ReplayOEM8Click on Analyze Results on right side oem9

Choose the directory , where we have captured the Replay  

oem10

Click Continue

oem11

 Generate report

 *****************Below Sample Report********** 

sample 

Share This Post with Your Friends over Social Media!

About the Author Rajat

I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney  Oracle Financials 11i and Release 12,12.2  Oracle RDBMS 7x,8i , 9i,10g and 11g  Amazon cloud .  Oracle RAC 10g and 11g  Oracle Applications Server  Oracle Web logic Server  OBIEE  Hyperion  SSO  Oracle Portal  PL/SQL  UNIX  SQL  MS-SQL 2008.  OEM 12c .  Data Guard .  Essbase .  Database backup and recovery.  Performance Tunning.  TimeSten

Leave a Comment:

3 comments
fan_of_this_site says March 30, 2010

Good post and very detailed . Few questions on RAT which I hope you will cover in your next post. Is RAT to run and test workload in to OEM database ? What am I goign to achieve or usecase for RAT ? Is it separately licensable product ?

Reply
Rajat says March 31, 2010

Thanks ,

No it can be possible even without OEM database too there is some script provided by Oracle using that you can replay those report in any database version which is higher than 10.2.0.4 ,But OEM is GUI mode and easy to generate report . metalink doc [ID 742645.1]for script

The question about what you achieve its all about query tunning , i think if your database is in lower version and want to upgrade to higher version and you have problem of your custom codes specially in 11i database ,which is the cause of slowing down the system and RBO is set , then its the ideal situation to check/capture your dodgy code and replay in the higher version and figure out the issue .

Yes you need to buy license to use this product

Reply
bis says June 29, 2010

Hey Rajat,

Nice Document. And truely appricicate your efforts. Just a typ found and please do not take it otherwise only for the benefit of the beginners –

To start the capture process – You have mentioned “Start the reply Use this below command

wrc system/password mode=calibrate replaydir=/oemtest_bin/db/oracle/RAT”

but seems it should be –

wrc system/password replaydir=/oemtest_bin/db/oracle/RAT”

Hope you keep up the good work.

Cheers!!

Bis

Reply
Add Your Reply