Oracle upgrade choices

[Printaporn_1]

Hi,

I need your advise , as my situation is:
- Host A Oracle 8.1.6 HP-UX 11.0
- I want to migrate to new server (Host B) with HP-UX 11i,Oracle 9.2.
- I have 20+ Oracle instances the biggest one is 300 GB.

What is the best path to do upgrade in term of downtime and smooth upgrade.
1. upgrade oracle at host a ,install 9i on Host B then copy datafiles.
2. install 8.1.6 in hostB copy datafiles then upgrade to 9.2
3. do export/import.
4. etc....

thanks in advance,

[twang]

Advice from oracle: direct upgrade from Oracle Server 8.1.5 or 8.1.6 to Oracle Server 9.2 is not supported. There are two options to get your database upgraded to 9.2:

1. First upgrade the database to 8.1.7, then upgrade the to 9.2. then upgrade 8.1.7 to 9.2.

2. Use a full database Export in 8.1.6. Recreate the database in 9.2 and import the dumpfile in 9.2.

In making a choice between the above suggested solutions you have to consider
the following:
- Installing 8.1.7 and 9.2 against the 8.1.6 version will require more disk space.
- The time to upgrade to 8.1.7 and 9.2 can be spread in 2 maintenance windows.
- The time to upgrade using the export-import method depends on the amount of data in the database.

Hope that help.

[Alexander M. Ermes]

Hi there.
Depending on the amount of data Twang's idea is ok. We have gone this way upgrading databases from version 7.0 up to 8.1.7
these days. Database sizes were between 20 GB and 70 GB.
Export and import also gives you the advantage to get rid of all the split extensions on the disks.
Rgds
Alexander M. Ermes

[John Flanagan]

I have never worked with a db of this size but I would also consider that it is possible to do a test run using the export/import method leaving your live system in place. Perhaps you could migrate instance by instance.

Gegards,

John.

[Yogeeraj_1]

HI,
*
Please allow me to add to the above replies...
*
I would opt for a migration -- not a export/import if I could. A migration is much faster.
*
However, only Oracle8 release 8.0.6 or later databases and Oracle8i release 8.1.5 or later databases can be upgraded directly to Oracle9i. Oracle8 databases earlier than 8.0.6 must be upgraded to Oracle8 release 8.0.6, and then to Oracle9i. Use the Database Upgrade Assistant when upgrading your data.
*
All Oracle8i versions are supported for upgrading to Oracle9i release 2.
*
I would only use an export import if i really wanted to rebuild the entire database.
*
All_objects, by user -- excluding SYS, SYSTEM and other internal type account, should have the same count after any migration. SYS and SYSTEM and others will have different counts (more objects in the data dictionary with each release).
*
The above referenced document has all of the steps needed.
*
If you choose the export/import -- here are some of the considerations:
*
o a user by user export/import will not export things like the "create user" and system grants (eg: grant DBA, grant CREATE TABLE, etc) for each user. You will have to migrate the "users" your self. (not their data -- their identity).
*
o a user by user export allows for parallel operations. I can export many at the same time, import many at the same time. It can be much much faster -- AND in the event of an error during either imp or exp -- the amount of "lost work" is much smaller. I would rather have trouble importing a single user rather then have trouble importing the entire database.
*
I myself go for the user by user export even though you have to be careful to recreate the user on the new database yourself. It is easy enough with some queries on DBA_USERS to generate the create user statements and DBA_ROLES and such to get the grants for each user.
*
Hope this helps!
*
Regards
Yogeeraj

[Indira Aramandla]

Hi,

From your query I gather that you have two servers with different releases of HP-UX and then you waht to upgrade your oracle 8.1.6 to oracle 9.2.

Now HOST A has HP-UX 11.0 and Oracle 8.1.6 software.

Host B had HP-UX 11i and Oracle 9.2 (64-bit) software.

There are few options available for you. This depends on the disk space available and the down time that you can afford.

Upgrade from 8.1.5 and 8.1.6 direct upgrade to 9.2 is not supported.

If you have enough disk space and wanted to have less down time then one of the options will be :

1) Upgrade to release 8.1.7 from 8.1.6 using the instructions in Oracle8i Migration Release 3(8.1.7).
2) Upgrade the release 8.1.7 database to the new release using the instructions in Oracle9i Database Migration Release 2 (9.2)
Chapter 3, "Upgrading a Database to the New Oracle9i Release".

The above involves installing oracle 8.1.7 and oracle 9.2 software on HOST A in different ORACLE HOMES and then do the upgrade from 8.1.6---->8.1.7----->9.2

Alternatively one can upgrade from 8.1.6 to 9.0.1 and then from 9.0.1 to 9.2.
This will involve installing oracle 9.0.1 and oracle 9.2 software on HOST A in different ORACLE HOMES and then do the upgrade from 8.1.6---->9.0.1----->9.2

If you can afford down time and do not have the disk space then, export the data on HOST A from 8.1.6 and inport onto HOST B into 9.2. This will only require installing oracle 9.2 on HOST B, but will take a while to import the data depending on the data volume. But to quicken the import, run multiple sessions of import for different schema, or for different set of tables for the same schema.

I hope this helps.

Attached is a check list for manuall upgrades for 8i to 9i