“The art of progress is to preserve order amid change.” Alfred North Whitehead

Managing Change with Real Application Testing and Snapshot StandbyBarry HodgesSenior Solution Architect, Sales Consulting, Oracle NZ

“The art of progress is to preserve order amid change.” Alfred North Whitehead British Mathematician and Philosopher, 1861-1947

IT Challenges

Motivation for change Application Tier Application Upgrades, Object Modifications, etc Database Tier Database Upgrades, Patches, Schema, Parameters, RAC nodes, Interconnect, OS Platforms, OS Upgrades, CPU, Memory, etc Storage Tier Storage Tier SAN Upgrades, ASM, more disk, etc

Majority of effort spent in developing tests, detecting and remediating regressions Database Upgrade Process: Steps • Gather information about PROD environment • Determine upgrade path & method • Prepare B/R strategy, clone and setup Test DB • Establish performance baseline before upgrade • Develop tests for database & applications • Test upgraded database & applications • Check performance before & after upgrade • Remediate regressions • Go Live!

Requires specialised application knowledge Need to provision the whole stack Simulated load 80/20? BA’s id top n business transactions TEST What is the correct throughput? Test Scripts need to be constantly rewritten as data changes Can we trust the simulated load? Simulate load by over throttling? Can we account for selects? Current Testing Methodology PRODUCTION Fail

Majority of effort spent in developing tests, detecting and remediating regressions Database Upgrade Process: Steps • Gather information about PROD environment • Determine upgrade path & method • Prepare B/R strategy, clone and setup Test DB • Establish performance baseline before upgrade • Develop tests for database & applications • Test upgraded database & applications • Check performance before & after upgrade • Remediate regressions • Go Live! Real Application Testing provides realistic testing for real-world systems

Capture Workload in Production • Capture full production workload • Move the captured workload to test system • Replay Workload in Test • Make the desired changes in test system • Replay workload with with production load, timing & concurrency characteristics • Honor commit ordering • Analyze & Report • Errors • Data divergence • Performance divergence Database Replay • Replay actual production database workload in test environment • Identify, analyse and fix potential instabilities before making changes to production

SQL • Capture SQL in Production • Capture SQL workload in production including statistics & binds • Move the SQL Tuning Set to test system • Re-execute SQL queries in Test • Make the desired changes in test system • Statements can be re-executed as often as is necessary in the test environment • Analyze & Report • Highlight improvements • Highlight regressions • Highlight highest impact statements • Use SQL Tuning Advisor to tune regressed statements SQL Performance Analyzer • Identify SQL performance regression before end-users are impacted • Identify, analyse and fix potential instabilities before making changes to production

Supported Changes Application Tier Unsupported Application Upgrades, Some Object Modifications Database Tier Database Upgrades (9i, 10g  11g), Patches, Schema, Parameters, RAC nodes, Interconnect, OS Platforms, OS Upgrades, CPU, Memory Supported Storage Tier SAN Upgrades, ASM, more disk, etc

Effort (Days) Understand Application Usage Install & Setup Generate Workload Run Test Identify Key Transactions Current Testing Methodology Total Testing Time • DB Replay: 2 weeks • LoadRunner: 30 weeks

Real Application Testing Demo Production (wgn1) Dell OPTIPLEX 755 Intel(R) Dual Core(TM)2 Duo CPU @ 2.33GHz 4 GB RAM Oracle Enterprise Linux 5.1 Oracle 11.1.0.6 Standby (wgn1sby)

Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby)

Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) DGMGRL> convert database 'sid' to snapshot standby;

SQL Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) Capture

Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) Prepare

SQL Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) Replay

Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) Report & Analyze

Data Guard Real Application Testing Demo Production (wgn1) Standby (wgn1sby) DGMGRL> convert database 'sid' to physical standby;

Value Proposition Oracle DB Replay & SPA 3rd Party SQL Testing Tools Capture with low overhead Yes No require explicit polling & external storage Test with production context Yes No not integrated with optimizer Transfer resolutions to production Yes Integrated with AWR, ASH, SQL Tuning Advisor & SQL Plan Management No • Technology that can only be built by Oracle

Weeks Days Value Proposition From: To: Days of development Months of development Production workloads Artificial workloads Complete workflows Partial workflows Automated Manual intensive Database Tier Complete Stack Low risk High risk

Resources • Oracle.com:http://www.oracle.com/database/real-application-testing.html • Oracle Real Application Testing on OTN: http://www.oracle.com/technology/products/manageability/database/index.html • Oracle Real Application Testing white papers: http://www.oracle.com/technology/products/database/oracle11g/pdf/real-application-testing-whitepaper.pdf • http://www.oracle.com/technology/products/manageability/database/pdf/ow07/db_replay_white_paper_ow07_1.pdf • http://www.oracle.com/technology/products/manageability/database/pdf/ow07/spa_white_paper_ow07.pdf

“The art of progress is to preserve order amid change.” Alfred North Whitehead