Oracle11g Best Practices with Thomas Kyte

Today I had the opportunity of assisting to this presentation at Bridgewater NJ with Thomas Kyte, as usual Tom's presentation was excellent, he always transmit that sense that he knows what we go through day to day and you actually feel identified with him , he has that something that you only get with many years of been in the field and experienced that, that something that you don't get just reading books, keeps you attention and make you have a good time. I wanted to share with you some of the points he covered and I consider very important. As a matter of fact I will look to implement some of this technologies / features in the next few months and offer them as part of the portfolio of services we have at OraProfessionals.

- Tom suggested the use of Physical DG in 11g for Testing, in 11gR2 you can use your physical DG for doing destructive test, after you are done you Flashback the DB to the point before the test and continue business as usual. I think this is great as I have plenty of DG databases, however I struggle with the developers to get time on the test database.

- Tom suggested as well the use of logical standby as a reporting solution, and I ask myself why no more people is using this technology. Nowadays probably the most common solution for a reporting server is Oracle Streams, however we know how fragile and heavy to maintain it can be. We rarely have any problems with Oracle DG, however I can't say the same for streams.

- 11gR2 will allow you to re-route in an automatic way your read only queries to a standby database. This will make the developers happy as they won't need to change their application code. Apparently this is configurable so if your standby is behind more than so many minutes, Oracle will resolve the query on the primary database.

- 11gR2 has a feature when using Real Application Testing (RAT) that allows you to use the sql performance analyzer (dbms_sqlpa) to compare the performance of your regressed SQLs with the new ones (assuming you are testing some database changes). RAT is one of the technologies that we plan to explore in the next few months. I want to clarify that RAT is not the product that will allow you to test some application functionality, this product will just tell you how your database will behave with similar workload. RAT requires for your objects in the source and the target to be identical.

- 11gR2 also has a Data Recovery advisor, you don't longer have to think what are the different ways that you can recover the database and try to determinate which one is the most efficient. The advisor will layout your options and give you an educated guesstimate of how long it will take for each option. RMAN will even create the script for you to restore depending of the option you selected; To me this is a priceless feature, I lost track of the times my customers requires to know how long will it take to restore their databases, and the answer is not always easy to provide due to the variables involved.

- Use of flashback database for application upgrades: You upgrade your application, if something goes wrong you flashback to the point before the upgrade. I actually like very much this idea, however be careful if you do not have a fast I/O subsystem, as enabling flashback database can deteriorate your performance up to 30%. If your application is sensitive to performance then this may not be a good idea for you.

- Flashback Data Archive: Allows you to have an easily accessible archive of historical data, that you can access using standard SQLs. (looking forward to hear more about it).

- 11g Automatic provisioning of partitions: Very good feature, you actually provision the new partitions once new rows are available to insert on them, so there is no need for the DBA to pre-allocate the partitions. I wish this was available in 1ogR2, as I could immediately use it.

During the meeting Oracle Support spoke about the upgrade paths to 10gR2, something to keep in mind If you are upgrading from a version such as 10.2.0.1, you can upgrade to 11g directly, however if you need to downgrade you need to go to 10.2.0.3 and from there to your original release, ex: 10.2.0.1

11g bring several nice new features, however I have been told that Oracle 11g brings as well stability, Oracle solved several bugs, and enhanced features that were difficult to work with.

Enjoy the rest of the summer :-)







-