There are mainly four phases involved in Oracle Database replay:<\/p>\n
- \n
- Workload capture<\/li>\n
- Workload pre-processing<\/li>\n
- Workload replay<\/li>\n
- Analysis and reporting<\/li>\n<\/ol>\n
![\"Screenshot:](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2013\/05\/Oracle-database-replay-phases.png\")
<\/p>\n1. Workload capture<\/h2>\n
In this phase users will capture the production workload from the database. Enabling the workload capture involves tracking and recording all external requests from users, application etc. and changes will be stored in binary files called capture files. These files will have information such as SCN, SQL Text, bind variables etc.<\/p>\n
Create a directory where you want to keep all your capture files:<\/p>\n
SQL> Create or replace directory\u00a0 db_replay_dir\u00a0 AS ' \/home\/oracle\/tools\/db_replay_dir';<\/pre>\n
Use START_CAPTURE procedure to start workload capture:<\/p>\n
\r\nBEGIN\r\nDBMS_WORKLOAD_CAPTURE.START_CAPTURE (name\u00a0\u00a0\u00a0\u00a0 => \u2018prd_server_capture\u2019,\r\n\t\t\t\tdir\u00a0\u00a0\u00a0\u00a0\u00a0 => \u2018db_replay_dir \u2018,\r\n\t\t\t\tduration => 900,\r\n\t\t\t\tcapture_sts => TRUE,\r\n\t\t\t\tsts_cap_interval => 300);\r\nEND;\r\n\/\r\n<\/pre>\n
If the DURATION<\/strong> parameter value is not specified then workload capture will continue until the FINISH_CAPTURE<\/strong> procedure is used.<\/p>\n
Use capture_sts parameter<\/strong> to capture a SQL tuning set in parallel with the workload capture.<\/p>\n
The default value of STS_CAP_INTERVAL<\/strong> is 300 and this parameter specifies the duration of the SQL tuning set capture.<\/p>\n
Use the FINISH_CAPTURE<\/strong> procedure to stop the workload capture:<\/p>\n
\r\nBEGIN\r\n\tDBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();\r\nEND;\r\n\/<\/pre>\n
To add filters to workload use ADD_FILTER<\/strong> procedure and to delete the filter use DELETE_FILTER<\/strong>.<\/p>\n
2. Workload pre-processing<\/h2>\n
In this phase, information in the capture files will be preprocessed and create all the metadata needed for replaying the workload. This step can be resource and time consuming and it’s recommended to perform this step on a test system where you want to replay the database work load.<\/p>\n
Copy the capture files from production to test server<\/h3>\n
It’s recommended to create a restore point, so that changes can be reverted after replay and same database used for other database work load tests:<\/p>\n
\r\nSQL> CREATE RESTORE POINT before_replay;\r\n<\/pre>\n
Create a directory on the test server where you want to copy the files:<\/p>\n
\r\nSQL> Create or replace directory\u00a0 db_replay_dir\u00a0 AS ' \/home\/oracle\/tools\/db_replay_dir';\r\n<\/pre>\n
Use the PROCESS_CAPTURE<\/strong> procedure to preprocess the captured workload:<\/p>\n
\r\nBEGIN\r\n\tDBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'db_replay_dir');\r\nEND;\r\n\/<\/pre>\n
It’s important that the preprocessing is done with the same database versions. Users must copy the capture files from production to the test system.<\/p>\n
3. Workload replay<\/h2>\n
In this phase users can replay the workload that’s captured and pre-processed. All the external requests which are recorded will be performed on the test system with same time and concurrency.<\/p>\n
Use the INITIALIZE_REPLAY<\/strong> procedure to initialize workload replay:<\/p>\n
\r\nBEGIN\r\n\tDBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(\r\n\t\t\treplay_name=> \u2018prd_server_replay\u2019,\r\n\t\t\treplay_dir => \u2018db_replay_dir\u2019);\r\nEND;\r\n\/\r\n<\/pre>\n
Use the PREPARE_REPLAY<\/strong> procedure to prepare workload replay on the test database system:<\/p>\n
\r\nBEGIN\r\n\tDBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE,\r\n\t\t\t\t\tcapture_sts => TRUE,\r\n\t\t\t\t\t sts_cap_interval => 300);\r\nEND;\r\n\/\r\n<\/pre>\n
The synchronization parameter default value is SCN<\/strong> this means the COMMIT<\/strong> order will be preserved and replay actions will be executed only after all dependent COMMIT<\/strong> actions are complete.<\/p>\n
Before you run the replay procedures make sure to estimate the number of clients, hosts necessary to replay the captured workload using wrc.exe<\/strong> (workload replay client):<\/p>\n
\r\n[oracle@server1]$ wrc\u00a0 mode=calibrate\u00a0 replaydir=\/home\/oracle\/tools\/db_replay_dir\r\n\r\nWorkload Replay Client: Release 11.2.0.1.0 - Production on Thu Apr 11 19:24:01 2013\r\n\r\nCopyright (c) 1982, 2009, Oracle and\/or its affiliates.\u00a0 All rights reserved.\r\n\r\nReport for Workload in: \/ home\/oracle\/tools\/db_replay_dir\r\n-----------------------\r\n\r\nRecommendation:\r\nConsider using at least 2 clients divided among 1 CPU(s).\r\n\r\nWorkload Characteristics:\r\n- max concurrency: 3 sessions\r\n- total number of sessions: 10\r\n\r\nAssumptions:\r\n- 1 client process per 50 concurrent sessions\r\n- 4 client process per CPU\r\n- think time scale = 100\r\n- connect time scale = 100\r\n- synchronization = TRUE\r\n<\/pre>\n
After determining the necessary clients and hosts needed to replay the workload, start the replay clients in replay mode:<\/p>\n
\r\n[oracle@server1]$ rc system\/xxxx@test_db mode=replay replaydir=\/home\/oracle\/tools\/db_replay_dir\r\nWorkload Replay Client: Release 11.2.0.1.0 - Production on Thu Apr 11 19:28:32 2013\r\n\r\nCopyright (c) 1982, 2009, Oracle and\/or its affiliates.\u00a0 All rights reserved.\r\n\r\nWait for the replay to start (19:28:32)\r\n<\/pre>\n
Use the START_REPLAY<\/strong> procedure to start a workload replay:<\/p>\n
\r\nBEGIN\r\n\tDBMS_WORKLOAD_REPLAY.START_REPLAY ();\r\nEND;\r\n\/\r\n<\/pre>\n
4. Analysis and Reporting:<\/h2>\n
In this phase users will perform detailed analysis of the reports from both the workload capture and workload replay phases. These reports will include information about errors, statistics about database time, average time spent, user call,\u00a0 session info and data divergence.<\/p>\n
For advanced analysis and comparison users can use AWR (Automatic work load repository) reports and SQL Performance Analyzer to compare the SQL tuning set from capture and replay.<\/p>\n
Query the DBA_WORKLOAD_REPLAYS<\/strong> view to see the information about all the workload replays.<\/p>\n
Use DBMS_WORKLOAD_CAPTURE.REPORT<\/strong> function to generate Workload capture report:<\/p>\n
\r\nDECLARE\r\n\tV_CAPID\t\tNUMBER;\r\n\tV_REPORT\tCLOB;\r\nBEGIN\r\n\tV_CAPID\u00a0 := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => \u2018db_replay_dir\u2019);\r\n\tV_REPORT := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => V_CAPID,\r\n\t\t\t\tformat => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);\r\nEND;\r\n\/\r\n<\/pre>\n
Where:<\/p>\n
- \n
- dir<\/strong> specifies the directory which contains the workload capture files<\/li>\n
- capture_id<\/strong> specifies the ID\u00a0 related to the directory which contains the workload capture files<\/li>\n<\/ul>\n
To get the capture_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO<\/strong> function.<\/p>\n
The report contains the information about workload capture such as filters, date, time, SCN of capture and overall statistics such as total database time captures, no of logins and transactions and any limitations due to version etc.<\/p>\n
Use DBMS_WORKLOAD_REPLAY.REPORT<\/strong> function to generate Workload replay report:<\/p>\n
\r\nDECLARE\r\n\tV_REPOTT\tCLOB;\r\n\tV_CAPID\t\tNUMBER;\r\n\tV_REPID\t\tNUMBER;\r\n\r\nBEGIN\r\nV_CAPID:= DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => \u2018db_replay_dir\u2019);\r\nSELECT MAX (id) \u00a0\u00a0INTO\u00a0\u00a0 V_REPID\r\n\tFROM\tdba_workload_replays\r\n\tWHERE\tcapture_id = V_CAPID;\r\nV_REPORT:= DBMS_WORKLOAD_REPLAY.REPORT(\r\n\t\t\t\treplay_id => V_REPID\r\n\t\t\t\t format\u00a0\u00a0\u00a0\u00a0 => \r\nDBMS_WORKLOAD_REPLAY.TYPE_HTML);\r\nEND;\r\n\/\r\n<\/pre>\n
Where:<\/p>\n
- \n
- dir<\/strong> specifies the directory which contains the workload replay files.<\/li>\n
- replay_id<\/strong> specifies the ID\u00a0 related to the directory which contains the workload replay files. To get the replay_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.<\/li>\n
- Format<\/strong>specifies the report format and three formats available.<\/li>\n<\/ul>\n
To Generate a TEXT report use DBMS_WORKLOAD_REPLAY.TYPE_TEXT
\nTo Generate a HTML report use DBMS_WORKLOAD_REPLAY.TYPE_HTML
\nTo Generate a XML report use\u00a0\u00a0 DBMS_WORKLOAD_REPLAY.TYPE_XML<\/p>\nThe report contains the information about workload replay and capture such as job name, duration, status, time, path, database information, replay clients and overall statistics like total database time captured and replayed, no of logins, transactions, replay divergence, error divergence etc.<\/p>\n
Once the analysis is competed, users are able to restore the database to its original state and can perform other replay tests.<\/p>\n
If you have created a restore point use it to restore the database, otherwise use your back to restore.<\/p>\n
Shutdown the database and startup in MOUNT state<\/strong>:<\/p>\n
\r\nSQL> SHUTDOWN IMMEDIATE;\r\nSQL> STARTUP MOUNT;\r\n<\/pre>\n
Restore the database to restore point and open the database with reset logs:<\/p>\n
\r\nSQL> FLASHBACK DATBASE TO RESTORE POINT before_replay;\r\nSQL> ALTER DATABASE OPEN RESETLOGS;\r\n<\/pre>\n
The users can use Enterprise manager\u00a0for Database replay, it’s a user friendly interface and users that don\u2019t have much experience using the command line can use EM for Database replay.<\/p>\n
The Database replay can be located under \u201cSoftware and Support\u201d:<\/p>\n
![\"Screenshot:](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2013\/05\/Oracle-enterprise-manager-database-replay.png\")
<\/p>\nConclusion<\/h2>\n
Database replay can be used to simulate actual production load and test performance as well as application functionality.\u00a0 It helps business to eliminate the risks associated with the change and adopt changes quickly in an easy & cost-effective way.<\/p>\n","protected":false},"excerpt":{"rendered":"
Database replay was introduced in Oracle 11g R1, and it allows you to capture workloads on a Production system and replay them in your Development or test environments while maintaining the unique characteristics of the workload. This enables the user to test a variety of system changes such as hardware\/software migration, operating system\/ database upgrade, patches, database config changes etc…<\/p>\n","protected":false},"author":316203,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73239","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316203"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73239"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73239\/revisions"}],"predecessor-version":[{"id":91720,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73239\/revisions\/91720"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73239"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- replay_id<\/strong> specifies the ID\u00a0 related to the directory which contains the workload replay files. To get the replay_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.<\/li>\n
- capture_id<\/strong> specifies the ID\u00a0 related to the directory which contains the workload capture files<\/li>\n<\/ul>\n
- dir<\/strong> specifies the directory which contains the workload capture files<\/li>\n