oracle

Oracle FAQs
Some Common Questions about Oracle DBA/System knowledge.
Can you enumerate the differences between the different versions of Oracle (i.e. disadvantages and advantages)?Oracle FAQs
Some Common Questions about Oracle DBA/System knowledge.
Can you enumerate the differences between the different versions of Oracle (i.e. disadvantages and advantages)?
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?
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
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.
Logicwala
http://logicwala.com Powered by Joomla! Generated: 11 January, 2009, 07:41
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.
Logicwala
http://
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?
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
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.
Logicwala
http://logicwala.com Powered by Joomla! Generated: 11 January, 2009, 07:41
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.
Logicwala
http://