Real Application Testing Certified With E-Business Suite
By Steven Chan (Oracle Development) on Nov 11, 2008
Like Advanced Compression, Oracle Real Application Testing is one of the new Oracle Database 11gR1 Enterprise Edition options that's generating a lot of excitement amongst E-Business Suite users. This separately-licenced 11gR1 Database option has real potential to streamline and accelerate testing cycles for database upgrades.
I'm pleased to announce that Oracle Real Application Testing is certified with Oracle Oracle E-Business Suite Release 11i and 12. E-Business Suite users can follow the standard database documentation for implementing Real Application Testing in their environments.
What's Real Application Testing?
Real Application Testing combines Database Replay, a workload capture and replay feature, with a SQL performance analyzer to help you test database tier changes.
Of the two components in Real Application Testing, I find the Database Replay module to be especially compelling, since this is where some real benefits are to be had. The SQL Performance Analyzer is interesting too, but I'll be focusing on the Database Replay part for the remainder of this article
From the official Database Replay documentation:
When workload capture is enabled, all external client requests directed to Oracle Database are tracked and stored in binary files—called capture files—on the file system. You can specify the location where the capture files will be stored. Once workload capture begins, all external database calls are written to the capture files. The capture files contain all relevant information about the client request, such as SQL text, bind values, and transaction information. Background activities and database scheduler jobs are not captured. These capture files are platform independent and can be transported to another system.
... Once the workload is replayed, in-depth reporting is provided for you to perform detailed analysis of both workload capture and replay.
The report summary provides basic information about the workload capture and replay, such as errors encountered during replay and data divergence in rows returned by DML or SQL queries. A comparison of several statistics—such as database time, average active sessions, and user calls—between the workload capture and the workload replay is also provided. For advanced analysis, Automatic Workload Repository (AWR) reports are available to enable detailed comparison of performance statistics between the workload capture and the workload replay.
How Could Apps Customers Use This?
E-Business Suite customers can use Database Replay to record all database traffic from the EBS applications tier to the database tier during a standard User Acceptance testing cycle. This recorded session can then be "played back" on another snapshot of the pre-testing database. One particularly cool aspect of being able to "replay" the captured database traffic is that no application tier is needed for the second test environment. Important note: capture files can only be replayed on 11gR1 or higher databases.
One possible scenario for testing an EBS database upgrade from the 10.2.0.4 Database to 126.96.36.199:
A. Record a Database Replay session against a "known good" reference environment:
- Take a snapshot of your E-Business Suite 188.8.131.52 + 10.2.0.4 Database testbed.
- Turn on Database Replay and ask your end-users to run their usual User Acceptance Tests on your testbed.
B. Now you're ready to replay that recorded session against the new testbed:
- Upgrade your snapshot to the 184.108.40.206 Database
- Run the recorded Database Replay session against the upgraded database
In this scenario, the upgraded database was tested without either an application tier or the involvement of your end-users.
Other scenarios where this would be useful include:
- Applying Critical Patch Updates to your E-Business Suite database
- Applying emergency patches to your E-Business Suite database
- Testing whether a new database server configuration works the same as the previous one
- Testing a database tier server migration from one platform to another
- Benchmarking the performance of one database server configuration against another
Workload Capture Restrictions
The Real Application Testing documentation notes that the following types of client requests are not captured in a workload:
- Direct path load of data from external files using utilities such as SQL*Loader
- Shared server requests (Oracle MTS)
- Oracle Streams
- Advanced replication streams
- 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)
Your Feedback Requested
Some Oracle technologies require special interoperability patches before they can be used in E-Business Suite environments. Real Application Clusters (RAC) is a good example of this. We provide best practices papers and interoperability patches for those technologies as needed.
Like our eagerly-anticipated 11gR1 Advanced Compression certification, Real Application Testing is transparent to the running application. In other words, Real Application Testing does not require any special interoperability patches or special implementation steps when used with E-Business Suite environments.
That said, our certification and QA teams within Oracle E-Business Suite Development are in the early stages of trying out this technology internally. If we come across any special "gotchas" or useful tips specific to Apps testing, we'll put together a Metalink Note with the details.
Likewise, if you come up with any special tips for using Real Application Testing with Apps, please feel free to post them as comments or email them to me directly.