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

I did a migration like this in 2000 - roughly in the following steps : - migrate from raw device to JFS (using dd piped over ftp in parallel spread over all nodes to the new machine)
- recreate controlfile and fire up DB on the new machine with single instance settings
- create a new DB next to the old DB on the new server (gave us a chance to increase the blocksize)
- figure out your new storage parameters for all segments (freelist groups, freelists, pctfree/used, initial/next, parallel degree (instance), buffer pools, partitioning)

• with ASSM and LMT tablespaces this step is now fairly easy - you should definitely go for LMT now - consider ASSM if you're not dealing with a huge datawarehouse mainly doing hash joins and FTS. FTS gets a performance hit with ASSM. - the index file is quite handy for things like this : I used it to
• precreate my tables just the way I like them and adjusted to single instance
• creating my indexes manually, but in parallel
• hacking away in the indexfile can be a tedious task, even for the ksh/awk/sed gurus among us, so I used Pretoria (http://sourceforge.net/projects/pretoria) which basically does some pretty printing on it, separating tables/indexes/constraints and replacing of storage parameters - after precreating the tables I basically did an export import between my old and my new database (in parallel over named pipes using http://sourceforge.net/projects/pepi) I have a copy now that does the same between 2 servers over the network (using dd and rsh/rexec )- so you don't need to clone your old database to your new server (this eliminates step 1 and 2) - after the import - create indexes in parallel - constraints in parallel (except FK) and last FK constraints

So the only thing you indeed need to worry about is :

- RBS
- redo threads ( x -> 1)
- freelist groups (x -> 1)
- parallel degree (inter instance + intra instance)
- instance settings (DLM settings etc)
- anythings I forgot ?

I would never choose to upgrade directly in your situation - recreate the DB and do an exp/imp !
It's a great opportunity to do some heavy housekeeping in your database (add partitions, go to LMT/ASSM, different blocksizes, etc) Good luck and have fun !

"David Fitzjarrell" <fitzjarrell_at_cox.net> wrote in message news:9711ade0.0407011506.11014bc6_at_posting.google.com...
> 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 Fri Jul 02 2004 - 09:50:41 CDT