Database Replay - New feature of Oracle 11g

What is your biggest concern when you need to make a change in the database—be it some minor change, such as altering initialization parameters and database attributes, or major but inevitable ones such as applying patchsets? What about for your upgrade to Oracle Database 11g itself?

For me, the biggest concern is the risk of the change "breaking" something. Even the most minor changes can have a domino effect, eventually producing a visible impact.
To minimize this risk, most shops make the change in a control environment similar to the production one, apply a workload similar to your production system's, and observe the impact. It's rather trivial, at least technologically speaking, to replicate your production system but reproducing the workload is a different story. That's easier said than done.

Most organizations attempt to do that using some third-party load generation tool that can run automatically to simulate real user activity. Although this approach may be adequate in most cases, it's never a truly faithful reproduction of your production database workload. These third-party tools merely execute a pre-written query several times with different parameters; you have to supply the query to the tool and give it a range of parameters that it can use randomly. This is not a representative workload of your production system but rather merely the running of a small part of your production workload executed several times—resulting in as little as 1 percent of your application code being tested. Worst of all, these tools require you to supply all the queries from the production workload yourself, which can take weeks or months for small applications or even up to a year for complex ones.

If you could, wouldn't it be a better approach to record all database operations—DML-related and otherwise—inside the database itself, and then replay them in the very sequence they occurred?

Enter Database Replay
In Oracle Database 11g, your wish is granted, and then some. The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.

Database Replay delivers half of what Oracle calls Oracle Database 11g's "Real Application Testing" option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can't see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)

Read more about this at http://Oracleonline.info/Oracle_11g_data_replay.html