Re: Oracle 8i OPS migration to Oracle 9i R2

gautrak_at_yahoo.com (Gautrak) wrote in message news:<76ceef56.0407010425.4708efda_at_posting.google.com>...
> Hi,
>
> We need to migrate Oracle 8.1.7 OPS database on solaris to Oracle 9i
> R2 single instance on new solaris box. Our db size is 100 GB but used
> space is approx 40 GB. time is not a constraint. what are the pros and
> cons of each of the following approaches :
> 1. install 8i on new box, copy data files, make other necessary
> changes and up this database in single instance mode. Then install
> oracle 9i on this box, upgrade 8i db to 9i.
> 2. Directly install 9i on new box, export user wise from 8i original
> db and import in 9i. take care of public synonyms, roles etc which are
> not covered in this export/import.
>
> Or should we follow another better approach ?
>
> TIA,
> Gautrak

You're ignoring a lot with your proposed plan, since you're running OPS and any applications using that configuration should be written with OPS in mind. This also includes such things as multiple freelist groups, degree of parallelism of the tables/indexes, additional rollback segments, etc. Option 1 won't work well because it's STILL a parallel instance, even though you only have one running, as evidenced by the init.ora files necessary to get that instance started. You would need to at least comment out the init.ora entries necessary for OPS before starting a single instance of an OPS configuration if you hope to upgrade that instance to a single 9i one; even then, you haven't addressed the multiple freelist groups, the degree of parallelism or the additional rollback segments present. Option 2 is a better approach, however you still have the same areas of concern I listed before (multiple freelist groups, degree of parallelism of tables/indexes and additional rollback segments).

This is a far larger task than one might expect; Howard J. Rogers commented on another post for this same type of conversion and mentioned all I've mentioned above, and a bit more. Simply exporting and importing will transfer tables/indexes/data from one server to another, but also transfers the table creation parameters as well, and for an OPS configuration table creation is a different beast than for a stand-alone instance, as multiple instances accessing them must be accounted for, hence the multiple freelist groups and the increased degree of parallelism.

An export is a good place to start on this journey; using imp with the indexfile parameter will provide you a table and index creation script you may edit to alter any parameters specific to OPS (the create table statements are present, however they are remarked so they won't execute; remove the REM statements to 'enable' these statements). Run this modified script in your new 9i instance to create empty tables (and their indexes), with more appropriate storage parameters for a single, stand-alone instance. (It should be understood that tablespaces, users and undo should be pre-created in your 9i instance prior to executing any export/import of data.) Executing imp once again, this time with the ignore=y parameter, will populate your tables with your data. It should also create the stored procedures, functions, packages, synonyms and any other objects the application user owns. I would follow this procedure for each user with objects in your OPS database.

This says nothing of the applications written to use an OPS configuration; these are likely written to more efficiently use such a configuration, and probably need to be modified. This, of course, is not your job, but needs to be done, nonetheless.

As you can probably see by now, this is not a trivial task, nor one to be taken lightly. I would carefully consider what has been done to make your OPS configuration successful, and learn what you need to 'back out' to convert such an application to use a single, stand-alone instance.

I wish you success in your endeavours.

David Fitzjarrell Received on Thu Jul 01 2004 - 18:06:47 CDT