Database News You Can Use
A monthly newsletter for Oracle users
If you are thinking of upgrading your database to Oracle 10g, you won’t want to miss our latest white paper. Written by Roger Schrag, Database Specialists founder, this paper is a real-life case study of our experience upgrading a mission critical Oracle 8i system to Oracle 10g.
On one hand, many DBAs would like to upgrade their databases to the newest Oracle release so that they can put the newest features to work. On the other hand, most conservative DBAs are hesitant to take a system that is running perfectly well and put it at risk by moving to the bleeding edge of Oracle’s latest offering.
In this white paper, Roger shares the upgrade strategy we chose and our overall impressions of the upgrade experience. You will learn in great detail about upgrade issues such as SGA sizing, query optimization changes, increased overhead, and overall performance results after the upgrade was completed. You’ll find the white paper at http://dbspecialists.wpengine.com/presentations.html#case_study_10g.
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Hotsos Performance Symposium
This month’s question comes to us from Gerardo in Minneapolis: We are currently running Oracle 9i and taking conventional hot backups to disk, which are then subsequently written to tape. What are the pros and cons of switching to RMAN, and should we switch?
Ian Jones of the Database Specialists team responds: There are some new and exciting enhancements to RMAN in Oracle 10g which we will mention later, but first let’s look at the Oracle 9i RMAN situation. RMAN provides some technical advantages, but it also suffers from a few drawbacks. Some of the advantages of RMAN over conventional hot backup scripts are:
- RMAN computes checksums for blocks during backups and restores, so block corruptions are found quickly.
- RMAN allows block recovery, which could be faster and less disruptive than recovering an entire data file.
- RMAN does not require tablespaces to be in hot backup mode, which can reduce the amount of redo generation during the backup.
- RMAN does not back up blocks that have never contained data, possibly making your backups smaller.
- RMAN supports incremental backups. We will discuss this feature in more detail later.
- RMAN allows the testing of backups via virtual restores.
- RMAN provides for easy parallelism, possibly reducing the overall backup time.
There are two main drawbacks of using RMAN in Oracle 9i to take backups to disk. First, RMAN does not produce and cannot work with compressed backup files. Since it is common for conventional hot backups to be written through an OS pipe to a compress process, an RMAN backup typically requires significantly more disk space than the equivalent hot backup.
Another issue is that RMAN is simpler to operate if its catalog is current and available—RMAN knows where the backup files and archive logs are located. If you are taking backups to disk and then outside of RMAN separately writing these backups to tape, you often end up having to manually perform the necessary restores before RMAN can use them.
Oracle Corporation continues to enhance RMAN in every release and Oracle 10g is no exception. In the limited space here, I would like to briefly highlight three applicable enhancements:
- RMAN in Oracle 10g is now able to backup and restore using compressed disk files using the “as compressed backupset” syntax. For example:
RMAN> backup as compressed backupset database;
- Prior to Oracle 10g, RMAN incremental backups resulted in smaller backup files but were often not much faster than full backups because RMAN needed to read every block in the database to decide if it needed to be backed up. Oracle 10g provides a way for the database to track which blocks have been modified so RMAN can now take truly incremental backups—just reading and writing the modified blocks. If a database only changes by relatively small amounts on a daily basis this can be a dramatic resource saver. The status of block tracking can be confirmed by querying the v$block_change_tracking view. Tracking takes place in a separately allocated journal file and is specified with a statement such as:
SQL> alter database enable block change tracking using file 'file_path';
According to Metalink note 271197.1, incremental backups are available with Oracle 10g Standard Edition. Previous versions of Oracle required Enterprise Edition for this feature.
- RMAN in Oracle 10g is able to merge an incremental backup into an existing level-0 image copy already on disk. This means we no longer need to hold both the original level-0 backup and the incremental backup on disk, and that subsequent recovery will be faster because the incremental is already applied. This allows you to take regular incremental backups but have the advantage of a recent full level-0 image copy available. Unfortunately, the merging can only take place when using image copies rather than backupsets and image copies cannot (currently) be compressed. Thus at the moment you can either compress your disk backups or merge in an incremental backup—but not both. The merging is performed with a command such as:
RMAN> recover copy of database with tag 'latest_full';
In summary, whether or not you should switch to RMAN depends on which release of Oracle you are using and which features are important to you. In an Oracle 9i environment you might want to stick with conventional hot backups if they are currently implemented and you don’t require any of RMAN’s existing capabilities. However, in an Oracle 10g environment, I would seriously consider switching to RMAN. Disk backups with RMAN are more practical in Oracle 10g and the incremental backup feature may save significant resources.
Database Specialists, Inc. founder and president, Roger Schrag, has been invited to join an exclusive list of recognized leaders in the Oracle industry featured at the third annual Hotsos Symposium. The conference—dedicated to issues of Oracle system performance—takes place March 6-9 in Dallas, Texas. Many well-known authors and speakers in the Oracle arena have been selected to present material on topics such as performance analysis, techniques for designing high performance applications, strategies for capacity planning and performance testing, Oracle troubleshooting, and real-life experiences.
This is the second year Roger has been invited to participate as a presenter at the Hotsos Symposium. For more information about the event, see http://www.hotsos.com/events/SYM05.php?event_id=36. Advance registration discounts apply until February 18, 2005.
If you’re really planning ahead, mark your calendar for IOUG Live! 2005. This is the annual International Oracle Users Group conference dedicated to the education of Oracle users. It’s a great place to meet other Oracle professionals and learn from other users who will share their knowledge and experience in dozens of technical sessions. Database Specialists will surely be there, and we’ll keep you posted on the topics we’ll be presenting in future issues of The Specialist. The event is scheduled for May 1-5, 2005 at the Walt Disney Swan and Dolphin Hotel in Lake Buena Vista, Florida. NoCOUG Elections
This year, Database Specialists continues its active participation on the board of directors for the Northern California Oracle Users Group (NoCOUG), a 400+ member organization that is dedicated to offering education, information sharing, and networking opportunities for users of Oracle products. Lisa Loper, VP of Operations at Database Specialists was elected again as NoCOUG Journal Editor. Roger Schrag, President of Database Specialists, was elected as Director of Conference Programming. Roger served as president of NoCOUG for the past two years. This year he will focus on conference content and continuing to recruit speakers such as Tom Kyte, Gaja Vaidyanatha, and Cary Millsap. For more information about NoCOUG, visit http://www.nocoug.org.