Oracle 10i is just around the corner, but recent surveys show that most of you are still using versions 8 or 8i. If you are considering upgrading from your current version, whatever it is, this Ask the Experts compilation can help. In it, Oracle gurus Brian Peasland, Karen Morton and Eli Leiba point out advantages of updating your software, how it will affect applications, hardware requirements and other issues to be aware of during the move.
Can you enumerate the differences between the different versions of Oracle (i.e. disadvantages and advantages)?Brian Peasland: Oracle databases have been around for quite a while now. Oracle released their 9i (9.0.1) version about a year ago. Many companies are still using the 8i (8.1.x), 8 (8.0.x) and 7.x database versions. For the most part, everything that is available in a lower version will be available in a higher version as well. There are a few minor exceptions, but the Oracle databases tend to be backwards compatible. When Oracle releases a new version, their documentation contains a list of all the features new to that version. So it is worthwhile for the DBA to read this documentation to discover what is new in the database. To give you an idea of what is "new" consider this...Oracle 8i introduced many new features for the developer. With 8i, you could run Java in the database, you had expanded tools to help with object-oriented development and 8i introduced some enhancements to support larger databases (Materialized Views, additions to partitioning). Oracle 9i introduced many new features to help the DBA such as the ability to change database configuration "on the fly", enhanced availability and enhanced managability. The advantage of a higher version is that you have more features and better capabilities. You also stay current with the latest "supported" versions. The disadvantage of these new systems is that you have to convert your older databases to the newer versions. This can sometimes cause application changes as well. The advantage of staying at a lower version is that you know it works and you don't have to change a thing. The disadvantage is that you can't use any of the latest and greatest features and that you may lose support.
What are the main advantages of upgrading from Oracle 7 to Oracle 8i/9i?
Eli Leiba: There are many advantages. Some of them are:
1) adding objects to the database
2) adding new data type such as BLOB/CLOB
3) new administrative options like moving tables between tablespaces
4) many NEW dbms packages
5) writing JAVA code inside Oracle
6) passing parameters to sp with the NOCOPY Option
7) smart sqlloader options
8) can work with array types
9) the new log miner
There is also a tip I wrote about the advantages of 8i that you can also read.
I am trying to find some information on the differences between Oracle 8.1.7 and Oracle 9i. I need to know which features from 8.1.7 are still supported in 9i, and which ones aren't.
Karen Morton: The Oracle Migration Guide, which can be found on Technet, is a good source to find lists of features for the particular version as well as changes to parameters, functions, dynamic performance views etc. There is also a New Features Guide that discusses all the new features of the product, lists desupported/deprecated features for the version and also includes a brief listing of new features added in the previous 1 or 2 versions.
What is the difference between Oracle 8i and Oracle 9i from an application's point of view? What differences between Oracle 8i and Oracle 9i that would require code changes in our application?
Brian Peasland: For a great majority of the time, when you upgrade from one version to the next version, you won't see a big difference. All of the features of 8i are still available in 9i. And of course, 9i includes many new features that you may wish to employ in your application.
The biggest problem applications face after an upgrade deals with the Cost Based Optimizer. Oracle will make many internal changes on how the CBO works. I've seen applications run slower with a new CBO. Some tweaking of INIT.ORA parameters that control the CBO may be necessary. My suggestion is to thoroughly test things out on a test platform before proceeding with your production database.
What is the difference between Oracle 8i and Oracle 9i?
Brian Peasland: Oracle 8i and 9i are different versions of the database. It's like asking what's the difference between Windows 95 and Windows 98! With Oracle each new version contains the same functionality as the previous version. So Oracle 9i is backwards compatible with 8i. Oracle 9i does many things differently internally so they are not exactly the same database engine. And Oracle 9i has a host of new features designed to make the database more stable, easier to manage and increase functionality. There are so many new features that Oracle Corp. provides a complete manual listing these new feature. The Oracle 9i New Features manual can be found on Oracle's Technet site at if you are interested. You will need to register for a free account if you want to view documentation on that site.
We are planning to upgrade to an Oracle 9i server. Is there a way to calculate or find out the hardware requirements we need based on the amount of data/calculations we are currently doing?
Brian Peasland: If you are using Oracle 8 or 8i and you upgrade the database to 9i and you do not change any INIT.ORA parameters, then you do not need any additional hardware requirements to run this database. Upgrading the database does not require more memory or CPU. But you may wish to take advantage of some new features of 9i, and that may require more memory.
The bottom line is, as long as you just upgrade to 9i and don't change your database configuration, then what you have working now, will work after the upgrade.
I will soon be installing 9i database on Sun 5.8. Our development team will be using this database through 9iAS Server loaded on Windows 2000 Server. What else do I need to do other than just load 9i and create a database on the Sun box?
Brian Peasland: You'll also need to set up user accounts for the developers, create tablespaces to hold tables and indexes, and work with the developers to create a robust, scalable application. It is vital that the DBA be involved in all aspects of the application.
What would be the best way to migrate an 8.0.5 database to 8i or 9i? The application that uses the Oracle database is coded on Power Builder.
Brian Peasland: The "best" way to migrate from 8.0 to 9i depends on many factors. Chief among them is the size of the database.
If I recall correctly, in order to go from 8.0 to 9i, you need to upgrade to 8i or 8.0.6 first. So if you want to use the upgrade scripts to upgrade your database, you will have to do this in two steps. Each step is not a particularly long step. If your database is sufficiently large, you will want to employ this method. The amount of time to process the upgrade scripts is not necessarily dependant on the database size. So a 1TB database can be upgraded quickly this way. If you do not want to manually perform these steps, you can use the Database Migration Assistant to perform the upgrades for you. But you'll have to install either Oracle 8.0.6 or 8i software on your machine for the first upgrade step.
Another method is to export/import your database. Export your 8.0.5 database. Then, remove that database and create a brand new instance with the 9i software. You now have a clean, fresh database. Then, import the dump file created above. The migration will be complete. This method can take a real long time if your database is large. With the export/import method of upgrading, the database size directly influences the upgrade time. But with this method, you can change your block size, reorganize your database, and fix chained and migrated rows of data. This may be important to you.
In either case, I strongly suggest that you thoroughly read the Oracle 9i Migration Guide before you proceed. As another tip, make sure to take a backup of your database immediately before the migration, and immediately after the migration is complete.
While upgrading the database from 8.0.6, we exported the database with Full=Y option. Now after installing a fresh database version 8.1.7, while using import of the entire dump, what will happen to the data dictionary and system tables owned by sys and system user?
Brian Peasland: The data dictionary tables owned by SYS are never exported, even if you specify FULL=Y on export. This is because any objects in the data dictionary will be rebuilt when you import into the new database. Don't worry about exporting and importing SYS. The export and import utilities will take care of everything for you.
The SYSTEM user is a little different. While objects owned by SYSTEM may be important, they are not data dictionary objects. So these objects will be exported and imported if performing FULL versions of export and import.