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
Orhan Eripek's Oracle Blog 