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 .

Cd /

./ stop_ebstst4

Cd /

./ stop_ebstst4

Apply the patch in EBSTSTS4 (9.2.0.7)

. 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 .

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 .

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 ReplayClick on Analyze Results on right side

Choose the directory , where we have captured the Replay

Click Continue

Generate report

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

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

Real Application Testing Oracle

About the Author Rajat

Leave a Comment:

3 comments