執行database replay進行升級測試
最近要做10g到11g的升級，對於效能方面的測試，一般使用RAT(Oracle Real Application Testing)技術。RAT中主要包含了兩種技術，一種叫Database Replay(資料庫重演)，另一種叫SQL Performance Analyzer(SQL效能分析)。
我們可以看到其實就是把生產環境的成百上千個使用者執行的語句進行捕獲。然後把這一部分負載資訊拿到測試環境上進行播放。播放的過程就相當於模擬了成百上千的使用者對資料庫的操作。但是這個過程有些操作是不支援的。具體參照下面列出的情景。
Workload Capture Restrictions
The following types of client requests are not supported.
Direct path load of data from external files using utilities such as SQL*Loader
Non-PL/SQL based Advanced Queuing (AQ)
Flashback queries
Oracle Call Interface (OCI) based object navigations
Non SQL-based object access
Distributed transactions (any distributed transactions that are captured will be replayed as local transactions)
Oracle Streams/Advanced Replication workload is not supported prior to 11.2.
Database session migration
Database Resident Connection Pooling ( DRCP )
XA transactions
Workloads having Object Out Bind
因此要避免這類的問題，我們可以實施過濾操作，將部分操作過濾出去。
使用Database Replay首先我們需要去檢視官方文件Document 560977.1 Using Real Application Testing Functionality in Earlier Releases。這個文件列出了一些先決條件，比如我們要從10.2.0.5的生產環境捕獲負載，然後拿到11.2.0.4環境上回放。這需要你在10.2.0.5和11.2.0.4上安裝相應的patch。其實我做了些測試，不安裝這個patch，也能夠測試成功。但是據Oracle人員稱會有一些影響。為了能夠精準的執行測試，建議還是安裝這些補丁。
Source DB Upgrade from release Destination DB Upgrade to any release What patch you need to apply? Download Information Comments
10.2.0.5.0 >=11.2.0.3.0 10.2.0.5.0 Patchset + one-off patch 9373986 and 11.2.0.3.0 + one-off patch 13947480
or
16086826 (16086826 includes 13947480)
or
17411249
(17411249 includes 16086826 and all other previous patches) One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch
10.2.0.5.0 >=11.2.0.4.0 10.2.0.5.0 Patchset + one-off patch 9373986and no mandatory patch required for 11.2.0.4.0 One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch
安裝完補丁之後，我們就可以做database replay的操作了，該操作主要分成四個步驟。
Workload Capture
Workload Processing
Workload Replay – including DML and SQL queries
Analysis and Reporting
我這邊做的一個例子是從10.2.0.5捕獲系統負載，然後拿到11.2.0.4上面去重放。按照Using Real Application Testing Functionality in Earlier Releases的要求，我們需要在10.2.0.5上面安裝Bug 9373986 : WORKLOAD DATA IS NOT CAPTURED WHENEVER RESTARTING WORKLOAD CAPTURE，因為我這只是模擬測試，所以沒有安裝，實際上可以捕獲負載成功。
一、負載捕獲
第一個任務就是做捕獲，可以透過命令列或者是EM來實現捕獲。
1.捕獲會把資料庫執行的整個活動寫入到指定的目錄當中。這裡我採用系統預設的DATA_PUMP_DIR目錄。
SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS DATA_PUMP_DIR /oracle/app/oracle/product/10.2.0/db_1/rdbms/log
2.為了更好的演示捕獲的過程，需要建立一個表，然後寫一些負載的指令碼。這裡我選擇了一些插入和查詢的SQL。
SQL> connect test/test Connected. create table t1 ( name varchar2(20) ) /
下面開始準備兩段PL/SQL的程式碼,模擬大批次的插入t1表和查詢scott.emp下的表.
declare v_sql varchar2(256); BEGIN FOR i IN 1 .. 10 LOOP v_sql:='insert into t1 values (''Name for ' ||i||''')'; execute immediate v_sql; commit; END LOOP; END; / declare v_sql varchar2(2000); BEGIN FOR i IN 1 .. 10 LOOP v_sql:='select * from scott.emp where rownum
3.設定過濾條件。這裡我們可以設定不捕捉SYS使用者的，不捕捉是PL/SQL program發起的。當然還有很多種可以過濾的方式，下面羅列了一下。設定完成過濾後，在我們執行捕獲負載的時候，我們可以設定INCLUDE和EXCLUDE兩個選項。INCLUDE的意思就是所有的Database請求都會捕獲，除了定義的過濾條件外。而EXCLUDE正好相反，只會捕捉我們定義的過濾。
If INCLUDE is used, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. If EXCLUDE is used, by default no user request to the database will be captured, except for the part of the workload defined by the filters. Other FILTER attributes which can be used are:-
– SESSION_ID – type NUMBER
– USER – type STRING
– MODULE – type STRING
– ACTION – type STRING
– PROGRAM – type STRING
– SERVICE – type STRING
SQL> exec dbms_workload_capture.ADD_FILTER(fname =>'FILTER_SYS',fattribute => 'USER',fvalue => 'TEST'); PL/SQL procedure successfully completed. SQL> select type, name, attribute, status, value from dba_workload_filters; TYPE NAME ATTRIBUTE STATUS VALUE ------------------------------ ------------------------- -------------------- ------ -------------------- CAPTURE TEST_CAP_FILTER1 USER NEW test
4.開始捕捉,這裡要記住很重要的一點，我們執行開始和結束，都要在這個session下執行。
SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 3 dir =>'DATA_PUMP_DIR', 4 default_action=>'EXCLUDE', 5 duration => NULL); 6 END; 7 / BEGIN * ERROR at line 1: ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799 ORA-06512: at line 2
執行出錯，這兒需要我們把引數PRE_11G_ENABLE_CAPTURE設定成ture.
SQL> alter system set PRE_11G_ENABLE_CAPTURE=true; System altered. SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 3 dir =>'DATA_PUMP_DIR', 4 default_action=>'EXCLUDE', 5 duration => NULL); 6 END; 7 / PL/SQL procedure successfully completed.
5.新開一個會話，執行語句。這裡我們執行我們前面寫的PL/SQL程式碼，同時我們在SYS下面在執行一些事務。看看我們設定的過濾有沒有生效。
SQL> connect test/test Connected. SQL> declare 2 v_sql varchar2(256); 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 v_sql:='insert into t1 values (''Name for ' ||i||''')'; 6 execute immediate v_sql; 7 commit; 8 END LOOP; 9 END; 10 / PL/SQL procedure successfully completed. SQL> declare 2 v_sql varchar2(2000); 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 v_sql:='select * from scott.emp where rownum connect test/test Connected. SQL> insert into t2 select * from dba_tables; 1535 rows created. SQL> commit; Commit complete.
6.回到捕獲的那個會話視窗，停止捕獲。
SQL> exec dbms_workload_capture.finish_capture; PL/SQL procedure successfully completed.
7.從資料庫中檢視我們捕獲的資訊。這裡我們可以看到我們的捕獲的事務數是10，我們只是捕獲了TEST使用者下的操作，而對於我們SYS下執行事務的操作是沒有捕獲的。
SQL> select name, directory, status, start_time, end_time, USER_CALLS,TRANSACTIONS from dba_workload_captures; NAME DIRECTORY STATUS START_TIM END_TIME USER_CALLS TRANSACTIONS -------------------- --------------- --------------- --------- --------- ---------- ------------ test_capture_1 DATA_PUMP_DIR COMPLETED 20-APR-14 20-APR-14 56 10
8.從作業系統目錄下檢視我們捕獲的檔案。
[oracle@ora10g log]$ ls -lrt total 80 -rw-r--r-- 1 oracle oinstall 126 Apr 20 13:47 wcr_scapture.wmd -rw-r--r-- 1 oracle oinstall 3190 Apr 20 13:48 wcr_56rwjh0000000.rec -rw-r--r-- 1 oracle oinstall 785 Apr 20 13:48 wcr_56rwuh0000002.rec -rw-r--r-- 1 oracle oinstall 4644 Apr 20 13:48 wcr_56rwrh0000001.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 13:48 wcr_56rx0h0000003.rec -rw-r--r-- 1 oracle oinstall 1030 Apr 20 13:48 wcr_56rx6h0000004.rec -rw-r--r-- 1 oracle oinstall 930 Apr 20 13:48 wcr_56rxyh0000005.rec -rw-r--r-- 1 oracle oinstall 1044 Apr 20 13:48 wcr_56ryah0000006.rec -rw-r--r-- 1 oracle oinstall 195 Apr 20 13:49 wcr_fcapture.wmd -rw-r--r-- 1 oracle oinstall 11224 Apr 20 13:49 wcr_cr.text -rw-r--r-- 1 oracle oinstall 25514 Apr 20 13:49 wcr_cr.html [oracle@ora10g log]$ tree . |-- wcr_56rwjh0000000.rec |-- wcr_56rwrh0000001.rec |-- wcr_56rwuh0000002.rec |-- wcr_56rx0h0000003.rec |-- wcr_56rx6h0000004.rec |-- wcr_56rxyh0000005.rec |-- wcr_56ryah0000006.rec |-- wcr_cr.html |-- wcr_cr.text |-- wcr_fcapture.wmd `-- wcr_scapture.wmd 0 directories, 11 files
9.檢視捕獲的報告。預設情況下執行捕獲會生成2個快照，我們還可以透過這兩個快照生成AWR報告，用於和重放的AWR進行比較。
SQL> select dbms_workload_capture.report(30,'TEXT') from dual; DBMS_WORKLOAD_CAPTURE.REPORT(30,'TEXT') -------------------------------------------------------------------------------- Database Capture Report For ORCL DB Name DB Id Release RAC Capture Name Status ------------ ----------- ----------- --- -------------------------- ---------- ORCL 1349524903 10.2.0.5.0 NO test_capture_1 COMPLETED Start time: 20-Apr-14 13:47:58 (SCN = 6740487) End time: 20-Apr-14 13:48:59 (SCN = 6740608) Duration: 1 minute 1 second Capture size: 13.84 KB Directory object: DATA_PUMP_DIR Directory path: /oracle/app/oracle/product/10.2.0/db_1/rdbms/log/ Directory shared in RAC: TRUE Filters used: 1 INCLUSION filter Captured Workload Statistics DB/Inst: ORCL/ Snaps: 116-117 -> 'Value' represents the corresponding statistic aggregated across the entire captured database workload. -> '% Total' is the percentage of 'Value' over the corresponding system-wide aggregated total. Statistic Name Value % Total ---------------------------------------- ------------- --------- DB time (secs) 0.03 15.00 Average Active Sessions 0.00 N/A User calls captured 56 19.65 User calls captured with Errors 0 N/A Session logins 5 71.43 Transactions 10 40.00 ------------------------------------------------------------- Top Events Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module containing Unreplayable CallsDB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module (Jobs and Background Activity)DB/Inst: ORCL/ Snaps: 116-11 No data exists for this section of the report. ------------------------------------------------------------- Top SQL (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Workload Filters DB/Inst: ORCL/ Snaps: 116-117 # Filter Name Type Attribute Value --- ------------------------ ------- ------------ -------------------------- 1 FILTER_TEST INCLUDE USER TEST ------------------------------------------------------------- End of Report
二、預處理重複資料
預處理需要我們把捕獲的檔案COPY到目標機器的Datadump目錄下面。
[oracle@ora10g log]$ scp * oracle@192.168.56.102:/oracle/app/oracle/admin/ora11/dpdump/ oracle@192.168.56.102's password: wcr_56rwjh0000000.rec 100% 3190 3.1KB/s 00:00 wcr_56rwrh0000001.rec 100% 4644 4.5KB/s 00:00 wcr_56rwuh0000002.rec 100% 785 0.8KB/s 00:00 wcr_56rx0h0000003.rec 100% 3190 3.1KB/s 00:00 wcr_56rx6h0000004.rec 100% 1030 1.0KB/s 00:00 wcr_56rxyh0000005.rec 100% 930 0.9KB/s 00:00 wcr_56ryah0000006.rec 100% 1044 1.0KB/s 00:00 wcr_cr.html 100% 25KB 24.9KB/s 00:00 wcr_cr.text 100% 11KB 11.0KB/s 00:00 wcr_fcapture.wmd 100% 195 0.2KB/s 00:00 wcr_scapture.wmd 100% 126 0.1KB/s 00:00 SQL> exec dbms_workload_replay.process_capture('DATA_PUMP_DIR'); PL/SQL procedure successfully completed.
執行完這個儲存過程之後，我們會發現在目錄下生成了一個pp11.2.0.4.0資料夾。資料夾有下列內容：
[oracle@11g pp11.2.0.4.0]$ ls -lrt total 96 -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_seq_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_scn_order.extb -rw-r--r-- 1 oracle oinstall 224 Apr 20 21:33 wcr_login.pp -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_conn_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_references.extb -rw-r--r-- 1 oracle oinstall 35 Apr 20 21:33 wcr_process.wmd -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_dep_graph.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_commits.extb -rw-r--r-- 1 oracle oinstall 3512 Apr 20 21:33 wcr_calibrate.xml
檢查需要多少個客戶端進行播放及需要消耗的資源。
[oracle@11g pp11.2.0.4.0]$ wrc mode=calibrate replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 21:39:27 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Report for Workload in: /oracle/app/oracle/admin/ora11/dpdump ----------------------- Recommendation: Consider using at least 1 clients divided among 1 CPU(s) You will need at least 3 MB of memory per client process. If your machine(s) cannot match that number, consider using more clients. Workload Characteristics: - max concurrency: 1 sessions - total number of sessions: 3 Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - 256 KB of memory cache per concurrent session - think time scale = 100 - connect time scale = 100 - synchronization = TRUE
三、執行重放
1.執行重放之前，我們要初始化重放。這裡仍然要注意，所有執行重放的系列操作都必須在同一個會話下執行。
SQL> exec dbms_workload_replay.initialize_replay (replay_name => 'test_replay_1', replay_dir => 'DATA_PUMP_DIR'); PL/SQL procedure successfully completed. SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; ID NAME PAR CAPTURE_ID STATUS USER_CALLS ---------- ------------------------- --- ---------- ---------------------------------------- ---------- 54 test_replay_1 NO 65 INITIALIZED
2.執行重放可以選擇COMMIT的順序是否一致。
SQL> exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE); PL/SQL procedure successfully completed. SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; ID NAME PAR CAPTURE_ID STATUS USER_CALLS ---------- ------------------------- --- ---------- ---------------------------------------- ---------- 54 test_replay_1 NO 65 PREPARE
3.開始執行replay,首先去命令列執行一段指令,然後回到原本的會話執行開始命令。
[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (22:27:21) SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY(); PL/SQL procedure successfully completed.
這個地方如果希望停止replay可以呼叫DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY()來取消replay。
回到replay的視窗，我們可以看到replay已經完成的螢幕輸出。
[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (22:27:21) Replay started (22:27:48) Replay finished (22:28:51)
我們也可以觀察後臺日誌。可以看到在執行完成後，啟動了DM和DW程式進行了一個匯出。
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 04/20/2014 22:27:48 Sun Apr 20 22:28:07 2014 DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 04/20/2014 22:28:06 Sun Apr 20 22:28:08 2014 DM00 started with pid=31, OS id=20650, job SYS.SYS_EXPORT_TABLE_01 Sun Apr 20 22:28:10 2014 DW00 started with pid=32, OS id=20652, wid=1, job SYS.SYS_EXPORT_TABLE_01
我們再觀察一下datadump的目錄，發現replay完成之後，匯出了以WRH$開頭的表。
[oracle@11g dpdump]$ ls -lrt total 88 -rw-r--r-- 1 oracle oinstall 126 Apr 20 22:23 wcr_scapture.wmd -rw-r--r-- 1 oracle oinstall 195 Apr 20 22:23 wcr_fcapture.wmd -rw-r--r-- 1 oracle oinstall 11224 Apr 20 22:23 wcr_cr.text -rw-r--r-- 1 oracle oinstall 25514 Apr 20 22:23 wcr_cr.html -rw-r--r-- 1 oracle oinstall 1044 Apr 20 22:23 wcr_56ryah0000006.rec -rw-r--r-- 1 oracle oinstall 930 Apr 20 22:23 wcr_56rxyh0000005.rec -rw-r--r-- 1 oracle oinstall 1030 Apr 20 22:23 wcr_56rx6h0000004.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 22:23 wcr_56rx0h0000003.rec -rw-r--r-- 1 oracle oinstall 785 Apr 20 22:23 wcr_56rwuh0000002.rec -rw-r--r-- 1 oracle oinstall 4644 Apr 20 22:23 wcr_56rwrh0000001.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 22:23 wcr_56rwjh0000000.rec drwxr-xr-x 2 oracle oinstall 4096 Apr 20 22:25 pp11.2.0.4.0 drwxr-xr-x 2 oracle oinstall 4096 Apr 20 22:28 rep366872083 [oracle@11g dpdump]$ cd rep366872083 [oracle@11g rep366872083]$ ls -lrt total 9168 -rw-r--r-- 1 oracle oinstall 827 Apr 20 22:28 wcr_replay.wmd -rw-r--r-- 1 oracle oinstall 15671 Apr 20 22:28 wcr_ra_366872083.log -rw-r----- 1 oracle oinstall 9326592 Apr 20 22:28 wcr_ra_366872083.dmp -rw-r----- 1 oracle oinstall 12288 Apr 20 22:28 wcr_rep_uc_graph_366872083.extb -rw-r--r-- 1 oracle oinstall 8412 Apr 20 22:28 wcr_rr_366872083.xml
最後驗證下我們的資料。
SQL> connect test/test Connected. SQL> select * from t1; NAME -------------------------------------------------------------------------------- Name for 1 Name for 2 Name for 3 Name for 4 Name for 5 Name for 6 Name for 7 Name for 8 Name for 9 Name for 10 10 rows selected.
四、生成報告
最後一步就是我們做完了replay，需要做個報告的對比，這裡我們可以透過awr報告進行對比，也可以使用自帶的包生成對比報告。如下所示：
SQL> select id,name,status,start_time,end_time,user_calls,dir_path from dba_workload_replays; ID NAME STATUS START_TIM END_TIME USER_CALLS DIR_PATH ---------- -------------------- -------------------- --------- --------- ---------- ---------------------------------------- 54 test_replay_1 COMPLETED 20-APR-14 20-APR-14 56 /oracle/app/oracle/admin/ora11/dpdump/ SQL> set long 999999 SQL> select dbms_workload_replay.report(54,'TEXT') from dual; DBMS_WORKLOAD_REPLAY.REPORT(54,'TEXT') -------------------------------------------------------------------------------- DB Replay Report for test_replay_1 -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------- | DB Name | DB Id | Release | RAC | Replay Name | Replay Status | -------------------------------------------------------------------------- | ORA11 | 753808255 | 11.2.0.4.0 | NO | test_replay_1 | COMPLETED | -------------------------------------------------------------------------- Replay Information -------------------------------------------------------------------------------- ------------------- | Information | Replay | Capture | -------------------------------------------------------------------------------- ------------------- | Name | test_replay_1 | test_capture_1 | -------------------------------------------------------------------------------- ------------------- | Status | COMPLETED | COMPLETED | -------------------------------------------------------------------------------- ------------------- | Database Name | ORA11 | ORCL | -------------------------------------------------------------------------------- ------------------- | Database | 11.2.0.4.0 | 10.2.0.5.0 | | Version | | | -------------------------------------------------------------------------------- ------------------- | Start Time | 20-04-14 14:27:48 | 20-04-14 13:47:58 | -------------------------------------------------------------------------------- ------------------- | End Time | 20-04-14 14:28:07 | 20-04-14 13:48:59 | -------------------------------------------------------------------------------- ------------------- | Duration | 19 seconds | 1 minute 1 second | -------------------------------------------------------------------------------- ------------------- | Directory | DATA_PUMP_DIR | DATA_PUMP_DIR | | Object | | | -------------------------------------------------------------------------------- ------------------- | Directory | /oracle/app/oracle/admin/ora11/dpdump/ | /oracle/app/oracle/ad min/ora11/dpdump/ | | Path | | | -------------------------------------------------------------------------------- ------------------- Replay Options --------------------------------------------------------- | Option Name | Value | --------------------------------------------------------- | Synchronization | SCN | --------------------------------------------------------- | Connect Time | 100% | --------------------------------------------------------- | Think Time | 100% | --------------------------------------------------------- | Think Time Auto Correct | TRUE | --------------------------------------------------------- | Number of WRC Clients | 1 (1 Completed, 0 Running ) | --------------------------------------------------------- Replay Statistics ------------------------------------------------------------ | Statistic | Replay | Capture | ------------------------------------------------------------ | DB Time | 0.451 seconds | 0.026 seconds | ------------------------------------------------------------ | Average Active Sessions | .02 | 0 | ------------------------------------------------------------ | User calls | 56 | 56 | ------------------------------------------------------------ | Network Time | 0.084 seconds | N/A | ------------------------------------------------------------ | Think Time | 13.829 seconds | N/A | ------------------------------------------------------------ Replay Divergence Summary ------------------------------------------------------------------- | Divergence Type | Count | % Total | ------------------------------------------------------------------- | Session Failures During Replay | 0 | 0.00 | ------------------------------------------------------------------- | Errors No Longer Seen During Replay | 0 | 0.00 | ------------------------------------------------------------------- | New Errors Seen During Replay | 0 | 0.00 | ------------------------------------------------------------------- | Errors Mutated During Replay | 0 | 0.00 | ------------------------------------------------------------------- | DMLs with Different Number of Rows Modified | 0 | 0.00 | ------------------------------------------------------------------- | SELECTs with Different Number of Rows Fetched | 0 | 0.00 | ------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------- Workload Profile Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top Service/Module/Action -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top SQL with Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top Sessions with Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Divergence Session Failures By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Error Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By SQL -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Session -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- DML Data Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By SQL -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Divergence magnitude -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- SELECT Data Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Divergence magnitude -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Clients Alerts -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Filters -------------------------------------------------- | No data exists for this section of the report. | --------------------------------------------------
我們可以看到其實就是把生產環境的成百上千個使用者執行的語句進行捕獲。然後把這一部分負載資訊拿到測試環境上進行播放。播放的過程就相當於模擬了成百上千的使用者對資料庫的操作。但是這個過程有些操作是不支援的。具體參照下面列出的情景。
Workload Capture Restrictions
The following types of client requests are not supported.
Direct path load of data from external files using utilities such as SQL*Loader
Non-PL/SQL based Advanced Queuing (AQ)
Flashback queries
Oracle Call Interface (OCI) based object navigations
Non SQL-based object access
Distributed transactions (any distributed transactions that are captured will be replayed as local transactions)
Oracle Streams/Advanced Replication workload is not supported prior to 11.2.
Database session migration
Database Resident Connection Pooling ( DRCP )
XA transactions
Workloads having Object Out Bind
因此要避免這類的問題，我們可以實施過濾操作，將部分操作過濾出去。
使用Database Replay首先我們需要去檢視官方文件Document 560977.1 Using Real Application Testing Functionality in Earlier Releases。這個文件列出了一些先決條件，比如我們要從10.2.0.5的生產環境捕獲負載，然後拿到11.2.0.4環境上回放。這需要你在10.2.0.5和11.2.0.4上安裝相應的patch。其實我做了些測試，不安裝這個patch，也能夠測試成功。但是據Oracle人員稱會有一些影響。為了能夠精準的執行測試，建議還是安裝這些補丁。
Source DB Upgrade from release Destination DB Upgrade to any release What patch you need to apply? Download Information Comments
10.2.0.5.0 >=11.2.0.3.0 10.2.0.5.0 Patchset + one-off patch 9373986 and 11.2.0.3.0 + one-off patch 13947480
or
16086826 (16086826 includes 13947480)
or
17411249
(17411249 includes 16086826 and all other previous patches) One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch
10.2.0.5.0 >=11.2.0.4.0 10.2.0.5.0 Patchset + one-off patch 9373986and no mandatory patch required for 11.2.0.4.0 One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch
安裝完補丁之後，我們就可以做database replay的操作了，該操作主要分成四個步驟。
Workload Capture
Workload Processing
Workload Replay – including DML and SQL queries
Analysis and Reporting
我這邊做的一個例子是從10.2.0.5捕獲系統負載，然後拿到11.2.0.4上面去重放。按照Using Real Application Testing Functionality in Earlier Releases的要求，我們需要在10.2.0.5上面安裝Bug 9373986 : WORKLOAD DATA IS NOT CAPTURED WHENEVER RESTARTING WORKLOAD CAPTURE，因為我這只是模擬測試，所以沒有安裝，實際上可以捕獲負載成功。
一、負載捕獲
第一個任務就是做捕獲，可以透過命令列或者是EM來實現捕獲。
1.捕獲會把資料庫執行的整個活動寫入到指定的目錄當中。這裡我採用系統預設的DATA_PUMP_DIR目錄。
SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS DATA_PUMP_DIR /oracle/app/oracle/product/10.2.0/db_1/rdbms/log
2.為了更好的演示捕獲的過程，需要建立一個表，然後寫一些負載的指令碼。這裡我選擇了一些插入和查詢的SQL。
SQL> connect test/test Connected. create table t1 ( name varchar2(20) ) /
下面開始準備兩段PL/SQL的程式碼,模擬大批次的插入t1表和查詢scott.emp下的表.
declare v_sql varchar2(256); BEGIN FOR i IN 1 .. 10 LOOP v_sql:='insert into t1 values (''Name for ' ||i||''')'; execute immediate v_sql; commit; END LOOP; END; / declare v_sql varchar2(2000); BEGIN FOR i IN 1 .. 10 LOOP v_sql:='select * from scott.emp where rownum
3.設定過濾條件。這裡我們可以設定不捕捉SYS使用者的，不捕捉是PL/SQL program發起的。當然還有很多種可以過濾的方式，下面羅列了一下。設定完成過濾後，在我們執行捕獲負載的時候，我們可以設定INCLUDE和EXCLUDE兩個選項。INCLUDE的意思就是所有的Database請求都會捕獲，除了定義的過濾條件外。而EXCLUDE正好相反，只會捕捉我們定義的過濾。
If INCLUDE is used, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. If EXCLUDE is used, by default no user request to the database will be captured, except for the part of the workload defined by the filters. Other FILTER attributes which can be used are:-
– SESSION_ID – type NUMBER
– USER – type STRING
– MODULE – type STRING
– ACTION – type STRING
– PROGRAM – type STRING
– SERVICE – type STRING
SQL> exec dbms_workload_capture.ADD_FILTER(fname =>'FILTER_SYS',fattribute => 'USER',fvalue => 'TEST'); PL/SQL procedure successfully completed. SQL> select type, name, attribute, status, value from dba_workload_filters; TYPE NAME ATTRIBUTE STATUS VALUE ------------------------------ ------------------------- -------------------- ------ -------------------- CAPTURE TEST_CAP_FILTER1 USER NEW test
4.開始捕捉,這裡要記住很重要的一點，我們執行開始和結束，都要在這個session下執行。
SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 3 dir =>'DATA_PUMP_DIR', 4 default_action=>'EXCLUDE', 5 duration => NULL); 6 END; 7 / BEGIN * ERROR at line 1: ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799 ORA-06512: at line 2
執行出錯，這兒需要我們把引數PRE_11G_ENABLE_CAPTURE設定成ture.
SQL> alter system set PRE_11G_ENABLE_CAPTURE=true; System altered. SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 3 dir =>'DATA_PUMP_DIR', 4 default_action=>'EXCLUDE', 5 duration => NULL); 6 END; 7 / PL/SQL procedure successfully completed.
5.新開一個會話，執行語句。這裡我們執行我們前面寫的PL/SQL程式碼，同時我們在SYS下面在執行一些事務。看看我們設定的過濾有沒有生效。
SQL> connect test/test Connected. SQL> declare 2 v_sql varchar2(256); 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 v_sql:='insert into t1 values (''Name for ' ||i||''')'; 6 execute immediate v_sql; 7 commit; 8 END LOOP; 9 END; 10 / PL/SQL procedure successfully completed. SQL> declare 2 v_sql varchar2(2000); 3 BEGIN 4 FOR i IN 1 .. 10 LOOP 5 v_sql:='select * from scott.emp where rownum connect test/test Connected. SQL> insert into t2 select * from dba_tables; 1535 rows created. SQL> commit; Commit complete.
6.回到捕獲的那個會話視窗，停止捕獲。
SQL> exec dbms_workload_capture.finish_capture; PL/SQL procedure successfully completed.
7.從資料庫中檢視我們捕獲的資訊。這裡我們可以看到我們的捕獲的事務數是10，我們只是捕獲了TEST使用者下的操作，而對於我們SYS下執行事務的操作是沒有捕獲的。
SQL> select name, directory, status, start_time, end_time, USER_CALLS,TRANSACTIONS from dba_workload_captures; NAME DIRECTORY STATUS START_TIM END_TIME USER_CALLS TRANSACTIONS -------------------- --------------- --------------- --------- --------- ---------- ------------ test_capture_1 DATA_PUMP_DIR COMPLETED 20-APR-14 20-APR-14 56 10
8.從作業系統目錄下檢視我們捕獲的檔案。
[oracle@ora10g log]$ ls -lrt total 80 -rw-r--r-- 1 oracle oinstall 126 Apr 20 13:47 wcr_scapture.wmd -rw-r--r-- 1 oracle oinstall 3190 Apr 20 13:48 wcr_56rwjh0000000.rec -rw-r--r-- 1 oracle oinstall 785 Apr 20 13:48 wcr_56rwuh0000002.rec -rw-r--r-- 1 oracle oinstall 4644 Apr 20 13:48 wcr_56rwrh0000001.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 13:48 wcr_56rx0h0000003.rec -rw-r--r-- 1 oracle oinstall 1030 Apr 20 13:48 wcr_56rx6h0000004.rec -rw-r--r-- 1 oracle oinstall 930 Apr 20 13:48 wcr_56rxyh0000005.rec -rw-r--r-- 1 oracle oinstall 1044 Apr 20 13:48 wcr_56ryah0000006.rec -rw-r--r-- 1 oracle oinstall 195 Apr 20 13:49 wcr_fcapture.wmd -rw-r--r-- 1 oracle oinstall 11224 Apr 20 13:49 wcr_cr.text -rw-r--r-- 1 oracle oinstall 25514 Apr 20 13:49 wcr_cr.html [oracle@ora10g log]$ tree . |-- wcr_56rwjh0000000.rec |-- wcr_56rwrh0000001.rec |-- wcr_56rwuh0000002.rec |-- wcr_56rx0h0000003.rec |-- wcr_56rx6h0000004.rec |-- wcr_56rxyh0000005.rec |-- wcr_56ryah0000006.rec |-- wcr_cr.html |-- wcr_cr.text |-- wcr_fcapture.wmd `-- wcr_scapture.wmd 0 directories, 11 files
9.檢視捕獲的報告。預設情況下執行捕獲會生成2個快照，我們還可以透過這兩個快照生成AWR報告，用於和重放的AWR進行比較。
SQL> select dbms_workload_capture.report(30,'TEXT') from dual; DBMS_WORKLOAD_CAPTURE.REPORT(30,'TEXT') -------------------------------------------------------------------------------- Database Capture Report For ORCL DB Name DB Id Release RAC Capture Name Status ------------ ----------- ----------- --- -------------------------- ---------- ORCL 1349524903 10.2.0.5.0 NO test_capture_1 COMPLETED Start time: 20-Apr-14 13:47:58 (SCN = 6740487) End time: 20-Apr-14 13:48:59 (SCN = 6740608) Duration: 1 minute 1 second Capture size: 13.84 KB Directory object: DATA_PUMP_DIR Directory path: /oracle/app/oracle/product/10.2.0/db_1/rdbms/log/ Directory shared in RAC: TRUE Filters used: 1 INCLUSION filter Captured Workload Statistics DB/Inst: ORCL/ Snaps: 116-117 -> 'Value' represents the corresponding statistic aggregated across the entire captured database workload. -> '% Total' is the percentage of 'Value' over the corresponding system-wide aggregated total. Statistic Name Value % Total ---------------------------------------- ------------- --------- DB time (secs) 0.03 15.00 Average Active Sessions 0.00 N/A User calls captured 56 19.65 User calls captured with Errors 0 N/A Session logins 5 71.43 Transactions 10 40.00 ------------------------------------------------------------- Top Events Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions Captured DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module containing Unreplayable CallsDB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top SQL Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions Filtered Out DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Events (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Service/Module (Jobs and Background Activity)DB/Inst: ORCL/ Snaps: 116-11 No data exists for this section of the report. ------------------------------------------------------------- Top SQL (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Top Sessions (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 116-117 No data exists for this section of the report. ------------------------------------------------------------- Workload Filters DB/Inst: ORCL/ Snaps: 116-117 # Filter Name Type Attribute Value --- ------------------------ ------- ------------ -------------------------- 1 FILTER_TEST INCLUDE USER TEST ------------------------------------------------------------- End of Report
二、預處理重複資料
預處理需要我們把捕獲的檔案COPY到目標機器的Datadump目錄下面。
[oracle@ora10g log]$ scp * oracle@192.168.56.102:/oracle/app/oracle/admin/ora11/dpdump/ oracle@192.168.56.102's password: wcr_56rwjh0000000.rec 100% 3190 3.1KB/s 00:00 wcr_56rwrh0000001.rec 100% 4644 4.5KB/s 00:00 wcr_56rwuh0000002.rec 100% 785 0.8KB/s 00:00 wcr_56rx0h0000003.rec 100% 3190 3.1KB/s 00:00 wcr_56rx6h0000004.rec 100% 1030 1.0KB/s 00:00 wcr_56rxyh0000005.rec 100% 930 0.9KB/s 00:00 wcr_56ryah0000006.rec 100% 1044 1.0KB/s 00:00 wcr_cr.html 100% 25KB 24.9KB/s 00:00 wcr_cr.text 100% 11KB 11.0KB/s 00:00 wcr_fcapture.wmd 100% 195 0.2KB/s 00:00 wcr_scapture.wmd 100% 126 0.1KB/s 00:00 SQL> exec dbms_workload_replay.process_capture('DATA_PUMP_DIR'); PL/SQL procedure successfully completed.
執行完這個儲存過程之後，我們會發現在目錄下生成了一個pp11.2.0.4.0資料夾。資料夾有下列內容：
[oracle@11g pp11.2.0.4.0]$ ls -lrt total 96 -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_seq_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_scn_order.extb -rw-r--r-- 1 oracle oinstall 224 Apr 20 21:33 wcr_login.pp -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_conn_data.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_references.extb -rw-r--r-- 1 oracle oinstall 35 Apr 20 21:33 wcr_process.wmd -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_dep_graph.extb -rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_commits.extb -rw-r--r-- 1 oracle oinstall 3512 Apr 20 21:33 wcr_calibrate.xml
檢查需要多少個客戶端進行播放及需要消耗的資源。
[oracle@11g pp11.2.0.4.0]$ wrc mode=calibrate replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 21:39:27 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Report for Workload in: /oracle/app/oracle/admin/ora11/dpdump ----------------------- Recommendation: Consider using at least 1 clients divided among 1 CPU(s) You will need at least 3 MB of memory per client process. If your machine(s) cannot match that number, consider using more clients. Workload Characteristics: - max concurrency: 1 sessions - total number of sessions: 3 Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - 256 KB of memory cache per concurrent session - think time scale = 100 - connect time scale = 100 - synchronization = TRUE
三、執行重放
1.執行重放之前，我們要初始化重放。這裡仍然要注意，所有執行重放的系列操作都必須在同一個會話下執行。
SQL> exec dbms_workload_replay.initialize_replay (replay_name => 'test_replay_1', replay_dir => 'DATA_PUMP_DIR'); PL/SQL procedure successfully completed. SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; ID NAME PAR CAPTURE_ID STATUS USER_CALLS ---------- ------------------------- --- ---------- ---------------------------------------- ---------- 54 test_replay_1 NO 65 INITIALIZED
2.執行重放可以選擇COMMIT的順序是否一致。
SQL> exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE); PL/SQL procedure successfully completed. SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; ID NAME PAR CAPTURE_ID STATUS USER_CALLS ---------- ------------------------- --- ---------- ---------------------------------------- ---------- 54 test_replay_1 NO 65 PREPARE
3.開始執行replay,首先去命令列執行一段指令,然後回到原本的會話執行開始命令。
[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (22:27:21) SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY(); PL/SQL procedure successfully completed.
這個地方如果希望停止replay可以呼叫DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY()來取消replay。
回到replay的視窗，我們可以看到replay已經完成的螢幕輸出。
[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (22:27:21) Replay started (22:27:48) Replay finished (22:28:51)
我們也可以觀察後臺日誌。可以看到在執行完成後，啟動了DM和DW程式進行了一個匯出。
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 04/20/2014 22:27:48 Sun Apr 20 22:28:07 2014 DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 04/20/2014 22:28:06 Sun Apr 20 22:28:08 2014 DM00 started with pid=31, OS id=20650, job SYS.SYS_EXPORT_TABLE_01 Sun Apr 20 22:28:10 2014 DW00 started with pid=32, OS id=20652, wid=1, job SYS.SYS_EXPORT_TABLE_01
我們再觀察一下datadump的目錄，發現replay完成之後，匯出了以WRH$開頭的表。
[oracle@11g dpdump]$ ls -lrt total 88 -rw-r--r-- 1 oracle oinstall 126 Apr 20 22:23 wcr_scapture.wmd -rw-r--r-- 1 oracle oinstall 195 Apr 20 22:23 wcr_fcapture.wmd -rw-r--r-- 1 oracle oinstall 11224 Apr 20 22:23 wcr_cr.text -rw-r--r-- 1 oracle oinstall 25514 Apr 20 22:23 wcr_cr.html -rw-r--r-- 1 oracle oinstall 1044 Apr 20 22:23 wcr_56ryah0000006.rec -rw-r--r-- 1 oracle oinstall 930 Apr 20 22:23 wcr_56rxyh0000005.rec -rw-r--r-- 1 oracle oinstall 1030 Apr 20 22:23 wcr_56rx6h0000004.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 22:23 wcr_56rx0h0000003.rec -rw-r--r-- 1 oracle oinstall 785 Apr 20 22:23 wcr_56rwuh0000002.rec -rw-r--r-- 1 oracle oinstall 4644 Apr 20 22:23 wcr_56rwrh0000001.rec -rw-r--r-- 1 oracle oinstall 3190 Apr 20 22:23 wcr_56rwjh0000000.rec drwxr-xr-x 2 oracle oinstall 4096 Apr 20 22:25 pp11.2.0.4.0 drwxr-xr-x 2 oracle oinstall 4096 Apr 20 22:28 rep366872083 [oracle@11g dpdump]$ cd rep366872083 [oracle@11g rep366872083]$ ls -lrt total 9168 -rw-r--r-- 1 oracle oinstall 827 Apr 20 22:28 wcr_replay.wmd -rw-r--r-- 1 oracle oinstall 15671 Apr 20 22:28 wcr_ra_366872083.log -rw-r----- 1 oracle oinstall 9326592 Apr 20 22:28 wcr_ra_366872083.dmp -rw-r----- 1 oracle oinstall 12288 Apr 20 22:28 wcr_rep_uc_graph_366872083.extb -rw-r--r-- 1 oracle oinstall 8412 Apr 20 22:28 wcr_rr_366872083.xml
最後驗證下我們的資料。
SQL> connect test/test Connected. SQL> select * from t1; NAME -------------------------------------------------------------------------------- Name for 1 Name for 2 Name for 3 Name for 4 Name for 5 Name for 6 Name for 7 Name for 8 Name for 9 Name for 10 10 rows selected.
四、生成報告
最後一步就是我們做完了replay，需要做個報告的對比，這裡我們可以透過awr報告進行對比，也可以使用自帶的包生成對比報告。如下所示：
SQL> select id,name,status,start_time,end_time,user_calls,dir_path from dba_workload_replays; ID NAME STATUS START_TIM END_TIME USER_CALLS DIR_PATH ---------- -------------------- -------------------- --------- --------- ---------- ---------------------------------------- 54 test_replay_1 COMPLETED 20-APR-14 20-APR-14 56 /oracle/app/oracle/admin/ora11/dpdump/ SQL> set long 999999 SQL> select dbms_workload_replay.report(54,'TEXT') from dual; DBMS_WORKLOAD_REPLAY.REPORT(54,'TEXT') -------------------------------------------------------------------------------- DB Replay Report for test_replay_1 -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------- | DB Name | DB Id | Release | RAC | Replay Name | Replay Status | -------------------------------------------------------------------------- | ORA11 | 753808255 | 11.2.0.4.0 | NO | test_replay_1 | COMPLETED | -------------------------------------------------------------------------- Replay Information -------------------------------------------------------------------------------- ------------------- | Information | Replay | Capture | -------------------------------------------------------------------------------- ------------------- | Name | test_replay_1 | test_capture_1 | -------------------------------------------------------------------------------- ------------------- | Status | COMPLETED | COMPLETED | -------------------------------------------------------------------------------- ------------------- | Database Name | ORA11 | ORCL | -------------------------------------------------------------------------------- ------------------- | Database | 11.2.0.4.0 | 10.2.0.5.0 | | Version | | | -------------------------------------------------------------------------------- ------------------- | Start Time | 20-04-14 14:27:48 | 20-04-14 13:47:58 | -------------------------------------------------------------------------------- ------------------- | End Time | 20-04-14 14:28:07 | 20-04-14 13:48:59 | -------------------------------------------------------------------------------- ------------------- | Duration | 19 seconds | 1 minute 1 second | -------------------------------------------------------------------------------- ------------------- | Directory | DATA_PUMP_DIR | DATA_PUMP_DIR | | Object | | | -------------------------------------------------------------------------------- ------------------- | Directory | /oracle/app/oracle/admin/ora11/dpdump/ | /oracle/app/oracle/ad min/ora11/dpdump/ | | Path | | | -------------------------------------------------------------------------------- ------------------- Replay Options --------------------------------------------------------- | Option Name | Value | --------------------------------------------------------- | Synchronization | SCN | --------------------------------------------------------- | Connect Time | 100% | --------------------------------------------------------- | Think Time | 100% | --------------------------------------------------------- | Think Time Auto Correct | TRUE | --------------------------------------------------------- | Number of WRC Clients | 1 (1 Completed, 0 Running ) | --------------------------------------------------------- Replay Statistics ------------------------------------------------------------ | Statistic | Replay | Capture | ------------------------------------------------------------ | DB Time | 0.451 seconds | 0.026 seconds | ------------------------------------------------------------ | Average Active Sessions | .02 | 0 | ------------------------------------------------------------ | User calls | 56 | 56 | ------------------------------------------------------------ | Network Time | 0.084 seconds | N/A | ------------------------------------------------------------ | Think Time | 13.829 seconds | N/A | ------------------------------------------------------------ Replay Divergence Summary ------------------------------------------------------------------- | Divergence Type | Count | % Total | ------------------------------------------------------------------- | Session Failures During Replay | 0 | 0.00 | ------------------------------------------------------------------- | Errors No Longer Seen During Replay | 0 | 0.00 | ------------------------------------------------------------------- | New Errors Seen During Replay | 0 | 0.00 | ------------------------------------------------------------------- | Errors Mutated During Replay | 0 | 0.00 | ------------------------------------------------------------------- | DMLs with Different Number of Rows Modified | 0 | 0.00 | ------------------------------------------------------------------- | SELECTs with Different Number of Rows Fetched | 0 | 0.00 | ------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------- Workload Profile Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top Service/Module/Action -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top SQL with Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Top Sessions with Top Events -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Divergence Session Failures By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Error Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By SQL -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Session -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- DML Data Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By SQL -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Divergence magnitude -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- SELECT Data Divergence By Application -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- By Divergence magnitude -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Clients Alerts -------------------------------------------------- | No data exists for this section of the report. | -------------------------------------------------- Replay Filters -------------------------------------------------- | No data exists for this section of the report. | --------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29960155/viewspace-1310473/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 執行 測試
- MySQL行級鎖測試MySql
- 執行緒和鎖,鎖升級執行緒
- A/B 測試：概念 ≠ 執行
- java執行緒測試Java執行緒
- Oracle replayc測試Oracle
- Oracle Database ReplayOracleDatabase
- 並行執行的學習與測試並行
- 使用 HTTPie 進行 API 測試HTTPAPI
- 使用PostMan進行API測試PostmanAPI
- postman進行http介面測試PostmanHTTP
- webbench進行壓力測試Web
- oracle dataguard 進行switchover測試Oracle
- postman工具進行介面測試Postman
- 對avalon的類名操作進行升級
- H5測試||測試執行階段測啥H5
- oracle rat database replayOracleDatabase
- 通用無線測試儀MT8870A為物聯網/M2M進行功能升級
- httprunner（11）執行測試報告HTTP測試報告
- Jest如何有序地執行測試
- 使用Gradle執行整合測試Gradle
- junit執行多個測試類
- 使用 MeterSphere 進行 Dubbo 介面測試
- 使用JUnit進行單元測試
- 使用Wiremock進行整合測試 - kubilayREMMock
- 使用Jmeter進行http介面測試JMeterHTTP
- 【java】使用jprofiler進行效能測試Java
- pytest多程式/多執行緒執行測試用例執行緒
- 使用console進行效能測試和計算程式碼執行時間
- 工業執行平穩提質升級XG
- 使用web client對 vcenter 進行補丁升級Webclient
- 使用Out of Place升級策略進行Oracle Patch操作Oracle
- 介面測試怎麼進行,如何做好介面測試
- python進階（15）多執行緒與多程式效率測試Python執行緒
- Linux命令執行時間測試Linux
- 如何執行指定的單元測試
- 程式池、執行緒池效率測試執行緒
- 請教，執行測試JdonRails出錯AI