Migrating Oracle 7.3.3 to Oracle 8.0.4

Migrating Oracle 7.3.3 to Oracle 8.0.4

Post by Sanjay Han » Sun, 20 Dec 1998 04:00:00



I am planning to do the migration from Oracle 7.3.3 on DEC unix to Oracle
8.0.4 on NT  4.0.  I will be taking full export backup as 'system' user
from oracle 7.3.3 and import it into oracle 8.0.4 after creating the
required tablespaces/rollback segments.

I am not sure for taking the full export backup from oracle 7.3.3, whether
I should set the parameter COMPRESS=Y or N. We are using the Oracle
database as a datawarehouse.  I have found that for some tables in Oracle
7.3.3 the initial extent is around 76 MB and next extent is around 70 MB,
if I use the option COMPRESS=Y, it results in the initial extent size of
around 320 MB for these tables. What is the implication of this parameter?
Whether I should use COMPRESS=Y or N for doing the migration.

Thanks in advance for your help/suggestions

 
 
 

Migrating Oracle 7.3.3 to Oracle 8.0.4

Post by Moto » Sun, 20 Dec 1998 04:00:00


Well, I tend to design and build all my datawarehouses via Oracle
Designer 2000, which includes good facilities for defining
object-level storage parameters. In other words, I'd be pretty upset
if you destroyed most of my hard work with a compress=y.

If the database you are moving is well designed and not heavily
fragmented, then I suggest use compress=n (I always use this). If
you've a bit of fragmentation on certain objects, you can always put
that right afterwards.

If you're db is in a bit of a mess and woud benefit from the
compress=y, then I guess you could use it. But that's not a very good
fix for a longer term problem, IMHO.

MotoX.

On 19 Dec 1998 09:36:08 GMT, "Sanjay Hans"


>I am planning to do the migration from Oracle 7.3.3 on DEC unix to Oracle
>8.0.4 on NT  4.0.  I will be taking full export backup as 'system' user
>from oracle 7.3.3 and import it into oracle 8.0.4 after creating the
>required tablespaces/rollback segments.

>I am not sure for taking the full export backup from oracle 7.3.3, whether
>I should set the parameter COMPRESS=Y or N. We are using the Oracle
>database as a datawarehouse.  I have found that for some tables in Oracle
>7.3.3 the initial extent is around 76 MB and next extent is around 70 MB,
>if I use the option COMPRESS=Y, it results in the initial extent size of
>around 320 MB for these tables. What is the implication of this parameter?
>Whether I should use COMPRESS=Y or N for doing the migration.

>Thanks in advance for your help/suggestions


 
 
 

Migrating Oracle 7.3.3 to Oracle 8.0.4

Post by MarkP286 » Sun, 20 Dec 1998 04:00:00



should set the parameter COMPRESS=Y or N. We are using the Oracle database as a
datawarehouse.  I have found that for some tables in Oracle 7.3.3 the initial
extent is around 76 MB and next extent is around 70 MB, if I use the option
COMPRESS=Y, it results in the initial extent size of around 320 MB for these
tables. What is the implication of this parameter? <<<

To which MotoX replied >>
If the database you are moving is well designed and not heavily fragmented,
then I suggest use compress=n (I always use this).  <<

Migration is a great oppurtunity to re-org your entire database.  Our DBA group
has used platform migrations more than once to re-define the entire data base.
As it is much faster to import without indexes  we predefine all our
application owners and tables.  Then we import the data with indexes=n,
ignore=y, and constraints=n.  Then we build our indexes from a script.  Follow
this with a full import with rows=n to get the constraints, packages, and
public synonyms.  This way you have total control of table and index placement
and sizing.  Doing the export/import this way can reduce the total time
involved for a sizable database from 1/2 to 2/3 (Unix to Unix via pipes).

Anyway, no matter how you do your import I would like to suggest that you
consider creating a special rollback segment to support your import.  You can
force the import to use this segment by taking all others offline.

Mark Powell  -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own
advice -

 
 
 

Migrating Oracle 7.3.3 to Oracle 8.0.4

Post by Moto » Mon, 21 Dec 1998 04:00:00


Mark,

With due respect to the good advice you offered, your comments sort of
drifted a little off the guys original question. A couple of extra
points need adding if he's going to take your advice, which I've
inserted below in the reply you gave. Note that I'm not disagreeing
with any of the advice you gave :-)


Quote:>Migration is a great oppurtunity to re-org your entire database.

But ONLY if this is NEEDED. Hence my warnings in my original reply.

Quote:>Our DBA group
>has used platform migrations more than once to re-define the entire data base.

As above. Don't let DBA's run away with a db until they've spoken with
the designers. I'm lucky, I end up doing both jobs, so I get a lot of
control.

Quote:>As it is much faster to import without indexes  we predefine all our
>application owners and tables.

Which assumes the guy has the original SQL to create the tables in the
appropriate tablespaces already sat on his system. If not, he can grep
them from the export file. Note that the 'clean up' of the storage
parameters can take quite some time for a large number of tables, and
the initial and next will always appear as rounded byte values, no
matter how you originally defined them (K or M)

It's for reasons like this I use D2K. It's worth it's weight in gold,
especially for larger/more complex databases. You can manage the
entire db from a decent tool and dramitically reduce the amount of
messing around you have to do with imp, exp and sql scripts.

Quote:>Then we import the data with indexes=n,
>ignore=y, and constraints=n.  Then we build our indexes from a script.  Follow
>this with a full import with rows=n to get the constraints, packages, and
>public synonyms. This way you have total control of table and index placement
>and sizing.

Again, only if you have all the original SQL or a database design
tool. And again, you can get it from exp files, but be prepared to do
a lot of work for a large(ish) database.

Quote:>Doing the export/import this way can reduce the total time
>involved for a sizable database from 1/2 to 2/3 (Unix to Unix via pipes).

Yep, and that's usually down to the index builds. Maybe worth
mentioning also that bitmapped indexes build much, much faster and use
much less space. So now may be the time to also assess whether they
would be of use on the system (as, of course, they may not).

Quote:

>Anyway, no matter how you do your import I would like to suggest that you
>consider creating a special rollback segment to support your import.  You can
>force the import to use this segment by taking all others offline.

Or just commit after each buffer fill on the import. We have tables
which are many 10's of Gigs in size, and we could not dedicate that
much rollback just for imports.

Quote:

>Mark Powell  -- Oracle 7 Certified DBA
>- The only advice that counts is the advice that you follow so follow your own
>advice -

MotoX

(Oracle 7 and Oracle 8 Certified Pro.)