MIGRATION ISSUES - 7.3.4 to 8.1.6 on NT

[nabaig]

I have a 10GB 7.3.4 on a NT server and I am in the process of upgrading it. First - testing the upgrade on a test server.

I have the following questions:

1. If i use the export/import method to upgrade, performance takes a big beating. Oracle Docs - Migration utility guide also says that. Is this true and has anybody faced it before.

2. Second try - On the existing 7.3.4 server, I installed 8.1.6 server and requested that Migration assistant kick off after install. The migration assistant failed and I lost the 7 database also. No controlfiles, datafiles etc. Since this was a test machine, it was OK.

3. What is the best and least troublesome way to do this migration other than export import?

Please go adding all your experiences, recommendations, watch it's as we go along.

Thanks,

Nizar

[jmodic]

10 GB is rather small database, so exp/imp path would be usable. However compared to migration utility/assistant it is very cumbersome: you have to create database first, probably also create tablespaces and then perform import. But it would be much slower than using migration utility/assistant.

Unless you want to change db_block_size during migration I would allways go with migration utility/assistant. I've done it both ways many time, I haven't had any severe problems with either of them. What do you mean by "The migration assistant failed and I lost the 7 database also"? Failed how? Have you got any error messages, either on screen or in the migration log file? What have you lost? Tablespaces, datafiles, what? What do you mean by lost - files do not exist where they used to be or what?

You can allways rerun migration assistant if it doesn't succed at first. Have you followed all the pre-migration requirenments as per documentation?

[nabaig]

Well, when i said I lost the 7 database, I meant all my control files for both the 7 and 8 database were missing.

Oracle docs state that when you do a exp/imp to migrate frpm 7 to 8, the file headres remain unchanged; i.e, they stay in a 7 format. Does this cause performance issues?

Thanks,
Nizar

[jmodic]

Originally posted by nabaig
Oracle docs state that when you do a exp/imp to migrate frpm 7 to 8, the file headres remain unchanged; i.e, they stay in a 7 format. Does this cause performance issues?

I must say I don't understand this. If you do exp/imp you will be generating totaly new tablespaces (either by hand prior to import or you let imp to do it for you), hence all the db files will be brand new "initialized" by 8.1 software. I don't understand what they mean by "file headers will remain uncahanged"!? Qould you provide in which manual/chapter have you read this?

[denevge]

1.
Be aware of the fact that oracle only supports migration to the first release. Migration from 7.3.4 to 8.1.6 is supported, migration to 8.1.6.1 ( for instance ) is not.

2.
I recently did the migration from 7.3.4 to 8.1.7, using the mig utility. ( not the GUI approach )
It take's some time to read the docs, but it's worth the trouble.
Everything is good documented in the Migration guide.
I had no problems.

Try to do it this way.

Regards
Gert

[nabaig]

FOR JURIJ!

Oracle Documentation - Migration guide

Chapter 3 - Preparing to migrate.

Section - Choose a migration method.

Here is the actual text:

Perform a full or partial export of an Oracle7 (or version 6) source database, followed by a full or partial import into an Oracle8i target database.

Export/Import can migrate parts of the database. Export/Import leaves datafile headers and actual data unchanged, and makes a new copy of the data.

Also the Section "Export/Import Effects on Migrated Databases" states that:

As a result of data defragmentation, database restructuring by the DBA, and the new Oracle8i software, expect changes in the following areas:

performance

data growth patterns

shared resource usage

data dictionary size

object organization

That is why I want to know from people who used exp/imp to tell me if there was a perf degradation after import to 8.1.6.

Nizar

[jmodic]

Originally posted by nabaig
FOR JURIJ!
...SNIP...
Export/Import leaves datafile headers and actual data unchanged, and makes a new copy of the data.
...SNIP...

Yes, sure, but the datafiles were allready created with 8i software, they have no information about Oracle7 whatsoever when the data is being importd into them! When you perform 8i full import there are only two options you have about the tablespaces:
a) You create them manualy (CREATE TABLESPACE) before you begin to import
b) You let the import process to create all tablespaces in its initial phase. If the datafiles are allready there (original datafiles from Oracle7 database), you must use option DESTROY=Y, which means that imp will simply *overwrite* the existing datafiles, so in efect it will create brand new datafiles.

In both cases the datafile headers don't even have a clue that there once was an Oracle7 database .

...SNIP...
As a result of data defragmentation, database restructuring by the DBA, and the new Oracle8i software, expect changes in the following areas:
performance
data growth patterns
shared resource usage
data dictionary size
object organization
...SNIP...

Of course the changes are to be expected in the specified areas if you use exp/imp. BTW, some (or most) of the above changes are to be expected even when you migrate with the migration utility/assistant. But of course those changes have nothing to do with the datafile headers. As a matter of fact, the first paragraph explicitely states what might be the main causes for the changed behaviour:
a) data defragmentation - as a result of imp process, all the datablocks are freshly "packed" together, which might have very beneficial consequences for the performance
b) database restructuring by the DBA - meaning that if you change the block size of your new database or if you restructure the physical layout of your datafiles it might have impact on your database
c) new Oracle8i software - obvious

...SNIP...
That is why I want to know from people who used exp/imp to tell me if there was a perf degradation after import to 8.1.6.
...SNIP...

I've done it many times (so I'm one of them) and I was just trying to clarify your obvious missconceptions and missunderstanding of how exp/imp works... Once more: datafile headers of fresh 8i database have pure 8i structure, so there could be no performance isues involving those datafile headers.

[nabaig]

Jurij,

Thank you for your detailed explanation.

My problem is, I used export import to migrate from 7.3.4 to 8.1.6 for an application with 30 users. Simple app.
I had acceptable performances when on 7.3.4

After migrating, server is creating excessive sessions (15 for each connected user approx).

Performance has gone down by 30%. Indexes are fine. I am wondering why this drop in perf. Any suggestions, ideas to tune this.

Nizar.

[shawish_sababa]

Hi nabaig

how many tables do you have? why not simply create them in your new db and convert you 7.3. data into text files and insert them useing sqlloader into your 8.1.... db. at this point it looks to me like this is the shortest way to do it.

[nabaig]

I have 350 tables and 700 indexes and 30 views. No way to do this by creating each one indivisually.

Nizar