The concept of change is almost universal. It is inevitable and it is cumbersome. Same goes for Changes in IT systems and database systems as well. Whether you want to change a small parameter value or you want to upgrade the underlying operating system or if you want to upgrade the database server itself the continuous fear of breaking something hangs over your head all the time. So a great amount of time is spent on testing to avoid any possible pitfalls. How you would like to test that the new parameter value, the new operating system or the newer version of database server will not break anything in your database? The best solution would be to replicate the entire workload of production database to your test system with new configurations in place before you actually do anything to your production database. Database Replay, a new feature in Oracle 11g, does exactly this and more.
[title type=”h2″ class=”tfuse”]Different from third party tools…[/title] There are many third party tools available which can perform testing on test systems before new configurations can be moved to production. The problem with these tools is that they repeat a set of SQL statements which you provide as many times as you want them to. This is hardly a real world scenario because some statements can only test part of database workload not the entire database’s workload. For these third party tools to work efficiently you will have to provide them with almost every statement passed to database by yourself. This is something that is next to impossible if not impossible.
On the other hand this is exactly the purpose of Database Replay. Database Replay is integrated into the database and when needed can capture the entire workload of the database. And then it applies the same workload on the test system which has the new configurations. Thus the new system is tested with same workload that is currently on your production database but with configurations that you want to have in future. Thus it enables you to get in concrete position to go with the change or to avoid it, no more speculations or assumptions.
[title type=”h2″ class=”tfuse”]Part of Real Application Testing…[/title] Database Replay is one half of Real Application Testing feature. The other part is known as SQL Performance Analyser. The fundamental difference is that database replay attempts to capture the workload of entire database while SQL performance analyser focuses more on SQLs and tries to improve SQL tuning. You cannot view and thus take any action on individual SQL statements in Database Replay while you can do this in SQL Performance Analyser.
Together these two features enable you the whole database system in advance before you move your new configurations into production.
[title type=”h2″ class=”tfuse”]How it works…[/title] The whole process consists of four steps which are best described by picture below. Ideally there will be two systems, one will be your existing production and the other will be the test system with your new configurations. But you can do this on one system as well. The ideal use case of one system scenario will be the change in initialization parameter value and you want to test how it affects the system.
Here are the four different steps:
- Capture Workload
- Pre-process the Workload
- Replay the Workload
- Analysing Reports
The first step is to capture the workload on your production system. The process is very simple and actual capturing is done without DBA’s intervention. Database Replay captures the workload in binary format and stores the files in a specified directory. So first step will be to create a database directory which should correspond to an empty operating system directory. Once that is done you can use the Enterprise Manager or command line interface to start the capture process. You will have to answer a few simple questions and apply filters if you want to. Filters can be specified to limit the capture like you can instruct it to capture only workload for Sys user etc.
The second step is to pre-process the workload generated in the first step. This is important because in order to Replay the workload, you will have to make changes in configuration settings like database name etc. Also there may be objects which will be on your production system but not on your test. And leaving them unattended or unchanged will cause the Replay to throw exceptions.
Once your workload is ready, you can start replaying that workload on your test system. This can also be done either using Database Control or command line. Again you just have to start the process and no further intervention is required in replaying the workload.
The last step is to analyse reports generated by replay process. This is the actual outcome of entire process and upon which decision of implementing or leaving the new proposed change will depend upon. There are variety of different reports available after the process has completed successfully which can help you figuring out how test system performed with production workload.