Oracle Real Application Testing (RAT) ile tasiyacagim database’in yeni makinada yeni platformunda (aix to linux) nasil calisacagini önceden görmek istedim. Benim icin önemli olan günün farkli saat araliklarinin capture’larini source ortamdan aldim ve process ettim. Olusan ilgili dosyalari hedef ortama tasidim ve bu capture’lari apply ettim. Capture ve replay periyotlarin raporlarini compare ederek ve iki ortamin awr raporlarini inceleyerek sorunlu queryleri tespit ettim. Sql profile’lar ve baseline’larin tasindigi ve tasinmadigi durumlar da uygulanarak karşılaştırıldı.

---start capture on source
create directory DIR_RAT as '/dir/rat';
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'CAP_2245_0800', 
dir => 'DIR_RAT',
duration => 32415
-- ,default_action => 'EXCLUDE'
);
END;
/
SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, status, round(capture_size/1024/1024) as capture_mb FROM dba_workload_captures order by start_time desc;
22 CAP_2245_0800

— capture ettigimiz workload’in awr’ina bakmak istersek (source):

BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id =>22);
END;
/

–source:

BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DIR_RAT');
END;
/

— for DAILY1:

create directory DIR_RAT2 as '/dir/rat2';
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'CAP_0900_1500', 
dir => 'DIR_RAT2',
duration => 21600
-- ,default_action => 'EXCLUDE'
);
END;
/
SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, status, round(capture_size/1024/1024) as capture_mb FROM dba_workload_captures order by start_time desc;
36 CAP_0900_1500

— capture ettigimiz workload’in awr’ina bakmak istersek (source):

BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id =>36);
END;
/

–source:

BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DIR_RAT2');
END;
/

— for DAILY2:

create directory DIR_RAT3 as '/dir/rat3';

 

BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'CAP_1540_2240', 
dir => 'DIR_RAT3',
duration => 25200
-- ,default_action => 'EXCLUDE'
);
END;
/
SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, status, round(capture_size/1024/1024) as capture_mb FROM dba_workload_captures order by start_time desc;
38 CAP_1540_2240

— capture ettigimiz workload’in awr’ina bakmak istersek (source):

BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id =>38);
END;
/

–source:

BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DIR_RAT3');
END;
/

— copy files to target
— shared bir lokal diski kullaniyoruz (/u02/app_acfs/rat):

sourcedbnode1@oracle </backup/rat> scp -r cap oracle@targetdb:/u02/app_acfs/rat
sourcedbnode1@oracle </backup/rat> scp -r capfiles oracle@targetdb:/u02/app_acfs/rat
sourcedbnode1@oracle </backup/rat> scp -r pp11.2.0.4.0/ oracle@targetdb:/u02/app_acfs/rat
sourcedbnode2@oracle </backup/rat/capfiles> scp -r inst2/ oracle@targetdb:/u02/app_acfs/rat/capfiles

–on target

create or replace directory DIR_RAT as '/u02/app_acfs/rat'

–on target

begin
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name=> 'CAP_2245_0800_2',
replay_dir=>'DIR_RAT');
end;
/
SELECT id,name, start_time,end_time, duration_secs,status FROM dba_workload_replays order by start_time desc;
begin
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization=>FALSE);
END;
/

— target db tns:
— on client
— client node’lara tns ekliyoz, bu node’lar clientimiz olsun:

TARGETDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = exa01-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TARGETDB)
)
)

— client’te bas:

wrc mode=calibrate replaydir=/u02/app_acfs/rat

— clientlari baslama öncesi hazirlik, client node’da bas:

wrc system/******@TARGETDB mode=replay replaydir=/u02/app_acfs/rat CONNECTION_OVERRIDE=TRUE

— on target

1.node 
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
end;
/

— istege bagli:
alter system set optimizer_features_enable=’11.2.0.3′ scope=both;

–AFTER REPLAY ON TARGET
— eod’un capture id’sini targetden al:

SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, status, round(capture_size/1024/1024) as capture_mb
FROM dba_workload_captures order by start_time desc;
1
select sys.DBMS_WORKLOAD_CAPTURE.IMPORT_AWR (capture_id => 1, staging_schema => 'TESTUSER') from dual;
SQL> select sys.DBMS_WORKLOAD_CAPTURE.IMPORT_AWR (capture_id => 1, staging_schema => 'TESTUSER') from dual;

SYS.DBMS_WORKLOAD_CAPTURE.IMPORT_AWR(CAPTURE_ID=>1,STAGING_SCHEMA=>'TESTUSER')
------------------------------------------------------------------------------
1150961657

SQL>
sourcedbnode1@oracle </dir/rat/cap> ls -la
total 4711720
drwxr-xr-x 2 oracle staff 256 Oct 08 12:00 .
drwxr-xr-x 5 oracle dba 256 Oct 04 09:03 ..
-rw-r----- 1 oracle staff 2411077632 Oct 08 12:00 wcr_ca.dmp
-rw-r--r-- 1 oracle staff 80898 Oct 08 12:00 wcr_ca.log
-rw-r----- 1 oracle staff 24576 Oct 08 12:00 wcr_cap_uc_graph.extb
-rw-r--r-- 1 oracle staff 66934 Oct 03 07:50 wcr_cr.html
-rw-r--r-- 1 oracle staff 26742 Oct 03 07:50 wcr_cr.text
-rw-r--r-- 1 oracle staff 1084420 Oct 03 07:55 wcr_cr.xml
-rw-r--r-- 1 oracle staff 247 Oct 03 07:49 wcr_fcapture.wmd
-rw-r--r-- 1 oracle staff 104 Oct 02 22:47 wcr_scapture.wmd
sourcedbnode1@oracle </dir/rat/cap> scp wcr_ca.dmp wcr_ca.log wcr_cap_uc_graph.extb oracle@targetdb:/u02/app_acfs/rat/cap
oracle@targetdb's password: 
wcr_ca.dmp 100% 2299MB 20.5MB/s 01:52 
wcr_ca.log 100% 79KB 79.0KB/s 00:00 
wcr_cap_uc_graph.extb 100% 24KB 24.0KB/s 00:00 
sourcedbnode1@oracle </dir/rat/cap>

— targetdeki replay id’yi aliyoruz:

SELECT id,name, start_time,end_time, duration_secs,status FROM dba_workload_replays order by start_time desc;
6

set long 100000000 longchunksize 100000000 linesize 200 head off feedback off echo off TRIMSPOOL ON TRIM ON
var report_bind clob;
BEGIN
DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (replay_id1 => 6, replay_id2=> NULL, format => 'HTML', result => :report_bind);
END;
/
SPOOL /u02/app_acfs/rat/compare_eod_report.html
print report_bind
SPOOL OFF

########## DAILY1 REPORT ########

— capture ettigimiz workload’in awr’ina bakmak istersek (source):

BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id =>41);
END;
/
sourcedbnode1@oracle </dir/rat2> scp * oracle@targetdb:/u02/app_acfs/rat2


sourcedbnode2@oracle </dir/rat2/capfiles> scp -r * oracle@targetdb:/u02/app_acfs/rat2/capfiles


--on target:

create or replace directory DIR_RAT2 as '/u02/app_acfs/rat2';

–on target:

begin
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name=> 'REP_DAILY1',
replay_dir=>'DIR_RAT2');
end;
/

begin
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization=>FALSE);
END;
/

— client node’da bas:

wrc mode=calibrate replaydir=/u02/app_acfs/rat2

— clientlari baslama öncesi hazirlik, client node’da bas:

wrc system/******@TARGETDB mode=replay replaydir=/u02/app_acfs/rat2 CONNECTION_OVERRIDE=TRUE
[oracle@testdbvm01 ~]$ wrc system/******@TARGETDB mode=replay replaydir=/u02/app_acfs/rat2 CONNECTION_OVERRIDE=TRUE
Workload Replay Client: Release 11.2.0.4.0 - Production on Mon Oct 8 15:20:28 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (15:20:28)

[oracle@testdbvm02 ~]$ wrc system/*******@TARGETDB mode=replay replaydir=/u02/app_acfs/rat2 CONNECTION_OVERRIDE=TRUE
Workload Replay Client: Release 11.2.0.4.0 - Production on Mon Oct 8 15:20:31 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (15:20:31)

— on target
— 1.node

BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
end;
/

— daily1 in capture’i target’e gelmis oldu, id’miz 12:

SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, status, round(capture_size/1024/1024) as capture_mb
FROM dba_workload_captures order by start_time desc;
SELECT id,name, start_time,end_time, duration_secs,status FROM dba_workload_replays order by start_time desc;
12 REP_DAILY1 08/10/18 12:21:18 IN PROGRESS
12 REP_DAILY1 08/10/18 12:21:18 08/10/18 18:21:19 21601 COMPLETED

— DAILY2 de ayni sekilde çalistirilir

22 REP_DAILY2 09/10/18 08:33:03 09/10/18 15:33:04 25201 COMPLETED

######### AWR ######
–source:

select * from dba_workload_captures
-- DAILY1
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures where id=22

--DAILY2
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures where id=36

--DAILY3
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures where id=38

–target:

select * from dba_workload_replays
-- DAILY1
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_replays where id=6

--DAILY2
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_replays where id=12

--DAILY3
select AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_replays where id=22